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 Friday, January 11, 2013 8:52 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369, Visits: 1,214
That diagnostic tool you use seems dangerous. I've seen people clogged-up their servers with third-party diagnostic tools, and also with SQL Profiler. Use lighter methods instead, like dmv's and highly filtered server-side trace or extended events.

First-aid would be to try sp_whoisactive. See what is in WAIT column. It also has option to display execution plan of running query.

See perfmon counters, especially System->Processor Queue Length. It should be 0 most of the time, and if it is above 2 for more than 1 second, you have a serious CPU problem.
See also SQLServer: Batch Statistics-> Compilations / sec and Recompilations / sec.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1406105
Posted Friday, January 11, 2013 1:21 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
anthony.green (1/11/2013)
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.


Hi,
Based of further investigation, it does not matter that I re-write the query using the DMV as I execute a select to the equivalent view SELECT * FROM sys.dm_tran_locks and I get the cpu spike too.

The issue is related to the following views:syslockinfo, sys.dm_tran_locks apparently when the server have 12000 sessions which is our case, the query to those views is causing CPU spike as we have another server with similar specs but the difference is the number of sessions and does have the spike. We are talking here of a very powerfull machine , 80 CPUs, 256 GB RAM

Can anyone offer some feedback regarding this, this is just happening with SQL Server 2008 R2 SP2,
Thanks
Post #1406245
Posted Friday, January 11, 2013 2:10 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369, Visits: 1,214
How many of this 12 000 connections is active (actually executing something, not just sitting there opened)?
How many NUMA nodes that machine have? Did you messed with CPU affinity mask setting?
Do you have any HA solution involved (cluster, mirror, replication) ?
Do you have compressed tables/indexes,
How many sql instances do you have on that machine? How many databases are there per instance?
What are overall waiting stats on the server?
What was the output of the sp_whoisactive (WAIT column) ?
It's very hard to solve the problem with little or no information.
This requires serious investigation. Consider hiring an expert to solve this.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1406259
Posted Saturday, January 12, 2013 9:35 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
Vedran Kesegic (1/11/2013)
How many of this 12 000 connections is active (actually executing something, not just sitting there opened)?
How many NUMA nodes that machine have? Did you messed with CPU affinity mask setting?
Do you have any HA solution involved (cluster, mirror, replication) ?
Do you have compressed tables/indexes,
How many sql instances do you have on that machine? How many databases are there per instance?
What are overall waiting stats on the server?
What was the output of the sp_whoisactive (WAIT column) ?
It's very hard to solve the problem with little or no information.
This requires serious investigation. Consider hiring an expert to solve this.


1- those connections are just opened we can have just 20 or 15 active
2-We haven't changed the affinity mask, we haven't change any configuration regarding NUMA,
3-We have transactional replication, but just some tables not the whole database and it is an Active/Passive Cluster
4-No compressed Tables or Indexes
5- Just one SQL Instance and 3 databases but just one database is the one in Production with at least 1500 users connected
6- top 5 waits of the server

wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
SLEEP_TASK 3685249015 2.61E+09 12549 1.44E+09
CXPACKET 3023764540 1.09E+10 2406037 1.35E+09
OLEDB 2084414050 3.43E+08 768572 0
SOS_SCHEDULER_YIELD 800830129 85869988 13180 84942124
LAZYWRITER_SLEEP 798502583 8.53E+09 5405941 22082789

7- This only happens when there is more than 12000 sessions opened and it is happening on this SQL version 2008 r2 enterprise as in SQL server 2005 enterprise I executed the same query under the same conditions and no CPU spike, apparently this is something related with the way Microsoft build this view on this SQL version
Post #1406380
Posted Saturday, January 12, 2013 6:58 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369, Visits: 1,214
This should not spike your CPU:

SELECT count(*) FROM sys.dm_tran_locks WITH(NOLOCK)

Run it on both machines and compare the numbers. Almost idle system is a very different situation than a busy server.
SQL server will have much more locks to wait for, much more memory allocated by connections (and thus memory will be probably spread across several NUMA nodes that additionally slows down the CPU when it wants to access that memory), different execution plans, different IO pressure, memory pressure, basically everything is different if the load is different.

Other thoughts:
If you only have 20 active connections for 12 000 opened connections - that is overwhelming number of idle connections and you have to reduce that number. You can do that by adjusting connection strings to open less connections initially, and all the clients should use one (or very few) connection strings. Because each connection string that is different by other connection strings even by one letter or one space - will start a new connection pool with initially opened connections that are idle.
Here are the connection string parameters for System.Data.SqlClient namespace, SqlConnection class's ConnectionString property: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

CXPACKET wait means you have parallelism involved, and one thread has finished before others and waits for others to finish. It can be controlled at system level by max degree of parallelism parametar, or on query level with MAXDOP hint. In DWH it makes sense that maxdop is 0 (auto) or more than 1, but in OLTP you probably want set that parameter to 1!

Can you describe that CPU spike little more? Is it really a "sipke" (a very short, sub-second period of time with very high activity)?
By "90% CPU" you mean:
a) one CPU usage climbed to 90% for a very short period of time
b) all 80 CPU's climbed to 90% (for a very short period of time)
c) 72 CPU's climbed to 100% and 8 CPUs were idle (which gives total cpu 90%)


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1406440
Posted Monday, January 14, 2013 1:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
APA0876 (1/11/2013)
anthony.green (1/11/2013)
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.


Hi,
Based of further investigation, it does not matter that I re-write the query using the DMV as I execute a select to the equivalent view SELECT * FROM sys.dm_tran_locks and I get the cpu spike too.

The issue is related to the following views:syslockinfo, sys.dm_tran_locks apparently when the server have 12000 sessions which is our case, the query to those views is causing CPU spike as we have another server with similar specs but the difference is the number of sessions and does have the spike. We are talking here of a very powerfull machine , 80 CPUs, 256 GB RAM

Can anyone offer some feedback regarding this, this is just happening with SQL Server 2008 R2 SP2,
Thanks


I didn't say it would improve the procedure, just that it will be supported in a newer version of SQL, the tables you are using will be removed from SQL in a newer version so if your monitoring is not up with the times and you migrate, your monitoring wont work.




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 #1406589
Posted Monday, January 14, 2013 1:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
Have you debugged to code to ensure that all connection object are being closed, dissposed and set to nothing. 12000 SPIDs for 15-20 active at anyone time seems a bit dramatatic. Could you not re-write the app to use connection pooling instead of spawning a new SPID as and when the app needs it?

Edit - Vedran beat me to the connection pooling.




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 #1406592
Posted Monday, January 14, 2013 2:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
anthony.green (1/14/2013)
Could you not re-write the app to use connection pooling instead of spawning a new SPID as and when the app needs it?
A side question , can we set the timeout limit for any query in sql server itself ? another thing does sql server have any concept like 'connection pooling' or it is managed by app only ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1406602
Posted Monday, January 14, 2013 2:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
As far as I know, connection pooling is done in the app as its an application thing, not a SQL thing.

As for the time out, that is set at the connection string, SQL doesnt have a time out setting for local queries, they run until they are told to stop or complete.




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 #1406604
Posted Monday, January 14, 2013 2:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
anthony.green (1/14/2013)
As for the time out, that is set at the connection string, SQL doesnt have a time out setting for local queries, they run until they are told to stop or complete.
i reason i am asking coz one of my friend told me that it can be done in MYsql so i was curious whether it is in sql or not ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1406606
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse