What is Statistic in Sql Server Part - 1

What is Statistic in Sql Server Part - 2

What is Statistic in Sql Server Part - 3

                                                          One of my favorite question during interviews is "What is SQL server Statistic". people do understands statistics but there is huge gap when it comes to real understanding in SQL server.

Statistics is a data collected by SQL engine about your data in data files to determine how it’s going to satisfy your SQL query.  statistics represent the distribution of the data within a column, or columns.

Statistics helps Sql engine to measure data in two different ways .  

1. Density : It is a ratio that shows just how many unique values there are within a given column. Lower density gives most efficient way of getting at your data
2. Distribution :This represents a statistical analysis of the kind of data that is in the first column available for statistics. In case of compound index its  suggested that the most selective column should be the leading edge. Here histogram is a visual representation of the distribution of the data

 Advantages :

• Query Optimizer uses them to estimate how many rows will be returned from a query plan
• If there is no statistics then sql server cant compare two plans efficiently.
• If there is no statistics sql might be force to do table or index scan.
• Statistics gives better choices to optimizer to choose how it gona acess data

SQL Server Update Stats FYI :

1.  To Check when stats was last updated :
SELECT name AS stats_name,
STATS_DATE (object_id, stats_id) AS statistics_update_date
FROM sys.stats
--> Last update Stats date 

2.  To check stats details
Syntax :
DBCC SHOW_STATISTICS ("table-name in double quotes" ,index_name ) WITH HISTOGRAM;
Example :
DBCC SHOW_STATISTICS ("emp" , PK_emp) WITH HISTOGRAM;

3.  By default, SQL Server updates index statistics automatically. Frequency of automatic updates depends on number of rows in the table. If you allow SQL Server to update statistics automatically it will use the following rules:

• a. a table has 6 or fewer rows, statistics will be updated after 6 changes
• b. a table has 500 or fewer rows, statistics will be updated after 500 changes
• c. a table has more than 500 rows, statistics will be updated after 20% of the total rows plus 500 rows are changed (INSERTED, UPDATED or DELETED)

4. SQL Server uses the rowmodctr column of the sysindexes table to determine the number of changes since the last update of statistics.

5. Several ways to turn off automatic update of statistics for a particular index or table. You can use:

• autostats system procedure
• STATISTICS_NORECOMPUTE option of CREATE INDEX
• RECOMPUTE option with CREATE STATISTICS
• RECOMPUTE option with UPDATE STATISTICS

6. You can update statistics manually by executing the UPDATE STATISTICS statement against a single table, indexed view or a particular index.

• Avoid FULLSCAN option unless you have truncated the table and repopulated it without rebuilding indexes.
• SAMPLE number / percent: if SAMPLE / FULLSCAN aren’t specified, SQL Server automatically determines the necessary sample size.

7.  sp_updatestats system procedure executes the UPDATE STATISTICS statement, but can only accept RESAMPLE as a parameter

• Starting from SQL Server 2005, sp_updatestats procedure updates statistics of only those tables that needed to be updated instead of updating all tables

8.  sp_autostats  

•  Output column: "Index Name" "AUTOSTATS" "Last Updated"
•  Can also use this procedure to turn automatic update of statistics on or off
•  Tablename without any quotes> , flagname ,

9. SQL Server has two database options; AUTO_CREATE_STATISTICSand AUTO_UPDATE_STATISTICS that control whether to create the statistics and update the statistics automatically.  In most cases these two options should always be turned ON

10. How do we know statistics is outdated : check out your “Actual Number of Rows” and “Estimated Number of Rows”. If these numbers are (consistently) fairly close, then most likely your statistics are up-to-date and used by the optimizer for the query. If not, time for you to re-check your statistics create/update frequency.

11. Rebuilding indexes does not update statistics on non-indexed columns. If you have any statistics objects which are not linked to any index then you can go with update statistics.

What is Statistic in Sql Server Part - 2

What is Statistic in Sql Server Part - 3