How to find out what my SQL Server clogs up?

Question

My SQL Server processor is around 90% for most of today.

I cannot restart it due to constant use.

Is it possible to find out what is causing this processor overload inside SQL?

I started SQL Profiler, but it’s so hard for me to say that something is causing it.

I ran sp_who2, but I'm not sure if everything means exactly, and if possible problems can be identified here.

To preempt any answers “it's probably just being used a lot,” today it has just begun with perfectly normal activity levels.

I am after any way to find what causes the sorrow of the CPU in SQL.

Answer 1

I assume due diligence here that you have confirmed that the processor is actually consumed by the SQL process (perfmon Product Category Counters will confirm this). Usually for such cases, you take a sample of the appropriate performance counters and compare them with the baseline set in normal operating conditions. Once you solve this problem, I recommend that you establish such a baseline for future comparisons.

You can find where exactly SQL is spending every single CPU cycle. But knowing where to look, you need to know a lot and experience. Is SQL 2005/2008 or 2000? Fortunately for 2005 and newer, there are a couple of shelf solutions. You already have a couple of good pointers here with John Samson's answer. I would like to add a recommendation for downloading and installing SQL Server Performance Reports . Some of these reports include top time or I / O requests, most data files used, etc., and you can quickly see where the problem is. The result is both numerical and graphic, so it is more useful for a beginner.

I would also recommend using the Adam Who Active script, although this is a bit more advanced.

Last but not least, I recommend that you download and read the white paper of the MS SQL Advisory Group on Performance Analysis: SQL 2005 Waits and Queues .

My recommendation is to also look at I / O. If you add load to a server that destroys a buffer pool (i.e., it needs so much data that it forces cached pages of data out of memory), the result will be a significant increase in CPU (it sounds amazing, but true). The culprit is usually a new query that scans a large table from end to end.

Answer 2

This query uses DMV to determine the most expensive queries using the 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 

Full Description: How to identify the most expensive SQL Server processor queries

Answer 3

Run any of them for a few seconds. You will find a high connection to the processor. Or: the stored CPU in the local variable WAITFOR DELAY, compares the stored and current CPU values

 select * from master..sysprocesses where status = 'runnable' --comment this out order by CPU desc select * from master..sysprocesses order by CPU desc 

It may not be the most elegant, but efficient and fast.

Answer 4

You can run SQL Profiler and filter by CPU or Duration to eliminate all the “small things”. Then it will be much easier for you to determine if you have a problem, similar to a specific stored procedure, that works much longer than it should (maybe a missing index or something else).

Two caveats:

• If the problem is a huge amount of tiny transactions, then the filter described above would rule them out and you would skip this.
• In addition, if the problem is one large-scale task (for example, an 8-hour analytical task or a poorly designed choice that should cross a billion lines), you may not see this in the profiler until it is completely dependent on what events you profile (sp: completed vs sp: statementcompleted).

But usually I start with Activity Monitor or sp_who2.

Answer 5

For a GUI approach, I would look at the Activity Monitor under control and sort by CPU.