Metadata – Table growth in data warehouses

While working out how to manage metadata (stored in a metadata database) for our data warehouse, the topic ‘table growth’ came up. In a data warehouse it is useful to know how big tables are and how these tables grow.

That way you can spot deviations and take action in time to keep performance optimal, for example by archiving older data in separate tables. In this article I give an example of how you can monitor table growth.

To see the growth in tables, you have to take a snapshot of the row counts of the tables every day. SQL Server keeps track of how many records a table has in its statistics. So you do not have to run counts on the tables. You may want to do this for every database in your SQL Instance. Then you want to write these row counts to a table, day after day.

The table below is an example table you can create to write the statistics to. You can of course extend it with columns that are useful to you.

{geshi xml:lang=“tsql” lines=“false”}
CREATE TABLE [dbo].[Tablerowcount](
[TableRowCountID] [varchar](128) NOT NULL,
[RegistrationDate] [datetime] NULL,
[DatabaseName] [varchar](64) NULL,
[TableName] [varchar](64) NULL,
[NrOfRecords] [int] NULL,
CONSTRAINT [PK_Tablerowcount] PRIMARY KEY CLUSTERED
(
[TableRowCountID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] {/geshi}

Next we create a stored procedure that writes the statistics for every database to the table. It has some hard-coded parts you must change, such as the database name (at insert into BI2_METABASE ). Replace it with your own database name.

{geshi xml:lang=“tsql” lines=“false”}
CREATE PROCEDURE [dbo].[sp_tablerowcount] ( @DEBUG BIT = 0 )
AS /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPC Purpose:            Inzamelen aantallen records per database per datum per dag
Created By:             Ronald Kraijesteijn
Created:                Maart 2010
Updated:                X
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Example
EXEC sp_tablerowcount 1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

BEGIN   
SET NOCOUNT ON

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @DB AS VARCHAR(32)
DECLARE @RC AS INT
DECLARE @DATE AS INT

SET @DATE = CAST(YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100
+ DAY(GETDATE()) AS VARCHAR(8))

--Opschonen bestaande aantal van vandaag (we willen geen dubbele registratie op een dag hebben! )
DELETE  FROM Tablerowcount
WHERE   CAST(YEAR(RegistrationDate) * 10000 + MONTH(RegistrationDate)
* 100 + DAY(RegistrationDate) AS VARCHAR(8)) = @DATE
SET @RC = @@ROWCOUNT
IF @DEBUG = 1
PRINT CAST(@RC AS VARCHAR(16))
+ ’ Deleted because they are already in today’’s statistics ’
--Databases ophalen uit systeemtabellen
DECLARE CUR_DB CURSOR
FOR SELECT  [name]
FROM    sys.databases
WHERE   [name] NOT IN ( ‘master’, ‘tempdb’, ‘model’, ‘msdb’ )

--Registreren aantal records per tabel
OPEN CUR_DB
FETCH CUR_DB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DEBUG = 1 PRINT ’Loading table statistics to metabase for database: ’ + @DB
SET @SQL = ’INSERT INTO BI2_METABASE.dbo.Tablerowcount                           
SELECT
(CAST(YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE()) AS VARCHAR(8)) + ‘’_‘’ +
@DB + ‘’_‘’ + O.name ) AS TableRowCountID
,GETDATE() AS RegistrationDate
,@DB AS DatabaseName   
,O.name AS TableName
,I.rowcnt AS NrOfRecords
FROM    ’ + @DB + ’.dbo.sysobjects AS O
INNER JOIN ’ + @DB
+ ’.dbo.sysindexes AS I ON O.id = I.id
WHERE   I.indid IN ( 0, 1 )
AND O.xtype = ’‘U’’
AND O.[name] NOT IN ( ’‘sysdiagrams’’, ’‘dtproperties’’ )
ORDER BY O.[name]’

BEGIN TRY
EXEC sp_executesql @SQL, N’@DB AS VARCHAR(32)’, @DB = @DB
SET @RC =  @@ROWCOUNT
IF @DEBUG = 1 PRINT ’Records loaded: ’ + CAST(@RC AS VARCHAR(8))
END TRY               
BEGIN CATCH
PRINT ERROR_MESSAGE() 
PRINT ’Error: processing table rowcount data for database ’
+ @DB
PRINT @SQL
CLOSE CUR_DB
DEALLOCATE CUR_DB
RETURN @RC   
END CATCH
FETCH CUR_DB INTO @DB
END
CLOSE CUR_DB
DEALLOCATE CUR_DB       
END{/geshi}

Note: I added an extra option for debugging. If you pass the value 1 to the stored procedure, you see what the output is. It also deletes all statistics that were already taken earlier that day, because you only want one snapshot per day.

You can then schedule this stored procedure with a SQL Server Agent Job, so it runs automatically at night and writes the statistics to the table.

For comments or questions, leave a message. I hope it helps.