Configuring Performance Collector SQL Backend DB/Tables

 To collect performance counter data from SQL instances, you need to choose a SQL instance which will house the performance collector database and tables.  It is recommended that you use a centralized performance collector database server for a given data center location and/or network zone [production versus test/demo].  The database server should have some higher availability [e.g. clustered with failover nodes].

Table Layout

The performance collector SQL backend database consist of 3 core tables.

  • CounterData – This table contains the raw data collected by each of the performance counters for a given SQL instance monitored.  Based on the collection frequency, there will be 1 row for each performance counter collected for the collection.  This table will go in infinitely and needs to be pruned/maintained.

CREATE TABLE [dbo].[CounterData](

       [GUID] [uniqueidentifier] NOT NULL,

       [CounterID] [int] NOT NULL,

       [RecordIndex] [int] NOT NULL,

       [CounterDateTime] [char](24) NOT NULL,

       [CounterValue] [float] NOT NULL,

       [FirstValueA] [int] NULL,

       [FirstValueB] [int] NULL,

       [SecondValueA] [int] NULL,

       [SecondValueB] [int] NULL,

       [MultiCount] [int] NULL,

CONSTRAINT [PK_CounterData] PRIMARY KEY CLUSTERED

([GUID] ASC, [CounterID] ASC, [RecordIndex] ASC))

GO

  • CounterDetails – This table contains a list of performance counters for a given SQL instance monitored.  This table has finite growth based on the number of SQL instances monitored and the performance counters captured.

CREATE TABLE [dbo].[CounterDetails](

       [CounterID] [int] IDENTITY(1,1) NOT NULL,

       [MachineName] [varchar](1024) NOT NULL,

       [ObjectName] [varchar](1024) NOT NULL,

       [CounterName] [varchar](1024) NOT NULL,

       [CounterType] [int] NOT NULL,

       [DefaultScale] [int] NOT NULL,

       [InstanceName] [varchar](1024) NULL,

       [InstanceIndex] [int] NULL,

       [ParentName] [varchar](1024) NULL,

       [ParentObjectID] [int] NULL,

       [TimeBaseA] [int] NULL,

       [TimeBaseB] [int] NULL,

CONSTRAINT [PK_CounterDetails] PRIMARY KEY CLUSTERED

([CounterID] ASC))

GO

  • DisplayToID – Contains information, such as rows captured, for a given SQL instance monitored.

CREATE TABLE [dbo].[DisplayToID](

       [GUID] [uniqueidentifier] NOT NULL,

       [RunID] [int] NULL,

       [DisplayString] [varchar](1024) NOT NULL,

       [LogStartTime] [char](24) NULL,

       [LogStopTime] [char](24) NULL,

       [NumberOfRecords] [int] NULL,

       [MinutesToUTC] [int] NULL,

       [TimeZoneName] [char](32) NULL,

CONSTRAINT [PK_DispalyToID] PRIMARY KEY CLUSTERED

([GUID] ASC),

CONSTRAINT [UQ_DiksplayToID_DisplayString] UNIQUE NONCLUSTERED

([DisplayString] ASC))

GO

Possible Enhancements — Partitioning & Compression

Since the CounterData table contains the raw data we would wish to query and act upon, the design of the table leaves a lot to be desired.  The clustered index is on a composite primary key column [GUID,CounterID,RecordIndex] and there are not other indexes on the table by default.  Also the CounterDateTime column, which stores the time the collection took place, is stored as a CHAR(24) which makes queries very difficult since you need to convert the CHAR(24) to a DATETIME in order to perform range based queries.  There is also the fact that there is no partitioning [for maintenance of the table] or compression [to keep the database size realistic].  There is also the fact that data is inserted into the CounterData table using BULK INSERT, which makes it hard to change the core table since you can’t add/modify any columns definitions.

