May 20, 2013 at 11:27 am
There are a number of nice queries (below) that show you what is using CPU time on your server. But I believe they pull this from the DMVs, so they show you ALL this info since your SQL Instance last started. Mine has been running for over 6 months, but right now our CPUs are getting slammed. So these reports show me what has been using CPU over that whole time period.
Is there any way to limit these reports to only show DMV data from say the last 24 hours, or even 1 hour? Or is there some other way to find out what is banging on my CPUs right now?
--identify the most costly queries by CPU
SELECT TOP 20
qs.sql_handle,
qs.execution_count,
qs.total_worker_time AS Total_CPU,
total_CPU_inSeconds = --Converted from microseconds
qs.total_worker_time/1000000,
average_CPU_inSeconds = --Converted from microseconds
(qs.total_worker_time/1000000) / qs.execution_count,
qs.total_elapsed_time,
total_elapsed_time_inSeconds = --Converted from microseconds
qs.total_elapsed_time/1000000,
st.text,
qp.query_plan
from
sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
ORDER BY qs.total_worker_time desc
-- Find queries that take the most CPU overall
SELECT TOP 50
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DiskReads = qs.total_physical_reads -- The worst reads, disk reads
,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads
,Executions = qs.execution_count
,TotalCPUTime = qs.total_worker_time
,AverageCPUTime = qs.total_worker_time/qs.execution_count
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time DESC
-- Find queries that have the highest average CPU usage
SELECT TOP 50
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,TextData = qt.text
,DiskReads = qs.total_physical_reads -- The worst reads, disk reads
,MemoryReads = qs.total_logical_reads --Logical Reads are memory reads
,Executions = qs.execution_count
,TotalCPUTime = qs.total_worker_time
,AverageCPUTime = qs.total_worker_time/qs.execution_count
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
,LastExecutionTime = qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time/qs.execution_count DESC
May 20, 2013 at 11:47 am
You should be able to filer on the column last_execution_time in the sys.dm_exec_query_stats Table.
I look at taskmgr.
Click on processes click on the CPU Column to sort by CPU time.
Then look at the processes.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 20, 2013 at 12:11 pm
Thank you, but when I look in Task Manager, the Image Name with the highest CPU is not surprisingly sqlservr.exe. And that is hovering around 99%. So I need to find out what's happening within SQL. I will keep digging.
May 20, 2013 at 12:38 pm
How many active connections do you have? EXEC sp_who3 to get that and it will tell you how much CPU is being consumed for each SPID.
I had this happen to me recently a few times and it was disk I/O was was causing the CPU to max out.
After I restarted the SQL Server Service it jumped up immediately to 99%.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 20, 2013 at 12:54 pm
I misread the topic.
Forget this reply.
May 20, 2013 at 12:57 pm
sp_who3 filters everything out unlike sp_who2.
I also use sp_whoisactive depending upon what I'm trying to accomplish.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 20, 2013 at 1:05 pm
When y'all say use sp_who3, which one do you mean? I found a bunch of different versions just by doing a google search:
http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3
http://mrdenny.com/applications/sp_who3
http://www.sqlservercentral.com/scripts/SP_who3/69906/
http://www.realworld-sqlserver.com/2012/01/spwho3-enhanced-and-efficient-version.html
There are probably more variants!
May 20, 2013 at 1:14 pm
The following is fine:
http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3
I execute it several times per day and if I want to capture the entire SQL Statement I execute sp_WhoIsActive.
Pick one and try it out.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 20, 2013 at 2:50 pm
If you are not a member of the Fixed Server Role sysadmin then you will not be able to view the active SPIDS.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2013 at 5:59 am
How about checking the Windows Task Manager first to make sure that it really is SQL Server using up the processor time.
May 21, 2013 at 6:13 am
kevaburg (5/21/2013)
How about checking the Windows Task Manager first to make sure that it really is SQL Server using up the processor time.
If you look above the OP already did that.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 21, 2013 at 6:18 am
Oooooops!
Sorry about that!
It might be worth restarting the instance if that is at all possible. I had this issue with one database because of a BCP import performed during a particularly high level of transactional activity in the database. Is there a possibility that might be the case here?
May 21, 2013 at 6:21 am
Hi Welsh!
You comments about disk IO could be it. I wonder though if the server isn't simply badly sized for the environment.
May 21, 2013 at 7:13 am
Thanks for all the comments. These are two servers in an Active/Active cluster. Each has 96 GB RAM, and dual 6-core Xeons, meaning 12 real cores, and Hyperthreading is on, so they show up as 24 cores in Task Manager. All SQL files are stored on multiple LUNs on a fast EMC SAN.
I believe the servers are right-sized because most of the time the CPUs are all down around 5%. But every once in a while they all jump up to 100% and stay there, maybe for as long as an hour. Customers complain. DBAs get yelled at! But then just as mysteriously they go back down again.
I don't LIKE mystery! I want to know what is causing this.
The only think I can think of at this point is to perhaps collect some numbers right as this is happening. Maybe dump values once every minute or every 5 minutes into a temp table or something, and then analyze. But I'm not sure what to collect, and making such a script will take some time. Seems like a project I'll have to fit in with all my other work!
But it sure seems like I can't be the first to ask this question: Right now my CPUs are at 100%... so what is causing this? Someone must have come up with a way to figure this out!
May 21, 2013 at 7:15 am
jpSQLDude (5/21/2013)
Thanks for all the comments. These are two servers in an Active/Active cluster. Each has 96 GB RAM, and dual 6-core Xeons, meaning 12 real cores, and Hyperthreading is on, so they show up as 24 cores in Task Manager. All SQL files are stored on multiple LUNs on a fast EMC SAN.I believe the servers are right-sized because most of the time the CPUs are all down around 5%. But every once in a while they all jump up to 100% and stay there, maybe for as long as an hour. Customers complain. DBAs get yelled at! But then just as mysteriously they go back down again.
I don't LIKE mystery! I want to know what is causing this.
The only think I can think of at this point is to perhaps collect some numbers right as this is happening. Maybe dump values once every minute or every 5 minutes into a temp table or something, and then analyze. But I'm not sure what to collect, and making such a script will take some time. Seems like a project I'll have to fit in with all my other work!
But it sure seems like I can't be the first to ask this question: Right now my CPUs are at 100%... so what is causing this? Someone must have come up with a way to figure this out!
Did you execute sp_who3?
Now is the time to do it.
That is a starting point.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply