patternsqlMinor
Debugging slow SQL Server performance with SQL Profiler
Viewed 0 times
debuggingsqlwithslowperformanceprofilerserver
Problem
I have been debugging an issue in our production environment(on-premises) where queries perform slowly even the columns are indexed. The table has roughly 200 million rows, is not partitioned and is in full recovery mode.
The DELETEs only affects 1 row and INSERTs are only 1 row too.
I can see the duration in SQL Profiler, but it occurs during Audit Logout which I don't understand. For example the DELETE on top of the picture seems to take 0ms to run, but the Audit Logout takes 2867ms and it also has huge amount of reads. I have monitored the DB with PerfMon, but cannot see any related I/O or CPU issues.
Does it make sense that DELETE or INSERT with such a table could take seconds? Would anyone have an idea what is causing the latency?
The DELETEs only affects 1 row and INSERTs are only 1 row too.
I can see the duration in SQL Profiler, but it occurs during Audit Logout which I don't understand. For example the DELETE on top of the picture seems to take 0ms to run, but the Audit Logout takes 2867ms and it also has huge amount of reads. I have monitored the DB with PerfMon, but cannot see any related I/O or CPU issues.
Does it make sense that DELETE or INSERT with such a table could take seconds? Would anyone have an idea what is causing the latency?
Solution
Below are the descriptions of the Duration and Reads trace column values from the Audit Logout Event documentation:
The Audit Logout duration value in your trace reflects the time between reused pooled connections and the Reads value is cumulative for all activity since the connection was physically opened. Neither are related to the query slowness and not particularly useful for troubleshooting query performance.
The RPC:Completed duration reflects the server-side query duration for parameterized queries. There is no evidence of queries running more than a few milliseconds in this trace data.
Does it make sense that DELETE or INSERT with such a table could take
seconds? Would anyone have an idea what is causing the latency?
If you do capture a long-running RPC call, check the plan captured by the Showplan XML Statistics Report to see if the actual plan is not optimal. If the plan looks good, consider that slowness can be caused by many factors, such as long-term blocking, resource waits, or running an unfiltered Profiler trace on a busy production server.
- Duration: Amount of time since the user logged in (approximately).
- Reads: Number of logical read I/Os issued by the user during the connection.
The Audit Logout duration value in your trace reflects the time between reused pooled connections and the Reads value is cumulative for all activity since the connection was physically opened. Neither are related to the query slowness and not particularly useful for troubleshooting query performance.
The RPC:Completed duration reflects the server-side query duration for parameterized queries. There is no evidence of queries running more than a few milliseconds in this trace data.
Does it make sense that DELETE or INSERT with such a table could take
seconds? Would anyone have an idea what is causing the latency?
If you do capture a long-running RPC call, check the plan captured by the Showplan XML Statistics Report to see if the actual plan is not optimal. If the plan looks good, consider that slowness can be caused by many factors, such as long-term blocking, resource waits, or running an unfiltered Profiler trace on a busy production server.
Context
StackExchange Database Administrators Q#325515, answer score: 4
Revisions (0)
No revisions yet.