January 14, 2013 at 1:28 am
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.
January 14, 2013 at 1:31 am
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.
January 14, 2013 at 2:19 am
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;-)
January 14, 2013 at 2:22 am
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.
January 14, 2013 at 2:26 am
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;-)
January 14, 2013 at 2:39 am
There is remote query timeout setting in SQL but that only takes affect if you are quering a remote server using something like linked servers, there is nothing in SQL to say timeout a query if it runs for longer than X minutes if it accesses a local DB.
January 14, 2013 at 2:44 am
anthony.green (1/14/2013)
There is remote query timeout setting in SQL but that only takes affect if you are quering a remote server using something like linked servers, there is nothing in SQL to say timeout a query if it runs for longer than X minutes if it accesses a local DB.
thanks
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 14, 2013 at 3:53 am
You can limit long-running queries with several methods.
With time limit:
http://sqltimes.wordpress.com/2011/09/20/use-query-governor-to-prevent-long-running-queries/[/url]
It is cost-based, estimates time before the query is even executed.
You can set it at instance level and connection level:
SET query_governor_cost_limit XY -- session level
E.g. Limit at server level than allow certain connections to be unlimited, or
do not limit at server level but limit certain connections.
With query governor by defining workload group limits, but it can't limit total query time.
http://msdn.microsoft.com/en-us/library/bb934146%28v=sql.105%29.aspx
REQUEST_MAX_CPU_TIME_SEC - limits CPU time for a query
REQUEST_MEMORY_GRANT_TIMEOUT_SEC - limit waiting time for memory to be granted
January 14, 2013 at 8:30 am
Vedran Kesegic (1/12/2013)
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%)
Executing the statement with the NOLOCK caused a CPU spike, but less around 70 %, without the NOLOCK cause 99% CPU spike (all 80 CPU's climbed to 90% (for a very short period of time))
The result of the query is 14273 vs 179
This SQL server has the default SQL settings except the Max degree of Parallelism to 6 and the max and Min server memory, the rest of the SQL settings are default, also no soft numa node created or CPU affinity mask changed
The application we are using is a third party and sadly behaves that way (openeing southands connections)
Also, we queried those views on SQL Server 2005 under the same load and not CPU spike everything started after we migrated to SQL Server 2008 R2 SP2, ???
?
January 14, 2013 at 8:45 am
Contact CSS with that result. All 80 cpu's should not spike because of a single, simple query.
January 14, 2013 at 9:00 am
On the server with the CPU spikes, please check the BIOS settings for Energy Saving features.
If enabled, disable them.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 14, 2013 at 9:19 am
SQLRNNR (1/14/2013)
On the server with the CPU spikes, please check the BIOS settings for Energy Saving features.If enabled, disable them.
Any documentation regarding this causing that behavior?
Thanks
January 14, 2013 at 9:22 am
APA0876 (1/14/2013)
SQLRNNR (1/14/2013)
On the server with the CPU spikes, please check the BIOS settings for Energy Saving features.If enabled, disable them.
Any documentation regarding this causing that behavior?
Thanks
Here is a starter
http://www.brentozar.com/archive/2010/10/sql-server-on-powersaving-cpus-not-so-fast/
And here is another thread with similar behavior
http://www.sqlservercentral.com/Forums/Topic1404370-391-1.aspx#bm1406641
But vendor documentation or Microsoft documentation - I have none. Just plenty of experience of it happening.
You can download CPU-z and see if your CPUS are being throttled back or not. That is usually a very good indicator.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 14, 2013 at 3:14 am
We had exactly the same issue on a customer database. It would spike on the DB, and when we imported the DB inhouse and ran the query, it completed in 7 seconds.
After some debugging and playing around, it was found that an index was the culprit. The query was in the format of OUTER QUERY - WHERE NOT EXISTS (INNER co-related QUERY).
The INNER co-related query had predicates on two columns, each of which had a different Non Clustered index, and was the one causing the spike.
When the two indexes were converted into one single index on both columns, the CPU spike disappeared on the customer environment.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply