How can I correlate SQL Server performance metrics to the stored procedures that are causing problems

Question

the situation is that there are certain stored procedures and/or ad-hoc SQL that is causing our CPU to spike from 30 to 80% and dropping all our indexes out of memory, I'm wondering if there is a well established way to correlate the performance spikes (CPU, Disk Read, etc.) to particular instances of SP running..

Answer 1

There are 2 indirect ways that I can think of

• Note CPU, Read, Writes via profiler of a stored proc call We've used this recently to spot expensive IO/CPU compared to duration

• Use DMVs to find most expensive on our system, this shows stored procs too

Example:

SELECT TOP 10 
    QS.total_logical_reads,
    QS.total_logical_writes, 
    QS.execution_count,
    QS.total_logical_reads + QS.total_logical_writes AS [IO_total],
    QS.total_logical_reads / QS.execution_count AS Avg_Reads_per_Execution,
    ST.[text] AS query_text,
    db_name(ST.dbid) AS database_name,
    ST.objectid AS OBJECT_ID
FROM 
    sys.dm_exec_query_stats  QS
    CROSS APPLY 
    sys.dm_exec_sql_text(sql_handle) ST
WHERE 
    QS.total_logical_reads + QS.total_logical_writes > 0 
    --AND ST.dbid IS NULL 
ORDER BY 
    [IO_total] DESC;

Answer 2

• Performance Monitor. If Sql Server 2008 --> Start, Program Files, Microsoft Sql Server 2008, Performance Tools, Sql Server Profiler. Produces a trace. Trace contains all sorts of information. For instance, with a bit of work, one could find the begin and end trace events of a stored procedure, note that the time that it was running covered the spikes, then further verify by looking @ the resource counters collected by the trace. Try this link over at sql server performance: http://www.sql-server-performance.com/articles/per/correlate_trace_performance_p1.aspx

• Use Alerts in SQL Server Agent to fire off custom T-SQL which captures what is running @ that particular time. Sql Server Agent, Alerts, General, new alert, Type = SQL Server Performance condition alert ( setup as you wish ), Response, Execute job. The job has a sql script which writes to a table - telling you what was happening. All sorts of worlds could be your oyster. For instance, you could ask yourself - hey, what clr is in memory right now?

What's in memory right now?

SELECT
  a.*
 ,cla.*
 ,clr_ad.*
 ,state_desc = CASE clr_ad.[state]
     WHEN N'E_APPDOMAIN_CREATING' THEN 'The AppDomain is being created.'
     WHEN N'E_APPDOMAIN_SHARED' THEN 'The runtime AppDomain is ready for use by multiple users.'
     WHEN N'E_APPDOMAIN_SINGLEUSER' THEN 'The AppDomain is ready for use in DDL operations. These differ from E_APPDOMAIN_SHARED in that shared AppDomains are used for CLR integration executions as opposed to DDL operations. Such AppDomains are isolated from other concurrent operations.'
     WHEN N'E_APPDOMAIN_DOOMED' THEN 'The AppDomain is scheduled to be unloaded, but there are currently threads executing in it.'
     WHEN N'E_APPDOMAIN_UNLOADING' THEN 'SQL Server has requested that the CLR unload the AppDomain, usually because the assembly that contains the managed database objects has been altered or dropped.'
     WHEN N'E_APPDOMAIN_UNLOADED' THEN 'The CLR has unloaded the AppDomain. This is usually the result of an escalation procedure due to ThreadAbort, OutOfMemory, or an unhandled exception in user code.'
     WHEN N'E_APPDOMAIN_ENQUEUE_DESTROY' THEN 'The AppDomain has been unloaded in CLR and set to be destroyed by SQL Server.'
     WHEN N'E_APPDOMAIN_DESTROY' THEN 'The AppDomain is in the process of being destroyed by SQL Server.'
     WHEN N'E_APPDOMAIN_ZOMBIE' THEN 'The AppDomain has been destroyed by SQL Server; however, not all of the references to the AppDomain have been cleaned up.'
     ELSE '?'
     END
FROM
  sys.dm_clr_loaded_assemblies cla
  JOIN sys.assemblies a
    ON cla.assembly_id = a.assembly_id
  JOIN sys.dm_clr_appdomains clr_ad
    ON cla.appdomain_address = clr_ad.appdomain_address