SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 update statistics taking too long
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

naushi44
Starting Member

USA
12 Posts

Posted - 08/20/2014 :  19:01:14  Show Profile  Reply with Quote
- 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

USA
37157 Posts

Posted - 08/20/2014 :  19:05:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
12 Posts

Posted - 08/21/2014 :  00:14:16  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 08/21/2014 :  12:37:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
12 Posts

Posted - 08/21/2014 :  12:42:18  Show Profile  Reply with Quote
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

USA
12 Posts

Posted - 08/21/2014 :  12:46:22  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 08/21/2014 :  12:58:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
12 Posts

Posted - 08/21/2014 :  13:19:47  Show Profile  Reply with Quote
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

Sweden
30276 Posts

Posted - 08/21/2014 :  16:32:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
12 Posts

Posted - 08/22/2014 :  11:51:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000