Home > SQL server DBA administration > Modified UPDATESTATISTIC script with Full Scan option for performance improvement

Modified UPDATESTATISTIC script with Full Scan option for performance improvement


Topic: Modified UPDATESTATISTIC script with Full Scan option  for performance improvement

 

 

As we are aware about that sp system stored procedure sp_updatestats does not  have ability to update  statistics with the FULLSCAN option so here query can’t determine the actual execution plan. I have observed that most of index are skipped. Due to the same since couple of months we have faced severe performance issue for most of the server in our project. I have modified script which use the Full scan parameter Exec sp_MSForEachtable ”update statistics ? with fullscan”.

 

I have tested it in our Citagus test environment and it is working as expected. I have captured the index statics  report before and after to make sure that script is working perfectly. If the statistics are not updated since long days so it will take some more time during initial implementation stage but the completion time will be decreased once it start to run daily basis cause only those tables are updated which is required. Please check it from your end and expedite with your comments. You can make the below script as store procedure to implement it as a job or can be implement direct to job step to ensure it should be follow on specific schedule as per your environment.

 

Note: Script can be applicable for SQL 2005 and SQL 2008

 

————————————————————————————————————————————————————————————————

 

DECLARE

@dbname varchar(1000),

@parentname varchar(255),

@SQLString VARCHAR (1000),

@ctrl CHAR (2),

@command varchar(1000)

SET @ctrl = CHAR (13) + CHAR (10)

DECLARE UpdateCursor CURSOR FOR

select [name]

from sys.sysdatabases where name not in

(

‘model’,

‘tempdb’

)

order by 1

OPEN UpdateCursor

FETCH NEXT FROM UpdateCursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

select @command =

use [‘+@dbname+’]

Exec sp_MSForEachtable ”update statistics ? with fullscan”

exec (@command)

FETCH NEXT FROM UpdateCursor INTO @dbname

END

CLOSE UpdateCursor

DEALLOCATE UpdateCursor

GO

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

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: