Saturday, June 16, 2012

Statistics in SQL Server

Statistics will help the optimizer to generate optimized cost based estimation plan. The purpose of the optimizer is actually generates a plan which will retreive data in a faster way. Statistics are help the optimizer to understand how many rows the table has, how many indexes that table has and estimated number of rows that is going to return, it uses all those information for estimating to generate a low cost plan. If the statistics are out of date then optimizer decision might be wrong. We need to make sure statistics are update to date to boost up the query performance.

Sp_helpstats <table_name>

Dbcc show_statistics (<table_name>, <statistics_name>)

Select * from sys.stats -- Displays information of statistics that are on Indexes

Select * from sys.stats_column – Displays columns information of the statistics

Select * from sys.columns – We can identify on which column the statistics got created.

Always make sure Auto_create statistics option to be true, unless you have specific requirement on database.

Using Create Statistics option we can create user-defined statistics on the table.

drop statistics table_name.statistics name

sp_autostats '<table_name>' -- displays last updates statistics date.

No comments: