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.

Answer 2

If this happens time to time, and it seems everything else is working just fine except the one query, my guess is that for some reason the query has a new query plan. To be sure, I would recommend checking what the query plan is normally and what it is when the performance is bad.

To get a query plan when the process is running you can use something like this:

SELECT qp.* 
FROM sys.dm_exec_requests r 
cross apply sys.dm_exec_query_plan(r.plan_handle) qp
WHERE session_id = <spid of the process here>;

If you want to check what's been happening, and also get the query plan later, you can look at dm_exec_query_stats. It also shows nice statistics like total logical reads, worker time and execution count per statement:

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,
creation_time,
last_execution_time,
cast(p.query_plan as xml) as query_plan
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text (sql_handle) t
cross apply sys.dm_exec_text_query_plan (plan_handle, statement_start_offset, statement_end_offset) p
where t.text like '%VeryBigChildTable%'
-- this way you'll get everything related to the big table
order by s.total_logical_reads desc

The statistics are available only for those plans that exist in cache, so if for some reason the plan gets dropped out, the statistics are lost too.