Tony’s Weblog

ASP.NET, MVC, AJAX, SilverLight, C#, WCF Web Service, SQL 2005 and other interesting stuff.

When do we use the UPDATE_STATISTICS command?

Posted by Tony on August 2, 2008

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

The following stored procedure can be used to run the “Update_Statistic” command on all the tables in your database:

==============

create procedure Update_Statistics
as

set nocount on

declare @tablename varchar(150)

declare table_name cursor for
select ‘UPDATE STATISTICS ‘ + name from sysobjects where type = ‘u’
order by name

open table_name

fetch next from table_name
into @tablename

WHILE @@FETCH_STATUS = 0

BEGIN
exec (@tablename)
print @tablename
FETCH NEXT FROM table_name INTO @tablename
END
print ”
print ‘All user table statistics have been updated’

CLOSE table_name
DEALLOCATE table_name

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: