November 10, 2008 at 6:17 am
I have a system I regularly profile for queries taking longer than 2 seconds recently out of the blue there has been a change in the longest running queries. A new query has turned up I hadn’t seen before, there haven’t been any changes to the schema etc so this query has always been there but has taken less than 2 seconds and happened regularly through out the day. THIS QUERY IS ADHOC.
If I ran this in QA it take a minute + for the first run then a second subsequent runs. But as this is an ADOC I guessing the execution plan is never saved so if a where clause value changes it will re do the execution plan?
Buffer cache is also fine.
What could cause this query to suddenly get so bad?
Many thanks
November 10, 2008 at 6:21 am
Have you had any recent data changes as in large amounts of updates or inserts?
I would check the stats on the indexes in that query
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 10, 2008 at 6:24 am
No large data updates, Also the query runs in under a second when they exuction plan has been worked out.
November 10, 2008 at 6:32 am
Is the plan the same or different?
Could you post the plan for sum of us to look at?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 10, 2008 at 7:06 am
Christopher Stobbs (11/10/2008)
Is the plan the same or different?Could you post the plan for sum of us to look at?
|--Filter(WHERE:(([prvsp].[PROCA_REFNO]=202052 AND [prvsp].[DISCH_DTTM]<>NULL) AND isnull([prvsp].[ARCHV_FLAG], 'N')='N'))
|--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([blurrr].[dbo].[PROVIDER_SPELLS] AS [prvsp]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([prcae].[PRVSP_REFNO]))
|--Filter(WHERE:(isnull([prcae].[ARCHV_FLAG], 'N')='N'))
| |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([blurrr].[dbo].[PROF_CARER_EPISODES] AS [prcae]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([dgpro].[SORCE_REFNO]))
| |--Filter(WHERE:([dgpro].[DPTYP_CODE]='PROCE' AND isnull([dgpro].[ARCHV_FLAG], 'N')='N'))
| | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([blurrr].[dbo].[DIAGNOSIS_PROCEDURES] AS [dgpro]) WITH PREFETCH)
| | |--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]), RESIDUAL:([Bmk1000]=[Bmk1000]))
| | |--Index Seek(OBJECT:([blurrr].[dbo].[DIAGNOSIS_PROCEDURES].[FK_DGPRO_PROCA] AS [dgpro]), SEEK:([dgpro].[PROCA_REFNO]=202052), WHERE:([dgpro].[SORCE_CODE]='PRCAE') ORDERED FORWARD)
| | |--Index Seek(OBJECT:([blurrr].[dbo].[DIAGNOSIS_PROCEDURES].[FK_DGPRO_ODPCD] AS [dgpro]), SEEK:([dgpro].[ODPCD_REFNO]=42517), WHERE:([dgpro].[SORCE_CODE]='PRCAE') ORDERED FORWARD)
| |--Index Seek(OBJECT:([blurrr].[dbo].[PROF_CARER_EPISODES].[CPK_PROF_CARER_EPISODES] AS [prcae]), SEEK:([prcae].[PRCAE_REFNO]=[dgpro].[SORCE_REFNO]) ORDERED FORWARD)
|--Index Seek(OBJECT:([blurrr].[dbo].[PROVIDER_SPELLS].[CPK_PROVIDER_SPELLS] AS [prvsp]), SEEK:([prvsp].[PRVSP_REFNO]=[prcae].[PRVSP_REFNO]) ORDERED FORWARD)
November 10, 2008 at 7:07 am
Erm you might want to copy and paste to notepad
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply