Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL Query causing CPU Spikes on SQL Server 2008 r2 Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 2:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:39 PM
Points: 127, Visits: 300
Hi,
I have a curious case here: there is a SQL Query that is causing 90% of CPU on one of the SQL servers also it run in 14 seconds, on other server with similar specs the query executes in less than a second and no cpu spikes also the server configurations are the same:
What can be happening here?

select distinct
spid = cast(p.spid as int),
program = p.program_name,
host = p.hostname,
login = p.loginame,
databasename = replace(db_name(ISNULL(convert(int,l.rsc_dbid),-1)),char(39),char(39)+char(39)),
object = ISNULL(l.rsc_objid,-1),
request_mode = substring (v.name, 1, 8),
blockedwait = b.waittime,
inputbuffer = case when isnull(p.net_address, '') = '' then null else (select case when dmv.objectid is null then left(max(text),4000) else null end from sys.dm_exec_sql_text(p.sql_handle) dmv group by dmv.objectid) end,
starttime = dateadd(ss,cast(b.waittime as float)/1000 * -1,getutcdate())
from
master..sysprocesses p (nolock)
left join master..sysprocesses b (nolock)
on p.spid = b.blocked
left outer join master..syslockinfo l (nolock)
on b.spid = l.req_spid
and l.req_status in (2,3)
left join master.dbo.spt_values v
on convert(int,l.[req_mode]) + 1 = v.number
where
p.blocked = 0
and p.program_name not like 'SQLAgent%'
and p.program_name not in ('SQL PerfMon')

and b.blocked <> 0
and b.waittime > 166000
and v.type = 'L'

Thanks for your help
Post #1405643
Posted Thursday, January 10, 2013 7:05 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
Check if actual execution plans are different. Are those virtual machines?

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1405722
Posted Thursday, January 10, 2013 8:12 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 17,729, Visits: 15,597
Please provide the actual execution plan for both servers for the query.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1405734
Posted Thursday, January 10, 2013 9:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:39 PM
Points: 127, Visits: 300
How can I send the execution plan on an xml format?
Thanks
Post #1405746
Posted Thursday, January 10, 2013 9:13 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 17,729, Visits: 15,597
See the first link in my signature. The instructions for posting the exec plan are there.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1405748
Posted Thursday, January 10, 2013 11:54 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:01 AM
Points: 2,840, Visits: 3,968
APA0876 (1/10/2013)
there is a SQL Query that is causing 90% of CPU on one of the SQL servers also it run in 14 seconds, on other server with similar specs the query executes in less than a second and no cpu spikes also the server configurations are the same:


did you run the query at the same time?
are both(or other servers ) returning same records for this query ?
do both servers have same load (like job or background prcosses)
do they have same equal no of connections (at the time of query) ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1405782
Posted Friday, January 11, 2013 8:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:39 PM
Points: 127, Visits: 300
Bhuvnesh (1/10/2013)
APA0876 (1/10/2013)
there is a SQL Query that is causing 90% of CPU on one of the SQL servers also it run in 14 seconds, on other server with similar specs the query executes in less than a second and no cpu spikes also the server configurations are the same:


did you run the query at the same time?
are both(or other servers ) returning same records for this query ?
do both servers have same load (like job or background prcosses)
do they have same equal no of connections (at the time of query) ?


Yes I run the query at the same time
Yes 0 records
different Load the server with the spikes is 20 % avg CPU and the other 5% Avg CPU
On the server having the CPU spike there are 1700 connections and the other 10 connections.

But what should I do, this query is exected every 6 minutes because is part of a diagnostic Tool.

Thanks
Ailyn
Post #1406055
Posted Friday, January 11, 2013 8:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:49 AM
Points: 5,216, Visits: 5,110
I would completly re-write it all, for one all the tables used are depreciated and are in the product for SQL 2000 compatability only, you really should be using the DMV's.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1406063
Posted Friday, January 11, 2013 8:18 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 17,729, Visits: 15,597
APA0876 (1/11/2013)
Bhuvnesh (1/10/2013)
APA0876 (1/10/2013)
there is a SQL Query that is causing 90% of CPU on one of the SQL servers also it run in 14 seconds, on other server with similar specs the query executes in less than a second and no cpu spikes also the server configurations are the same:


did you run the query at the same time?
are both(or other servers ) returning same records for this query ?
do both servers have same load (like job or background prcosses)
do they have same equal no of connections (at the time of query) ?


Yes I run the query at the same time
Yes 0 records
different Load the server with the spikes is 20 % avg CPU and the other 5% Avg CPU
On the server having the CPU spike there are 1700 connections and the other 10 connections.

But what should I do, this query is exected every 6 minutes because is part of a diagnostic Tool.

Thanks
Ailyn


Post the execution plans so we can better see what is happening and provide better answers.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1406064
Posted Friday, January 11, 2013 8:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:39 PM
Points: 127, Visits: 300
APA0876 (1/10/2013)
How can I send the execution plan on an xml format?
Thanks


I posted two SQLPlans one for the server with the spike and the other sql for server that is OK


  Post Attachments 
ServerWSpike.sqlplan (17 views, 75.46 KB)
ServerOK.sqlplan (4 views, 72.49 KB)
Post #1406090
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse