22.09.2017 21:31
jobgemws 4 3000 Источник

Предисловие


Часто возникает потребность контролировать рост всех таблиц и файлов всех баз данных.

В данной статье будет рассмотрен пример того, как можно автоматизировать сбор данных о росте таблиц и файлов баз данных.

Решение


1) Создадим представление о размерах всех таблиц для каждой БД (базы данных):

Код
USE [НАЗВАНИЕ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE view [inf].[vTableSize] as
with pagesizeKB as (
	SELECT low / 1024 as PageSizeKB
	FROM master.dbo.spt_values
	WHERE number = 1 AND type = 'E'
)
,f_size as (
	select p.[object_id], 
		   sum([total_pages]) as TotalPageSize,
		   sum([used_pages])  as UsedPageSize,
		   sum([data_pages])  as DataPageSize
	from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
	left join sys.internal_tables it on p.object_id = it.object_id
	WHERE OBJECTPROPERTY(p.[object_id], N'IsUserTable') = 1
	group by p.[object_id]
)
,tbl as (
	SELECT
	  t.[schema_id],
	  t.[object_id],
	  i1.rowcnt as CountRows,
	  (COALESCE(SUM(i1.reserved), 0) + COALESCE(SUM(i2.reserved), 0)) * (select top(1) PageSizeKB from pagesizeKB) as ReservedKB,
	  (COALESCE(SUM(i1.dpages), 0) + COALESCE(SUM(i2.used), 0)) * (select top(1) PageSizeKB from pagesizeKB) as DataKB,
	  ((COALESCE(SUM(i1.used), 0) + COALESCE(SUM(i2.used), 0))
	    - (COALESCE(SUM(i1.dpages), 0) + COALESCE(SUM(i2.used), 0))) * (select top(1) PageSizeKB from pagesizeKB) as IndexSizeKB,
	  ((COALESCE(SUM(i1.reserved), 0) + COALESCE(SUM(i2.reserved), 0))
	    - (COALESCE(SUM(i1.used), 0) + COALESCE(SUM(i2.used), 0))) * (select top(1) PageSizeKB from pagesizeKB) as UnusedKB
	FROM sys.tables as t
	LEFT OUTER JOIN sysindexes as i1 ON i1.id = t.[object_id] AND i1.indid < 2
	LEFT OUTER JOIN sysindexes as i2 ON i2.id = t.[object_id] AND i2.indid = 255
	WHERE OBJECTPROPERTY(t.[object_id], N'IsUserTable') = 1
	OR (OBJECTPROPERTY(t.[object_id], N'IsView') = 1 AND OBJECTPROPERTY(t.[object_id], N'IsIndexed') = 1)
	GROUP BY t.[schema_id], t.[object_id], i1.rowcnt
)
SELECT
  @@Servername AS Server,
  DB_NAME() AS DBName,
  SCHEMA_NAME(t.[schema_id]) as SchemaName,
  OBJECT_NAME(t.[object_id]) as TableName,
  t.CountRows,
  t.ReservedKB,
  t.DataKB,
  t.IndexSizeKB,
  t.UnusedKB,
  f.TotalPageSize*(select top(1) PageSizeKB from pagesizeKB) as TotalPageSizeKB,
  f.UsedPageSize*(select top(1) PageSizeKB from pagesizeKB) as UsedPageSizeKB,
  f.DataPageSize*(select top(1) PageSizeKB from pagesizeKB) as DataPageSizeKB
FROM f_size as f
inner join tbl as t on t.[object_id]=f.[object_id]

GO


2) Создадим специальную БД и в ней определим таблицу для хранения информации по росту всех таблиц всех БД:

