Statistic Repository

I have seen posts from many great SQL masters saying that the first thing they do when diagnosing a performance problem is to update the stats or review the stats; I too have seen the power of updated stats, so when an internal customer of mine was having some issues with stats, it became helpful for me to create a Stats Repository to track and monitor changes in statistics.

The idea is, record once a day the stats info on each statistic in a given database, this can be done across servers (Prod, Test, Dev, etc) if you want to compare to see why something runs better on one server and not on another.

The first step is to create a holding table, if you have a DBA database to store maintenance stuff, put it there, but for this post I'll use master:

------------------------------------------------------
-- SETUP STORAGE TABLE TO STORE DAILY STATS DETAILS --
------------------------------------------------------
USE [master]
GO

/****** Object:  Table [dbo].[StatsHistory]  ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[StatsHistory](
 [ServerName] [sysname] NULL,
 [DatabaseName] [sysname] NULL,
 [TableName] [sysname] NULL,
 [StatName] [sysname] NOT NULL,
 [Updated] [datetime] NULL,
 [TableRows] [int] NULL,
 [RowsSampled] [int] NULL,
 [PercentSampled] [float] NULL,
 [Steps] [int] NULL,
 [Density] [int] NULL,
 [AverageKeyLength] [int] NULL,
 [StringIndex] [varchar](3) NULL,
 [FilterExpression] [varchar](512) NULL,
 [UnfilteredRows] [int] NULL,
 [StatColumns] [varchar](500) NULL,
 [BatchRunID] [int] NULL,
 [CreateDate] [datetime] NULL
) ON [PRIMARY]

The next step is to create a function that will be used to concatenate the columns for the Stat into a list. Because of how sys.stats_columns works, you'll need to put this in every database you want to monitor

-----------------------------------------------------
-- NEEDS TO BE RUN FOR EVERY DB YOU ARE MONITORING --
--     THIS FUNCTION CREATES A LIST OF COLUMNS     --
--       INCLUDED IN THE PASSED IN STATISTIC       --
-----------------------------------------------------

USE []
GO

/****** Object:  UserDefinedFunction [dbo].[StatColumnIDToList]    Script Date: 12/19/2014 10:11:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Eric Zierdt
-- Create date: 12/9/2014
-- Description: Converts the ColumnIDs to a List
-- USE: SELECT dbo.StatColumnIDToList(357576312,2)
-- =============================================
CREATE FUNCTION [dbo].[StatColumnIDToList] 
(
 -- Add the parameters for the function here
 @ObjectID INT
 ,@StatsID INT
)
RETURNS VARCHAR(2000)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @List VARCHAR(2000)

 SELECT @List = COALESCE(@List + ',', '') + Cast(SC.column_id As varchar(5))
 FROM sys.stats_columns SC 
 WHERE SC.object_id = @ObjectID AND SC.stats_id = @StatsID

 -- Return the result of the function
 RETURN @List

END

The next step is to create a job which you will schedule to run daily. This job should execute the following code to generate the stat info and insert it into the table we made in step 1:

-- =================================================
-- Author:  Eric Zierdt
-- Create date: 12/9/2014
-- Description: INSERTS STATS DATA INTO REPOSITORY
-- =================================================
DECLARE @ServerName VARCHAR(120)
  ,@DBName VARCHAR(120)
  ,@TableName VARCHAR(120)
  ,@StatName VARCHAR(120)
  ,@SQL VARCHAR(MAX)
  ,@BatchRunID INT
  ,@ColumnList VARCHAR(2000)
  
SELECT @BatchRunID = MAX(BatchRunID)+1
FROM master.dbo.StatsHistory
  

CREATE TABLE #StatsTable (
 ServerName SYSNAME NULL
 ,DatabaseName SYSNAME NULL
 ,TableName SYSNAME NULL
 ,StatName SYSNAME
 ,Updated DATETIME
 ,TableRows INT
 ,RowsSampled INT
 ,PercentSampled AS CAST(RowsSampled AS FLOAT)/CAST(TableRows AS FLOAT)*100 
 ,Steps INT
 ,Density INT
 ,AverageKeyLength INT
 ,StringIndex VARCHAR(3)
 ,FilterExpression VARCHAR(512)
 ,UnfilteredRows INT
 ,StatColumns VARCHAR(500)
 ,BatchRunID INT
 ,CreateDate DATETIME
)

CREATE NONCLUSTERED INDEX [IDX_StatTable_Name] ON #StatsTable
(
 [TableName] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF
  , ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Primary]

DECLARE ZCursor CURSOR LOCAL FAST_FORWARD FOR
(
    SELECT  @@SERVERNAME AS ServerName, DB_NAME() AS DBName, OBJECT_NAME(object_id) AS TableName 
           ,name AS StatName, dbo.StatColumnIDToList(object_id,stats_id)
    FROM    sys.stats
    WHERE   OBJECT_NAME(object_id) NOT LIKE ''sys%''
            AND OBJECT_NAME(object_id) NOT LIKE ''MS%''
            AND OBJECT_NAME(object_id) NOT LIKE ''queue%'' 
            AND OBJECT_NAME(object_id) NOT LIKE ''filestream%'' 
            --AND OBJECT_NAME(object_id) = ''MSdistribution_agents''
)
OPEN ZCursor 
FETCH NEXT FROM ZCursor INTO @ServerName, @DBName, @TableName,@StatName,@ColumnList
 WHILE @@FETCH_STATUS = 0
 BEGIN
  --PRINT @StatName
  SET @SQL = ''DBCC SHOW_STATISTICS ('' + @TableName+ '',"'' + @StatName +''") WITH STAT_HEADER,NO_INFOMSGS ;''
  --PRINT @SQL
  INSERT INTO #StatsTable
  (
   StatName
   ,Updated
   ,TableRows
   ,RowsSampled
   ,Steps
   ,Density
   ,AverageKeyLength
   ,StringIndex
   ,FilterExpression
   ,UnfilteredRows
  )
  EXEC(@SQL)

  UPDATE #StatsTable
  SET TableName = @TableName
   ,DatabaseName = @DBName
   ,ServerName = @ServerName
   ,BatchRunID = @BatchRunID
   ,StatColumns = @ColumnList
   ,CreateDate = GETDATE()
  WHERE TableName IS NULL

  FETCH NEXT FROM ZCursor INTO @ServerName, @DBName, @TableName,@StatName,@ColumnList
 END

CLOSE ZCursor
DEALLOCATE ZCursor

--SELECT * FROM #StatsTable WHERE TableRows <> RowsSampled ORDER BY TableName

INSERT INTO master.dbo.StatsHistory
(
 ServerName
 ,DatabaseName
 ,TableName
 ,StatName
 ,Updated
 ,TableRows
 ,RowsSampled
 ,PercentSampled
 ,Steps
 ,Density
 ,AverageKeyLength
 ,StringIndex
 ,FilterExpression
 ,UnfilteredRows
 ,StatColumns
 ,BatchRunID
 ,CreateDate 
)
SELECT ServerName
  ,DatabaseName
  ,TableName
  ,StatName
  ,Updated
  ,TableRows
  ,RowsSampled
  ,PercentSampled
  ,Steps
  ,Density
  ,AverageKeyLength
  ,StringIndex
  ,FilterExpression
  ,UnfilteredRows
  ,StatColumns
  ,BatchRunID
  ,CreateDate 
FROM #StatsTable

DROP TABLE #StatsTable

Now you can do some fun things with your repository table, like this:

-- =====================================================
-- Author:  Eric Zierdt
-- Create date: 12/9/2014
-- Description: SAMPLE CODE TO QUERY STATS REPOSITORY
-- =====================================================
DECLARE @BatchRunID INT
SELECT  @BatchRunID = MAX(BatchRunID) FROM master.dbo.StatsHistory

/**  SEE ALL STATS **/
SELECT  *
FROM    master.dbo.StatsHistory
WHERE BatchRunID = @BatchRunID

