Update Stats job

Question

Post reply

Update Stats job

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

SkyBVI SSCoach Points: 15128 More actions · Answer 1

@ mutthu

The disk is normal E drive

I am backing up locally .

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Geoff A SSChampion Points: 11511 More actions · Answer 2

yes... 5 hours for 100 GB is WAY to long....

your disk sub system is either 15 years old or something is wrong because of a driver update at the OS level...

on my basic 7200 RPM disk, i can backup 100 GB WITH verification in about 90 minutes with native SQL tools

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 3

SKYBVI (11/9/2010)
@ nakul
Do I have to run the script while the job status is exectuing?
Regards,
Sushant

Yes. Typically, you would run this the morning you see the job as suspended/waiting in sp_who2 - before stopping the job.

There are hundreds of reasons for poor performance - the only way to troubleshoot performance issues is be to find out what are the pain points - for which this query should help. Without this, rest is mere speculation.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

Hi Sushant,

If your update stats take very long to run, instead of using the Full Scan option run the updat stats with reduced sampling rate say 50%. If its taking longer try the update stats with 40%, 30% or evn 10% whichever is better for your environment. Statistics need to be updated regularly, no matter how good indexes you have. Otherwise performance will suffer.

Thank You,

Best Regards,

SQLBuddy

SkyBVI SSCoach Points: 15128 More actions · Answer 5

@ sqlbuddy

Thxs for ur reply

How can I run the update stats with reduced sampling rate ?

Where do i have to specify that(50% or 40%), if I am running it through maintenance task

or if i run it manually, then what is the script?

Thanks.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

Hi Sushanth,

In the Maintenance Plan -- Update Statistics Task,

You will see the "Scan Type" option, for that option select

Sample By X percent

Thank You,

Best Regards,

SQLBuddy

SkyBVI SSCoach Points: 15128 More actions · Answer 7

@ sqlbuddy

There is no option like scan type.

I just have options to select table, view or both and in the update portion: column stats or index stats or all existing stats.

I guess you are referring in sql 2000 or sql 2008

I am using sql server 2005 EE.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 8

I tried running sp_updatestats in the night, but it failed with error:-

Executing the query "sp_updatestats;" failed with the following error: "Transaction (Process ID 57) was deadlocked on lock resources with another process and has been c... The package execution fa... The step failed.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP