Metadata – Tabelgroei in Datawarehouses

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.

{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}

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.

{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}

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.