Код
USE [НАЗВАНИЕ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [srv].[TableStatistics](
	[Row_GUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_TableStatistics_Row_GUID]  DEFAULT (newid()),
	[ServerName] [nvarchar](255) NOT NULL,
	[DBName] [nvarchar](255) NOT NULL,
	[SchemaName] [nvarchar](255) NOT NULL,
	[TableName] [nvarchar](255) NOT NULL,
	[CountRows] [bigint] NOT NULL,
	[DataKB] [int] NOT NULL,
	[IndexSizeKB] [int] NOT NULL,
	[UnusedKB] [int] NOT NULL,
	[ReservedKB] [int] NOT NULL,
	[InsertUTCDate] [datetime] NOT NULL CONSTRAINT [DF_TableStatistics_InsertUTCDate]  DEFAULT (getutcdate()),
	[Date]  AS (CONVERT([date],[InsertUTCDate])) PERSISTED,
	[CountRowsBack] [bigint] NULL,
	[CountRowsNext] [bigint] NULL,
	[DataKBBack] [int] NULL,
	[DataKBNext] [int] NULL,
	[IndexSizeKBBack] [int] NULL,
	[IndexSizeKBNext] [int] NULL,
	[UnusedKBBack] [int] NULL,
	[UnusedKBNext] [int] NULL,
	[ReservedKBBack] [int] NULL,
	[ReservedKBNext] [int] NULL,
	[AvgCountRows]  AS ((([CountRowsBack]+[CountRows])+[CountRowsNext])/(3)) PERSISTED,
	[AvgDataKB]  AS ((([DataKBBack]+[DataKB])+[DataKBNext])/(3)) PERSISTED,
	[AvgIndexSizeKB]  AS ((([IndexSizeKBBack]+[IndexSizeKB])+[IndexSizeKBNext])/(3)) PERSISTED,
	[AvgUnusedKB]  AS ((([UnusedKBBack]+[UnusedKB])+[UnusedKBNext])/(3)) PERSISTED,
	[AvgReservedKB]  AS ((([ReservedKBBack]+[ReservedKB])+[ReservedKBNext])/(3)) PERSISTED,
	[DiffCountRows]  AS (([CountRowsNext]+[CountRowsBack])-(2)*[CountRows]) PERSISTED,
	[DiffDataKB]  AS (([DataKBNext]+[DataKBBack])-(2)*[DataKB]) PERSISTED,
	[DiffIndexSizeKB]  AS (([IndexSizeKBNext]+[IndexSizeKBBack])-(2)*[IndexSizeKB]) PERSISTED,
	[DiffUnusedKB]  AS (([UnusedKBNext]+[UnusedKBBack])-(2)*[UnusedKB]) PERSISTED,
	[DiffReservedKB]  AS (([ReservedKBNext]+[ReservedKBBack])-(2)*[ReservedKB]) PERSISTED,
	[TotalPageSizeKB] [int] NULL,
	[TotalPageSizeKBBack] [int] NULL,
	[TotalPageSizeKBNext] [int] NULL,
	[UsedPageSizeKB] [int] NULL,
	[UsedPageSizeKBBack] [int] NULL,
	[UsedPageSizeKBNext] [int] NULL,
	[DataPageSizeKB] [int] NULL,
	[DataPageSizeKBBack] [int] NULL,
	[DataPageSizeKBNext] [int] NULL,
	[AvgDataPageSizeKB]  AS ((([DataPageSizeKBBack]+[DataPageSizeKB])+[DataPageSizeKBNext])/(3)) PERSISTED,
	[AvgUsedPageSizeKB]  AS ((([UsedPageSizeKBBack]+[UsedPageSizeKB])+[UsedPageSizeKBNext])/(3)) PERSISTED,
	[AvgTotalPageSizeKB]  AS ((([TotalPageSizeKBBack]+[TotalPageSizeKB])+[TotalPageSizeKBNext])/(3)) PERSISTED,
	[DiffDataPageSizeKB]  AS (([DataPageSizeKBNext]+[DataPageSizeKBBack])-(2)*[DataPageSizeKB]) PERSISTED,--показывает как изменяется само приращение
	[DiffUsedPageSizeKB]  AS (([UsedPageSizeKBNext]+[UsedPageSizeKBBack])-(2)*[UsedPageSizeKB]) PERSISTED,--показывает как изменяется само приращение
	[DiffTotalPageSizeKB]  AS (([TotalPageSizeKBNext]+[TotalPageSizeKBBack])-(2)*[TotalPageSizeKB]) PERSISTED,--показывает как изменяется само приращение
 CONSTRAINT [PK_TableStatistics] PRIMARY KEY CLUSTERED 
(
	[Row_GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING ON
GO


За сам размер таблицы отвечает TotalPageSizeKB.

Сумма TotalPageSizeKB всех таблиц БД+размер системных таблиц=размеру данных БД.

3) Определим процедуру сбора информации:
Код
USE [НАЗВАНИЕ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [srv].[InsertTableStatistics]
AS
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	declare @dt date=CAST(GetUTCDate() as date);
    declare @dbs nvarchar(255);
	declare @sql nvarchar(max);

	select [name]
	into #dbs
	from sys.databases;

	while(exists(select top(1) 1 from #dbs))
	begin
		select top(1)
		@dbs=[name]
		from #dbs;

		set @sql=
		N'INSERT INTO [srv].[TableStatistics]
	         ([ServerName]
			   ,[DBName]
	         ,[SchemaName]
	         ,[TableName]
	         ,[CountRows]
	         ,[DataKB]
	         ,[IndexSizeKB]
	         ,[UnusedKB]
	         ,[ReservedKB]
			 ,[TotalPageSizeKB]
			 ,[UsedPageSizeKB]
			 ,[DataPageSizeKB])
	   SELECT [Server]
		  ,[DBName]
	         ,[SchemaName]
	         ,[TableName]
	         ,[CountRows]
	         ,[DataKB]
	         ,[IndexSizeKB]
	         ,[UnusedKB]
	         ,[ReservedKB]
			 ,[TotalPageSizeKB]
			 ,[UsedPageSizeKB]
			 ,[DataPageSizeKB]
		FROM ['+@dbs+'].[inf].[vTableSize];';

		exec sp_executesql @sql;

		delete from #dbs
		where [name]=@dbs;
	end

	drop table #dbs;

	declare @dt_back date=CAST(DateAdd(day,-1,@dt) as date);

	;with tbl1 as (
		select [Date], 
			   [CountRows],
			   [DataKB],
			   [IndexSizeKB],
			   [UnusedKB],
			   [ReservedKB],
			   [ServerName], 
			   [DBName], 
			   [SchemaName], 
			   [TableName],
			   [TotalPageSizeKB],
			   [UsedPageSizeKB],
			   [DataPageSizeKB]
		from [srv].[TableStatistics]
		where [Date]=@dt_back
	)
	, tbl2 as (
		select [Date], 
			   [CountRows], 
			   [CountRowsBack],
			   [DataKBBack],
			   [IndexSizeKBBack],
			   [UnusedKBBack],
			   [ReservedKBBack],
			   [ServerName], 
			   [DBName], 
			   [SchemaName], 
			   [TableName],
			   [TotalPageSizeKBBack],
			   [UsedPageSizeKBBack],
			   [DataPageSizeKBBack]
		from [srv].[TableStatistics]
		where [Date]=@dt
	)
	update t2
	set t2.[CountRowsBack]		=t1.[CountRows],
		t2.[DataKBBack]			=t1.[DataKB],
		t2.[IndexSizeKBBack]	=t1.[IndexSizeKB],
		t2.[UnusedKBBack]		=t1.[UnusedKB],
		t2.[ReservedKBBack]		=t1.[ReservedKB],
		t2.[TotalPageSizeKBBack]=t1.[TotalPageSizeKB],
		t2.[UsedPageSizeKBBack]	=t1.[UsedPageSizeKB],
		t2.[DataPageSizeKBBack]	=t1.[DataPageSizeKB]
	from tbl1 as t1
	inner join tbl2 as t2 on t1.[Date]=DateAdd(day,-1,t2.[Date])
	and t1.[ServerName]=t2.[ServerName]
	and t1.[DBName]=t2.[DBName]
	and t1.[SchemaName]=t2.[SchemaName]
	and t1.[TableName]=t2.[TableName];

	;with tbl1 as (
		select [Date], 
			   [CountRows], 
			   [CountRowsNext],
			   [DataKBNext],
			   [IndexSizeKBNext],
			   [UnusedKBNext],
			   [ReservedKBNext],
			   [ServerName], 
			   [DBName], 
			   [SchemaName], 
			   [TableName],
			   [TotalPageSizeKBNext],
			   [UsedPageSizeKBNext],
			   [DataPageSizeKBNext]
		from [srv].[TableStatistics]
		where [Date]=@dt_back
	)
	, tbl2 as (
		select [Date], 
			   [CountRows],
			   [DataKB],
			   [IndexSizeKB],
			   [UnusedKB],
			   [ReservedKB],
			   [ServerName], 
			   [DBName], 
			   [SchemaName], 
			   [TableName],
			   [TotalPageSizeKB],
			   [UsedPageSizeKB],
			   [DataPageSizeKB]
		from [srv].[TableStatistics]
		where [Date]=@dt
	)
	update t1
	set t1.[CountRowsNext]		=t2.[CountRows],
		t1.[DataKBNext]			=t2.[DataKB],
		t1.[IndexSizeKBNext]	=t2.[IndexSizeKB],
		t1.[UnusedKBNext]		=t2.[UnusedKB],
		t1.[ReservedKBNext]		=t2.[ReservedKB],
		t1.[TotalPageSizeKBNext]=t2.[TotalPageSizeKB],
		t1.[UsedPageSizeKBNext]	=t2.[UsedPageSizeKB],
		t1.[DataPageSizeKBNext]	=t2.[DataPageSizeKB]
	from tbl1 as t1
	inner join tbl2 as t2 on t1.[Date]=DateAdd(day,-1,t2.[Date])
	and t1.[ServerName]=t2.[ServerName]
	and t1.[DBName]=t2.[DBName]
	and t1.[SchemaName]=t2.[SchemaName]
	and t1.[TableName]=t2.[TableName];
END
GO


Данное решение можно модифицировать с целью собирать со всех нужных экземпляров MS SQL Server данные по размерам таблиц всех БД.

4) Определим представление по собранной информации:

Код
USE [НАЗВАНИЕ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [srv].[vTableStatisticsShort] as 
with d as (select DateAdd(day,-1,max([Date])) as [Date] from [srv].[TableStatistics])
SELECT t.[ServerName]
      ,t.[DBName]
      ,t.[SchemaName]
      ,t.[TableName]
      ,t.[CountRows]
      ,t.[DataKB]
      ,t.[IndexSizeKB]
      ,t.[UnusedKB]
      ,t.[ReservedKB]
      ,t.[InsertUTCDate]
      ,t.[Date]
      ,t.[CountRowsBack]
      ,t.[CountRowsNext]
      ,t.[DataKBBack]
      ,t.[DataKBNext]
      ,t.[IndexSizeKBBack]
      ,t.[IndexSizeKBNext]
      ,t.[UnusedKBBack]
      ,t.[UnusedKBNext]
      ,t.[ReservedKBBack]
      ,t.[ReservedKBNext]
      ,t.[AvgCountRows]
      ,t.[AvgDataKB]
      ,t.[AvgIndexSizeKB]
      ,t.[AvgUnusedKB]
      ,t.[AvgReservedKB]
      ,t.[DiffCountRows]
      ,t.[DiffDataKB]
      ,t.[DiffIndexSizeKB]
      ,t.[DiffUnusedKB]
      ,t.[DiffReservedKB]
      ,t.[TotalPageSizeKB]
      ,t.[TotalPageSizeKBBack]
      ,t.[TotalPageSizeKBNext]
      ,t.[UsedPageSizeKB]
      ,t.[UsedPageSizeKBBack]
      ,t.[UsedPageSizeKBNext]
      ,t.[DataPageSizeKB]
      ,t.[DataPageSizeKBBack]
      ,t.[DataPageSizeKBNext]
      ,t.[AvgDataPageSizeKB]
      ,t.[AvgUsedPageSizeKB]
      ,t.[AvgTotalPageSizeKB]
      ,t.[DiffDataPageSizeKB]
      ,t.[DiffUsedPageSizeKB]
      ,t.[DiffTotalPageSizeKB]
  FROM d
  inner join [SRV].[srv].[TableStatistics] as t on d.[Date]=t.[Date]
  where t.[CountRowsBack] is not null
	and	t.[CountRowsNext] is not null
GO


Здесь стоит обратить внимание на Diff. Если он больше 0, то это означает, что таблица с каждым днем все быстрее растет.
Предполагается, что сбор будет производиться 1 раз в 24 часа.
Аналогичным образом можно автоматизировать и сбор роста файлов всех БД, используя следующее представление:
Код
USE [НАЗВАНИЕ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

select	t2.[DB_Name] as [DBName]
			,t1.FileId 
			,t1.NumberReads
			,t1.BytesRead
			,t1.IoStallReadMS
			,t1.NumberWrites
			,t1.BytesWritten
			,t1.IoStallWriteMS 
			,t1.IoStallMS
			,t1.BytesOnDisk
			,t1.[TimeStamp]
			,t1.FileHandle
			,t2.[Type_desc]
			,t2.[FileName]
			,t2.[Drive]
			,t2.[Physical_Name]
			,t2.[Ext]
			,t2.[CountPage]
			,t2.[SizeMb]
			,t2.[SizeGb]
			,t2.[Growth]
			,t2.[GrowthMb]
			,t2.[GrowthGb]
			,t2.[GrowthPercent]
			,t2.[is_percent_growth]
			,t2.[database_id]
			,t2.[State]
			,t2.[StateDesc]
			,t2.[IsMediaReadOnly]
			,t2.[IsReadOnly]
			,t2.[IsSpace]
			,t2.[IsNameReserved]
			,t2.[CreateLsn]
			,t2.[DropLsn]
			,t2.[ReadOnlyLsn]
			,t2.[ReadWriteLsn]
			,t2.[DifferentialBaseLsn]
			,t2.[DifferentialBaseGuid]
			,t2.[DifferentialBaseTime]
			,t2.[RedoStartLsn]
			,t2.[RedoStartForkGuid]
			,t2.[RedoTargetLsn]
			,t2.[RedoTargetForkGuid]
			,t2.[BackupLsn]
from fn_virtualfilestats(NULL, NULL) as t1
inner join [inf].[ServerDBFileInfo] as t2 on t1.[DbId]=t2.[database_id] and t1.[FileId]=t2.[File_Id]
GO


Результат


В данной статье был рассмотрен пример автоматизации сбора информации по размерам и росту всех таблиц и файлов всех баз данных. Это дает полный контроль над изменением размеров как самих файлов БД, так и ее таблиц, а также вовремя принимать меры по уменьшению таблицы или файла или для увеличения носителя информации (или разбиение информации на несколько носителей).

Источники:


» MSDN

Комментарии (4)


  1. Pilat
    24.09.2017 11:07
    #10433850
    +1

    Вот бы ещё в название или хотя бы в начало статьи вынести указание что это конкретно под MS SQL.


    1. jobgemws Автор
      24.09.2017 11:36
      #10433890

      Так в метках же стоит MS SQL SERVER


      1. ckpunT
        24.09.2017 17:27
        #10434292

        В мобильной версии метки не видны и интрига сохраняется до второй половины статьи.


        1. jobgemws Автор
          24.09.2017 17:29
          #10434304

          Не знал об этом, точнее не замечал.
          Но в любом случае уверен, что подобный метод легко можно реализовать и в других СУБД