update stats - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

update stats

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-08-08 : 10:37:54

Has anyone noticed a performance improvement during trading hours when they replaced sp_updatestats with UPDATE STATISTICS FULLSCAN in their nightly maintenance?
Or is it negligible?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-08 : 13:38:53

UPDATE STATISTICS FULLSCAN is more accurate, it generates stats based on all rows in the table instead of data sampling.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-08 : 14:16:12

We are unable to run FULLSCAN even at night due to how long it takes. We scan 25% of the data each night.

I use this instead of sp_updatestats:
http://weblogs.sqlteam.com/tarad/archive/2006/08/14/11194.aspx

Tara Kizer
http://weblogs.sqlteam.com/tarad/

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-08-08 : 16:19:06

wonderfully helpful and knowledgeable as always, thanks tara

as a result, i spotted another one of your sprocs I haven't used before: isp_ALTER_INDEX.
BOL says that it's the same as DBReIndex. So what's the benefit of using Alter Index?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-08 : 16:35:30

ALTER INDEX is for SQL Server 2005. DBCC DBREINDEX is for 2000. ALTER INDEX provides much better options in determining what to reindex.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-08-17 : 06:32:51

Tara,
Could you take a moment to explain the value of the @minFragPercent, @maxFragPercent & @minRowCount params?
thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-17 : 11:55:25

They are used to decide which indexes to defragment.

There's no point defragmenting indexes that aren't fragmented by very much. There's also no point defragmenting smallish tables either. The values you pick for these are going to be trial and error.

We use @minFragPercent = 10 and @minRowCount = 1000 or 10000. I don't use the @maxFragPercent so we set it to 100. I just added that option in case anyone wanted to do the defragmentation in batches.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources