SQL Server Statistics Collection and Analysis

By: Alejandro Cobar   |   Updated: 2021-07-16   |   Comments (2)   |   Related: > Monitoring

Problem

I have a SQL Server query that has always worked just fine, but suddenly it is getting stuck and it’s taking forever to complete. As far as I know, absolutely nothing has changed in the query nor the structure of the table nor the SQL Server database itself, so why is it taking so long all of the sudden?

Well to be honest, I’ve heard the exact same thing not only in practice, but also in job interviews, and it is a scenario that could potentially make us turn our heads to SQL Server statistics. Therefore, the purpose of this tutorial will be to give you a way to collect all statistics information from all SQL Server instances under your support for centralized analysis.

Solution

Within this module, I present a PowerShell script that collects basic statistics information from all instances under your support. With this solution, you will have up to date information from your entire database infrastructure to help you tackle things in a proactive fashion.

Prerequisites

In order to use this module, you need to create the core objects found here.

Database Objects

For this particular module, only one table will be created to store information about the collected statistics data from each instance.

This is the structure of the table, so you can get an idea of what data will be stored. If you want to add more fields to this table, make sure to adjust the structure within the PowerShell script and adapt the respective logic that will handle the additional fields.

CREATE TABLE [monitoring].[Statistics](
    [serverId]                  [INT]NOT NULL,
    [database]                  [VARCHAR](255) NOT NULL,
    [schema]                    [VARCHAR](255) NOT NULL,
    [table]                     [VARCHAR](255) NOT NULL,
    [column]                    [VARCHAR](255) NOT NULL,
    [name]                      [VARCHAR](255) NOT NULL,
    [lastUpdatedDate]           [DATETIME] NULL,
    [totalRows]                 [BIGINT] NULL,
    [sampledRows]               [BIGINT] NULL,
    [sampleRate]                [DECIMAL](10,2) NULL,   
    [data_collection_timestamp] [DATETIME] NOT NULL
 
    CONSTRAINT PK_Statistics PRIMARY KEY CLUSTERED (serverId,[database],[schema],[table],[column],[name]),
    CONSTRAINT FK_Statistics_MasterServerList FOREIGN KEY (serverId) REFERENCES inventory.MasterServerList(serverId) ON DELETE NO ACTION ON UPDATE NO ACTION,
) ON [PRIMARY]

Table

• monitoring.Statistics
  • serverId: serverid ties back to inventory.MasterServerList
  • database: the database that houses the table relevant to the statistics information.
  • schema: the schema of the table.
  • table: the name of table that contains the actual data.
  • column: the name of the column relevant to the statistics information.
  • name: the current name of the statistics.
  • lastUpdatedDate: the name date/time when the statistics were last updated (NULL if never).
  • totalRows: the number of rows in the table.
  • sampledRows: the number of rows that were sampled in the last statistics update round.
  • sampleRate: the percent of the total rows that were sampled in the last statistics update round.
  • data_collection_timestamp: the date/time when the PowerShell script was executed to fetch the statistics information from all the instances.

PowerShell Script

The PowerShell script that creates the above object and inserts data into the monitoring.Statistics table is called:

• Get-MSSQL-Instance-Statistics.ps1

The script has some validations that will help you check if key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered.

If you have followed along the other modules, you will notice that we have been storing all objects in "C:\temp", but you can use any folder you want. If you make a change to the central folder location, you will need to edit the first line in the following PowerShell script to specify the new folder location.

How to Use

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

Option 1

• Right click on Get-MSSQL-Instance-Statistics.ps1 and select Run with PowerShell

Option 2

• Open a command window and navigate to the folder where you saved the above files and run

powershell "C:\temp\Get-MSSQL-Instance-Statistics.ps1"

Option 3

• Schedule this as a SQL Server Agent Job to run on a regular basis.

Option 4

• Schedule this as a Windows Task Scheduler job to run on a regular basis.

Check Creation of Database and Objects

After running the PowerShell script, we can see the objects that are created.

If we query the monitoring.Statistics table, we can see the data that has been collected.

This is a data sample collected from my 2 instances and a few database tables I have in them.

Important Notes

• The PowerShell script will store only the information from the last execution, no historical data is retained. If you’d like to keep information from previous executions, you would have to modify the script and adapt it to your particular use case.
• For multi-column statistics, you will see 1 entry in the table, per column, as you can see in the example screenshot I posted, where there are multiple entries for the “PK_Statistics” statistic.

Checking for Errors

To check for errors query the monitoring.ErrorLog table using the following query:

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-Statistics'

If you’d like to know the SQL Server instance that got the errors, you would have to issue the query like this:

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name 
   ELSE CONCAT(msl.server_name,'\',msl.instance) 
   END AS instance,
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Get-MSSQL-Instance-Statistics'

Useful Queries

Which statistics have never been updated?

SELECT *
FROM monitoring.[Statistics]
WHERE lastUpdatedDate IS NULL;

Display all statistics sorted by lastUpdatedDate

SELECT *
FROM monitoring.[Statistics]
ORDER BY lastUpdatedDate DESC;

Which statistics have not been updated in the past X days?

SELECT *
FROM monitoring.[Statistics]
WHERE DATEDIFF(DAY,GETDATE(),lastUpdatedDate) > X;

Which statistics have a low sample rate?

*You determine the value of X.

SELECT *
FROM monitoring.[Statistics]
WHERE sampleRate < X;

Download Scripts

• Download the scripts for this module

Next Steps

• With this information, you can craft a reporting/alerting mechanism. This will allow you to address things proactively instead of reactively.
• Check out some of the other parts of this series:
  • Monitoring SQL Server with PowerShell Core Object Setup
  • Monitoring SQL Server with PowerShell Instance Data Collection
  • Monitoring SQL Server with PowerShell Instance Jobs Collection
  • Monitoring SQL Server with PowerShell Instance Jobs Last Execution
  • Monitoring SQL Server with PowerShell Instance Backups
  • Monitoring SQL Server with PowerShell Instance Database and Database Files

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips