AFINN.NET

I recently ran into an issue where the sqlserver.exe process on my data warehouse server was continously running at close to 100%. Stopping and starting the service did not help as it would continously climb back up to near 100% within a few minutes. I gave PSS a call and they helped me to identify the issue which I thought might be good to share.

To put a little perspective on the environment; my data warehouse server is a dedicated cluster with quad processors and 8 GB on each node. The DW database is approx. 80GB and there are no other sql instances or databases running on the platform which is why I could not figure out why the CPU continuously stayed pegged as there was more than enough hardware to handle that role. Since there were multiple hyperthreaded processors, the first thing we attempted was to disable hyperthreading in the BIOS. No luck. The next item on the agenda was to make sure that the MAXDOP (Max Degree of Parallelism) was set properly for the server. This was done by running the following command and making the appropriate changes from the returned results to the maxdop properties of the server in Management Studio:

select case when cpu_count / hyperthread_ratio > 8 then 8 else cpu_count / hyperthread_ratio end as optimal_maxdop_setting from sys.dm_os_sys_info.

After changing the setting to the output from the select statement, my CPU utilization dropped down to around a steady 40% to 50% utilization. Better, but not the 3% to 5% normal utilization the server used to run with.

After analyzing a few perfmon and SQL Profiler traces, PSS identified a stored procedure that was taking too long to run on the State dataset from “StandardDatasetMaintenance”. After some additional analysis, it turned out that there were several missing indexes which needed to be rebuilt. I ran the following two commands against the database:

EXEC sp_MSForEachTable  “UPDATE STATISTICS ? with fullscan”

EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 100)”

After these commands completed (took about 90 minutes), the CPU utilization was returned back to 5% or below. I have scheduled these as maintenance tasks to run every 30 days. It took nearly three weeks of headache to finally resolve the high CPU utilization so I hope this saves some time for others who admin SCOM, but are not DBA’s.