SQL Server Statistics Collection and Analysis


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


Monitor the performance of Azure SQL Database, Amazon RDS for SQL Server, and SQL Server

Free MSSQLTips Webinar: Monitor the performance of Azure SQL Database, Amazon RDS for SQL Server, and SQL Server

Attend this webinar to learn how to manage the performance of SQL Server not only on-premises but also in the cloud for Azure SQL Database, Amazon RDS for SQL Server as well as SQL Server using a single tool.


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.

database objects

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

sample data

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

Next Steps





get scripts

next tip button



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

View all my tips


Article Last Updated: 2021-07-16

Comments For This Article




Friday, July 16, 2021 - 1:01:46 PM - JEREMY KADLEC Back To Top (89021)
Steve,

Thank you for letting us know. We have updated the tip.

Thank you,
Jeremy Kadlec
Community Co-Leader

Friday, July 16, 2021 - 11:29:52 AM - Steve Scanlan Back To Top (89020)
Hi, the link to the scripts is broken?


download














get free sql tips
agree to terms