Pages

Thursday, May 8, 2014

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.

No comments:

Post a Comment