WinTill :: Knowledge Base



Updating Database Usage Statistics

Updating Database Usage Statistics

When performance issues arise with querying databases, be it stored procedures, views or tables, it may be time to force an update on all usage statistics. The first step is to find the offending database, unless you have time to run the script against every single database. 

Before clearing the cache and updating statistics it would be advisable to check the fragmentation on the tables first. For benefits and a how to guide on re indexing table, please refer to the related articles below.

Use the script below to update the statistics on all objects within the database (ie. tables, views and stored procedures), clear all cache and force an update on the usage statistics.
This ensures that the tables, views and stored procedures will run at their best performance as statistics become out dated even when the update statistics automatically option is enabled. The clearing of cache and forcing the usage update ensures they are up to date, rather than waiting for SQL to automatically update these as the database grows.

NOTE:- If you are going to be re indexing tables, do this BEFORE running the script below.

USE [DatabaseName]
GO
EXEC sp_updatestats
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC UPDATEUSAGE(0)
GO



Related Articles

Attachments

No attachments were found.

Visitor Comments

Article Details

Last Updated
13th of June, 2011

Would you like to...

Print this page  Print this page

Email this page  Email this page

Post a comment  Post a comment

 Subscribe me

Subscribe me  Remove from favorites

Remove Highlighting Remove Highlighting

Edit this Article

Quick Edit

Export to PDF


User Opinions

100% thumbs up 0% thumbs down (1 vote)

How would you rate this answer?




Thank you for rating this answer.

Continue