Update Statistics hitting 100% CPU

Update Statistics hitting 100% CPU

I have an Update Stats job setup which runs everyday at 4 AM and is pegging the CPU to 100% for a few secs. The DB is around 14 GB and SQL 2005/ Win 2k3.

This is a custom job with simple login update statistics [tblName] with Full Scan

I also tried using maintenance plan job, but even that is causing CPU spikes.

I tried to use MAXDOP in the command but was encountering Syntax errors.

Is there a way to run this job to ensure that there are no CPU spikes to 100%.

Thanks,
LVL 8
dba2dbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Few questions:

1. Have you applied SQL Server 2005 SP3
2. Is there any scheduled job other than this running at 4 am
3. Check whether your CPU reaches 100% when Indexes are rebuild.
4. Check whether you have sufficient disk space in both MDF and LDF and tempdb for these operations.
5. What is the Disk IO at that point of time.
6. Do you have Index Rebuild/ Reorganize job scheduled to run at regular intervals.
chapmandewCommented:
I don't think there is an option to set the processor affinity for an update statistics statement.
dba2dbaAuthor Commented:
Thanks for your Response Jegan:

1. Have you applied SQL Server 2005 SP3 - Yes, we are on SP3.

2. Is there any scheduled job other than this running at 4 am: No, there is minimum activity at 4 AM
3. Check whether your CPU reaches 100% when Indexes are rebuild. - No It does not spike during Index Rebuild.

4. Check whether you have sufficient disk space in both MDF and LDF and tempdb for these operations.- Yes, we dont have any space issues with TempDB and the user databases.

5. What is the Disk IO at that point of time - The Avg disk queue length; _Total is high during this job execution. It is averaging at ~ 130. This seems to be a problem.

6. Do you have Index Rebuild/ Reorganize job scheduled to run at regular intervals - Yes, We have a weekly job that rebuilds all indexes over 30% fragmentation and reorganizes the ones below 30%.

Also, can you please let me know if we could use MAXDOP with update stats or if there could be a MAXDOP setting at connection level.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> The Avg disk queue length; _Total is high during this job execution. It is averaging at ~ 130. This seems to be a problem.

Then I suspect that this might be one of the reasons which is causing High CPU usage.

>> can you please let me know if we could use MAXDOP with update stats or if there could be a MAXDOP setting at connection level.

Since you have used MAXDOP hint for Index, kindly let me know how many processors you have and the MAXDOP value set. Ideally for a 8 processor system, its recommended to have MAXDOP set to 4 to allow CPU for other processes in the system. Since you don't have CPU spikes during Index Rebuilds, its recommended to have MAXDOP set to some lower value and try once.
chapmandewCommented:
Avg disk queue length may indicate a problem, it may not.  It makes sense it has spikes when you're running a long query.  Whats the avg page life expectancy?
dba2dbaAuthor Commented:
Thanks Jegan and chapmandew.

We dont use MAXDOP with Index Jobs. It is a Dual CPU Quad core box - 8 cores. The server level MAXDOP is the default value.

The Page life expectancy is around 9 hours.

I was just trying few things and noticed that if I use sp_updatestats in the database it completes in a flash.

From the documentation I see that "In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items."
http://msdn.microsoft.com/en-us/library/ms173804(v=SQL.90).aspx

Could you please explain me if this would give all the benefits of the UPDATE STATISTICS [tblName]
WITH FULLSCAN

Since we have a highly OLTP system with good number of inserts and updates. I wanted to make sure that the stats are updated in the best possible way.

Thanks,



balasundaram_sCommented:
Make sure enough physical memory is available on the server, intermittent CPU spikes could be due to paging.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> I was just trying few things and noticed that if I use sp_updatestats in the database it completes in a flash.

Yes, because your Index and Table statistics would be up to date and hence Execution of your query would use Optimal Execution plan making the difference.
And lines from BOL is correct as sp_updatestats would update only the objects where statistics is not current. If statistics of that object are current, then it won't update.

You can use STATS_DATE function to identify when the statistics is last updated.

>> Could you please explain me if this would give all the benefits of the UPDATE STATISTICS [tblName]
WITH FULLSCAN

If you use FULLSCAN option, then it would scan for all the rows / records in your table and would try to update the statistics accordingly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.