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 2008 Forums
 SQL Server Administration (2008)
 update statistics taking too long

Author  Topic 

naushi44
Starting Member

12 Posts

Posted - 2014-08-20 : 19:01:14
- sql server 2008
- windows 2008
- 400 tables - about 400 billion rows
process - using dbcc command to rebuild indexes and do update statistics every day - active datatase

problem: index rebuild takes 1.5 hours but update statistics takes 8 hours

can any one help why update stat takes so long - thanks

naushi hussain

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-20 : 19:05:07
If you are rebuilding indexes, then you should not also be updating stats on those same indexes: http://www.sqlskills.com/blogs/paul/search-engine-qa-10-rebuilding-indexes-and-updating-statistics/

How are you updating stats? Are you doing a fullscan? You should consider doing it based on table sizes. Here's what I've used in the past:


SET @RowsSampled =
CASE
WHEN @RowCount < 500000 THEN '100 PERCENT'
WHEN @RowCount < 1000000 THEN '50 PERCENT'
WHEN @RowCount < 5000000 THEN '25 PERCENT'
WHEN @RowCount < 10000000 THEN '10 PERCENT'
WHEN @RowCount < 50000000 THEN '5 PERCENT'
WHEN @RowCount < 100000000 THEN '2 PERCENT'
WHEN @RowCount < 1000000000 THEN '1 PERCENT'
ELSE '20000000 ROWS'
END


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

naushi44
Starting Member

12 Posts

Posted - 2014-08-21 : 00:14:16
Thank you for the quick reply. I will try - does this mean I have to do the whole thing with a script? Would oyu have the rest of the script. I don't know how to use rowsample.

naushi hussain
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-21 : 12:37:51
quote:
Originally posted by naushi44

Thank you for the quick reply. I will try - does this mean I have to do the whole thing with a script? Would oyu have the rest of the script. I don't know how to use rowsample.

naushi hussain



Let's first stop updating stats after you rebuild the indexes as that is unnecessary/double work. Please read the link I posted for details.



Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

naushi44
Starting Member

12 Posts

Posted - 2014-08-21 : 12:42:18
we have now been suggested by our consultant that we should use sp_updatestats since it only updates those tables that need updates. is that true? I cant google the answer

naushi hussain
Go to Top of Page

naushi44
Starting Member

12 Posts

Posted - 2014-08-21 : 12:46:22
thank you - I read yout limk and we are going to stop the updates. also please see mu question about sp_updates. I will update the forum with our results tomorrow. thanks for all the help

naushi hussain
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-21 : 12:58:29
Many people use sp_updatestats, but it does have some limitations. If you are rebuilding indexes daily, then you likely don't need to bother with updating stats. I wouldn't rebuild indexes daily though. If I were to rebuild indexes, then I'd probably do it weekly and then do the update stats on the other 6 days.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

naushi44
Starting Member

12 Posts

Posted - 2014-08-21 : 13:19:47
Thank you Tara. we will try these scenarios and i will post results.

naushi hussain
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-21 : 16:32:16
Also have a look at Ola Hallengrens script for this
http://ola.hallengren.com


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

naushi44
Starting Member

12 Posts

Posted - 2014-08-22 : 11:51:30
Since I just came on board I spoke to the consultant in India who designed it. He told me that they only reorg the indexes and rebuild only if it passes the 30% threshold. so they have to do update statistics which makes sence. Starting Monday the code 'sp-updatestats' will replace 'update statistics full scan' that they are currently using. We are hoping to reduce the process to 2 hours from 8 hours.

will keep you posted. Thanks again got your continued support.

naushi hussain
Go to Top of Page
   

- Advertisement -