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:
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:
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:
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!
Featured Online Courses:
- AI Essentials: A Beginner’s Guide to Artificial Intelligence
- SQL Server 2022: What’s New – New and Enhanced Features
- Working with Python on Windows and SQL Server Databases
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- A Guide on How to Start and Monetize a Successful Blog
- SQL Server Installation and Setup Best Practices
- The TempDB System Database in SQL Server
- SQL Server 2016: TempDB Enhancements
- tempdb growth
- Introduction to SQL Server Machine Learning Services
- Essential SQL Server Administration Tips
- Within Which Context Does SQL Server Access Network Resources?
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:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior Database and Software Architect, Certified Database, Cloud and AI professional, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Moreover, Artemakis teaches on Udemy, you can check his courses here.