Technical Article

The Automated DBA: Space Usage Snapshotter Lite (non-sysadmin dbo)

,

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_BuildSpaceLogLite
By Jesse Roberge - YeshuaAgapao@Yahoo.com

The SpaceLog builder
Records database space usage and buffer cache usage statistics into the SpaceLog_* tables.
Keeps a record of database and table growth for reporting of growth trends.
Stats include Reserved, Used, Data, and buffer cache page counts for row, lob (VarChar-Max, text etc), and overflow data (data rows with varchar columns going over 8000 bytes).
Stats also include some computed columns such as BTree (Used-Data), unused (Reserved-Used), and sums of row+lob+overflow for each of reserved, used, data, and buffer.
The database, dataspace, schema, and table levels give stats for the nonclustered index and the table itself (heap or clustered index).
The lite version does not require VIEW_SERVER_STATE permission and can be used in environments where the user has only dbo access. The columns for buffer cache stats will be all zeroes.
It does require the VIEW_DATABASE_STATE permission, which a user in the db_owner database role automatically has.

Required Input Parameters
None

Optional Input Parameters
@UpdateUsage tinyint=0 Default and recommended to be off. Use only if you must have the most accurate and up to date numbers. Will run DBCC UpdateUsage to scan every table in the database to re-count all allocations and rows, which can hog disk IO for serveral hours.

Usage
EXECUTE Admin.Util_BuildSpaceLogLite @UpdateUsage=0

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

--Create Admin schema if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='Admin') EXECUTE ('CREATE SCHEMA Admin')

IF OBJECT_ID('Admin.Util_BuildSpaceLogLite', 'P') IS NOT NULL DROP PROCEDURE Admin.Util_BuildSpaceLogLite
GO

/*
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_BuildSpaceLogLite
By Jesse Roberge - YeshuaAgapao@Yahoo.com

The SpaceLog builder
Records database space usage and buffer cache usage statistics into the SpaceLog_* tables.
Keeps a record of database and table growth for reporting of growth trends.
Stats include Reserved, Used, Data, and buffer cache page counts for row, lob (VarChar-Max, text etc), and overflow data (data rows with varchar columns going over 8000 bytes).
Stats also include some computed columns such as BTree (Used-Data), unused (Reserved-Used), and sums of row+lob+overflow for each of reserved, used, data, and buffer.
The database, dataspace, schema, and table levels give stats for the nonclustered index and the table itself (heap or clustered index).
The lite version does not require VIEW_SERVER_STATE permission and can be used in environments where the user has only dbo access.  The columns for buffer cache stats will be all zeroes.
It does require the VIEW_DATABASE_STATE permission, which a user in the db_owner database role automatically has.

Required Input Parameters
	None

Optional Input Parameters
	@UpdateUsage tinyint=0			Default and recommended to be off.  Use only if you must have the most accurate and up to date numbers.  Will run DBCC UpdateUsage to scan every table in the database to re-count all allocations and rows, which can hog disk IO for serveral hours.

Usage
	EXECUTE Admin.Util_BuildSpaceLogLite @UpdateUsage=0

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
*/

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

IF OBJECT_ID('Admin.SpaceLog_Database', 'U') IS NULL
BEGIN
	CREATE TABLE Admin.SpaceLog_Database
	(
		Date DateTime NOT NULL,
		DatabaseID int NOT NULL,
		DatabaseName sysname NOT NULL,
		Rows BigInt NOT NULL,
		RowReserved BigInt NOT NULL,
		RowUsed BigInt NOT NULL,
		RowData BigInt NOT NULL,
		RowBTree AS RowUsed-RowData,
		RowUnused AS RowReserved-RowUsed,
		RowBuffer BigInt NOT NULL,
		IndexReserved BigInt NOT NULL,
		IndexUsed BigInt NOT NULL,
		IndexData BigInt NOT NULL,
		IndexBuffer BigInt NOT NULL,
		IndexBTree AS IndexUsed-IndexData,
		IndexUnused AS IndexReserved-IndexUsed,
		LOBReserved BigInt NOT NULL,
		LOBUsed BigInt NOT NULL,
		LOBUnused AS LOBReserved-LOBUsed,
		LOBBuffer BigInt NOT NULL,
		OverflowReserved BigInt NOT NULL,
		OverflowUsed BigInt NOT NULL,
		OverflowUnused AS OverflowReserved-OverflowUsed,
		OverflowBuffer BigInt NOT NULL,
		TotalReserved AS RowReserved+LOBReserved+OverflowReserved,
		TotalUsed AS RowUsed+LOBUsed+OverflowUsed,
		TotalBuffer AS RowBuffer+LOBBuffer+OverflowBuffer,
		TotalUnused AS (RowReserved+LOBReserved+OverflowReserved+IndexReserved)-(RowUsed+LOBUsed+OverflowUsed+IndexUsed),
		CONSTRAINT PK_C_IX__Admin__SpaceLog_DataBase__Date_DatabaseID PRIMARY KEY NONCLUSTERED (Date, DatabaseID) WITH FILLFACTOR=90,
		CONSTRAINT U_UX__Admin__SpaceLog_Database__Date_DatabaseName UNIQUE CLUSTERED (Date, DatabaseName) WITH FillFactor=90
	)
