Introduction

On the off chance that you have not been capturing baselines on your production or live servers, then today is the day you can begin. This article gives scripts, legitimate for SQL Server 2005, SQL Server 2008 and higher, which anybody can use to capture essential data around an SQL Server instance. All you need is a database in which to store the data (ideally your own particular database, not a framework or production database) and some scheduled jobs.

MCSE Training – Resources (Intense)

How to Create the Database

How about we begin by making the database to store this data, on the off chance that you don’t have one yet. If you don’t mind, take note that in Listing 1 you may need to change the location, file size and file growth settings as fitting for your environment, and that you should make a backup of your database all the time.

USE [master];
GO

CREATE DATABASE [DistributionData] ON PRIMARY 
( NAME = N'DistributionData', 
  FILENAME = N'M:\UserDBs\ DistributionData.mdf', 
  SIZE = 512MB, 
  FILEGROWTH = 512MB
) LOG ON 
( NAME = N' DistributionData _log', 
  FILENAME = N'M:\UserDBs\ DistributionData _log.ldf', 
  SIZE = 128MB, 
  FILEGROWTH = 512MB
);

ALTER DATABASE [BaselineData] SET RECOVERY SIMPLE;

Listing 1: Database to store baseline performance data

Collecting Configuration Data (sys.configurations)

One of the keys to fruitful base lining is to begin small. The easiest information to gather consistently is configuration information, which is kept in the sys.configurations list view. This view lists various settings such as minimum and maximum memory, max level of parallelism, and whether xp_cmdshell is enabled. By logging the contents of this view to a table all the time, we capture a history of the instance configuration settings, permitting you to recognize effortlessly if a setting has changed. This perspective can’t let us know who changed the setting though, so we have to audit the ERRORLOG or default follow to discover the guilty party, but we will realize that something changed. On the other hand, we’ll have the capacity to demonstrate that a setting has not changed, which is frequently a similarly significant feature of investigating. While design baselines may appear to be commonplace, they can rapidly preclude what is not the issue.

Listing 2 below creates our ConfigData table; here we’ll store this information.

USE [DistributionData];
GO

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

IF NOT EXISTS ( SELECT  *
                FROM    [sys].[tables]
                WHERE   [name] = N'ConfigData'
                        AND [type] = N'U' ) 
    CREATE TABLE [dbo].[ConfigData]
        (
          [ConfigurationID] [int] NOT NULL ,
          [Name] [nvarchar](35) NOT NULL ,
          [Value] [sql_variant] NULL ,
          [ValueInUse] [sql_variant] NULL ,
          [CaptureDate] [datetime]
        )
    ON  [PRIMARY];
GO

CREATE CLUSTERED INDEX CI_ConfigData ON [dbo].[ConfigData] ([CaptureDate],[ConfigurationID]);

Listing 2: Create the ConfigData table

Next, we’ll have to set up a job to log the settings to the dbo.ConfigData table frequently. Apparently, the setup of your example won’t change as often as possible, so running the occupation once per day is most likely all that is required. Incorporate into your job the statement in Listing 3, to log the settings.

USE [DistributionData];
GO

INSERT  INTO [dbo].[ConfigData]
        ( [ConfigurationID] ,
          [Name] ,
          [Value] ,
          [ValueInUse] ,
          [CaptureDate]
        )
        SELECT  [configuration_id] ,
                [name] ,
                [value] ,
                [value_in_use] ,
                GETDATE()
        FROM    [sys].[configurations];

Listing 3: Statement to gather and log configuration data, within a scheduled job

You may think that it’s helpful to rapidly look at a “snapshot” of the values captured on day xX with those caught on day Y to see what changed between the two dates. For the two dates given, the dbo.usp_SysConfigReport stored procedure in Listing 4 will yield the values for all settings where the values change between the two dates. In the event that it gives back no rows, then no settings have changed.

USE [DistributionData];
GO

IF OBJECTPROPERTY(OBJECT_ID(N'usp_SysConfigReport'), 'IsProcedure') = 1 
    DROP PROCEDURE usp_SysConfigReport;
GO

CREATE PROCEDURE dbo.usp_SysConfigReport
    (
      @OlderDate DATETIME ,
      @RecentDate DATETIME
    )
AS 
    BEGIN;

        IF @RecentDate IS NULL
            OR @OlderDate IS NULL 
            BEGIN;
                RAISERROR(N'Input parameters cannot be NULL', 16, 1);
                RETURN;
            END;

        SELECT  [O].[Name] ,
                [O].[Value] AS "OlderValue" ,
                [O].[ValueInUse] AS "OlderValueInUse" ,
                [R].[Value] AS "RecentValue" ,
                [R].[ValueInUse] AS "RecentValueInUse"
        FROM    [dbo].[ConfigData] O
                JOIN ( SELECT   [ConfigurationID] ,
                                [Value] ,
                                [ValueInUse]
                       FROM     [dbo].[ConfigData]
                       WHERE    [CaptureDate] = @RecentDate
                     ) R ON [O].[ConfigurationID] = [R].[ConfigurationID]
        WHERE   [O].[CaptureDate] = @OlderDate
                AND ( ( [R].[Value] <> [O].[Value] )
                      OR ( [R].[ValueInUse] <> [O].[ValueInUse] )
                    )
    END;

Listing 4: The dbo.usp_SysConfigReport stored procedure

In Listing 5, we recover a list of substantial dates and then utilize two of them to execute the stored procedure, entering the older date as the first data:

USE [DistributionData];
GO
SELECT DISTINCT
        [CaptureDate]
FROM    [dbo].[ConfigData]
ORDER BY [CaptureDate];

EXEC dbo.usp_SysConfigReport '2014-08-02 15:09:56.290',
    '2014-08-24 15:10:41.963';

Listing 5: Return a list of valid dates, and then execute the stored procedure

