Three Sample reports for the Automated DBA: Space Usage SnapShotter

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

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

IF OBJECT_ID('Util.Util_Report_SpaceLog_LargestIndexesByBufferCache', 'P') IS NOT NULL DROP PROCEDURE Util.Util_Report_SpaceLog_LargestIndexesByBufferCache
IF OBJECT_ID('Util.Util_Report_SpaceLog_LargestTablesByReserved', 'P') IS NOT NULL DROP PROCEDURE Util.Util_Report_SpaceLog_LargestTablesByReserved
IF OBJECT_ID('Util.Util_Report_SpaceLog_TableSizeRunningGrowth', 'P') IS NOT NULL DROP PROCEDURE Util.Util_Report_SpaceLog_TableSizeRunningGrowth
GO

/**
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_Report_SpaceLog_LargestIndexesByBufferCache
Util_Report_SpaceLog_LargestTablesByReserved
Util_Report_SpaceLog_TableSizeRunningGrowth
By Jesse Roberge - YeshuaAgapao@Yahoo.com

Sample reports for the Automated DBA: Space Usage SnapShotter
Report 1: Gets the 100 largest indexes by total buffer cache space usage
Report 2: Gets the 100 largest tables by total reserved pages
Report 3: Gets the largest tables in each database (optionally limiting to a single database) and provides a running total of space usage as well as the growth from the previous snapshot.
Size sorting is by the most recent date sample.
These procs should go into the same database as the SpaceLog_* tables
There are many more potential reports that can be created from the data captured by the Space Usage SnapShotter.

Required Input Parameters
none

Optional Input Parameters
@MinPrime=1The minimum prime value to generate.  You will usually want this at 1
@MaxPrime=2097152The maximum prime value to generate.
The default value should use all of the values for most peoples' table of numbers
(622^2=386884 for mine to completely fill 2-level deep clustered index).

Usage:
EXECUTE Util.Util_Report_SpaceLog_LargestIndexesByBufferCache @Days=1
EXECUTE Util.Util_Report_SpaceLog_LargestTablesByReserved @Days=1
EXECUTE Util.Util_Report_SpaceLog_TableSizeRunningGrowth @Days=7, @DatabaseName='CalvaryHelps'

Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagarize, conceal the source, proprietarize modifications, or prohibit copying & re-distribution of this script/proc.

This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as
    published by the Free Software Foundation, either version 3 of the
    License, or (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Lesser General Public License for more details.

    see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
**/
CREATE PROCEDURE Util.Util_Report_SpaceLog_LargestIndexesByBufferCache
@Days int
AS

--Top list of indexes by buffer cache usage
SELECT TOP 100
SpaceLog_Index.Date, SpaceLog_Index.DatabaseName, SpaceLog_Index.SchemaName, SpaceLog_Index.TableName, SpaceLog_Index.IndexID, SpaceLog_Index.IndexName,
Rows, SpaceLog_Index.TotalReserved AS TotalReserved_KB, CONVERT(numeric(19,6), CONVERT(numeric(19,6), SpaceLog_Index.TotalReserved)/1024.0) AS TotalReserved_MB,
SpaceLog_Index.TotalBuffer AS TotalBuffer_KB, CONVERT(numeric(19,6), CONVERT(numeric(19,6), SpaceLog_Index.TotalBuffer)/1024.0) AS TotalBuffer_MB
FROM
(
SELECT Date, DatabaseName, ROW_NUMBER() OVER (PARTITION BY DatabaseName ORDER BY Date DESC) AS RowNumber
FROM Admin.SpaceLog_Database
WHERE Date>DateAdd(dd, @Days*-1, GETDATE())
) AS DateSort
JOIN Admin.SpaceLog_Index ON DateSort.Date=SpaceLog_Index.Date AND DateSort.DatabaseName=SpaceLog_Index.DatabaseName
WHERE DateSort.RowNumber=1
ORDER BY TotalBuffer DESC, DatabaseName, IndexID, SchemaName, TableName
GO

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

CREATE PROCEDURE Util.Util_Report_SpaceLog_LargestTablesByReserved
@Days int
AS