END

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

IF OBJECT_ID('Admin.SpaceLog_DataSpace', 'U') IS NULL
BEGIN
	CREATE TABLE Admin.SpaceLog_DataSpace
	(
		Date DateTime NOT NULL,
		DatabaseID int NOT NULL,
		DatabaseName sysname NOT NULL,
		DataSpaceID int NOT NULL,
		DataSpaceName sysname NOT NULL,
		DataSpaceType Char(2) NOT NULL,
		DataSpaceTypeDesc AS CASE DataSpaceType WHEN 'FG' THEN 'Filegroup' WHEN 'PS' THEN 'Partition Scheme' WHEN 'FD' THEN 'FILESTREAM data filegroup' ELSE '' END,
		Rows BigInt NOT NULL,
		RowReserved BigInt NOT NULL,
		RowUsed BigInt NOT NULL,
		RowData BigInt NOT NULL,
		RowBTree AS RowUsed-RowData,
		RowUnused AS RowReserved-RowUsed,
		RowBuffer BigInt NOT NULL,
		IndexReserved BigInt NOT NULL,
		IndexUsed BigInt NOT NULL,
		IndexData BigInt NOT NULL,
		IndexBTree AS IndexUsed-IndexData,
		IndexUnused AS IndexReserved-IndexUsed,
		IndexBuffer BigInt NOT NULL,
		LOBReserved BigInt NOT NULL,
		LOBUsed BigInt NOT NULL,
		LOBBuffer BigInt NOT NULL,
		LOBUnused AS LOBReserved-LOBUsed,
		OverflowReserved BigInt NOT NULL,
		OverflowUsed BigInt NOT NULL,
		OverflowUnused AS OverflowReserved-OverflowUsed,
		OverflowBuffer BigInt NOT NULL,
		TotalReserved AS RowReserved+LOBReserved+OverflowReserved+IndexReserved,
		TotalUsed AS RowUsed+LOBUsed+OverflowUsed+IndexUsed,
		TotalBuffer AS RowBuffer+LOBBuffer+OverflowBuffer+IndexBuffer,
		TotalUnused AS (RowReserved+LOBReserved+OverflowReserved+IndexReserved)-(RowUsed+LOBUsed+OverflowUsed+IndexUsed),
		CONSTRAINT PK_C_IX__Admin__SpaceLog_DataSpace__Date_DatabaseID_DataSpaceID PRIMARY KEY NONCLUSTERED (Date, DatabaseID, DataSpaceID) WITH FILLFACTOR=90,
		CONSTRAINT U_UX__Admin__SpaceLog_DataSpace__Date_DatabaseName_DataSpaceName UNIQUE CLUSTERED (Date, DatabaseName, DataSpaceName) WITH FillFactor=90
	)
END

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

IF OBJECT_ID('Admin.SpaceLog_Schema', 'U') IS NULL
BEGIN
	CREATE TABLE Admin.SpaceLog_Schema
	(
		Date DateTime NOT NULL,
		DatabaseID int NOT NULL,
		DatabaseName sysname NOT NULL,
		SchemaID int NOT NULL,
		SchemaName sysname NOT NULL,
		Rows BigInt NOT NULL,
		RowReserved BigInt NOT NULL,
		RowUsed BigInt NOT NULL,
		RowData BigInt NOT NULL,
		RowBTree AS RowUsed-RowData,
		RowUnused AS RowReserved-RowUsed,
		RowBuffer BigInt NOT NULL,
		IndexReserved BigInt NOT NULL,
		IndexUsed BigInt NOT NULL,
		IndexData BigInt NOT NULL,
		IndexBTree AS IndexUsed-IndexData,
		IndexUnused AS IndexReserved-IndexUsed,
		IndexBuffer BigInt NOT NULL,
		LOBReserved BigInt NOT NULL,
		LOBUsed BigInt NOT NULL,
		LOBUnused AS LOBReserved-LOBUsed,
		LOBBuffer BigInt NOT NULL,
		OverflowReserved BigInt NOT NULL,
		OverflowUsed BigInt NOT NULL,
		OverflowUnused AS OverflowReserved-OverflowUsed,
		OverflowBuffer BigInt NOT NULL,
		TotalReserved AS RowReserved+LOBReserved+OverflowReserved+IndexReserved,
		TotalUsed AS RowUsed+LOBUsed+OverflowUsed+IndexUsed,
		TotalBuffer AS RowBuffer+LOBBuffer+OverflowBuffer+IndexBuffer,
		TotalUnused AS (RowReserved+LOBReserved+OverflowReserved+IndexReserved)-(RowUsed+LOBUsed+OverflowUsed+IndexUsed),
		CONSTRAINT PK_C_IX__Admin__SpaceLog_Scehma__Date_DatabaseID_SchemaID PRIMARY KEY NONCLUSTERED (Date, DatabaseID, SchemaID) WITH FILLFACTOR=90,
		CONSTRAINT U_UX__Admin__SpaceLog_Scehma__Date_DatabaseName_SchemaName UNIQUE CLUSTERED (Date, DatabaseName, SchemaName) WITH FillFactor=90
	)
