Statistics

Hi
Today You will know about statistics
What are statistics?
It collects about the distribution of data in columns and Indexes.This data is used for Query optimizer to determine the plan.
In most cases SQL Server manages the statistics automatically.
Automatic Statistics :
By Default SQL Server databases automatically create and update statistics.YOu can disable this but better to keep it.SQL Server needs this statistics to do a good job with Query processing.

Generally Statistics contain the below information:

• Number of rows & pages occupied by tables data
• Time that statistics were last updated
• The average length of keys in column
• Histograms showing the distribution of data in column
• String summaries used when performing LIKE Queries on character data

How to  See Statistics :
Statistics Can be seen in two ways through T-SQL and SSMS

T-SQL : By using sp_helpstats system procedure you can see statistics in a single table

Ex: In this example Iam using SALES.SALESORDERDETAIL table in ADVENTUREWORKS Database.

sp_helpstats returns statistics information on specified table. It is having two values.In the above figure I used Stats to retreive the information only lists to statistics not associated with index.

In the above picture I used ALL value to lists statistics for all indexes and columns.

If you want detailed information for an Individual statistics you can also use DBCC SHOW_STATISTICS.

DBCC SHOW_STATISTICS ('Tablename','Statisticsname')

SSMS :

Connect to a database and expand the Object Explorer tree until you find the table of interest, then drill into the Statistics folder underneath the table. There you'll find a graphical representation of all of the statistics on the table, as shown in Figure 

 

If you right-click on one of these statistics and select Properties, you'll see the general information on the statistic

 Updating Statistics :

The data in a database is not static. Even statistics are also not fixed. As you add,delete,update rows SQL Server needs to adjust the statistics so that they still provide good guidance for the query optimizer. When you run a query, SQL Server checks statistics used in that query's plan to see whether they're outdated.

Normally statistics will get outdated in the below ways :

• If row count in table has gone from zero to any non zero value
• If table is having 500 rows and you need to modify 500 rows (add,delete,update)
• If table is having more than 500 rows and at least 500 plus 20% of the row count rows have modified.

 How to Update Statistics :

By using T-SQL you can update statistics manually.

Updating all statistics for a single table:

The following example updates the distribution statistics for all indexes on the SalesOrderDetail table.
 USE AdventureWorks;
GO
UPDATE STATISTICS Sales.SalesOrderDetail;
GO
Updating only the statistics for a single index:

The following example updates only the distribution information for the AK_SalesOrderDetail_rowguid index of the SalesOrderDetail table.
USE AdventureWorks;
GO
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;
GO
Updating statistics for specific statistics groups (collections) by using 50 percent sampling:
The following example creates and then updates the statistics group for the Name and ProductNumber columns in the Product table.

USE AdventureWorks;
GO
CREATE STATISTICS Products
    ON Production.Product ([Name], ProductNumber)
    WITH SAMPLE 50 PERCENT
-- Time passes. The UPDATE STATISTICS statement is then executed.
UPDATE STATISTICS Production.Product(Products)
    WITH SAMPLE 50 PERCENT;;
Updating statistics for a specific statistics groups (collections) by using FULLSCAN and NORECOMPUTE:

The following example updates the Products statistics group (collection) in the Product table, forces a full scan of all rows in the Product table, and turns off automatic statistics updating for the statistics group (collection).

USE AdventureWorks;
GO
UPDATE STATISTICS Production.Product(Products)
    WITH FULLSCAN, NORECOMPUTE;
GO

As you can see, you can update all of the statistics for a table or view, or pick a particular statistic to update by index name or statistic name. You can also specify some update options:

FULLSCAN forces a complete read of every row of data.
SAMPLE lets you specify how much of the data SQL Server should sample in building statistics. By default, it will figure out its own sampling coverage.
RESAMPLE uses the same sampling rate as the last time the statistic was gathered.
ALL, COLUMNS, or INDEX specify whether the update should be to all statistics, column statistics, or index statistics. The default is all statistics.
NORECOMPUTE turns off automatic statistics gathering for this table or statistic.