Given the BULK INSERT attempts to insert data into the CounterData table, there is nothing to say that CounterData can’t be a view instead.  The solution below presumes you are performance collector database is housed on a SQL 2008 Enterprise database instance.

  • Create a new DAYOFYEAR [1-366] partition scheme and function for maintenance of the table.

CREATE PARTITION FUNCTION [PF_PartitionDay](SMALLINT) AS RANGE FOR VALUES

(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79, 80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119, 120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139, 140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159, 160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179, 180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199, 200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219, 220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239, 240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259, 260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279, 280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299, 300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319, 320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339, 340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359, 360,361,362,363,364,365,366)

GO

CREATE PARTITION SCHEME [PS_PartitionDay] AS PARTITION [PF_PartitionDay] ALL TO ([PRIMARY])

GO

  • Drop the current CounterData table, create a new CounterData_TABLE table and build CounterData as a view.

DROP TABLE [dbo].[CounterData]

GO

CREATE TABLE [dbo].[CounterData_TABLE](

       [GUID] [uniqueidentifier] NOT NULL,

       [CounterID] [int] NOT NULL,

       [RecordIndex] [int] NOT NULL,

       [CounterDateTime] [char](24) NOT NULL,

       [CounterValue] [float] NOT NULL,

       [FirstValueA] [int] NULL,

       [FirstValueB] [int] NULL,

       [SecondValueA] [int] NULL,

       [SecondValueB] [int] NULL,

       [MultiCount] [int] NULL,

       [EventDate] [datetime2](7) NOT NULL,

       [DayOfYear] [smallint] NOT NULL,

 CONSTRAINT [PK_CounterData_TABLE] PRIMARY KEY NONCLUSTERED

([GUID] ASC,[CounterID] ASC,[RecordIndex] ASC,[DayOfYear] ASC))

ON [PS_PartitionDay](DayOfYear)

GO

/* Add default values for new EventDate & DayOfYear columns. */

ALTER TABLE [dbo].[CounterData_TABLE] ADD  CONSTRAINT [DF_CounterData_TABLE_EventDate]  DEFAULT (GETDATE()) FOR [EventDate]

GO

ALTER TABLE [dbo].[CounterData_TABLE] ADD  CONSTRAINT [DF_CounterData_TABLE_DayOfYear]  DEFAULT (DATEPART(DAYOFYEAR,GETDATE())) FOR [DayOfYear]

GO

/* New clustered index on EventDate column */

CREATE CLUSTERED INDEX [IDX_CounterData_TABLE_EventDate] ON [dbo].[CounterData_TABLE] ([EventDate] ASC,[DayOfYear] ASC)

ON [PS_PartitionDay](DayOfYear)

GO

/* New clustered index on CounterID column */

CREATE NONCLUSTERED INDEX [IDX_CounterData_TABLE_CounterID] ON [dbo].[CounterData_TABLE] ([CounterID] ASC)

INCLUDE ([EventDate],[DayOfYear])

ON [PS_PartitionDay](DayOfYear)

GO

/* Create new CounterData VIEW. */

CREATE VIEW [dbo].[CounterData] AS

SELECT [GUID]

      ,[CounterID]

      ,[RecordIndex]

      ,[CounterDateTime]

      ,[CounterValue]

      ,[FirstValueA]

      ,[FirstValueB]

      ,[SecondValueA]

      ,[SecondValueB]

      ,[MultiCount]

FROM  [dbo].[CounterData_TABLE]

GO

  • Modify the performance collector tables for ROW compression.

ALTER INDEX ALL ON CounterData_TABLE REBUILD WITH (ONLINE=ON,DATA_COMPRESSION=ROW)

GO

ALTER INDEX ALL ON CounterDetails REBUILD WITH (ONLINE=ON,DATA_COMPRESSION=ROW)

GO

ALTER INDEX ALL ON DisplayToID REBUILD WITH (ONLINE=ON,DATA_COMPRESSION=ROW)

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s