Earlier this weekend, I had an urgent product to help. One of my clients reached out to me as their application stopped running in the middle night on Saturday. The issue was indeed a strange one as they have very little traffic on weekends and particularly during the night. We immediately started working with my Comprehensive Database Performance Health Check service. While working together we found very interesting details from the server cache.
Quick Investigation – High CPU and Low IO
We started systematically and scientifically started to find the root cause of the performance slowness. It was very strange to us that we have really not much traffic but the server was running very high in terms of resources. We noticed that CPU was running around 100% for a longer duration whereas the disk IO was next to nothing. The memory consumption was stable around 90% of available memory as that is what we had adjusted with Max Server Memory.
Looking at High CPU and Low IO, I pretty much believed this could be the cause of the runaway queries or stored procedures which are running in the loop without accessing the tables or the conditions which are there for accessing the tables are not qualifying. Once I reach to this conclusion, I ran the following query to check the server cache to identify frequently ran stored procedures and I was pleasantly surprised to find one offending stored procedure which was running continuously in multiples in the loop and it had no real code inside it.
Frequently Ran Stored Procedure from Server Cache
Here is the script which will list frequently running stored procedure on your entire server.
SELECT DB_NAME(qt.[dbid]) AS 'DatabaseName', OBJECT_SCHEMA_NAME(qt.[objectid], qt.[dbid]) AS 'SchemaName', OBJECT_NAME(qt.[objectid], qt.[dbid]) AS 'SPName', qs.execution_count AS 'Execution Count', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_logical_reads AS 'TotalLogicalReads', qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second', qs.creation_time 'CreationTime' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE DB_NAME(qt.[dbid]) IS NOT NULL --remove to see all queries ORDER BY qs.total_logical_reads DESC
Remember above query retrieves data from the server cache. If you have restarted your server recently or your cache was removed/invalidated in the near past, you should let your server run for a considerable amount of time before you make the decision based on the data.
If you have a similar script, I request you to post in the comments section and I will publish it with due credit to you.
Reference: Pinal Dave (https://blog.sqlauthority.com)
