SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Query causing CPU Spikes on SQL Server 2008 r2


SQL Query causing CPU Spikes on SQL Server 2008 r2

Author
Message
Vedran Kesegic
Vedran Kesegic
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2044 Visits: 1266
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

APA0876
APA0876
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 Visits: 400
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
Vedran Kesegic
Vedran Kesegic
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2044 Visits: 1266
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

APA0876
APA0876
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 Visits: 400
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
Vedran Kesegic
Vedran Kesegic
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2044 Visits: 1266
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

anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25312 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25312 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13702 Visits: 4077
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;-)
anthony.green
anthony.green
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25312 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13702 Visits: 4077
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;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search