This article is half-done without your Comment! *** Please share your thoughts via Comment ***
The statistics of the Database Objects are playing very important role because Query Optimizer uses these statistics to choose the best execution plan.
Query Optimizer requires different information like: number of rows, index information, change count and others.
The updated statistics always give a better performance so we should find last updated statistics time and if it is outdated, we should update statistics for that particular object.
I am going to share three different scripts to find information about the information of tables and indexes.
Using DBCC:
|
1 2 3 |
-- This dbcc works up to SQL Server 2012. DBCC SHOW_STATISTICS ('Table_Name', 'Index_Name') GO |
Using sys.stats:
|
1 2 3 4 5 6 |
SELECT OBJECT_NAME(object_id) AS ObjectName ,name AS StatisticName ,STATS_DATE(object_id, stats_id) AS StatisticUpdateDate FROM sys.stats; GO |
Using sys.indexes:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT SCHEMA_NAME(SCHEMA_ID) AS SchemaName ,OBJECT_NAME(o.OBJECT_ID) AS ObjectName ,i.name AS IndexName ,STATS_DATE(o.OBJECT_ID,i.index_id) AS StatisticUpdateDate FROM sys.indexes AS i JOIN sys.objects AS o ON i.object_id = o.object_id WHERE o.object_id > 100 AND index_id > 0 AND is_ms_shipped = 0 GO |