END

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

IF OBJECT_ID('Admin.SpaceLog_Table', 'U') IS NULL
BEGIN
	CREATE TABLE Admin.SpaceLog_Table
	(
		Date DateTime NOT NULL,
		DatabaseID int NOT NULL,
		DatabaseName sysname NOT NULL,
		SchemaID int NOT NULL,
		SchemaName sysname NOT NULL,
		TableID int NOT NULL,
		TableName sysname NOT NULL,
		TableDataSpaceID int NOT NULL,
		TableDataSpaceName sysname NOT NULL,
		TableDataSpaceType Char(2) NOT NULL,
		TableDataSpaceTypeDesc AS CASE TableDataSpaceType WHEN 'FG' THEN 'Filegroup' WHEN 'PS' THEN 'Partition Scheme' WHEN 'FD' THEN 'FILESTREAM data filegroup' ELSE '' END,
		LOBDataSpaceID int NOT NULL,
		LOBDataSpaceName sysname NOT NULL,
		LOBDataSpaceType Char(2) NOT NULL,
		LOBDataSpaceTypeDesc AS CASE LOBDataSpaceType WHEN 'FG' THEN 'Filegroup' WHEN 'PS' THEN 'Partition Scheme' WHEN 'FD' THEN 'FILESTREAM data filegroup' ELSE '' END,
		Rows BigInt NOT NULL,
		RowReserved BigInt NOT NULL,
		RowUsed BigInt NOT NULL,
		RowData BigInt NOT NULL,
		RowBTree AS RowUsed-RowData,
		RowUnused AS RowReserved-RowUsed,
		RowBuffer BigInt NOT NULL,
		IndexReserved BigInt NOT NULL,
		IndexUsed BigInt NOT NULL,
		IndexData BigInt NOT NULL,
		IndexBTree AS IndexUsed-IndexData,
		IndexUnused AS IndexReserved-IndexUsed,
		IndexBuffer BigInt NOT NULL,
		LOBReserved BigInt NOT NULL,
		LOBUsed BigInt NOT NULL,
		LOBUnused AS LOBReserved-LOBUsed,
		LOBBuffer BigInt NOT NULL,
		OverflowReserved BigInt NOT NULL,
		OverflowUsed BigInt NOT NULL,
		OverflowUnused AS OverflowReserved-OverflowUsed,
		OverflowBuffer BigInt NOT NULL,
		TotalReserved AS RowReserved+LOBReserved+OverflowReserved+IndexReserved,
		TotalUsed AS RowUsed+LOBUsed+OverflowUsed+IndexUsed,
		TotalBuffer AS RowBuffer+LOBBuffer+OverflowBuffer+IndexBuffer,
		TotalUnused AS (RowReserved+LOBReserved+OverflowReserved+IndexReserved)-(RowUsed+LOBUsed+OverflowUsed+IndexUsed),
		CONSTRAINT PK_C_IX__Admin__SpaceLog_Table__Date_DatabaseID_TableID PRIMARY KEY NONCLUSTERED (Date, DatabaseID, TableID) WITH FILLFACTOR=90,
		CONSTRAINT U_UX__Admin__SpaceLog_Table__Date_DatabaseName_TableName_SchemaName UNIQUE CLUSTERED (Date, DatabaseName, TableName, SchemaName) WITH FillFactor=90
	)
END

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

