May 23, 2016 at 11:23 am
hello all!
recently we have had a problem with our app that had started executing one of the SPs with extreme speed: about 1K executions per second.
Despite on the SP was light, such number of executions had loaded our server. All the other functionality had started failing with timeouts b/c of I/O overhead.
could someone point me to the way to catch such excessive executions on an earlier stage? once such functionality has been put on live server?
I've taken a look at extended events. But I am not too familiar with them enough.
sqlserver.module_end event is as seems to be suitable. But how to design the event session to catch ALL SPs that execute more than N times a sec?
there are the different ways I know: the SQL Trace, or sys.dm_exec_procedure_stats DMV. bot both of them have disadventages: the first one loads the server? and hard to analyze, the second one is innacurate, as the cache flushes sometimes - often when high load
May 23, 2016 at 11:35 am
i believe this is one of Glenn Berry's scripts, that determine the most used stored procs.
you could infer that the procedure called the most, might be the ones called the most per second as well.
note that this is limiting itself by current database context. WHERE qs.database_id = DB_ID()
one of the calculations is Calls Per second, which would probably be helpful as an average.
SELECT 'Purpose: Top 250 most used stored procedures, targets for tuning.' AS notes;
SELECT TOP (250) p.NAME AS [SP Name]
,qs.execution_count
,ISNULL(qs.execution_count / DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second]
,qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
,qs.total_worker_time AS [TotalWorkerTime]
,qs.total_elapsed_time
,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
,qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC
OPTION (RECOMPILE);
Lowell
May 23, 2016 at 11:44 am
thank You very much Lowell!
I've recently had started one session, and put the server down by this statement:
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='EXEC_SP')
DROP EVENT SESSION EXEC_SP ON SERVER;
CREATE EVENT SESSION [EXEC_SP] ON SERVER
ADD EVENT sqlserver.module_end(
ACTION(sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_id,
sqlserver.username)
WHERE ([sqlserver].[equal_i_sql_ansi_string]([object_type],'P') --SPS
AND
source_database_id=24)
AND
([package0].[greater_than_equal_ansi_string]([object_name],'p')
OR[package0].[greater_than_equal_ansi_string]([object_name],'atisp')))
ADD TARGET package0.synchronous_event_counter
go
ALTER EVENT SESSION EXEC_SP
ON SERVER
STATE=start
GO
will the code You provide work without such pain on our live server, tha has about 4K transactions per sec in rush hours?
and... sys.dm_exec_procedure_stats as I know gets innacurate results sometimes as the server flushes cache when high loaded
May 23, 2016 at 12:08 pm
My prod system s busy, but not 500 queries per second busy.
I've run that particular query quite a few times on production, during prod hours, since it's hitting the DMV's, i'm not too gun shy on querying it.
at least give it a try and see if it strains your system.
Lowell
May 23, 2016 at 2:24 pm
Actually, the goal is to provide the functionality to prevent the app firing the server and putting it down by excessive quering. Despite on the wrong app's coding or DOS attacks.
needs some logic to inform DBA's that something is going wrong, and needs to work with to prevent the server down. The query You provide gives the average value of number of executions per second. When the plan is in cache for a long period, the avg will not reflect to peaks of N/per sec
my thoughts were to gather executions of SPs grouping the numbers by hour. Then compare the particular measurement with the average of the same day(days) of week with the same hour.
certainly, I'll try to use your query.
May 24, 2016 at 6:51 am
One glitch with the query You provide
once plan is recompiled, it shows wrong numbers
PS: sorry, not recompiled, but cached. In such case the plan for the SP is exluded from the cache, and then put again... I was surprised that the record is not exluded, but its cached_time was updated... and the SP that runs rarely is shown on the top
May 24, 2016 at 10:25 am
I've updated a bit Your query
SELECT 'Purpose: the most used stored procedure, targets for tuning.' AS notes;
SELECT TOP (1) p.NAME AS [SP Name]
,qs.execution_count
,ISNULL(qs.execution_count / DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second]
,qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
,qs.total_worker_time AS [TotalWorkerTime]
,qs.total_elapsed_time
,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
,qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
--we are don't need for the SPs for which the cache is just created, or recreated - at least they should be in cache for 10 minutes
and case when ISNULL(qs.execution_count / DATEDIFF(Second, qs.cached_time, GETDATE()), 0)>0 then datediff(second,qs.cached_time,qs.last_execution_time) end>600
--the real problem when the SP fires more than 500 times per second
and ISNULL(qs.execution_count / DATEDIFF(Second, qs.cached_time, GETDATE()), 0)>500
ORDER BY 3 DESC
OPTION (RECOMPILE);
May 26, 2016 at 5:31 am
Hello again, I am happy of Your advises if any
I found a situation, that confused me
Once I've got a snapshot of sys.dm_exec_procedure_stats into temp table
after ~20 minutes I've got the another snapshot from the same DMV
comparing the results I've found one SP that was run 2431 times (during that 20 minutes)
but the trace, that was running at the same time period cought only ONE execution of the SP
In addition I was inspecting sys.[dm_exec_cached_plans] ... but there were unusual values to the plan of the SP... refcount=3, usecounts=1
Q: Where should I look in addition to find all the executions of the SPs like I've mentioned
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply