3 октября

Мониторинг места в хранилищах

SQLMicrosoft SQL ServerBig DataХранение данныхХранилища данных

Всем привет, Хабровчане!

Одна из проблем хранилищ данных, которая часто возникает в процессе работы - это постоянное увеличение их размеров. А добавление все новых и новых источников данных только ускоряет заполнение места на дисках.

Да, конечно же настройка чистки самых больших таблиц и периода историцируемости позволят сократить неконтролируемое увеличение места. Но если речь идет о хранилищах, которые бодро наполняются и добавляются всё новые "большие" таблицы и количество их увеличивается, то вопрос места в 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]
GO

CREATE  PROCEDURE  [prm].[load_etl_log]
AS
declare 
@run_id int
BEGIN

--Если сегодня был запуск очищаем текущюую статистику и перезаливаем
delete from lemon.prm.dwh_size_of_tables where ddate = cast(getdate() as date);

--Для страых периодов  храним только статистику только на начало и на середину месяца
delete from  lemon.prm.dwh_size_of_tables
where (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_tables
SELECT 

	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.data
				THEN (a1.used + ISNULL(a4.used, 0)) - a1.data
			ELSE 0
			END
		) * 8 AS index_size_KB
	,--Индексы (КБ)
	(
		CASE 
			WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
				THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
			ELSE 0
			END
		) * 8 AS unused_KB --Не используется (КБ)
		
	FROM (
				SELECT ps.object_id
					,SUM(CASE 
							WHEN (ps.index_id < 2)
								THEN row_count
							ELSE 0
							END) 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 used
				FROM sys.dm_db_partition_stats ps
				WHERE ps.object_id NOT IN (
						SELECT object_id
						FROM sys.tables
						WHERE is_memory_optimized = 1
						)
				GROUP BY ps.object_id
		) AS a1
	LEFT OUTER JOIN (
				SELECT it.parent_id
					,SUM(ps.reserved_page_count) AS reserved
					,SUM(ps.used_page_count) AS used
				FROM sys.dm_db_partition_stats ps
				INNER 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_replace

			WHILE @@FETCH_STATUS = 0  
			BEGIN
				set @SQL_text_final = replace (@sql_text,'{SCHEMA_FOR_REPLACE}',@schema_for_replace);  
				execute (@SQL_text_final)

			FETCH NEXT FROM bki_cursor INTO @schema_for_replace
			END   
	
		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_tables
where ddate = cast(getdate() as date)-- and  db_name='DWH'
 order by reserved_GB desc

Статистика места в DWH по базам
--Статистика места  в 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_tables
where ddate = cast(getdate() as date)-- and  db_name='DWH'
 group by   ddate,run_id,db_name
order  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 ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  procedure [prm].[dwh_daily_size_statistics]   @sdate date, @edate date
AS
BEGIN

	--Собираем подневную статистику
		declare   @str nvarchar(4000)		
		set @str=
				 stuff
				 (
				  (	 
					select  N','+ 'round(cast(sum(case when ddate =  cast('''+ cast( ddate as nvarchar)+'''as date)  then	reserved_KB	end) as float) /1024/1024,0)  ['+ cast( ddate as nvarchar)+']'+char(10)
					from ( 
						select distinct ddate from  lemon.prm.dwh_size_of_tables
						where ddate >=@sdate and  ddate<@edate
						) t
					 order by t.ddate
				   for xml path('')
				  ,type
				  ).value('.','nvarchar(max)'),
				  1,0,''
				 )-- column_string

		--print @str


		exec ('

		 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 ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE procedure [prm].[dwh_monthly_size_statistics]   @sdate date, @edate date
AS
begin 
--Собираем помесячую статистику
declare   @str2 nvarchar(4000)		
set @str2=
		 stuff
		 (
		  (	 
			select  N','+ 'round(cast(sum(case when ddate =  cast('''+ cast( ddate as nvarchar)+'''as date)  then	reserved_KB	end) 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_tables
				where 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_name
order  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 ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [prm].[dwh_monthly_table_size_statistics]   @sdate date, @edate date ,@db_name nvarchar(100)
AS
begin 
--Собираем помесячую статистику
declare   @str2 nvarchar(4000)		
set @str2=
		 stuff
		 (
		  (	 
			select  N','+ 'round(cast(sum(case when ddate =  cast('''+ cast( ddate as nvarchar)+'''as date)  then	reserved_KB	end) 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_tables
				where 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_tables
				where 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_tables
where 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 по ссылке.

До скорых встреч !!

Теги:dwhetletl-процессыedwхранилищехранение данныхbigdataoracleхранилища данныхoralce
Хабы: SQL Microsoft SQL Server Big Data Хранение данных Хранилища данных
+4
3,1k 35
Комментарии 7
Похожие публикации
Курс по аналитике данных
30 ноября 202053 500 ₽SkillFactory
Data Engineer
30 ноября 202080 000 ₽OTUS
BIG DATA с нуля
22 декабря 202019 700 ₽Нетология
MS SQL Server Developer
10 марта 202135 000 ₽OTUS
Лучшие публикации за сутки