update statistics taking too long

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/

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

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/

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

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

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/

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources