When we use SP_UPDATESTATS

We have common doubts when to update statistics, before index or after index. To resolve this query we must understand, what is the statistics is? Why we need the statistics? If we can solve this problem we must solve our previous query. In this article I am trying to solve this problem.

The query optimizer uses the statistics to create the query plan to improve the query performance. The query optimizer automatically generates the necessary statistics to build high quality query plan to improve the performance of the query.

In fewer cases we need to create statistics for better result.

The statistics of the query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed views.

The query optimizer use this statistics to estimates the cardinality, or number of rows, in the query result. These cardinality estimates enable the query optimizer to create a high-quality query plan.  The query optimizer could use cardinality estimates to choose the index seek operator instead index scan operator, and in doing so improve query performance.

We can use the following query to determine that the query optimizer has created statistics for a query predicate column. It queries the catalog views sys.stats and sys.stats_columns to return the database object name, the column name, and the statistics name for all of the columns that have single-column statistics.

When the query optimizer creates statistics on single columns as a result of using the AUTO_CREATE_STATISTICS option, the statistics name starts with _WA.

SELECT OBJECT_NAME(s.object_id) AS object_name,

       COL_NAME(sc.object_id, sc.column_id) AS column_name,

       s.name AS statistics_name

FROM   sys.stats s

       INNER JOIN sys.stats_columns sc ON s.stats_id = sc.stats_id

                                          AND s.object_id = sc.object_id

WHERE s.name like '_WA%'

ORDER BY s.name;

GO

Now we are going to solve this query that I mentioned in the top of my article.

"When we are going to update the Statistics by sp_updatestats, after or before rebuilding the query"

To solve this query we must take some example of t-sql statements.

Step-1 [ Create Table with primary key ]

CREATE TABLE tbl_stat

(

  ID             INT,

  SNAME          CHAR(2000)

  CONSTRAINT PK_tbl_stat PRIMARY KEY CLUSTERED (ID)

)

Step-2 [ Now Insert Some records in the Table Objects ]

BEGIN

      DECLARE @RowID DECIMAL=0

      WHILE @RowID <> 15000

        BEGIN

             SET @RowID = @RowID + 1

             INSERT INTO tbl_stat(ID, SNAME)

             SELECT @RowID, 'Student-' + CONVERT(VARCHAR(5), @RowID)

        END

END

GO

SELECT * FROM tbl_stat

Step-3 [ Now see the statistics look like for primary key ]

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

Name	PK_tbl_stat
Updated	NULL
Rows	NULL
Rows Sampled	NULL
Steps	NULL
Density	NULL
Average key length	NULL
String Index	NULL
Filter Expression	NULL
Unfiltered Rows	NULL

Step-4 [Let's rebuild the index to start with ]

ALTER INDEX PK_tbl_stat ON tbl_stat REBUILD

Step-5 [ Now See the Statistics Again ]

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

Name	PK_tbl_stat
Updated	Apr 19 2012 12:09AM
Rows	15000
Rows Sampled	15000
Steps	3
Density	1
Average key length	4
String Index	NO
Filter Expression	NULL
Unfiltered Rows	15000

Step-6 [ Conclusion ]

Here in this example we find that the rebuilding the index automatically update the statistics.

But what about the statistics that are not the part of the Index.

Step-7 [ Create Non-Index Statistics ]

CREATE STATISTICS stats_NIndxData ON tbl_stat(SNAME)

Step-8 [ Observation ]

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

n  No Changes Found

Rebuild the Index Again

ALTER INDEX PK_tbl_stat ON tbl_stat REBUILD

Now check

DBCC SHOW_STATISTICS ('tbl_stat', PK_tbl_stat) WITH STAT_HEADER

n  No Changes Found

Step-9 [ Now run the sp_updatestats ]

EXEC sp_updatestats

DBCC SHOW_STATISTICS ('tbl_stat', stats_NIndxData) WITH STAT_HEADER

Name	PK_tbl_stat
Updated	Apr 19 2012 12:18AM
Rows	15000
Rows Sampled	7068
Steps	15
Density	1
Average key length	2000
String Index	YES
Filter Expression	NULL
Unfiltered Rows	15000

Step-8 [ Remarks ]

So the answer of the query is "You must run the sp_updatestats after rebuilding the index.

Hope you like it.

Posted by: MR. JOYDEEP DAS