|
|
|
SSC Veteran
 
Group: General Forum Members
Last Login: Monday, May 07, 2012 7:45 AM
Points: 250,
Visits: 447
|
|
Guys,
The CPU on the SQL 2008 sp2 64bit box is always at 70% utilization which is causing queries to time out especially the ones which have sorting operations
like 'DISTINCT' and 'ORDER BY' clause. On further investigation from performance monitor we found that one of the SQL processes was consuming most of the CPU.
We looked for the the processid in the sysprocess and found that cmd check point was the related processid consuming the resources.
spid kpid status hostname cmd DB Name
11 1948 background CHECKPOINT master
When the CPU is normal with < 10% utilization interspersed with spikes, the sorting queries comeback in 0 secs.
Below are the env variables
System: Windows 2008 R2 Standard 64bit
SQL Server: Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
DB Size: 3gb
Number of DBS
on the instance: 2
DB Modes: Stand by/read only since these are destination log shipping databases with logs restored every night.
Any suggestions and inputs would help.
Thanks
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 6:36 AM
Points: 2,018,
Visits: 3,208
|
|
What do you see if you set up perfmon to collect CPU information and SQLServer:Buffer Manager Checkpoint pages/sec?
David
@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 07, 2012 7:45 AM
Points: 250,
Visits: 447
|
|
Thanks for the response David
I look at perfmon and gathered the process which consumes most CPU, subsequently from that process I grabbed the corresponding thread and plugged it in the the query below
select cmd, cpu, spid, lastwaittype
from master..sysprocesses
--WHERE CMD = 'RESOURCE MONITOR'
WHERE KPID = '1764'
cmd cpu spid lastwaittype
RESOURCE MONITOR 321531906 1 PREEMPTIVE_XE_CALLBACKEXECUTE
Please let me know if you need any further info
Thanks
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 6:36 AM
Points: 2,018,
Visits: 3,208
|
|
Yes, I see what you did. What I need to know though is Checkpoint pages/sec high when CPU is high and is there always a direct correlation. The wait type information is not helpful to me at this point.
So, if you could run perfmon and look at those two counters together and get back to me that would be great.
Note: I see that you had this similar issue back in November. Are you still looking for the same answer?
David
@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 07, 2012 7:45 AM
Points: 250,
Visits: 447
|
|
David,
Back in November when we had the issue with "Resource Monitor" upon investigation it was indicated that it is a know issue and SP2 would take care of it (Pls see the links below). Since then we applied SP2 but the problem is still persistent.
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/bd9e4476-39e2-46f8-a426-bef9601f9ad9
http://support.microsoft.com/kb/968722/en-us
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 6:36 AM
Points: 2,018,
Visits: 3,208
|
|
Ok - thanks for the update. Please let me know what you see with those two counters.
David
@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 07, 2012 7:45 AM
Points: 250,
Visits: 447
|
|
| David there is a direct correlation i.e checkpoint pages is high when cpu is high.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 6:36 AM
Points: 2,018,
Visits: 3,208
|
|
So, there is very little you can do to alleviate that aside from making sure that your IO subsystem can handle that activity; which at present it is not.
David
@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 12,320,
Visits: 23,448
|
|
Once you know the CPU is high, I'd start drilling down on what's causing it to be that high. What processes are using it up? I'd go to the DMO objects and start gathering metrics on CPU, and I'd monitor them to see when you're hitting waits, what those waits are, and what those queries are doing. It might be a system configuration issue, but it's most likely a fundamental tuning issue, incorrect or missing indexes, badly written queries, etc.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for Red Gate Software
|
|
|
|
|
Right there with Babe
Group: General Forum Members
Last Login: Wednesday, May 16, 2012 1:33 PM
Points: 715,
Visits: 1,474
|
|
Enable Optimize for Adhoc Workloads option for the SQL Server Instance. This will reduce the procedure cache bloating and the CPU compilation overhead.
sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'optimize for ad hoc workloads', 1
go
reconfigure
go
Aslo check for the following Perfmon Counters and let us know their values
SQL Server: SQL Statistics -- Batch Requests/Sec
SQL Server: SQL Statistics -- SQL Compilations/Sec
SQL Server: SQL Statistics -- SQL ReCompilations/Sec
Thank You,
Best Regards,
SQLBuddy
|
|
|
|
