All about Statistics [ Part – I ]

Introduction

I got a query from one of my friends. He is asking about when to use sp_updatestats and how frequently we can use it. To answer his query, I decide to write some words related to Statistics. Hope it will be informative for all of us.

Use of Statistics Objects

We all know about the important of statistics object to make efficient execution plan by optimizer. If the statistics object is not updated properly causes poor execution plan and down the performance factors of query.

When the Statistics Object Created

When we are creating the Index the Statistics object is created automatically called the Index Statistics. These statistics will exist as long as the index exists.

Second, assuming the database option Auto Create Statistics is enabled, which it is by default, SQL Server will create single-column statistics whenever a column, which is not already the leading column in an existing index, is used in a query predicate (e.g. in the search condition of a WHERE clause, or in a JOIN condition). We refer to these as Column Statistics. We can also use the CREATE STATISTICS command to create single- and multi-column statistics manually.

When the Statistics Object Updated

When we Insert/Update/Delete records from Table objects the SQL Server automatically Insert/Update/Delete corresponding rows in the Index.

 But the Statistics is not updated like this, here we mean to say that when we Insert/Update/Delete the records from Table objects the Index updated automatically but Statistics not. Although the Auto Update Statistics option of the Database is enabled.

So we have to understand when the statistics is updated. It depends on certain volume threshold.

As data changes in our tables, the statistics - all the statistics - will be updated based on the following formula:

·         When a table with no rows gets a row

·         When 500 rows are changed to a table that is less than 500 rows

·         When 20% + 500 are changed in a table greater than 500 rows

Every time we modify a record in a table, SQL Server tracks it via the rcmodified column in a hidden system table. SQL Server 2005 tracked this information in the sys.rowsetcolumns table, In SQL Server 2008 (and later) sys.rowsetcolumns merged with sys.syshobtcolumns and became sys.sysrscols.

When we create or rebuild (not reorganize, just rebuild) an index, SQL Server generates the statistics with a FULLSCAN, i.e. it scans all the rows in the table to create a histogram that represents the distribution of data in the leading column of the index. Likewise, SQL will auto-create column statistics with a full sample.

When to Update Statistics Manually

Suppose we have table objects of 100 millions of records and SQL server is going to Update the Statistics Objects when 20% of the records of 100 million is effected by Insert/Update/Delete. So we have to wait long for Statistics Object update and result is poor exaction plan creation by SQL server. In this situation we have to update the statistics object manually.

In cases where we know data distribution in a column is "skewed", it may be necessary to update statistics manually with a full sample, or create a set of filtered statistics, in order to generate query plans of good quality.

Examining the Statistics

                            

sp_helpstats 'tbl_CUSTOMERDTLS', 'ALL'

statistics_name                     statistics_keys

---------------                   ----------------

_WA_Sys_00000001_03BB8E22           CUSTID

_WA_Sys_00000002_03BB8E22           CUSTNAME

_WA_Sys_00000003_03BB8E22           TOTALSALES

_WA_Sys_00000004_03BB8E22           GRADE

IX_CUSTID_tbl_CUSTOMERDTLS          CUSTID

The better approach

SELECT  [sch].[name] + '.' + [so].[name] AS [TableName] ,

        [si].[index_id] AS [Index ID] ,

        [ss].[name] AS [Statistic] ,

        STUFF(( SELECT  ', ' + [c].[name]

                FROM    [sys].[stats_columns] [sc]

                        JOIN [sys].[columns] [c]

                         ON [c].[column_id] = [sc].[column_id]

                            AND [c].[object_id] = [sc].[OBJECT_ID]

                WHERE   [sc].[object_id] = [ss].[object_id]

                        AND [sc].[stats_id] = [ss].[stats_id]

                ORDER BY [sc].[stats_column_id]

              FOR

                XML PATH('')

              ), 1, 2, '') AS [ColumnsInStatistic] ,

        [ss].[auto_Created] AS [WasAutoCreated] ,

        [ss].[user_created] AS [WasUserCreated] ,

        [ss].[has_filter] AS [IsFiltered] ,

        [ss].[filter_definition] AS [FilterDefinition] ,

        [ss].[is_temporary] AS [IsTemporary]