An extra advantage of capturing this information is that we can audit and check the present settings. It is outside the extent of this article to survey every setting and highlight potential issues but, for instance, you’ll need to guarantee that the quality for max server memory (MB) is not exactly the aggregate memory on the server (see http://sqlskills.com/blogs/jonathan/post/How-much-memory-does-my-SQL-Server-actually-need.aspx for further details).

Collecting Server and Instance Data (SERVERPROPERTY)

Capturing the configuration settings is a decent beginning, however despite everything we have work to do. Utilizing the SERVERPROPERTY built in function, we can capture more data about the instance, and additionally some server information. Once more, normally, this data does not change, but rather it can be exceptionally useful in case of group failovers and patching. For instance, the result from SERVERPROPERTY incorporates ComputerNamePhysicalNetBios, which is the name of the PC on which the SQL Server example is as of now running. In the event that we haven’t arranged Failover Clustering, this value does not change, but rather for a clustered implementation, the value changes at whatever point a failover happens.

The ProductVersion option lists the SQL Server version as major.minor.build.revision. The most ideal approach to verify whether administration packs, aggregate redesigns or hotfixes have been connected is to use ProductVersion. In a perfect world, DBAs are constantly mindful of cluster failovers or adaptation changes, yet capturing information from SERVERPROPERTY allows authoritative confirmation and, every now and then, we can utilize it to affirm the elements that we can preclude.

Listing 6 creates a ServerConfig table, in which to store the server information.

USE DistributionData;
GO

IF NOT EXISTS ( SELECT  *
                FROM    [sys].[tables]
                WHERE   [name] = N'ServerConfig'
                        AND [type] = N'U' ) 
    CREATE TABLE [dbo].[ServerConfig]
        (
          [Property] NVARCHAR(128) ,
          [Value] SQL_VARIANT ,
          [CaptureDate] DATETIME
        );
GO

CREATE CLUSTERED INDEX CI_ServerConfig ON [dbo].[ServerConfig]   ([CaptureDate],[Property]);

Listing 6: Create the ServerConfig table

For the configuration data, we have to create a job to log frequently log the settings to the dbo.ServerConfig table. Since we have to gather this information on generally the same schedule with respect to the configuration data (i.e. day by day), we can basically attach the code in Listing 7 to the same job.

USE DistributionData;
GO

SET NOCOUNT ON;

BEGIN TRANSACTION;
INSERT  INTO [dbo].[ServerConfig]
        ( [Property] ,
          [Value]
        )
        EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',
            N'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
            N'ProcessorNameString';
UPDATE  [dbo].[ServerConfig]
SET     [CaptureDate] = GETDATE()
WHERE   [Property] = N'ProcessorNameString'
        AND [CaptureDate] IS NULL;
COMMIT;

INSERT  INTO [dbo].[ServerConfig]
        ( [Property] ,
          [Value] ,
          [CaptureDate]
        )
        SELECT  N'MachineName' ,
                SERVERPROPERTY('MachineName') ,
                GETDATE();
INSERT  INTO [dbo].[ServerConfig]
        ( [Property] ,
          [Value] ,
          [CaptureDate]
        )
        SELECT  N'ServerName' ,
                SERVERPROPERTY('ServerName') ,
                GETDATE();
INSERT  INTO [dbo].[ServerConfig]
        ( [Property] ,
          [Value] ,
          [CaptureDate]
        )
        SELECT  N'InstanceName' ,
                SERVERPROPERTY('InstanceName') ,
                GETDATE();
INSERT  INTO [dbo].[ServerConfig]
        ( [Property] ,
          [Value] ,
          [CaptureDate]
        )
        SELECT  N'IsClustered' ,
                SERVERPROPERTY('IsClustered') ,
                GETDATE();
INSERT  INTO [dbo].[ServerConfig]
        ( [Property] ,
          [Value] ,
          [CaptureDate]
        )
        SELECT  N'ComputerNamePhysicalNetBios' ,
                SERVERPROPERTY('ComputerNamePhysicalNetBIOS') ,
                GETDATE();
INSERT  INTO [dbo].[ServerConfig]
        ( [Property] ,
          [Value] ,
          [CaptureDate]
        )
        SELECT  N'Edition' ,
                SERVERPROPERTY('Edition') ,
                GETDATE();
INSERT  INTO [dbo].[ServerConfig]
        ( [Property] ,
          [Value] ,
          [CaptureDate]
        )
        SELECT  N'ProductLevel' ,
                SERVERPROPERTY('ProductLevel') ,
                GETDATE();
INSERT  INTO [dbo].[ServerConfig]
        ( [Property] ,
          [Value] ,
          [CaptureDate]
        )
        SELECT  N'ProductVersion' ,
                SERVERPROPERTY('ProductVersion') ,
                GETDATE();

DECLARE @TRACESTATUS TABLE
    (
      [TraceFlag] SMALLINT ,
      [Status] BIT ,
      [Global] BIT ,
      [Session] BIT
    );

INSERT  INTO @TRACESTATUS
        EXEC ( 'DBCC TRACESTATUS (-1)'
            );

IF ( SELECT COUNT(*)
     FROM   @TRACESTATUS
   ) > 0 
    BEGIN;
        INSERT  INTO [dbo].[ServerConfig]
                ( [Property] ,
                  [Value] ,
                  [CaptureDate]
                )
                SELECT  N'DBCC_TRACESTATUS' ,
                        'TF ' + CAST([TraceFlag] AS VARCHAR(5))
                        + ': Status = ' + CAST([Status] AS VARCHAR(1))
                        + ', Global = ' + CAST([Global] AS VARCHAR(1))
                        + ', Session = ' + CAST([Session] AS VARCHAR(1)) ,
                        GETDATE()
                FROM    @TRACESTATUS
                ORDER BY [TraceFlag];
    END;
ELSE 
    BEGIN;
        INSERT  INTO [dbo].[ServerConfig]
                ( [Property] ,
                  [Value] ,
                  [CaptureDate]
                )
                SELECT  N'DBCC_TRACESTATUS' ,
                        'No trace flags enabled' ,
                        GETDATE()
    END;

Listing 7: Logging Server configuration data

Everything All we need now is a stored procedure that will permit us to audit the information caught for the accessible properties (listed inside of the procedure).

USE [DistributionData];
GO

IF OBJECTPROPERTY(OBJECT_ID(N'usp_ServerConfigReport'), 'IsProcedure') = 1 
    DROP PROCEDURE usp_ServerConfigReport;
GO

CREATE PROCEDURE dbo.usp_ServerConfigReport
    (
      @Property NVARCHAR(128) = NULL
    )
AS 
    BEGIN;
        IF @Property NOT IN ( N'ComputerNamePhysicalNetBios',
                              N'DBCC_TRACESTATUS', N'Edition',
                              N'InstanceName',
                              N'IsClustered', N'MachineName',
                              N'ProcessorNameString', N'ProductLevel',
                              N'ProductVersion', N'ServerName' ) 
            BEGIN;
                RAISERROR(N'Valid values for @Property are:
                            ComputerNamePhysicalNetBios, DBCC_TRACESTATUS,
                            Edition, InstanceName, IsClustered,
                            MachineName, ProcessorNameString,
                            ProductLevel, ProductVersion, or ServerName',
                         16, 1);
                RETURN;
            END;

        SELECT  *
        FROM    [dbo].[ServerConfig]
        WHERE   [Property] = ISNULL(@Property, Property)
        ORDER BY [Property] ,
                [CaptureDate]
    END;

Listing 8: Creating the dbo.usp_ServerConfigReport stored procedure

We can execute the stored procedure with no data parameters, which will give back all rows, or we can return values for a particular property.

-- return all rows
EXEC dbo.usp_ServerConfigReport
-- return information for a specific property
EXEC dbo.usp_ServerConfigReport N'ComputerNamePhysicalNetBios'

Listing 9: Executing the dbo.usp_ServerConfigReport stored procedure

Collecting Performance Data

Baseline data turns out to be valuable to a great degree while troubleshooting performance issues and the SQL Server DMVs give an abundance of data. For instance, we can recover SQL Server counter information from the sys.dm_os_performance_counter DMV. There are various counters from which to pick, and the scripts here will log just a chosen few. Just review the counters captured and change them as you see fit so you capture the set that’s most important for your environment. Once more, a survey of the considerable number of counters, and the normal or prescribed value for each, is outside the extent of this article.

Listing 10 creates a log table for the counters.

USE [DistributionData];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF NOT EXISTS ( SELECT  *
                FROM    [sys].[tables]
                WHERE   [name] = N'PerfMonData'
                        AND [type] = N'U' ) 
    CREATE TABLE [dbo].[PerfMonData]
        (
          [Counter] NVARCHAR(770) ,
          [Value] DECIMAL(38, 2) ,
          [CaptureDate] DATETIME,
        )
    ON  [PRIMARY];
GO

CREATE CLUSTERED INDEX CI_PerfMonData ON [dbo].[PerfMonData] ([CaptureDate],[Counter]);

CREATE NONCLUSTERED INDEX IX_PerfMonData ON [dbo].[PerfMonData] ([Counter], [CaptureDate]) INCLUDE ([Value]);

Listing 10: Create the PerfMonData logging table

Kindly note the following warnings when you create the indexes:

Warning! The maximum key length is 900 bytes. The index 'CI_PerfMonData' has maximum length of 1548 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'IX_PerfMonData' has maximum length of 1548 bytes. For some combination of large values, the insert/update operation will fail.

SQL server issues these warnings in light of the fact that the datatype for the Counter section is nvarchar and can expend up to 1540 bytes. By outline, the segment can deal with the most extreme sizes of the object_name, counter_name and instance_name sections linked (fromsys.dm_os_performance_counters). Neighborhood testing did not discover any events of a most extreme length above 900 bytes, however in the event that this happens in your surroundings then you should truncate the sections while catching them.

Make another job to execute the script in Listing 11, which logs the counters to the dbo.PerfMonData table. Because of the way of a few counters, a postponement is hard-coded into the script with the goal that it can catch two examples of the counter, and after that ascertain the required counter esteem. The deferral is as of now 10 seconds, but you can alter this as sought. To better comprehend the diverse counter sorts and how they are figured, please see Amit Banerjee’s post, “What does cntr_type mean?,” or Chapter 7 of the book Performance Tuning with SQL Server Dynamic Management Views, by Louis Davidson and Tim Ford (accessible as a free eBook).

We have to catch execution counters more often than arrangement data (e.g. each 1, 5 or 10 minutes). Acknowledge, however, that there is a tradeoff between recurrence of gathering and the amount of information. The more often we gather information, the more granular our data, which may be helpful when investigating or distinguishing patterns. Be that as it may, this likewise makes more information and may be more awkward to oversee. At the point, when choosing how often to log the information to a table, consider how frequently you will use this data and to what extent you plan to keep it. Like clockwork is ordinarily a decent place to begin.

USE [DistributionData];
GO

SET NOCOUNT ON;

DECLARE @PerfCounters TABLE
    (
      [Counter] NVARCHAR(770) ,
      [CounterType] INT ,
      [FirstValue] DECIMAL(38, 2) ,
      [FirstDateTime] DATETIME ,
      [SecondValue] DECIMAL(38, 2) ,
      [SecondDateTime] DATETIME ,
      [ValueDiff] AS ( [SecondValue] - [FirstValue] ) ,
      [TimeDiff] AS ( DATEDIFF(SS, FirstDateTime, SecondDateTime) ) ,
      [CounterValue] DECIMAL(38, 2)
    );

INSERT  INTO @PerfCounters
        ( [Counter] ,
          [CounterType] ,
          [FirstValue] ,
          [FirstDateTime]
        )
        SELECT  RTRIM([object_name]) + N':' + RTRIM([counter_name]) + N':'
                + RTRIM([instance_name]) ,
                [cntr_type] ,
                [cntr_value] ,
                GETDATE()
        FROM    sys.dm_os_performance_counters
        WHERE   [counter_name] IN ( N'Page life expectancy',
                                    N'Lazy writes/sec', N'Page reads/sec',
                                    N'Page writes/sec', N'Free Pages',
                                    N'Free list stalls/sec',
                                    N'User Connections',
                                    N'Lock Waits/sec',
                                    N'Number of Deadlocks/sec',
                                    N'Transactions/sec',
                                    N'Forwarded Records/sec',
                                    N'Index Searches/sec',
                                    N'Full Scans/sec',
                                    N'Batch Requests/sec',
                                    N'SQL Compilations/sec',
                                    N'SQL Re-Compilations/sec',
                                    N'Total Server Memory (KB)',
                                    N'Target Server Memory (KB)',
                                    N'Latch Waits/sec' )
        ORDER BY [object_name] + N':' + [counter_name] + N':'
                + [instance_name];

WAITFOR DELAY '00:00:10';

UPDATE  @PerfCounters
SET     [SecondValue] = [cntr_value] ,
        [SecondDateTime] = GETDATE()
FROM    sys.dm_os_performance_counters
WHERE   [Counter] = RTRIM([object_name]) + N':' + RTRIM([counter_name])
                                                                  + N':'
        + RTRIM([instance_name])
        AND [counter_name] IN ( N'Page life expectancy', 
                                N'Lazy writes/sec',
                                N'Page reads/sec', N'Page writes/sec',
                                N'Free Pages', N'Free list stalls/sec',
                                N'User Connections', N'Lock Waits/sec',
                                N'Number of Deadlocks/sec',
                                N'Transactions/sec',
                                N'Forwarded Records/sec',
                                N'Index Searches/sec', N'Full Scans/sec',
                                N'Batch Requests/sec',
                                N'SQL Compilations/sec',
                                N'SQL Re-Compilations/sec',
                                N'Total Server Memory (KB)',
                                N'Target Server Memory (KB)',
                                N'Latch Waits/sec' );

UPDATE  @PerfCounters
SET     [CounterValue] = [ValueDiff] / [TimeDiff]
WHERE   [CounterType] = 272696576;

UPDATE  @PerfCounters
SET     [CounterValue] = [SecondValue]
WHERE   [CounterType] <> 272696576;

INSERT  INTO [dbo].[PerfMonData]
        ( [Counter] ,
          [Value] ,
          [CaptureDate]
        )
        SELECT  [Counter] ,
                [CounterValue] ,
                [SecondDateTime]
        FROM    @PerfCounters;

Listing 11: Logging Performance Counter data

Once we notice the job is executing regularly, we then review the information with the stored procedure in Listing 12.

USE [DistributionData];
GO
IF OBJECTPROPERTY(OBJECT_ID(N'usp_PerfMonReport'), 'IsProcedure') = 1 
    DROP PROCEDURE usp_PerfMonReport;
GO
CREATE PROCEDURE dbo.usp_PerfMonReport
    (
      @Counter NVARCHAR(128) = N'%'
    )
AS 
    BEGIN;
        SELECT  *
        FROM    [dbo].[PerfMonData]
        WHERE   [Counter] LIKE @Counter
        ORDER BY [Counter] ,
                [CaptureDate]
    END;

Listing 12: Creating the dbo.usp_PerfMonReport stored procedure

The stored procedure will then return all the data in the table, or information for only one counter based on the input.

-- return information all counters 
EXEC dbo.usp_PerfMonReport 

-- return information for a specific counter
EXEC dbo.usp_PerfMonReport N'%Page life expectancy%'

Listing 13: Returning performance counter data

Purging Historical Data

After some time, we’ll collect a high volume of data and we ought to purge it occasionally. It is dependent upon you to choose up to what extent to keep the data, and you can keep the configuration information longer than performance data, as there is less data in the long run and it is not captured as often.

The PurgeOldData stored procedure in Listing 14 acknowledges two input values, which connote the number of days’ worth of data to keep, for configuration and performance counter data respectively.

USE [DistributionData];
GO

IF OBJECTPROPERTY(OBJECT_ID(N'usp_PurgeOldData'), 'IsProcedure') = 1 
    DROP PROCEDURE usp_PurgeOldData;
GO

CREATE PROCEDURE dbo.usp_PurgeOldData
    (
      @PurgeConfig SMALLINT ,
      @PurgeCounters SMALLINT
    )
AS 
    BEGIN;
        IF @PurgeConfig IS NULL
            OR @PurgeCounters IS NULL 
            BEGIN;
                RAISERROR(N'Input parameters cannot be NULL', 16, 1);
                RETURN;
            END;
        DELETE  FROM [dbo].[ConfigData]
        WHERE   [CaptureDate] < GETDATE() - @PurgeConfig;

        DELETE  FROM [dbo].[ServerConfig]
        WHERE   [CaptureDate] < GETDATE() - @PurgeConfig;

        DELETE  FROM [dbo].[PerfMonData]
        WHERE   [CaptureDate] < GETDATE() - @PurgeCounters;
    END;

Listing 14: Purging old data

To retain the most recent 30 days of setup data, and the most recent 7 days of performance counter data, execute:

EXEC dbo.usp_PurgeOldData 30, 7

Conclusion

The scripts in this article are for SQL Server 2005, SQL Server 2008 and higher and represent only the beginning of the baseline information it’s possible to capture about a system. Feel free to tailor and expand upon these scripts as needed for your solutions. Be sure to include all of these tables in your regular index maintenance. In particular, the non-clustered index on the dbo.PerfMonData table will fragment rapidly, but it exists to optimize reporting on the performance counters.