SQL Request time suddenly increase

Question

I have an SQL request which executes usually with less than 1,5s. But sometimes, it takes more than 30s and this behaviour last hours.

Here is the actual request :

set dateformat ymd; 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

SELECT  Categories.Id as CategoryId, 
    ( 
     CASE when VeryBigChildTable.Type = 9 
    then 
        Categories.Name
    else 
        NULL
    end
    ) as CategoryName, 
    OS1.Id,
    OS1.StartDateUTC,
    VeryBigChildTable.StartDateUTC as StartDateUTCSP,
    Categories.Client_Id,
    VeryBigChildTable.ArticleScriptArticleSPError_ArticleSP_Id as FailedStep, 
    VeryBigChildTable.[Order] as Position,
    (
      CASE when Categories.Id IS null
        then 
        ( cast(TSHeaders.Id as varchar(3)) + '__' + cast(OS1.Id as varchar(15) ))
        else
    ( cast(Categories.Id as varchar(3)) + '_' + cast(OS1.Id as varchar(15) ))
    end )
     as GroupKey,
    ( 
     CASE when VeryBigChildTable.Type = 9 
    then 
         TSHeaders.CurrentName
    else 
        NULL
    end
    ) , 
    SPHeaders.Id,
 stuff((
    SELECT '|' + ArticleCategories.Name
    from ArticleCategories 
   inner join ArticleCategoryArticleScript 
    on ArticleCategoryArticleScript.BigTable_Id = OS1.Id
    and ArticleCategoryArticleScript.ArticleCategories_Id = ArticleCategories.Id
    for xml path('')),1,1,'')

from BigTable as OS1
inner join VeryBigChildTable on VeryBigChildTable.ArticleScript_Id = OS1.Id
inner join TSHeaders on TSHeaders.Id = OS1.TSArticle_Id
inner join SPHeaders on SPHeaders.Id = VeryBigChildTable.SPHeader_Id
inner join Categories on Categories.TSHeader_Id = OS1.TSArticle_Id
left outer join ArticleNetworks on ArticleNetworks.ArticleSPArticleNetwork_ArticleNetwork_Id = VeryBigChildTable.Id
where 
OS1.StartDateUTC >= '2015-06-18 10:12:15'  
and OS1.StartDateUTC <= '2015-06-19 10:12:15'  
and TSHeaders.Id in (319,318,322,323,324,326,328,343,345,346,347,550,552,561,565,595,612,613)  
and Categories.Id in (494,491,484,487,511,235,241,245,265,539,540,242,236,239,240,267,268,269) 

This query returns about 20K line.

VeryBigChildTable is 260 millions line table and BigTable is 60 millions line table. Other tables are very small (less than 2K line).

My configuration : SQL Server 2008 R2 (Mirrored) on Windows 2008 Server, Xeon 16 cores and 32GB RAM.

What may cause this request run sometimes more than 30 s ? (Knowing that I did not found any indexation task during that period)

What can I do to optimize this request ?

All your remarks are welcome.

Answer 1

It could have many causes.

• many other processes running in parallel and you have cpu pressure. You can check the is_idle flag for example: SELECT * FROM sys.dm_os_schedulers WHERE scheduler_id <= 256
• you may have memory pressure due to other bigger queries. You can check your current memory allocations SELECT * FROM sys.dm_exec_query_memory_grants. Maybe you'll see some more queries requesting for memory. You can also take a look at SELECT * FROM sys.dm_os_process_memory.
• you may try to RECOMPILE your query by using OPTION(RECOMPILE) at the end of your query to get a new query plan which fits the current situation.