FROM    [sys].[stats] [ss]

        JOIN [sys].[objects] AS [so] ON [ss].[object_id] = [so].[object_id]

        JOIN [sys].[schemas] AS [sch] ON [so].[schema_id] = [sch].[schema_id]

        LEFT OUTER JOIN [sys].[indexes] AS [si]

              ON [so].[object_id] = [si].[object_id]

                 AND [ss].[name] = [si].[name]

WHERE   [so].[object_id] = OBJECT_ID(N'tbl_CUSTOMERDTLS')

ORDER BY [ss].[user_created] ,

        [ss].[auto_created] ,

        [ss].[has_filter];

GO

In next version we are trying to discuss more about it.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Statistics

Introduction

To improve the performance of any query the query optimizer uses the statistics to create query plan.  In most of the case the query optimizer generate the necessary statistics for high quality query plan. In few cases we need to create the additional statistics or modify the query design.

What is Statistics

Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view.

Query optimizer uses this statistical information to estimate the number of rows, in the query result set and by this way the optimizer to create a high-quality query plan.

Query optimizer uses this cardinality estimation to choose the index seek operator instead of the index scan operator, and in doing so improve query performance.

How we can Update Statistics

We can update statistics by using UPDATE STATISTICS or sp_updatestats

But Microsoft recommended that to keeping AUTO_UPDATE_STATISTICS set to ON so that the query optimizer continues to routinely update statistics.

To determine when the statistics where last update use the 

STATS_DATE ( object_id , stats_id )

Example

SELECT name AS index_name,

    STATS_DATE(object_id, index_id) AS statistics_update_date

FROM sys.indexes

WHERE object_id = OBJECT_ID('dbo.tbl_CUSTOMER');

GO

index_name                                                   statistics_update_date

PK__tbl_CUST__3D9DDA317BE3C753     2014-04-04 17:31:24.810

When to Update Statistics

We have to update the statistics in the following condition

·         Query execution times are slow.

·         Insert operations occur on ascending or descending key columns.

·         After maintenance operations.

Create Statistics

In MS SQL Server the statistics can be created by using CREATE STATICSTIC command or by CREATE INDEX command.

The statistical information created using CREATE STATISTICS command is equivalent to the statistics built by a CREATE INDEX command on the same columns.

The only difference is that the CREATE STATISTICS command uses sampling by default while the CREATE INDEX command gathers the statistics with full scan since it has to process all rows for the index anyway.

Example

CREATE STATISTICS [IX_STATS_CUSTID]

ON [dbo].[tbl_CUSTOMER]([CUSTID])

WITH SAMPLE 50 PERCENT;

As we are here using 50% sampling of the row, for any big table random sampling may not produce the actual statistics. Therefore, for bigger tables, we may need to use the resample option on UPDATE STATISTICS. The resample option will maintain the full scan statistics for the indexes and sample statistics for the rest of the columns.

The statistical information is updated when 20% of the rows changed.

Update Statistics

Updating of statistics ensures that any query that runs get the up-to-date statistics to satisfy the query needs. This is introduced in MS SQL 2005 version and it is similar to the sp_updatestats command.

UPDATE STATISTICS [dbo].[tbl_CUSTOMER]

WITH FULLSCAN, ALL

GO

Hope you like it.

Posted by: MR. JOYDEEP DAS

Index and Statistics

Introductions

Microsoft SQL Server collects statistical information related to indexes and column data stored in the database. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data.

If we understand the index statistics in better way, it helps us to optimize and fine tune our queries.

As the Statistics play a very important role in the performance of MS SQL Server query.

In this article I am trying discussing related to it. Before writing this article, I read several article and MSDN to gathering facts related to it.

Point to Focus

In this article I am trying to focus related to the following things of the SQL Server Index statistics.

1.    What we mean by MS SQL server Index statistics

2.    When the Index Statistics are Updated

3.    Rules used by AUTO_UPDATE_STATISTICS