/**  SEE ALL STATS OLDER THAN 1 MONTH  **/
SELECT  *
FROM    master.dbo.StatsHistory
WHERE BatchRunID = @BatchRunID
  AND Updated < DATEADD(MONTH,-1,GETDATE())

/**  SEE ALL NON-FULLSCAN STATS  **/
SELECT  *
FROM    master.dbo.StatsHistory
WHERE BatchRunID = @BatchRunID
  AND PercentSampled < 100


/**  CREATE UPDATE STAT SCRIPT FOR ALL NON-FULLSCAN STATS  **/
SELECT  TableName, StatName, TableRows, PercentSampled, 'UPDATE STATISTICS dbo.' + TableName + ' ' + StatName + ' WITH FULLSCAN --' + CAST(TableRows AS VARCHAR(10)) AS SQLCode
FROM    master.dbo.StatsHistory
WHERE   BatchRunID = @BatchRunID
  AND PercentSampled < 100

Extended Events with a view

So last time I wrote, I talked about my interest in Extended Events; I have been playing with them a bit since then and I came across something that made them so much easier to work with. This is not my own idea, but I wanted to share it as it blew my mind away with how simple it is, yet how I never thought of it. The idea is to take your ugly XQuery code and put it in a view, and to make it even better, create a Schema just for your Extended Events. I created a Schema called "XE" then when I want to get data from an Extended Event, I just have to type SELECT * FROM [XE]. and I let IntelliSense or SQLPrompt fill out the available options. This essentially makes querying Extended Events as easy as using a DMV. So in my last post, we created a Extended Even called "XE_Log_Shrink", If I wanted to implement this idea for this XE, I would do the following: Create the Schema (if I haven't previously done this):

USE [master]
GO
CREATE SCHEMA [XE] AUTHORIZATION [dbo]
GO

Create the View (using the XQuery from my previous post:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/************************************************************
 * AUTHOR: Eric Zierdt          *
 * CREATED: 05/08/2012          *
 * USAGE: SELECT * FROM [XE].[vw_Log_Shrink]  *
 ************************************************************/
CREATE VIEW [XE].[vw_Log_Shrink]
AS
 
WITH Data
AS (
 SELECT CAST(target_data AS XML) AS TargetData
 FROM sys.dm_xe_session_targets dt
 JOIN  sys.dm_xe_sessions ds ON ds.address = dt.event_session_address
 JOIN  sys.server_event_sessions ss ON ds.Name = ss.Name
 WHERE dt.target_name = 'ring_buffer'
  AND ds.Name = 'XE_Log_Shrink'
)
SELECT  DATEADD(hour,-5,XEventData.XEvent.value('(@timestamp)[1]', 'datetime'))  AS event_timestamp
   ,XEventData.XEvent.value('@name', 'varchar(4000)')       AS event_name
   ,DB_NAME(XEventData.XEvent.value('(data/value)[2]', 'VARCHAR(100)'))  AS DatabaseName
   ,XEventData.XEvent.value('(action/value)[2]', 'VARCHAR(512)')    AS client_hostname
   ,XEventData.XEvent.value('(action/value)[3]', 'VARCHAR(512)')    AS nt_username
   ,XEventData.XEvent.value('(action/value)[6]', 'VARCHAR(512)')    AS username
   ,XEventData.XEvent.value('(action/value)[5]', 'VARCHAR(512)')    AS sql_text
   ,XEventData.XEvent.value('(action/value)[4]', 'VARCHAR(512)')    AS session_id
   ,XEventData.XEvent.value('(action/value)[1]', 'VARCHAR(512)')    AS client_app_name
FROM Data d
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent )

GO

Thats all you have to do, now you can query this like a DMV, just write:

SELECT *
FROM [XE].[vw_Log_Shrink]
WHERE [event_timestamp] > '5/8/2012'

Let me know your thoughts.

Sync Config Settings between Servers

I'm setting up a new Staging Environment and am trying to find ways we can sync Staging with our production environment. My current task is to Sync the configurations and settings. Here is the script I came up with for the Config Settings. Hope it's of use.

/***************************************************
 ** Title:  Sync Config Settings between Servers  **
 ** Author: Eric Zierdt                           **
 ** Date:   03/10/2011                            **
 ***************************************************/

/**************************************
 ** STEP 1: Turn on Advanced Options **
 **************************************/
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO


/*******************************************
 ** STEP 2: Populate the values from Prod **
 *******************************************/
-- SELECT * INTO #TMP_PROD_Configs FROM ProductionServer.msdb.sys.configurations C


/*****************************************
 ** STEP 3: Run Cursor and copy results **
 *****************************************/
DECLARE @SQL VARCHAR(MAX)
  ,@name VARCHAR(300)
  ,@value SQL_VARIANT

DECLARE SYNC_CURSOR CURSOR FOR
 SELECT TOC.name, TOC.value
 FROM sys.configurations C
 LEFT JOIN #TMP_PROD_Configs TOC ON C.configuration_id = TOC.configuration_id
 WHERE C.value <> TOC.VALUE
   AND C.configuration_id NOT IN (1543,1544)

OPEN SYNC_CURSOR
FETCH NEXT FROM SYNC_CURSOR 
INTO @name, @value
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @SQL = '
sp_configure ''' + @Name + ''', ' + CAST(@Value AS VARCHAR(25)) + '
GO
RECONFIGURE
GO
'
PRINT @SQL

 FETCH NEXT FROM SYNC_CURSOR 
 INTO @name, @value
END
CLOSE SYNC_CURSOR 
DEALLOCATE SYNC_CURSOR


/*************************************************
 ** STEP 4: Paste results below, review and run **
 *************************************************/





/**********************************************
 ** STEP 5: Be good and drop your temp table **
 **********************************************/
DROP TABLE #TMP_PROD_Configs


/***************************************
 ** STEP 6: Turn off Advanced Options **
 ***************************************/
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO


/***************************************
 ** Query to compare values if needed **
 ***************************************/
SELECT C.configuration_id, C.name, C.value,C.value_in_use, C.description
  , TOC.configuration_id, TOC.name, TOC.value,TOC.value_in_use, TOC.description
FROM sys.configurations C
LEFT JOIN #TMP_PROD_Configs TOC ON C.configuration_id = TOC.configuration_id
WHERE C.value <> TOC.VALUE
  AND C.configuration_id NOT IN (1543,1544)