IF OBJECT_ID('Admin.SpaceLog_Index', 'U') IS NULL
BEGIN
	CREATE TABLE Admin.SpaceLog_Index
	(
		Date DateTime NOT NULL,
		DatabaseID int NOT NULL,
		DatabaseName sysname NOT NULL,
		SchemaID int NOT NULL,
		SchemaName sysname NOT NULL,
		TableID int NOT NULL,
		TableName sysname NOT NULL,
		IndexID int NOT NULL,
		IndexName sysname NOT NULL,
		IndexDataSpaceID int NOT NULL,
		IndexDataSpaceName sysname NOT NULL,
		IndexDataSpaceType Char(2) NOT NULL,
		IndexDataSpaceTypeDesc AS CASE IndexDataSpaceType WHEN 'FG' THEN 'Filegroup' WHEN 'PS' THEN 'Partition Scheme' WHEN 'FD' THEN 'FILESTREAM data filegroup' ELSE '' END,
		PrimaryKey bit NOT NULL,
		UniqueConstraint bit NOT NULL,
		UniqueIndex bit NOT NULL,
		IgnoreDuplicateKey bit NOT NULL,
		IndexFillFactor TinyInt NOT NULL,
		Rows BigInt NOT NULL,
		RowReserved BigInt NOT NULL,
		RowUsed BigInt NOT NULL,
		RowData BigInt NOT NULL,
		RowBTree AS RowUsed-RowData,
		RowUnused AS RowReserved-RowUsed,
		RowBuffer BigInt NOT NULL,
		LOBReserved BigInt NOT NULL,
		LOBUsed BigInt NOT NULL,
		LOBUnused AS LOBReserved-LOBUsed,
		LOBBuffer BigInt NOT NULL,
		OverflowReserved BigInt NOT NULL,
		OverflowUsed BigInt NOT NULL,
		OverflowUnused AS OverflowReserved-OverflowUsed,
		OverflowBuffer BigInt NOT NULL,
		TotalReserved AS RowReserved+LOBReserved+OverflowReserved,
		TotalUsed AS RowUsed+LOBUsed+OverflowUsed,
		TotalBuffer AS RowBuffer+LOBBuffer+OverflowBuffer,
		TotalUnused AS (RowReserved+LOBReserved+OverflowReserved)-(RowUsed+LOBUsed+OverflowUsed),
		CONSTRAINT PK_C_IX__Admin__SpaceLog_Index__Date_DatabaseID_TableID_IndexID PRIMARY KEY NONCLUSTERED (Date, DatabaseID, TableID, IndexID) WITH FILLFACTOR=90,
		CONSTRAINT U_UX__Admin__SpaceLog_Index__Date_DatabaseName_TableName_IndexName_SchemaName UNIQUE CLUSTERED (Date, DatabaseName, TableName, IndexName, SchemaName) WITH FillFactor=90
	)
END

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

IF OBJECT_ID('Admin.SpaceLog_Partition', 'U') IS NULL
BEGIN
	CREATE TABLE Admin.SpaceLog_Partition
	(
		Date DateTime NOT NULL,
		DatabaseID int NOT NULL,
		DatabaseName sysname NOT NULL,
		SchemaID int NOT NULL,
		SchemaName sysname NOT NULL,
		TableID int NOT NULL,
		TableName sysname NOT NULL,
		IndexID int NOT NULL,
		IndexName sysname NOT NULL,
		PartitionID BigInt NOT NULL,
		HobtID BigInt NOT NULL,
		PartitionNumber int NOT NULL,
		Rows BigInt NOT NULL,
		RowReserved BigInt NOT NULL,
		RowUsed BigInt NOT NULL,
		RowData BigInt NOT NULL,
		RowBTree AS RowUsed-RowData,
		RowUnused AS RowReserved-RowUsed,
		RowBuffer BigInt NOT NULL,
		RowDataSpaceID int NOT NULL,
		RowDataSpaceName sysname NOT NULL,
		LOBReserved BigInt NOT NULL,
		LOBUsed BigInt NOT NULL,
		LOBUnused AS LOBReserved-LOBUsed,
		LOBBuffer BigInt NOT NULL,
		LOBDataSpaceID int NOT NULL,
		LOBDataSpaceName sysname NOT NULL,
		OverflowReserved BigInt NOT NULL,
		OverflowUsed BigInt NOT NULL,
		OverflowUnused AS OverflowReserved-OverflowUsed,
		OverflowBuffer BigInt NOT NULL,
		OverflowDataSpaceID int NOT NULL,
		OverflowDataSpaceName sysname NOT NULL,
		TotalReserved AS RowReserved+LOBReserved+OverflowReserved,
		TotalUsed AS RowUsed+LOBUsed+OverflowUsed,
		TotalBuffer AS RowBuffer+LOBBuffer+OverflowBuffer,
		TotalUnused AS (RowReserved+LOBReserved+OverflowReserved)-(RowUsed+LOBUsed+OverflowUsed),
		CONSTRAINT PK_C_IX__SpaceLog_SpaceLog_Partition__Date_DatabaseID_TableID_IndexID_PartitionID PRIMARY KEY NONCLUSTERED (Date, DatabaseID, TableID, IndexID, PartitionID) WITH FILLFACTOR=90,
		CONSTRAINT U_IX__Admin__SpaceLog_SpaceLog_Partition__Date_DatabaseID_PartitionID UNIQUE NONCLUSTERED (Date, DatabaseID, PartitionID) WITH FILLFACTOR=90,
		CONSTRAINT U_IX__Admin__SpaceLog_SpaceLog_Partition__Date_DatabaseName_TableName_IndexName_PartitionID_SchemaName UNIQUE CLUSTERED (Date, DatabaseName, TableName, IndexName, PartitionID, SchemaName) WITH FILLFACTOR=90
	)
