SQL Server 2012 – Troubleshooting Slow Stored Procedure Performance

performancequery-performancesql serversql-server-2012

I have a stored procedure with a single large query which takes an ID as a parameter and returns some counts. It has quite a few inner joins and left outer joins but they are all on indexes.

The stored procedure runs most of the time in around 3-4 seconds, but sometimes takes over 10 seconds (there is a mailer setup in the SQL Server for sending emails for queries that take over 10 seconds). It also ran for over 100 seconds a couple of times. How do I troubleshoot this and what could be the possible reasons for the varied execution times?

The query is something like this:

SET NOCOUNT ON;

DECLARE @date2 datetime;
SET @date2 = GETDATE();

WITH lstats 
AS
(
    SELECT 
        [T1].id1, [T3].flag1, [T3].num1, [T3].string1, 
        [T3].date1, [T3].string2, [T3].id
    FROM 
        [dbo].[table1] AS [T1]
    INNER JOIN  
        [dbo].[table2] AS [T2] ON [T1].id1 = [t2].id1
    INNER JOIN 
        [dbo].[tabl3] AS [T3] ON [T2].[id2] = [T3].[id2]
    WHERE 
        ([T3].[flag1] = 1 AND [T3].id = @id 
         AND (([T3].[num1] IS NULL) OR (-1 <> CAST( [T3].[num1] AS int))))
)
SELECT 
    [l].[id1] AS [id1], 
    [l].[id] AS [id], 
    [lu].[id] AS [idx],
    CAST(CAST([date2] AS datetime2) AS datetime2) AS [date2], 
    (SELECT COUNT(1) AS [A1] 
     FROM lstats AS [ls]    
     WHERE ([l].id1 = [ls].id1)) AS Count1,
    (CASE WHEN (2 = CAST( [Type] AS int)) 
            THEN
               (SELECT COUNT(1) AS [A1]
                FROM lstats AS [ls]     
                WHERE ([ls].id1 = [l].id1) 
                  AND (([ls].[date1] IS NULL) OR ([ls].[date1] <= @date2)) 
                  AND (([ls].[string2] IS NULL) OR (NOT ([ls].[string2] LIKE '%sometext%'))))
            ELSE
               (SELECT COUNT(1) AS [A1]
                FROM lstats AS [ls]     
                WHERE ([ls].id1 = [l].id1) 
                  AND (([ls].[string1] IS NULL) OR ('sometext2' <> [ls].[string1])) 
                  AND (([ls].[date1] IS NULL) OR ([ls].[date1] <= @date2)) 
                  AND (([ls].[string2] IS NULL) OR ( NOT ([ls].[string2] LIKE '%sometext%'))))
         END) AS Count2
FROM 
    [dbo].[table1] AS [l]
INNER JOIN 
    [dbo].[table4] AS [lu] ON [l].[id1] = [lu].[id1]
LEFT OUTER JOIN 
    [dbo].[table5] AS [les] ON [l].[id1] = [les].[id1]
LEFT OUTER JOIN 
    [dbo].[table6] AS [u] ON [l].[id] = [u].[ID]
LEFT OUTER JOIN 
    [dbo].[table7] AS [lt] ON [l].[id3] = [lt].[id3]
WHERE 
    ([lu].[id] = @id OR (@someflag = 1 AND [u].ID = @id))

Best Answer

First thing to check is if there's blocking happening. If the procedure / statement is blocked by another process then it's quite clear why it takes longer.

If that's not the case, you can try to check from plan cache if there's something that explains it. Here's one short example query to get some of the figure from there:

select top 100
SUBSTRING(t.text, (s.statement_start_offset/2)+1,
((CASE s.statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE s.statement_end_offset
END - s.statement_start_offset)/2) + 1) as statement_text,
t.text,
s.total_logical_reads, s.total_logical_reads / s.execution_count as avg_logical_reads,
s.total_worker_time, s.total_worker_time / s.execution_count as avg_worker_time,
s.execution_count
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text (sql_handle) t
where t.text like '%name_of_proc%'
order by s.total_worker_time desc

There's also a lot of other columns, like max_elapsed_time, max_worker_time, max_logical_reads etc. That might give some insight.

Related Question