4.    Determine that the Index statistics are out of date.

5.    Updating the statistics

6.    How to see the content of Index statistics

7.    New feature for statistics in MS SQL Server 2008

What we mean by MS SQL server Index statistics

Before going to the actual definition of Index statistics, I am going to take an example so that we can understand it easily.

"Suppose you are a football coach and u have 20 players in your team and for paying final tournament you need 11 players out of 20.  Now, you have to know (or maintain a list) all the statistics of each payer before making your final team." 

Index statistics contains information related to the distribution of index key values. It means that the number of rows associated with each key value. To determine what kind of execution plan to be used when processing a query MS SQL Server query optimizer uses this information.

After creating the table Index when we perform the insert, update, or delete operations in the table, the statistics of the table become out of date.  The MS SQL Server doesn't update the statistics information every time we made some changes (Insert/Update/Delete Operations) on the table. Statistics track the distribution of values within an index or within a particular column.

Note that, if a column is not indexed but can benefit from an index, SQL Server will automatically create statistics for that column.

When the Index Statistics are updated

It is very important to understand, when the MS SQL Server update the statistics. The database settings AUTO_UPDATE_STATISTICS controls when the statistics are automatically updated. By default the AUTO_UPDATE_STATISTICS is true that means that the statistics are automatically updated.

MS SQL Server determines that when to update the statistics after creating it. It is based on how old the statics is.  It determines the outdated statistics based on number of Insert, Update and Delete from the date when the statistics is last updated and then recreate the statistics based on a threshold.

This threshold is relative to the number of records in the table. It means that the when the DML operation performs the index statistics slowly get older, until SQL Server determines to update the statistics.

Limitations:

When we have very large table and we are doing bulk Insert/Update/Delete operation the AUTO_UPDATE_STATISTICS can be overhead on our system. Because the                        AUTO_ UPDATE_STATISTICS busy to update the statistics and causes the system overhead. In such type of situation we must turn off the AUTO_UPDATE_STATISTICS and later we must manually update the statistics.

Rules used by AUTO_UPDATE_STATISTICS

"rowmodctr" column of the sysindexes table is used to determine the number of changes made since the last update of the statistics.

When the MS SQL Server updates the statistics it will follow the following rules.

Ø  If our table have 6 or fewer rows, the statistics will be updated after 6 changes.

Ø  If a table has 500 or fewer rows, statistics will be updated after 500 changes

Ø  If a table has more than 500 rows, statistics will be updated after 20% of the total rows    plus 500 rows are changed.  

Syntax related to AUTO_UPDATE_STATISTICS

The syntax is mentioned bellow:

sp_helpdb DBName

GO

ALTER DATABASE DBName SET AUTO_UPDATE_STATISTICS ON

GO

Determine that the Index statistics are out of date

MS SQL Server uses the sampling methods to keep track of the last time when the statistics were updated. This information is used to determine how old your statistics is.

The function STATS_DATE is used to determine when the statistics was last updated. The sample script is uses this function to display index statistic date for all user defined indexes.

SELECT  schema_name(o.schema_id)AS SchemaName,

        OBJECT_NAME(si.object_id)AS TableName,

        si.nameAS IndexName,

        STATS_DATE(i.object_id, i.index_id)AS StatDate

FROM    sys.indexes si

        INNER JOIN sys.objects o

             ON si.object_id= o.object_id

        INNER JOIN sys.indexes i

             ON i.object_id= si.object_id

                AND i.index_id = si.index_id

WHERE   o.type<>'S'

        AND STATS_DATE(i.object_id, i.index_id)IS NOT NULL;

Updating the statistics

As we see that the problem related to MS SQL Server updating statistics automatically, to get the optimal output we need to manually update it when needed.

To update an Index statistics we can drop the Index and then recreate the Index, it will automatically update the statistics information. It works but it is not the good way to update statistics information manually.

The system stored procedure named "sp_updatestats" helps us to update the statistical information.

The syntax is mentioned below:

sp_updatestats[ [ @resample = ] 'resample']