END
GO

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

CREATE PROCEDURE Admin.Util_BuildSpaceLogLite
	@UpdateUsage tinyint=0
AS

DECLARE @Date DateTime
DECLARE @DatabaseID int

--SET @Date=dbo.fn_DateRound_Hour(GetDate())
SELECT @DatabaseID=DB_ID(), @Date=GetDate()

IF @UpdateUsage=1 DBCC UpdateUsage(0) WITH NO_INFOMSGS, COUNT_ROWS

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

--Partition
INSERT INTO Admin.SpaceLog_Partition
	(
		Date, DatabaseID, DatabaseName,
		SchemaID, SchemaName, TableID, TableName,
		IndexID, IndexName, PartitionID, HobtID,
		PartitionNumber, Rows,
		RowReserved, RowUsed,
		RowData, RowBuffer,
		RowDataSpaceID, RowDataSpaceName,
		LOBReserved, LOBUsed,
		LOBBuffer,
		LOBDataSpaceID, LOBDataSpaceName,
		OverflowReserved, OverflowUsed,
		OverflowBuffer,
		OverflowDataSpaceID, OverflowDataSpaceName
	)
SELECT
	@Date AS Date, @DatabaseID AS DatabaseID, databases.name AS DatabaseName,
	schemas.schema_id, schemas.name AS SchemaName, dm_db_partition_stats.object_id AS TableID, tables.name AS TableName,
	dm_db_partition_stats.index_id AS IndexID, ISNULL(indexes.name,'[HEAP]') AS IndexID, partitions.partition_id AS PartitionID, partitions.hobt_id AS HOBTID,
	dm_db_partition_stats.partition_number AS PartitionNumber, dm_db_partition_stats.row_count AS Rows,
	CONVERT(bigint, in_row_reserved_page_count)*8 AS RowReserved, CONVERT(bigint, in_row_used_page_count)*8 AS RowUsed,
	CONVERT(bigint, in_row_data_page_count)*8 AS RowData, CONVERT(bigint, ISNULL(in_row_buffer_pages,0))*8 AS RowBuffer,
	ISNULL(in_row_data_space_id,0) AS RowDataSpaceID, in_row_data_space_name AS RowDataSpaceName,
	CONVERT(bigint, lob_reserved_page_count)*8 AS LOBReserved, CONVERT(bigint, lob_used_page_count)*8 AS LOBUsed,
	CONVERT(bigint, ISNULL(lob_buffer_pages,0))*8 AS LOBBuffer,
	ISNULL(partitions.lob_data_space_id,0) AS LOBDataSpaceID, lob_data_space_name AS LOBDataSpaceName,
	CONVERT(bigint, row_overflow_reserved_page_count)*8 AS OverflowReserved, CONVERT(bigint, row_overflow_used_page_count)*8 AS OverflowUsed,
	CONVERT(bigint, ISNULL(row_overflow_buffer_pages,0))*8 AS OverflowBuffer,
	ISNULL(row_overflow_data_space_id,0) AS OverflowDataSpaceID, row_overflow_data_space_name AS OverflowDataSpaceName
FROM
	(
		SELECT
			partitions.partition_id, MAX(partitions.partition_number) AS partition_number, MAX(partitions.hobt_id) AS hobt_id,
			MAX(partitions.object_id) AS object_id, MAX(partitions.index_id) AS index_id,
			0 AS in_row_buffer_pages,
			0 AS lob_buffer_pages,
			0 AS row_overflow_buffer_pages,
			MAX(CASE WHEN allocation_units.type=1 THEN ISNULL(data_spaces.data_space_id,0) ELSE 0 END) AS in_row_data_space_id,
			MAX(CASE WHEN allocation_units.type=2 THEN ISNULL(data_spaces.data_space_id,0) ELSE 0 END) AS lob_data_space_id,
			MAX(CASE WHEN allocation_units.type=3 THEN ISNULL(data_spaces.data_space_id,0) ELSE 0 END) AS row_overflow_data_space_id,
			MAX(CASE WHEN allocation_units.type=1 THEN ISNULL(data_spaces.name,'') ELSE '' END) AS in_row_data_space_name,
			MAX(CASE WHEN allocation_units.type=2 THEN ISNULL(data_spaces.name,'') ELSE '' END) AS lob_data_space_name,
			MAX(CASE WHEN allocation_units.type=3 THEN ISNULL(data_spaces.name,'') ELSE '' END) AS row_overflow_data_space_name
		FROM
			sys.partitions
			JOIN sys.allocation_units ON
				partitions.partition_id=allocation_units.container_id AND allocation_units.type IN (1,3) OR
				partitions.hobt_id=allocation_units.container_id AND allocation_units.type=2
			JOIN sys.data_spaces ON allocation_units.data_space_id=data_spaces.data_space_id
		GROUP BY partitions.partition_id
	) AS partitions
	JOIN sys.dm_db_partition_stats ON partitions.partition_id=dm_db_partition_stats.partition_id
	JOIN sys.indexes ON partitions.object_id=indexes.object_id AND partitions.index_id=indexes.index_id
	JOIN sys.databases ON databases.database_id=@DatabaseID
	JOIN sys.tables ON dm_db_partition_stats.object_id=tables.object_id
	JOIN sys.schemas ON tables.schema_id=schemas.schema_id
