SQL Query causing CPU Spikes on SQL Server 2008 r2

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL Query causing CPU Spikes on SQL Server...

29 posts, Page 2 of 3

««1 2 3 »»

SQL Query causing CPU Spikes on SQL Server 2008 r2

Rate Topic

Display Mode

Topic Options

Author

Message

Vedran Kesegic

Posted Friday, January 11, 2013 8:52 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:08 AM
Points: 342, Visits: 1,072

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.

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1406105

APA0876

Posted Friday, January 11, 2013 1:21 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 1:46 PM
Points: 112, Visits: 255

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

Vedran Kesegic

Posted Friday, January 11, 2013 2:10 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:08 AM
Points: 342, Visits: 1,072

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.

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1406259

APA0876

Posted Saturday, January 12, 2013 9:35 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 1:46 PM
Points: 112, Visits: 255

Vedran Kesegic (1/11/2013)

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

Vedran Kesegic

Posted Saturday, January 12, 2013 6:58 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:08 AM
Points: 342, Visits: 1,072

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%)

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1406440

anthony.green

Posted Monday, January 14, 2013 1:28 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

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

anthony.green

Posted Monday, January 14, 2013 1:31 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1406592

Bhuvnesh

Posted Monday, January 14, 2013 2:19 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1406602

anthony.green

Posted Monday, January 14, 2013 2:22 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Post #1406604

Bhuvnesh

Posted Monday, January 14, 2013 2:26 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1406606

« Prev Topic | Next Topic »

29 posts, Page 2 of 3

««1 2 3 »»

Permissions