Is this a symptom of an overloaded server?

I've been trying to diagnose slow-downs in an application. For this I've logged the SQL Server extended events.

• For this question i'm looking at one particular stored procedure.

• But there are a core set of a dozen stored procedures that equally can be used as an apples-to-apples investigation

• and whenever i manually run one of the stored procedures, it always runs fast

• and if a user tries again: it will run fast.

The execution times of the stored procedure vary wildly. A lot of the executions of this stored procedure return in

• duration vs physical reads

• duration vs cpu time

And none seem to give any correlation; none seem to be the cause

-
duration vs logical reads: whether a little, or a lot of logical reads, the duration still fluctuates wildly:

-
duration vs physical reads: even if the query wasn't served from the cache, and a lot of physical reads were needed, it doesn't affect duration:

-
duration vs cpu time: Whether the query took 0s of CPU time, or a full 2.5s of CPU time, the durations have the same variability:


Bonus: I noticed that the Duration v Physical Reads and Duration v CPU time look very similar. This is proven out if i try to correlate CPU time with Physical Reads:





Turns out a lot of CPU usage comes from I/O. Who knew!

So if there's nothing about the act of executing the query that can account for the differences in execution time, does that imply that it's something unrelated to CPU or hard drive?

If the CPU or hard drive were the bottleneck; wouldn't it be the bottleneck?

If we hypothesize that it was the CPU that was the bottleneck; that the CPU is under-powered for this server:

• then wouldn't executions using more CPU time take longer?

• since they have to complete with others using the overloaded CPU?

Similarly for the hard-drives. If we hypothesize that the hard-drive was a bottleneck; that the hard-drives don't have enough random through-put for this server:

• then wouldn't executions

Have a look at the wait_stats and it will show what the biggest bottlenecks are on your SQL server.

I recently experienced a problem where an external application was intermittently slow. Running stored procedures on the server itself was always fast though.

Performance monitoring showed nothing to be concerned about at all with SQL Caches or the RAM usage and IO on the server.

What helped narrow down the investigation was querying the wait stats that are collected by SQL in sys.dm_os_wait_stats

The excellent script on the SQLSkills website will show you the ones you are experiencing most. You can then narrow your search to identify the causes.

Once you know what waits are the big issues this script will help narrow down what session/database is experiencing the waits:

SELECT OSW.session_id,
       OSW.wait_duration_ms,
       OSW.wait_type,
       DB_NAME(EXR.database_id) AS DatabaseName
FROM sys.dm_os_waiting_tasks OSW
INNER JOIN sys.dm_exec_sessions EXS ON OSW.session_id = EXS.session_id
INNER JOIN sys.dm_exec_requests EXR ON EXR.session_id = OSW.session_id
OPTION(Recompile);

The above query and further detail is from the MSSQLTips website.

The sp_BlitzFirst script from Brent Ozar's website will also show you what's causing slowdowns.

StackExchange Database Administrators Q#244068, answer score: 2