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%'
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