WHERE tables.type='U'

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

--Index
INSERT INTO Admin.SpaceLog_Index
	(
		Date, DatabaseID, DatabaseName, SchemaID, SchemaName, TableID, TableName,
		IndexID, IndexName, IndexDataSpaceID, IndexDataSpaceName, IndexDataSpaceType,
		PrimaryKey, UniqueConstraint, UniqueIndex, IgnoreDuplicateKey, IndexFillFactor,
		Rows, RowReserved, RowUsed, RowData, RowBuffer, LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
	)
SELECT
	Date, @DatabaseID AS DatabaseID, databases.name AS DatabaseName, tables.schema_id AS SchemaID, schemas.name AS SchemaName, TableID, tables.name AS TableName,
	IndexID, ISNULL(indexes.name,'[HEAP]') AS IndexName, indexes.data_space_id AS IndexDataSpaceID, data_spaces.name AS IndexDataSpaceName, data_spaces.type AS IndexDataSpaceType,
	is_primary_key AS PrimaryKey, is_unique_constraint AS UniqueConstraint, is_unique AS UniqueIndex, ignore_dup_key AS IngoreDuplicateKey, fill_factor AS IndexFillFactor,
	Rows, RowReserved, RowUsed, RowData, RowBuffer, LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
FROM
	(
		SELECT
			@Date AS Date, TableID, IndexID, SUM(Rows) AS Rows,
			SUM(RowData) AS RowData, SUM(RowUsed) AS RowUsed, SUM(RowReserved) AS RowReserved, SUM(RowBuffer) AS RowBuffer,
			SUM(LOBUsed) AS LOBUsed, SUM(LOBReserved) AS LOBReserved, SUM(LOBBuffer) AS LOBBuffer,
			SUM(OverflowUsed) AS OverflowUsed, SUM(OverflowReserved) AS OverflowReserved, SUM(OverflowBuffer) AS OverflowBuffer
		FROM Admin.SpaceLog_Partition
		WHERE SpaceLog_Partition.Date=@Date AND SpaceLog_Partition.DatabaseID=@DatabaseID
		GROUP BY TableID, IndexID
	) AS SpaceLog_Partition
	JOIN sys.indexes ON SpaceLog_Partition.TableID=indexes.object_id AND SpaceLog_Partition.IndexID=indexes.index_id
	LEFT OUTER JOIN sys.data_spaces ON indexes.data_space_id=data_spaces.data_space_id
	JOIN sys.tables ON SpaceLog_Partition.TableID=tables.object_id
	JOIN sys.schemas ON tables.schema_id=schemas.schema_id
	JOIN sys.databases ON databases.database_id=@DatabaseID
WHERE tables.type='U'

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

--Table
INSERT INTO Admin.SpaceLog_Table
	(
		Date, DatabaseID, DatabaseName, SchemaID, SchemaName, TableID, TableName,
		TableDataSpaceID, TableDataSpaceName, TableDataSpaceType,
		LOBDataSpaceID, LOBDataSpaceName, LOBDataSpaceType,
		Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
		LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
	)
SELECT
	Date, @DatabaseID AS DatabaseID, databases.name AS DatabaseName, tables.schema_id AS SchemaID, schemas.name AS SchemaName, TableID, tables.name AS TableName,
	TableDataSpaceID, data_spaces.name AS TableDataSpaceName, data_spaces.type AS TableDataSpaceType,
	tables.lob_data_space_id AS LOBDataSpaceID, ISNULL(data_spaces_lob.name,'') AS LOBDataSpaceName, ISNULL(data_spaces_lob.type,'') AS LOBDataSpaceType,
	Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
	LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