Parameters

[ @resample =] 'resample'

Specifies that sp_updatestats will use the RESAMPLE option of the UPDATE STATISTICS statement. If 'resample' is not specified, sp_updatestats updates statistics by using the default sampling. Resample is varchar(8) with a default value of NO.

Example:

USE AdventureWorks2012;

GO

EXECsp_updatestats;

The UPDATE STATISTICS is another option to update the statistical information.

The syntax is mentioned bellow:

UPDATE STATISTICS table_or_indexed_view_name

    [

        {

            { index_or_statistics__name }

          | ( { index_or_statistics_name } [ ,...n ] )

                }

    ]

    [    WITH

        [

            FULLSCAN

            | SAMPLE number { PERCENT | ROWS }

            | RESAMPLE

            | <update_stats_stream_option> [ ,...n ]

        ]

        [ [ , ] [ ALL | COLUMNS | INDEX ]

        [ [ , ] NORECOMPUTE ]

    ] ;

<update_stats_stream_option> ::=

    [ STATS_STREAM = stats_stream ]

    [ ROWCOUNT = numeric_constant ]

    [ PAGECOUNT = numeric_contant ]

For more information about syntax, follow the MSDN

http://msdn.microsoft.com/en-us/library/ms187348.aspx

Example:

USE AdventureWorks2012;

GO

UPDATE STATISTICS Sales.SalesOrderDetail WITH FULLSCAN;

GO

How to see the content of Index statistics

To see the actual contents of the statistics we use the DBCC SHOW_STATISTICS.

The syntax is mentioned bellow.

DBCC SHOW_STATISTICS (Table_Name, Index_Name)

For more information about SHOW_STATISTICS, follow the MSDN

http://msdn.microsoft.com/en-us/library/ms174384.aspx

Parameters:

Table_Name : The name of the Table.

Index_Name : The Index name of the Mentioned Table.

New feature for statistics in MS SQL Server 2008

The new feature of statistics in SQL 2008 are mentioned as per MSDN

SQL Server 2008 provides the option to create filtered statistics, which are restricted to a subset of the rows in the table defined by a predicate, also called a filter. Filtered statistics are either created explicitly, or more commonly, through creating a filtered index.

·         Automatic creation: As mentioned, filtered statistics are automatically created as a by-product of filtered indexes. Unlike regular statistics, filtered statistics are never automatically created by the query optimizer as a result of query and update statements.

·         Automatic update: To determine when statistics need to be automatically updated, the query optimizer uses column modification counters. For filtered statistics, the counter is multiplied by the selectivity of the filter, which ranges from 0 to 1, to compensate for the fact that the column counter tracks changes to all rows in the table, not the changes to the rows in the filtered statistics object.

·         Sampling: To build the statistics, SQL Server 2008 reads every nth data page, according to the sampling rate, and then it applies the filter, if any.

·         If you don't specify sampling rate, the default behaviour is to sample based on the number of rows in the table and, for filtered statistics, based on the selectivity of the filter. The more selective the filter, the more rows need to be sampled to gather enough data to build the histogram.

·         Cardinality estimate: As with regular statistics, the optimizer attempts to pick the most relevant statistics when performing cardinality estimate. There is an additional check that the optimizer must make for filtered statistics: whether the statistics predicate contains the query predicate (that is, whether the query predicate must be true for a row if the statistics predicate is true for that row). Out of the possible filtered statistics, the query optimizer favours those that are as close to the query predicate as possible.

For more information about it, follow the MSDN

http://msdn.microsoft.com/en-us/library/dd535534(v=sql.100).aspx

Related tropics

1.    When we use sp_updatestats
http://sqlknowledgebank.blogspot.in/2012/04/when-we-use-spupdatestats.html

Referential Sources

http://msdn.microsoft.com/en-us/library/dd535534(v=sql.100).aspx

http://msdn.microsoft.com/en-us/library/ms187348.aspx

http://msdn.microsoft.com/en-us/library/ms174384.aspx

Hope you like it.

Posted by: MR. JOYDEEP DAS