What are SQL Server Statistics and Where are they Stored - Article on SQLNetHub

What are SQL Server Statistics and Where are they Stored?

by

A few days ago, a friend asked me where are SQL Server statistics stored. Are they stored in one of SQL Server’s system databases or are they stored in each database individually?

In this short tip, we will take a look into this and answer the question, but first, let’s talk about SQL Server statistics.

 

What are SQL SQL Server Statistics?

SQL Server statistics are metadata stored in the form of binary large objects (BLOBs) in SQL Server system tables. These statistics is information related to the distribution of values in one or more columns of a table or indexed view.

 

The Importance of SQL Server Statistics

SQL Server statistics are very important since they are used by SQL Server Query Optimizer in order to build execution plans that improve the performance of queries. SQL Server Query Optimizer achieves that, by estimating the cardinality or number of rows in the query result, all these based on the statistics.

 

When are SQL Server Statistics Updated?

When an index is rebuilt, SQL Server automatically updates the relevant statistics as well. Note however, with an index rebuild, column statistics are not automatically updated. Therefore, it is a good practice, after an index rebuild, to also update the column statistics.

Here’s an example of updating the column statistics for the “Person.Person” table in the sample database “AdventureWorks2017”:

USE [AdventureWorks2017];
GO
UPDATE STATISTICS Person.Person WITH FULLSCAN, COLUMNS;
GO

 

Manually Updating SQL Server Statistics

In case you want to manually update the SQL Server statistics, you can use the “UPDATE STATISTICS” T-SQL statement (learn more).

 

Example 1 – Update Statistics for a Specific Index

USE [AdventureWorks2017];
GO
UPDATE STATISTICS Person.Person IX_Person_LastName_FirstName_MiddleName;
GO

 

Example 2 – Update All Statistics in a Table

USE [AdventureWorks2017];
GO
UPDATE STATISTICS Person.Person;
GO

 

Example 3 – Update All Statistics in a Database

USE [AdventureWorks2017];
GO
EXEC sp_updatestats;
GO

 

How to Check SQL Server Statistics

There are 2 ways for checking statistics in SQL Server: (a) via SQL Server Management Studio, and (b) using T-SQL.

 

Checking SQL Server Statistics via SSMS

In order to view table statistics in SQL Server using SSMS, you navigate to Database – Tables, you select the table for which you want to check its statistics, and then you navigate to the “Statistics” tab.

Below, you can see a screenshot with the current statistics for the table “Person.Person” of the “AdventureWorks2017” sample database:

What are SQL Server Statistics and Where are they Stored - Article on SQLNetHub

If you right-click on any of the above statistics items, you will be able to open its “Properties” dialog, and thus among other, see many useful information as well as be able to update these statistics. You can see the example below, which illustrates this:

What are SQL Server Statistics and Where are they Stored - Article on SQLNetHub

 

Checking SQL Server Statistics via T-SQL

Another way to check the table statistics in SQL Server, is using T-SQL scripts.

For example, by executing the below script, within the context of the “AdventureWorks2017” sample database, you will get the same statistics list and info as before, when we used SSMS:

SELECT 
 sp.stats_id as StatisticsID, 
 [name] as StatisticsName, 
 last_updated, 
 [rows] as TotalRows, 
 rows_sampled, 
 steps, 
 unfiltered_rows
FROM sys.stats AS s 
 CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID('Person.Person')
ORDER BY s.[name]

 

In the below screenshot, you can see what I got when ran the above T-SQL script:

What are SQL Server Statistics and Where are they Stored - Article on SQLNetHub

 

Where are SQL Server Statistics Stored?

As you might have figured out from the above T-SQL example, SQL Server statistics are stored in the sys.stats system catalog view, which contains a row for each statistics object for SQL Server tables, indexes and indexed views in the database. This catalog view, in combination with the system catalog view sys.stats_columns and the system DMV sys.dm_db_stats_properties,  provide useful information about statistics.

 

Best Practices for SQL Server Statistics Management

The best advice, when it comes to proper SQL statistics maintenance, is to always keep it up to date.

Outdated statistics, can negatively affect performance, because they might lead to not-optimized execution plans.

By default, SQL Server, automatically creates statistics on single columns in query predicates, when necessary, for improving performance. Since this database-level setting is by default set to ON, you are advised not to change it.

Another useful option in SQL Server, which is by default set to ON, is the AUTO_UPDATE_STATISTICS option. This option, allows SQL Server Database Engine, to automatically update statistics, when necessary, in order to improve performance. Again, you are advised not to change this setting.

Last but not least, another best-practice recommendation for keeping your SQL Server statistic healthy, is to create and regularly execute a maintenance plan that updates outdated statistics.

 

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Featured Online Courses:

 

Read Also:

 

Check our other related SQL Server Administration articles.

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check out our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 4.29 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

What are SQL Server Statistics and Where are they Stored? Click to Tweet

 

Views: 10,312