FROM
	(
		SELECT
			@Date AS Date, TableID, MAX(CASE WHEN IndexID<2 THEN IndexDataSpaceID ELSE 0 END) AS TableDataSpaceID, MAX(Rows) AS Rows,
			SUM(CASE WHEN IndexID<2 THEN RowData ELSE 0 END) AS RowData, SUM(CASE WHEN IndexID<2 THEN RowUsed ELSE 0 END) AS RowUsed, SUM(CASE WHEN IndexID<2 THEN RowReserved ELSE 0 END) AS RowReserved, SUM(CASE WHEN IndexID<2 THEN RowBuffer ELSE 0 END) AS RowBuffer,
			SUM(CASE WHEN IndexID>1 THEN RowData ELSE 0 END) AS IndexData, SUM(CASE WHEN IndexID>1 THEN RowUsed ELSE 0 END) AS IndexUsed, SUM(CASE WHEN IndexID>1 THEN RowReserved ELSE 0 END) AS IndexReserved, SUM(CASE WHEN IndexID>1 THEN RowBuffer ELSE 0 END) AS IndexBuffer,
			SUM(CASE WHEN IndexID<2 THEN LOBUsed ELSE 0 END) AS LOBUsed, SUM(CASE WHEN IndexID<2 THEN LOBReserved ELSE 0 END) AS LOBReserved, SUM(CASE WHEN IndexID<2 THEN LOBBuffer ELSE 0 END) AS LOBBuffer,
			SUM(CASE WHEN IndexID<2 THEN OverflowUsed ELSE 0 END) AS OverflowUsed, SUM(CASE WHEN IndexID<2 THEN OverflowReserved ELSE 0 END) AS OverflowReserved, SUM(CASE WHEN IndexID<2 THEN OverflowBuffer ELSE 0 END) AS OverflowBuffer
		FROM Admin.SpaceLog_Index
		WHERE SpaceLog_Index.Date=@Date AND SpaceLog_Index.DatabaseID=@DatabaseID
		GROUP BY TableID
	) AS SpaceLog_Index
	JOIN sys.tables ON SpaceLog_Index.TableID=tables.object_id
	JOIN sys.schemas ON tables.schema_id=schemas.schema_id
	LEFT OUTER JOIN sys.data_spaces ON SpaceLog_Index.TableDataSpaceID=data_spaces.data_space_id
	LEFT OUTER JOIN sys.data_spaces AS data_spaces_lob ON tables.lob_data_space_id=data_spaces_lob.data_space_id
	JOIN sys.databases ON databases.database_id=@DatabaseID
WHERE tables.type='U'

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

--Schema
INSERT INTO Admin.SpaceLog_Schema
	(
		Date, DatabaseID, DatabaseName, SchemaID, SchemaName,
		Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
		LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
	)
SELECT
	Date, @DatabaseID AS DatabaseID, databases.name AS DatabaseName, SchemaID, schemas.name AS SchemaName,
	Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer, LOBReserved,
	LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
FROM
	(
		SELECT
			@Date AS Date, SchemaID, SUM(Rows) AS Rows,
			SUM(RowData) AS RowData, SUM(RowUsed) AS RowUsed, SUM(RowReserved) AS RowReserved, SUM(RowBuffer) AS RowBuffer,
			SUM(IndexData) AS IndexData, SUM(IndexUsed) AS IndexUsed, SUM(IndexReserved) AS IndexReserved, SUM(IndexBuffer) AS IndexBuffer,
			SUM(LOBUsed) AS LOBUsed, SUM(LOBReserved) AS LOBReserved, SUM(LOBBuffer) AS LOBBuffer,
			SUM(OverflowUsed) AS OverflowUsed, SUM(OverflowReserved) AS OverflowReserved, SUM(OverflowBuffer) AS OverflowBuffer
		FROM Admin.SpaceLog_Table
		WHERE SpaceLog_Table.Date=@Date AND SpaceLog_Table.DatabaseID=@DatabaseID
		GROUP BY SchemaID
	) AS SpaceLog_Table
	JOIN sys.schemas ON SpaceLog_Table.SchemaID=schemas.schema_id
	JOIN sys.databases ON databases.database_id=@DatabaseID

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

--Database
INSERT INTO Admin.SpaceLog_Database
	(
		Date, DatabaseID, DatabaseName,
		Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
		LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
	)
SELECT
	Date, @DatabaseID AS DatabaseID, databases.name AS DatabaseName,
	Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
	LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
FROM
	(
		SELECT
			@Date AS Date, SUM(Rows) AS Rows,
			SUM(RowData) AS RowData, SUM(RowUsed) AS RowUsed, SUM(RowReserved) AS RowReserved, SUM(RowBuffer) AS RowBuffer,
			SUM(IndexData) AS IndexData, SUM(IndexUsed) AS IndexUsed, SUM(IndexReserved) AS IndexReserved, SUM(IndexBuffer) AS IndexBuffer,
			SUM(LOBUsed) AS LOBUsed, SUM(LOBReserved) AS LOBReserved, SUM(LOBBuffer) AS LOBBuffer,
			SUM(OverflowUsed) AS OverflowUsed, SUM(OverflowReserved) AS OverflowReserved, SUM(OverflowBuffer) AS OverflowBuffer
		FROM Admin.SpaceLog_Table
		WHERE SpaceLog_Table.Date=@Date AND SpaceLog_Table.DatabaseID=@DatabaseID
	) AS SpaceLog_Table
	JOIN sys.databases ON databases.database_id=@DatabaseID

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

