2

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))
  • 1
    Post the query and execution plan so we can have a look – Mark Sinkinson Jan 27 '15 at 15:11
  • Do you have any data that supports the faster executions use one set of parameters / values, and the slower ones use different? If the same procedure using the same parameters and values is slow sometimes and fast other times, and they're run from the same place by the same users, it's likely not the query's fault, but rather other things like blocking or even general system pressure. – Aaron Bertrand Jan 27 '15 at 15:56
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.