Sql Server – Statistics (Create, Update and delete Statistics)

Sql Server – Statistics (Create, Update and delete Statistics)

If you are reading this Article, I assume that you know the basics of Sql Server, SQL and database concepts. Let’s get started…

Statistics are data about data or metadata. Statistics contains information about data distribution in a table or column, and number of rows in table. Usually statistics are created on a single column but we can create statistics on multiple columns also. So basically statistics are the statistical information about data distribution in one or more columns in a table or an index.  Statistics creates densities in case of multi column Statistics to contain the correlation between column values.  Each Statistics creates a histogram for displaying the distribution of the values in the column or first column in case of multi column Statistics. There is an option to create filtered Statistics also,

A well defined filtered Statistics enhance the performance of the query that select data from a subset of data. Filtered Statistics itself is a huge topic and beyond this article. SO let’s focus on job in hand.

This information is used during creation of execution plan and quality of plan directly depends on the quality of statistics. For example query optimizer can use cardinality (number of records) to choose the best join option (Merge /Nested loop/Hash join) for given tables.  Here is the example of execution plan and how it uses the Statistics.

AUTO_CREATE_STATISTICS 

When AUTO_CREATE_STATISTICS option is on, Sql Server creates single column statistics for all the columns in a table.

AUTO_UPDATE_STATISTICS

When AUTO_UPDATE_STATISTICS Option is on Sql Server updates the statistics whenever it determines statistics are out of date.

INCREMENTAL STATS

When INCREMENTAL STATS is Off, Sql Server drops the existing statistics and re create them. When this option is On, it creates per partition statistics.

Why Statistics are important?

As I mentioned above, Query Optimizer use the Statistics to create the high quality execution plans.Statistics directly impact the plan quality because information about the data distribution, quantity and indexes are used by the Query Optimizer to create the plan and more accurate the information so does the plan.

How to find them?

You can check Statistics by using the Sql server management studio or dbcc show_statistics command. Below are the screen shots to find statistics in Sql server management studio.

 Server>Databases>YourDatabase>Table>statistics

Here you can see the list of all the statistics for the table.

If you want to see the details, select the statistics then right click and select Properties. You will get a pop up window which shows informations as shown in below screenshot –

You can also use the below mentioned query to get statistics for a table. Just replace YourTableName  with table name.

SELECT

        S.name   AS StatisticsName,  

      STATS_DATE(S.object_id,   S.stats_id) AS StatisticsUpdatedDate,

                  S.auto_created,

                  S.user_created,

                  S.no_recompute,

                  S.has_filter,

                  S.filter_definition,

                  S.is_temporary,

                  S.is_incremental,

                  SP.rows,

                  SP.rows_sampled,

                  SP.steps,

                  SP.unfiltered_rows,

                  SP.modification_counter

FROM sys.stats S

OUTER APPLY sys.dm_db_stats_properties(S.object_id, S.stats_id) as SP

WHERE OBJECT_NAME(S.object_id) = 'YourTableName'

ORDER BY name;

GO

How to create, delete and update Statistics?

You can create Statistics through graphical interface in Sql server management studio or by using command CREATE STATISTICS.

Select Statistics then right click and choose option New Statistics, you will get a pop up window which is show in below screenshot. Fill the details like Name, select the columns and click Ok.

Using  Command , Here is the basic syntax for that

CREATE STATISTICS InternetSalesSt1 

    ON dbo.FactInternetSales (ProductKey, SalesAmount) 

    WITH FULLSCAN; 

GO 

You can delete the Statistics in same way, select the Statistics, right click and then select delete option.  

You can also you use the Drop Statistics command, here is the syntax for that -

DROP STATISTICS dbo.FactInternetSales.InternetSalesSt1; 

GO 

 

If you want to update the Statistics , you can use the command Update Statistics.

Here is the syntax for that –

This statement updates all the Statistics for the given table in our example it is FactInternetSales table.

USE  YourDatabaseName; 

UPDATE STATISTICS dbo.FactInternetSales; 

GO 

 

If you want to update Statistics for full database , here is the syntax -

USE  YourDatabaseName; 

GO 

EXEC sp_updatestats;