Sunday, September 11, 2011

Update Statistics In SQL Server

Update statistics in regular time interval, will boost your performance of the application, if have insertion, updation, or deletion happening in the table then update statistics is required.

1.  Updating all statistics for a single table.
Here is the simple query to updates the statistics for all the existing indexes on any single table. 

USE AdventureWorks; 
GO
UPDATE STATISTICS dbo.TableName; 
GO 

2.  Updating only the statistics for a single index.
Here is the query to updates the statistics for any particular specific index of the Table.

USE AdventureWorks;
GO
UPDATE STATISTICS dbo.TableName AK_IndexName;
GO 

3.  Find Statistics Last Update Date. 
The below query will tell you when was the last updated statistics, this command will not only tell you when statistics were last updated, but also provide you information on the kind of statistics that has been collected for the index you are examining.

USE AdventureWorks;
GO
DBCC SHOW_STATISTICS ('table_name' , index_name)
GO 

NOTE: Each database has two options related to statistics: auto create statistics and auto update statistics. Typically, both should be left enabled for databases.

UPDATE STATISTICS, Update Statistics Before or After an Index Rebuild, Find Statistics Update Date, Update Statistics in sql server 2005, When are Statistics Updated, Updating statistics about indexes, How to Update Statistics, updating statistics sql server 2008, does updating statistics lock tables, updating statistics sql server 2005, updating statistics sql server 2012, UPDATE STATISTICS statement, Understanding and Tuning Update Statistics, Performance tuning tips, ReIndexing Database Tables and Update Statistics, Improving Performance of UPDATE STATISTICS, Terrible SQL reads performance, The Misunderstood and Abused UPDATE STATISTICS Command, UPDATE STATISTICS necessary to improve performance, update index statistics, Update Statistics on Production Server, CPU Bottle neck

0 comments:

Post a Comment