mercredi 28 janvier 2015

SQL Server 2012 stored procedure runs very slow sometimes


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))




Aucun commentaire:

Enregistrer un commentaire