Tijdens het uitwerken van het beheer van metadata (opslag in een metadata-database) voor ons Datawarehouse kwam het onderdeel ‘tabelgroei’ naar voren. In een Datawarehouse is het zinvol om te weten hoe groot tabellen zijn en hoe de groei van deze tabellen zich ontwikkeld.

Zo kun je afwijkingen herkennen en tijdig maatregelen treffen om de performance optimaal te houden door bijvoorbeeld oudere data te archiveren in aparte tabellen. In dit artikel geef ik een voorbeeld hoe je de tabelgroei kunt monitoren.

Om de groei in tabellen te onderkennen zul je per dag een snapshot moeten maken van de stand van de tabellen. SQL Server houdt in de statistieken bij hoeveel records er in een tabel aanwezig zijn. Je hoeft dus geen counts te doen over de tabellen. Wellicht wil je dit voor iedere database in je SQL Instance doen. Vervolgens wil je de stand van de tabellen wegschrijven in een tabel, iedere dag weer.

Onderstaande tabel is een voorbeeldtabel die je kunt aanmaken om de statistieken in weg te schrijven. Je kunt deze uiteraard uitbreiden met kolommen die voor jou zinvol zijn.

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]

Vervolgens gaan we een stored procedure aanmaken die de statistieken voor iedere database gaat wegschrijven in de tabel. Er zitten wat hard-coded zaken in die je moet aanpassen zoals de databasenaam (bij insert into BI2_METABASE ), vervang dit voor jouw databasenaam.

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

N.B. Ik heb een extra optie ingesteld voor debugging. Als je de waarde 1 meegeeft met de stored procedure zie je wat de uitvoer is. Verder gooit hij alle statistieken weg die al eerder op de dag zijn gemaakt omdat je slechts een snapshot per dag wenst.

Deze stored procedure kun je vervolgens via een SQL Server Agent Job schedulen zodat hij ’s nachts automatisch wordt aangeroepen en de statistieken wegschrijft.

Voor opmerkingen of vragen, laat een bericht achter. Hopelijk heb je er wat aan.