--Data Space (Filegroup)
INSERT INTO Admin.SpaceLog_DataSpace
	(
		Date, DatabaseID, DatabaseName,
		DataSpaceID, DataSpaceName, DataSpaceType,
		Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
		LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
	)
SELECT
	@Date AS Date, @DatabaseID AS DatabaseID, databases.name AS DatabaseName,
	DataSpaceID, data_spaces.name AS DataSpaceName, data_spaces.type AS DataSpaceType,
	Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
	LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
FROM
	(
		SELECT
			CASE WHEN Row.DataSpaceID IS NOT NULL THEN Row.DataSpaceID WHEN LOB.DataSpaceID IS NOT NULL THEN LOB.DataSpaceID ELSE Overflow.DataSpaceID END AS DataSpaceID, ISNULL(Rows,0) AS Rows,
			ISNULL(RowReserved,0) AS RowReserved, ISNULL(RowUsed,0) AS RowUsed, ISNULL(RowData,0) AS RowData, ISNULL(RowBuffer,0) AS RowBuffer,
			ISNULL(IndexReserved,0) AS IndexReserved, ISNULL(IndexUsed,0) AS IndexUsed, ISNULL(IndexData,0) AS IndexData, ISNULL(IndexBuffer,0) AS IndexBuffer,
			ISNULL(LOBReserved,0) AS LOBReserved, ISNULL(LOBUsed,0) AS LOBUsed, ISNULL(LOBBuffer,0) AS LOBBuffer,
			ISNULL(OverflowReserved,0) AS OverflowReserved, ISNULL(OverflowUsed,0) AS OverflowUsed, ISNULL(OverflowBuffer,0) AS OverflowBuffer
		FROM
			(
				SELECT
					RowDataSpaceID AS DataSpaceID, SUM(CASE WHEN IndexID<2 THEN Rows ELSE 0 END) AS Rows,
					SUM(CASE WHEN IndexID<2 THEN RowReserved ELSE 0 END) AS RowReserved, SUM(CASE WHEN IndexID<2 THEN RowUsed ELSE 0 END) AS RowUsed,
					SUM(CASE WHEN IndexID<2 THEN RowData ELSE 0 END) AS RowData, SUM(CASE WHEN IndexID<2 THEN RowBuffer ELSE 0 END) AS RowBuffer,
					SUM(CASE WHEN IndexID>1 THEN RowReserved ELSE 0 END) AS IndexReserved, SUM(CASE WHEN IndexID>1 THEN RowUsed ELSE 0 END) AS IndexUsed,
					SUM(CASE WHEN IndexID>1 THEN RowData ELSE 0 END) AS IndexData, SUM(CASE WHEN IndexID>1 THEN RowBuffer ELSE 0 END) AS IndexBuffer
				FROM Admin.SpaceLog_Partition
				WHERE RowDataSpaceID>0 AND Date=@Date AND DatabaseID=@DatabaseID
				GROUP BY RowDataSpaceID
			) AS Row
			FULL OUTER JOIN (
				SELECT
					LOBDataSpaceID AS DataSpaceID,
					SUM(LOBReserved) AS LOBReserved, SUM(LOBUsed) AS LOBUsed, SUM(LOBBuffer) AS LOBBuffer
				FROM Admin.SpaceLog_Partition
				WHERE LOBDataSpaceID>0 AND Date=@Date AND DatabaseID=@DatabaseID
				GROUP BY LOBDataSpaceID
			) AS LOB ON Row.DataSpaceID=LOB.DataSpaceID
			FULL OUTER JOIN (
				SELECT
					OverflowDataSpaceID AS DataSpaceID,
					SUM(OverflowReserved) AS OverflowReserved, SUM(OverflowUsed) AS OverflowUsed, SUM(OverflowBuffer) AS OverflowBuffer
				FROM Admin.SpaceLog_Partition
				WHERE OverflowDataSpaceID>0 AND Date=@Date AND DatabaseID=@DatabaseID
				GROUP BY OverflowDataSpaceID
			) AS Overflow ON Row.DataSpaceID=Overflow.DataSpaceID
	) AS DataSpaces
	JOIN sys.data_spaces ON DataSpaces.DataSpaceID=data_spaces.data_space_id
	JOIN sys.databases ON databases.database_id=@DatabaseID
GO

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content