--Top list of tables by total reserved
SELECT TOP 100
SpaceLog_Table.Date, SpaceLog_Table.DatabaseName, SpaceLog_Table.SchemaName, SpaceLog_Table.TableName, Rows,
Rows, SpaceLog_Table.TotalReserved AS TotalReserved_KB, CONVERT(numeric(19,6), CONVERT(numeric(19,6), SpaceLog_Table.TotalReserved)/1024.0) AS TotalReserved_MB,
SpaceLog_Table.TotalBuffer AS TotalBuffer_KB, CONVERT(numeric(19,6), CONVERT(numeric(19,6), SpaceLog_Table.TotalBuffer)/1024.0) AS TotalBuffer_MB
FROM
(
SELECT Date, DatabaseName, ROW_NUMBER() OVER (PARTITION BY DatabaseName ORDER BY Date DESC) AS RowNumber
FROM Admin.SpaceLog_Database
WHERE Date>DateAdd(dd, @Days*-1, GETDATE())
) AS DateSort
JOIN Admin.SpaceLog_Table ON DateSort.Date=SpaceLog_Table.Date AND DateSort.DatabaseName=SpaceLog_Table.DatabaseName
WHERE DateSort.RowNumber=1
ORDER BY TotalReserved DESC, DatabaseName, SchemaName, TableName
GO

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

CREATE PROCEDURE Util.Util_Report_SpaceLog_TableSizeRunningGrowth
@DatabaseName sysname='',
@Days int
AS

