Всем привет Хабровчане!!
Одной из проблем хранилищ данных, которая часто возникает в процессе работы - это постоянное увеличение их размеров. А добавление все новых и новых источников данных только ускоряет заполнение места на дисках.
Да, конечно же настройка чистки самых больших таблиц и периода историцируемости позволят сократить неконтролируемое увеличение места. Но если речь идет о хранилищах, которые бодро наполняются и добавляются всё новые "большие" таблицы, и количество их увеличивается то вопрос места в DWH всегда становится ребром. И возникает вопрос "А куда же ушло место?", "Что можно почистить?" или "Как обосновать руководству расширение хранилища?" Системы мониторинга на подобие ZABBIX позволяют только верхнеуровнево отследить увеличение дискового пространства на полке но не дают возможности отследить рост самих объектов в базе.
Сегодня хочу поделится своим маленьким лайфхаком как легко можно поставить на мониторинг размеры таблиц на примере MS SQL для дальнейшего анализа и оптимизации базы. Это маленькое решение которое может помочь сэкономить кучу времени чтобы проанализировать "Куда же ушло все место в хранилище?". Данный принцип можно применить и на других базах (Oracle, PostgreSQL и т.д.) с той лишь разницей, что названия системных таблиц будут другие.
Ниже описан небольшой план и набор скриптов MS SQL чтобы автоматизировать мониторинг места:
Это будет регламентное задание , которое собирает статистику ежедневно.
1) На первом шаге создаем таблицу для хранения истории и счетчик. В этой таблице будет сохранятся ежедневная история статистики для каждой таблицы.
CREATE SEQUENCE prm.sq_etl_log_1 AS bigint START WITH 1 INCREMENT BY 1 CREATE TABLE prm.dwh_size_of_tables(ddate date NULL,--Дата на момент который смотрим статистику таблицыrun_id numeric(14, 0) NOT NULL,--ID Запуска сбора статистики, Счетчикdb_name varchar(20) NOT NULL,--База данныхschema_name sysname NOT NULL,--Схема таблицыtable_name sysname NOT NULL,--Название таблицыrow_count bigint NULL,--Количество строк в таблицеreserved_KB bigint NULL,--Ощий размер таблицы вместе с индесамиdata_KB bigint NULL,--Размер самих данных в таблице index_size_KB bigint NULL,--Размер индексовunused_KB bigint NULL--неиспрользованное место)
2) Далее необходимо создать процедуру которая будет ежедневно запускаться и собирать статистику по-таблично. Эту процедуру необходимо поставить на ежедневное задание для запуска. Она собирает срез размеров таблиц на текущий день.
Скрипт процедуры представлен ниже:
Скрипт процедуры
USE [LEMON]GOCREATE PROCEDURE [prm].[load_etl_log]ASdeclare @run_id intBEGIN--Если сегодня был запуск очищаем текущюую статистику и перезаливаемdelete from lemon.prm.dwh_size_of_tables where ddate = cast(getdate() as date);--Для страых периодов храним только статистику только на начало и на середину месяцаdelete from lemon.prm.dwh_size_of_tableswhere (DATEPART(day, ddate)not in (1,15) and ddate < dateadd(month ,-2, getdate())) DECLARE @SQL_text varchar(max),@SQL_text_final varchar(max); ; set @SQL_text= 'USE {SCHEMA_FOR_REPLACE};insert into lemon.prm.dwh_size_of_tablesSELECT cast(getdate() as date) date_time,'''+ convert(nvarchar , @run_id ) +''' run_id ,''{SCHEMA_FOR_REPLACE}'' db_name,a3.name AS schema_name,--Схемаa2.name AS table_name,--Имя таблицыa1.rows AS row_count,--Число записей(a1.reserved + ISNULL(a4.reserved, 0)) * 8 AS reserved_KB,--Зарезервировано (КБ)a1.data * 8 AS data_KB,--Данные (КБ)(CASE WHEN (a1.used + ISNULL(a4.used, 0)) > a1.dataTHEN (a1.used + ISNULL(a4.used, 0)) - a1.dataELSE 0END) * 8 AS index_size_KB,--Индексы (КБ)(CASE WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.usedTHEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.usedELSE 0END) * 8 AS unused_KB --Не используется (КБ)FROM (SELECT ps.object_id,SUM(CASE WHEN (ps.index_id < 2)THEN row_countELSE 0END) AS [rows],SUM(ps.reserved_page_count) AS reserved,SUM(CASE WHEN (ps.index_id < 2)THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)END) AS data,SUM(ps.used_page_count) AS usedFROM sys.dm_db_partition_stats psWHERE ps.object_id NOT IN (SELECT object_idFROM sys.tablesWHERE is_memory_optimized = 1)GROUP BY ps.object_id) AS a1LEFT OUTER JOIN (SELECT it.parent_id,SUM(ps.reserved_page_count) AS reserved,SUM(ps.used_page_count) AS usedFROM sys.dm_db_partition_stats psINNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)WHERE it.internal_type IN (202,204)GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)INNER JOIN sys.all_objects a2 ON (a1.object_id = a2.object_id)INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)WHERE a2.type <> N''S''AND a2.type <> N''IT''';DECLARE @request_id nvarchar(36), @schema_for_replace nvarchar(100)DECLARE bki_cursor CURSOR FOR SELECT name as schem FROM sys.databases--Здесь можно перечислить список баз по которым собираем статистику/* where name in ('DWH','DWH_copy','VN','VN_test') --and name ='DWH'*/OPEN bki_cursor FETCH NEXT FROM bki_cursor INTO @schema_for_replaceWHILE @@FETCH_STATUS = 0 BEGINset @SQL_text_final = replace (@sql_text,'{SCHEMA_FOR_REPLACE}',@schema_for_replace); execute (@SQL_text_final)FETCH NEXT FROM bki_cursor INTO @schema_for_replaceEND CLOSE bki_cursor; DEALLOCATE bki_cursor;END
3) Теперь по мере наполнения таблицы dwh_size_of_tables можно смотреть статистику по-таблично и по базам. Для просмотра можно воспользоваться вот таким удобным скриптом ниже.
Статистика места в DWH по таблицам
--Статистика места в DWH по таблицамselect top 10 ddate -- [Дата],run_id --,db_name --БД-,schema_name --Схема,table_name --Имя таблицы,row_count --Число записей,round(cast(reserved_KB as float) /1024/1024,2) as reserved_GB --Зарезервировано (КБ),round(cast(data_KB as float) /1024/1024,2) as data_GB --Данные (КБ),round(cast(index_size_KB as float) /1024/1024,2) as index_size_GB --Индексы (КБ),round(cast(unused_KB as float) /1024/1024,2) as unused_GB--Не используется (КБ) from lemon.prm.dwh_size_of_tableswhere ddate = cast(getdate() as date)-- and db_name='DWH' order by reserved_GB desc
--Статистика места в DWH по Базам select ddate -- [Дата],run_id --,db_name --БД-,round(cast(sum(reserved_KB) as float) /1024/1024,2) as reserved_GB --Зарезервировано (КБ),round(cast(sum(data_KB) as float) /1024/1024,2) as data_GB --Данные (КБ),round(cast(sum(index_size_KB) as float) /1024/1024,2) as index_size_GB --Индексы (КБ),round(cast(sum(unused_KB) as float) /1024/1024,2) as unused_GB--Не используется (КБ),sum(row_count) row_count--Число записей from lemon.prm.dwh_size_of_tableswhere ddate = cast(getdate() as date)-- and db_name='DWH' group by ddate,run_id,db_nameorder by ddate,run_id,sum(data_KB+index_size_KB) desc
4) Далее создаем еще 3 процедуры, которые позволят нам очень удобно просматривать историю по базам и по таблично. Эти процедуры используются не для сбора статистики а для показа этой статистики в красивом виде. Причем указав период за который хотим посмотреть статистику, она по-колоночно разбивает статистику.
Дневная статистика места по базам. Указываем период за который смотрим
USE [LEMON]GO/****** Object: StoredProcedure [prm].[dwh_daily_size_statistics] Script Date: 02.09.2020 18:35:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE procedure [prm].[dwh_daily_size_statistics] @sdate date, @edate dateASBEGIN--Собираем подневную статистикуdeclare @str nvarchar(4000)set @str= stuff ( ( select N','+ 'round(cast(sum(case when ddate = cast('''+ cast( ddate as nvarchar)+'''as date) thenreserved_KBend) as float) /1024/1024,0) ['+ cast( ddate as nvarchar)+']'+char(10)from ( select distinct ddate from lemon.prm.dwh_size_of_tableswhere ddate >=@sdate and ddate<@edate) t order by t.ddate for xml path('') ,type ).value('.','nvarchar(max)'), 1,0,'' )-- column_string--print @strexec (' select db_name --БД-'+@str+' from lemon.prm.dwh_size_of_tables--where ddate = cast(getdate() as date) group by db_name--order by db_name');end ;GO
USE [LEMON]GO/****** Object: StoredProcedure [prm].[dwh_monthly_size_statistics] Script Date: 02.09.2020 18:35:09 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE procedure [prm].[dwh_monthly_size_statistics] @sdate date, @edate dateASbegin --Собираем помесячую статистикуdeclare @str2 nvarchar(4000)set @str2= stuff ( ( select N','+ 'round(cast(sum(case when ddate = cast('''+ cast( ddate as nvarchar)+'''as date) thenreserved_KBend) as float) /1024/1024,0) ['+ CAST(year( ddate) as nvarchar) +'_'+ CAST(month( ddate) as nvarchar)--cast( ddate as nvarchar)+']'+char(10)from ( select distinct ddate from lemon.prm.dwh_size_of_tableswhere ddate >=@sdate and ddate<@edate and day(ddate)=1) t order by t.ddate for xml path('') ,type ).value('.','nvarchar(max)'), 1,0,'' )exec (' select db_name --БД---,table_name'+@str2+' from lemon.prm.dwh_size_of_tables--where ddate = cast(getdate() as date) group by db_name--,table_nameorder by db_name');end;GO
USE [LEMON]GO/****** Object: StoredProcedure [prm].[dwh_monthly_table_size_statistics] Script Date: 02.09.2020 18:36:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [prm].[dwh_monthly_table_size_statistics] @sdate date, @edate date ,@db_name nvarchar(100)ASbegin --Собираем помесячую статистикуdeclare @str2 nvarchar(4000)set @str2= stuff ( ( select N','+ 'round(cast(sum(case when ddate = cast('''+ cast( ddate as nvarchar)+'''as date) thenreserved_KBend) as float) /1024/1024,0) ['+ CAST(year( ddate) as nvarchar) +'_'+ CAST(month( ddate) as nvarchar)--cast( ddate as nvarchar)+']'+char(10)from ( select distinct ddate from lemon.prm.dwh_size_of_tableswhere ddate >=@sdate and ddate<@edate and day(ddate)=1 ) t order by t.ddate for xml path('') ,type ).value('.','nvarchar(max)'), 1,0,'' ) declare @ORDER_DATE NVARCHAR(100) SET @ORDER_DATE= convert(nvarchar, year( @edate) ) +'_'+ convert(nvarchar, month( @edate) ) SELECT @ORDER_DATE = convert(nvarchar, year( DDATE) ) +'_'+ convert(nvarchar, month( DDATE) ) FROM (select MAX( ddate ) DDATE from lemon.prm.dwh_size_of_tableswhere ddate >=@sdate and ddate<@edate and day(ddate)=1 ) tt ;declare @ddb_name nvarchar(100)set @ddb_name = case when @db_name is null then '' else ' and '+ 'db_name= '''+@db_name + '''' end exec (' select db_name --БД-,table_name'+@str2+' from lemon.prm.dwh_size_of_tableswhere 1=1 ' + @ddb_name + '-- ddate = cast(getdate() as date) group by db_name,table_name order by db_name,['+ @ORDER_DATE +'] desc');end;
5) В итоге у нас получились 3 процедуры которые позволяют :
A) Смотреть историю увеличения/уменьшения БД подневно
B) Смотреть историю увеличения/уменьшения БД помесячно
C) Смотреть историю увеличения/уменьшения таблиц помесячно. Очень удобно когда нужно отследить по конкретной таблице когда по ней пошел рост.
Да , конечно же есть различные варианты написания запроса (в том числе использовать PIVOT), но эти процедуры удобны тем, что однажды написав его, больше не нужно каждый раз тратить время на написание нового запроса. Достаточно просто вызвать его передав, как параметр, нужный период истории.
--Дневная статистика места по базам указываем период за который смотримexec LEMON.prm.dwh_daily_size_statistics @sdate ='2020-08-01', @edate ='2020-09-01'--Месячная статистика места по базам указываем период за который смотримexec LEMON.prm.dwh_monthly_size_statistics @sdate ='2020-03-01', @edate ='2020-09-01'--Месячная статистика места по каждой таблицеexec LEMON.prm.dwh_monthly_table_size_statistics @sdate ='2020-02-01', @edate ='2020-08-01', @db_name ='DWH'--если указываем null то показывает все таблицы по всем базам
Как видно на картинке выше по ней очень удобно просмотреть какая база начала резко увеличиваться в размерах. Более того этими тремя процедурами очень быстро можно найти , например, таблицу или базу которая начала в какой-то момент сильно расти. Особенно удобно когда в хранилище уже созданы тысячи объектов, и ручной поиск уже не применим.
Вывод: Настроив небольшой такой функционал по мониторингу места можно очень сильно упростить жизнь в будущем, в части касающейся роста базы и поиска объектов в хранилище, которые сильно выросли. Более того, это поможет определить по каким проектам или системам наблюдается рост размера хранилища и легко обосновать руководству, например, необходимость дополнительного места или настроить чистку таблиц, по которым наблюдается быстрый рост.
На этом я пожалуй закругляюсь и надеюсь что эта статья будет полезна кому-нибудь. Оставляйте свои комментарии у кого есть другие способы по анализу места в хранилищах. Буду рад любым отзывам.
P.S. Все скрипты выложены на GitHub по ссылке ниже:
https://github.com/michailo87/MSSQL
До скорых встреч !!
