T-SQL Tuesday #025 – Monitoring table growth

T-SQL Tuesday

This month’s T-SQL Tuesday is being hosted by Allen White (Blog | Twitter). The topic is sharing your T-SQL scripts that you use to make your job easier. Below is one of mine.

One of the things I like to keep track of is table growth; that is, which tables grow the most over a period of time. This helps me to determine if I have enough database space allocated, and gives me a rough estimate of when the servers will need more drive space added.

At first I just monitored the size of the databases files and watched how big they were getting and how much or little free space was still available. But I also wanted a way to see just where that growth was. So I would run sp_spaceused on occasion on our biggest tables and compare each set of results to the previous set.

However this is not very efficient. As our application grew and more and more tables were being added it was harder to know just which tables were being affected the most.

So finally I began to use two un documented procedures to look at all tables in all databases. The first is sp_MSForEachDB, the second is sp_MSForEachTable. Each does just what it sounds like; runs a query against all databases or loops through all tables. I’m not going to go into detail here as neither procedure is new and each has been written about countless times before.

My procedure builds a dynamic SQL statement, getting the schema name and table name using sp_MSForEachTable. Part of that statement is an INSERT statement so I can store the results. Then I run the dynamic statement against all user databases on the server.

USE [PerfDB]
GO

CREATE TABLE [dbo].[TableSpace](
[RunDate] [datetime] NOT NULL,
[DatabaseName] [nvarchar](128) NULL,
[SchemaName] [nvarchar](128) NULL,
[TableName] [nvarchar](128) NOT NULL,
[NumRows] [int] NOT NULL,
[Reserved] [nchar](16) NOT NULL,
[DataUsed] [nchar](16) NOT NULL,
[IndexUsed] [nchar](16) NOT NULL,
[Unused] [nchar](16) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TableSpace] ADD  CONSTRAINT [DF_TableSpace_RunDate]  DEFAULT (getdate()) FOR [RunDate]
GO

CREATE PROCEDURE [dbo].[prc_TableSpace]
AS

SET NOCOUNT ON;

DECLARE @SQL varchar(8000)

SELECT @SQL = '
IF ''@'' <> ''master'' AND ''@'' <> ''model'' AND ''@'' <> ''msdb'' AND ''@'' <> ''tempdb''
BEGIN
USE [@] EXECUTE sp_MSForEachTable ''INSERT INTO PerfDB.dbo.TableSpace (TableName, NumRows, Reserved, DataUsed, IndexUsed, Unused) EXEC sp_spaceused ''''?'''';
UPDATE PerfDB.dbo.TableSpace SET SchemaName = LEFT(''''?'''', CHARINDEX(''''.'''', ''''?'''', 1) - 2) WHERE SchemaName IS NULL;
UPDATE PerfDB.dbo.TableSpace SET DatabaseName = ''''@'''' WHERE DatabaseName IS NULL; ''
END
'

EXEC sp_MSforeachdb @SQL, '@'

UPDATE PerfDB.dbo.TableSpace
SET SchemaName = REPLACE(SchemaName, '[', '')

I set up a weekly job that runs during a period of inactivity. I also have a job that runs an SSIS package to collect the table results into a central repository. Then I can run the procedure like the one below to show me how much every table grew in the past week, or a similar one that shows the growth over a period of time.

CREATE PROCEDURE [dbo].[TableSizeCompare]
AS

SET NOCOUNT ON

CREATE TABLE #myTable (
ServerName        NVARCHAR(25),
RunDate            DATETIME,
DatabaseName    NVARCHAR(128),
SchemaName        NVARCHAR(128),
TableName        NVARCHAR(128),
NumRows            INT,
ReservedInKB    INT,
DataUsedInKB    INT,
IndexUsedInKB    INT,
RowNum            TINYINT)

;WITH TableSpaceCTE (ServerName, RunDate, DatabaseName, SchemaName, TableName, NumRows,
ReservedInKB, DataUsedInKB, IndexUsedInKB, RowNum) AS
(
SELECT ServerName, RunDate, DatabaseName, SchemaName, TableName, NumRows,
CONVERT(INT, LEFT(Reserved, LEN(Reserved) - 3)) AS ReservedInKB,
CONVERT(INT, LEFT(DataUsed, LEN(DataUsed) - 3)) AS DataUsedInKB,
CONVERT(INT, LEFT(IndexUsed, LEN(IndexUsed) - 3)) AS IndexUsedInKB,
ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName, SchemaName, TableName ORDER BY RunDate DESC) AS RowNum
FROM PerfDB_DW.dbo.TableSpace
)
INSERT INTO #myTable
SELECT ServerName, RunDate, DatabaseName, SchemaName, TableName, NumRows,
ReservedInKB, DataUsedInKB, IndexUsedInKB, RowNum
FROM TableSpaceCTE
WHERE RowNum <= 2

SELECT
MaxRun.ServerName
, MaxRun.DatabaseName
, MaxRun.SchemaName
, MaxRun.TableName
, MaxRun.RunDate AS LastRunDate
, MinRun.RunDate AS PriorRunDate
, MaxRun.NumRows AS CurrentNumRows
, MinRun.NumRows AS PriorNumRows
, MaxRun.NumRows - MinRun.NumRows AS NumRowGrowth
, MaxRun.ReservedInKB AS CurrentReservedInKB
, MinRun.ReservedInKB AS PriorReservedInKB
, MaxRun.ReservedInKB -  MinRun.ReservedInKB AS ReservedGrowthInKB
, MaxRun.DataUsedInKB AS CurrentDataUsedInKB
, MinRun.DataUsedInKB AS PriorDataUsedInKB
, MaxRun.DataUsedInKB -  MinRun.DataUsedInKB AS DataUsedGrowthInKB
, MaxRun.IndexUsedInKB AS CurrentIndexUsedInKB
, MinRun.IndexUsedInKB AS PriorIndexUsedInKB
, MaxRun.IndexUsedInKB -  MinRun.IndexUsedInKB AS IndexUsedInKB
FROM
(SELECT * FROM #myTable WHERE RowNum = 1) AS MaxRun
INNER JOIN
(SELECT * FROM #myTable WHERE RowNum = 2) AS MinRun
ON MaxRun.ServerName = MinRun.ServerName AND MaxRun.DatabaseName = MinRun.DatabaseName
AND MaxRun.SchemaName = MinRun.SchemaName AND MaxRun.TableName = MinRun.TableName
ORDER BY MaxRun.ServerName, MaxRun.DatabaseName, MaxRun.SchemaName, MaxRun.TableName

image