--Current Size, Current Percentage, Growth from prior date, Running Total, Running Percentage
WITH
SpaceLog_Table AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY SpaceLog_Table.DatabaseName, SpaceLog_Table.SchemaName, SpaceLog_Table.TableName ORDER BY SpaceLog_Table.Date DESC) AS Date_RowNumber,
ROW_NUMBER() OVER (PARTITION BY SpaceLog_Table.DatabaseName, SpaceLog_Table.Date ORDER BY SpaceLog_Table.TotalReserved DESC, SpaceLog_Table.SchemaName, SpaceLog_Table.TableName) AS Running_RowNumber,
SpaceLog_Table.Date, SpaceLog_Table.DatabaseName, SpaceLog_Table.SchemaName, SpaceLog_Table.TableName, SpaceLog_Table.TotalReserved, SpaceLog_Table.Rows AS TotalRows,
CASE WHEN SpaceLog_Table_Sum.sum_TotalReserved=0 THEN 0 ELSE CONVERT(Numeric(19,6), 100)*CONVERT(Numeric(19,6), SpaceLog_Table.TotalReserved)/CONVERT(Numeric(19,6), SpaceLog_Table_Sum.sum_TotalReserved) END AS PercentReserved,
CASE WHEN SpaceLog_Table_Sum.sum_TotalRows=0 THEN 0 ELSE CONVERT(Numeric(19,6), 100)*CONVERT(Numeric(19,6), SpaceLog_Table.Rows)/CONVERT(Numeric(19,6), SpaceLog_Table_Sum.sum_TotalRows) END AS PercentRows
FROM
Admin.SpaceLog_Table
JOIN (
SELECT Date, DatabaseName, SUM(TotalReserved) AS sum_TotalReserved, SUM(Rows) AS sum_TotalRows FROM Admin.SpaceLog_Table GROUP BY Date, DatabaseName
) AS SpaceLog_Table_Sum ON SpaceLog_Table.date=SpaceLog_Table_Sum.date AND SpaceLog_Table.DatabaseName=SpaceLog_Table_Sum.DatabaseName
WHERE
SpaceLog_Table.Date>DateAdd(dd, -90, GetDate())
--AND SpaceLog_Table.DatabaseName LIKE CASE WHEN @DatabaseName='' THEN SpaceLog_Table.DatabaseName ELSE @DatabaseName END
)
SELECT
SpaceLog_Table1.Date, SpaceLog_Table1.DatabaseName, SpaceLog_Table1.SchemaName, SpaceLog_Table1.TableName,
CONVERT(Numeric(19,6), CONVERT(Numeric(19,6), MAX(SpaceLog_Table1.TotalReserved))/1024.0) AS Reserved_MB,
MAX(SpaceLog_Table1.TotalRows) AS Rows,
CONVERT(Numeric(19,6), CONVERT(Numeric(19,6), MAX(SpaceLog_Table1.TotalReserved-SpaceLog_Table3.TotalReserved))/1024.0) AS GrowthFromPriorDate_MB,
MAX(SpaceLog_Table1.TotalRows-SpaceLog_Table3.TotalRows) AS GrowthFromPriorDate_Rows,
CONVERT(numeric(9,6), MAX(SpaceLog_Table1.PercentReserved)) AS ReservedPercentage,
CONVERT(numeric(9,6), MAX(SpaceLog_Table1.PercentRows)) AS RowsPercentage,
CONVERT(Numeric(19,6), CONVERT(Numeric(19,6), SUM(SpaceLog_Table2.TotalReserved))/1048576.0) AS Running_Reserved_GB,
CONVERT(numeric(9,6), SUM(SpaceLog_Table2.PercentReserved)) AS Running_Reserved_Percentage--,
--CONVERT(Numeric(19,6), CONVERT(Numeric(19,6), SUM(SpaceLog_Table2.TotalRows))/1048576.0) AS Running_Rows,
--CONVERT(numeric(9,6), SUM(SpaceLog_Table2.PercentRows)) AS Running_Rows_Percentage
FROM
--CTE
SpaceLog_Table AS SpaceLog_Table1
--Sorting, using only most recent date sample
JOIN (
SELECT
ROW_NUMBER() OVER (ORDER BY SpaceLog_Table.DatabaseName, SpaceLog_Table.Date, SpaceLog_Table.TotalReserved DESC, SpaceLog_Table.SchemaName, SpaceLog_Table.TableName) AS Sort_RowNumber,
SpaceLog_Table.DatabaseName, SpaceLog_Table.Date, SpaceLog_Table.SchemaName, SpaceLog_Table.TableName
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY DatabaseName ORDER BY Date DESC) AS RowNumber, Date, DatabaseName
FROM Admin.SpaceLog_Database
) AS TopDate
JOIN Admin.SpaceLog_Table ON TopDate.Date=SpaceLog_Table.Date AND TopDate.DatabaseName=SpaceLog_Table.DatabaseName
WHERE TopDate.RowNumber=1
) AS SpaceLog_Table_Sort ON
SpaceLog_Table1.DatabaseName=SpaceLog_Table_Sort.DatabaseName
AND SpaceLog_Table1.SchemaName=SpaceLog_Table_Sort.SchemaName AND SpaceLog_Table1.TableName=SpaceLog_Table_Sort.TableName
--Growth From Prior Date
LEFT OUTER JOIN SpaceLog_Table AS SpaceLog_Table3 ON
SpaceLog_Table3.Date_RowNumber=SpaceLog_Table1.Date_RowNumber+1 AND SpaceLog_Table3.DatabaseName=SpaceLog_Table1.DatabaseName
AND SpaceLog_Table3.SchemaName=SpaceLog_Table1.SchemaName AND SpaceLog_Table3.TableName=SpaceLog_Table1.TableName
--Running Aggregates
INNER JOIN SpaceLog_Table AS SpaceLog_Table2 ON
SpaceLog_Table2.Running_RowNumber<=SpaceLog_Table1.Running_RowNumber
AND SpaceLog_Table2.Date=SpaceLog_Table1.Date AND SpaceLog_Table2.DatabaseName=SpaceLog_Table1.DatabaseName
GROUP BY SpaceLog_Table_Sort.Sort_RowNumber, SpaceLog_Table1.DatabaseName, SpaceLog_Table1.Date, SpaceLog_Table1.SchemaName, SpaceLog_Table1.TableName
ORDER BY SpaceLog_Table_Sort.Sort_RowNumber, SpaceLog_Table1.DatabaseName, SpaceLog_Table1.Date DESC, Running_Reserved_Percentage, SpaceLog_Table1.SchemaName, SpaceLog_Table1.TableName
GO

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

/*
--Peek at all to space log tables
SELECT TOP 100 * FROM Admin.SpaceLog_Partition ORDER BY DatabaseName, Date DESC, TotalReserved DESC
SELECT TOP 100 * FROM Admin.SpaceLog_Index ORDER BY DatabaseName, Date DESC, TotalReserved DESC
SELECT TOP 100 * FROM Admin.SpaceLog_Table ORDER BY DatabaseName, Date DESC, TotalReserved DESC
SELECT TOP 100 * FROM Admin.SpaceLog_Schema ORDER BY DatabaseName, Date DESC, TotalReserved DESC
SELECT TOP 100 * FROM Admin.SpaceLog_DataSpace ORDER BY DatabaseName, Date DESC, TotalReserved DESC
SELECT TOP 100 * FROM Admin.SpaceLog_Database ORDER BY DatabaseName, Date DESC, TotalReserved DESC
*/GO

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