update statistics job taking longer to complete

Article
05/03/2012

Question

_{Thursday, May 3, 2012 1:51 PM}

Hello All,

I have scheduled an update statiscs job on production environment that runs daily at 6:00 AM. Earlier it used to take 45 mins to complete but over the time the execution time has gone up to 4 hours. This is actually causing slowness on production environment as these are the peak hours. Looking at the job result or sql server log things look normal. I was wondering what was causing this job to take so long to complete. Need your valuable inputs friends.

Thanks

All replies (9)

_{Friday, May 4, 2012 12:43 AM ✅Answered}

It this is only affecting specific tables target the statistics updates to those tables specifically and not the entire database. If you are constantly deleting data in this manner I'd recommend looking at whether you could implement a sliding window partitioning scheme on the tables so that removing the data is a simple switch out operation and metadata change, then you could leverage filtered statistics at the partition level to mitigate against doing full object rebuilds.

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

_{Thursday, May 3, 2012 2:20 PM}

Hello All,

I have scheduled an update statiscs job on production environment that runs daily at 6:00 AM. Earlier it used to take 45 mins to complete but over the time the execution time has gone up to 4 hours. This is actually causing slowness on production environment as these are the peak hours. Looking at the job result or sql server log things look normal. I was wondering what was causing this job to take so long to complete. Need your valuable inputs friends.

Thanks

How much has the size of the database increased over this period of time? The time it takes to update statistics is primarily a factor of how much data the database holds, and how fast SQL Server can read through that data from disk to be able to build the histograms.

_{Thursday, May 3, 2012 2:29 PM}

Hello All,

I have scheduled an update statiscs job on production environment that runs daily at 6:00 AM. Earlier it used to take 45 mins to complete but over the time the execution time has gone up to 4 hours. This is actually causing slowness on production environment as these are the peak hours. Looking at the job result or sql server log things look normal. I was wondering what was causing this job to take so long to complete. Need your valuable inputs friends.

Thanks

How much has the size of the database increased over this period of time? The time it takes to update statistics is primarily a factor of how much data the database holds, and how fast SQL Server can read through that data from disk to be able to build the histograms.

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

The database has grown to 240 GB from 80GB. The database has 51 tables and of them 12 tables are large. Please find the screen shot below. I am scheduling update stats to run daily as we are supposed to defragment indexes once in every 2 weeks.

_{Thursday, May 3, 2012 3:18 PM}

As Johnathan mentioned the dependency for statistics to updated & reviewing at your current requirement, we can suggest:

This is a little obvious choice that you may already would have thought of but just penning it down. Change schedule of the update statistics job to prior which may help the completion before business hours.
Pick & choose individual tables or group of tables that are necessary to update stats daily so that it can finish in daily maintenance window. You can use following t-sql:

UPDATE STATISTICS <Table|View> WITH FULLSCAN;

- Change the sampling behavior from FULLSCAN to less than 100% sampling. It is recommended 100% but looking at your scenario we can go with less sampling on business days & FULLSCAN when maintenance window is larger than the business day. You may use following t-sql:

UPDATE STATISTICS <Table|View> WITH SAMPLE 70 PERCENT;

_{Thursday, May 3, 2012 4:28 PM}

If your database tripled in size, it would be feasible that you statistics updates could take three times as long if your I/O subsystem is strained under the read operations. I would change my maintenance to do weekly index maintenance using Ola Hallengren's scripts which perform analysis based maintenance and only rebuild indexes that actually need it done:

http://ola.hallengren.com

And then implement a mid week statistics update using his same maintenance package. Unless you have significant changes that result in skewed statistics during an auto-update, typically you don't need to do daily updates of every statistic in the database. If you find that you have a stat that gets skewed, implement a separate job that targets that statistic only for each day, otherwise you aren't likely getting a lot of benefit from what you are doing.

_{Thursday, May 3, 2012 5:48 PM}

You got good advice already and adding one additional bit of information, which may not be the issue in this case. UPDATE STATS uses tempdb and make sure your tempdb is properly configured. I would also check how much IO are you doing against the tempdb compared to other user databases on that server and any auto growth on tempdb and if instant file initialization is setup correctly and try to optimize tempdb for IO.

http://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx http://connect.microsoft.com/SQLServer/feedback/details/457024/update-statistics-top-100-percent-and-sort-warnings#details http://connect.microsoft.com/SQLServer/feedback/details/483247/document-the-tempdb-usage-by-update-statistics

Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/

_{Thursday, May 3, 2012 6:48 PM}

If your database tripled in size, it would be feasible that you statistics updates could take three times as long if your I/O subsystem is strained under the read operations. I would change my maintenance to do weekly index maintenance using Ola Hallengren's scripts which perform analysis based maintenance and only rebuild indexes that actually need it done:

http://ola.hallengren.com

And then implement a mid week statistics update using his same maintenance package. Unless you have significant changes that result in skewed statistics during an auto-update, typically you don't need to do daily updates of every statistic in the database. If you find that you have a stat that gets skewed, implement a separate job that targets that statistic only for each day, otherwise you aren't likely getting a lot of benefit from what you are doing.

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Thanks Jon for the reply. I have scheduled few purge data jobs that run everynight..they delete more than a couple of million records in few tables and more than a couple of hundred thousand records in some tables every night. Hence I had to set up the update statistics job to run daily...

_{Thursday, May 3, 2012 6:50 PM}

You got good advice already and adding one additional bit of information, which may not be the issue in this case. UPDATE STATS uses tempdb and make sure your tempdb is properly configured. I would also check how much IO are you doing against the tempdb compared to other user databases on that server and any auto growth on tempdb and if instant file initialization is setup correctly and try to optimize tempdb for IO.

http://sqlblog.com/blogs/linchi_shea/archive/2009/08/16/performance-impact-tempdb-and-update-stats.aspx http://connect.microsoft.com/SQLServer/feedback/details/457024/update-statistics-top-100-percent-and-sort-warnings#details http://connect.microsoft.com/SQLServer/feedback/details/483247/document-the-tempdb-usage-by-update-statistics

Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/

The tempdb sits on a seperate drive configured on a raid 1+0 and I have instant file initialization in place already.

_{Friday, May 4, 2012 2:34 PM}

It this is only affecting specific tables target the statistics updates to those tables specifically and not the entire database. If you are constantly deleting data in this manner I'd recommend looking at whether you could implement a sliding window partitioning scheme on the tables so that removing the data is a simple switch out operation and metadata change, then you could leverage filtered statistics at the partition level to mitigate against doing full object rebuilds.

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Thanks again Jon.

Share via

update statistics job taking longer to complete

Question

All replies (9)

Additional resources