SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query plan changes over time, query performance severely impacted


Query plan changes over time, query performance severely impacted

Author
Message
cunningham
cunningham
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 856
Hi all,

I have a query that takes 2 seconds to compile and run (first time round) when the server is restarted. When the server has been up and running for a day or two, I will try and run the query and all of a sudden it will take 10 minutes. Nearly all of this time is spent compiling the query, and the execution plan has changed all of a sudden and I don't understand why. This system is not yet live so is not subject to change in data, the databases are refreshed from live every morning at 7am. This is eventually going to be the live system and was supposed to be improved performance for the end users, but at the moment we are stuck on this long running query issue.

There are NO wait times while the query is running, no significant cost in the plan, it is all compile time. The server is a virtual server with 8 cores and 12gb of memory, sql capped at 10gb it has SAN disk. there is still available memory for the OS (300mb according to perfmon). Thought this might be external memory pressure and was hoping to see available mb at 0 at the time when the performance deteriorated but this was not the case. I am out of ideas and the deadline is approaching, any thoughts will be appreciated.

Find the fast and slow query plans attached.

It's a right old conundrum
Thanks
Rich
Attachments
FastExecutionPlan.sqlplan (16 views, 3.00 MB)
SlowExecutionPlan.sqlplan (18 views, 1.00 MB)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218396 Visits: 41996
richard.austin (7/4/2013)
Hi all,

I have a query that takes 2 seconds to compile and run (first time round) when the server is restarted. When the server has been up and running for a day or two, I will try and run the query and all of a sudden it will take 10 minutes. Nearly all of this time is spent compiling the query, and the execution plan has changed all of a sudden and I don't understand why. This system is not yet live so is not subject to change in data, the databases are refreshed from live every morning at 7am. This is eventually going to be the live system and was supposed to be improved performance for the end users, but at the moment we are stuck on this long running query issue.

There are NO wait times while the query is running, no significant cost in the plan, it is all compile time. The server is a virtual server with 8 cores and 12gb of memory, sql capped at 10gb it has SAN disk. there is still available memory for the OS (300mb according to perfmon). Thought this might be external memory pressure and was hoping to see available mb at 0 at the time when the performance deteriorated but this was not the case. I am out of ideas and the deadline is approaching, any thoughts will be appreciated.

Find the fast and slow query plans attached.

It's a right old conundrum
Thanks
Rich


There are 2 things that I can think of off the top of my head that might be causing this. Parameter sniffing (which a recompile of the proc will usually fix) or data being added to indexes which only an index maintenance plan would fix (along with a rebuild of stats if only doing a reorg instead of a rebuild).

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
cunningham
cunningham
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 856
Thanks for your response. I am running the same query each time and not changing the parameters so don't think parameter sniffing is the problem. This query has been running fine for a year on another server which is less specced (one cpu and 2gb memory, but it is the only application using this particulalr server).

I have rebuilt the indexes but to no avail.

I have tried running the query with query hints on the join (hash, loop and merge hints) and the query returns in a couple of seconds. The optimiser is having a hard time compiling the query, but this only started happening yesterday when the query plan changed.
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42146 Visits: 20011
The optimiser was timing out because of a logic inconsistency in your WHERE clause. Try this:
SELECT c.Title
, c.Surname
, c.[Known As]
, c.Gender
, c.[Start Date]
, c.[Leaving Date]
, c.[Post Name]
, c.Category
, c.[Key Unit Name 1]
, c.[Cost To]
, c.FTE
, lcg.[Grade Name]
, c.[Payroll Name]
FROM [SelectHR].[Employee].[Current and Leaver Positions with Organisation As At Evaluation Date] c
INNER JOIN [SelectHR].[Person].[Laser Current Grade] lcg
ON ((c.[Person Number] = lcg.[Person Number])
AND (c.[Appointment Number] = lcg.[Appointment Number]))
AND (c.[Career Number] = lcg.[Career Number])
WHERE (c.[Start Date] < {ts '2013-06-30 12:55:40'}
AND c.[Leaving Date] IS NULL
AND c.[Payroll Name] = N'CCF')

OR
(c.[Start Date] < {ts '2013-06-30 12:55:40'}
AND c.[Leaving Date] >= {ts '2013-06-01 12:55:36'}
AND c.[Payroll Name] = N'CCF')



Compare the WHERE clause to yours.
Compare the results to yours - they are likely to be very different (the rowcount will be much less).
Post the actual execution plan of the new query if and when you have validated the results as correct.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
cunningham
cunningham
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 856
Thank you, but i am running your revised query and it too is taking a long time. It is worth mentioning that I have since added a join hint to the query (tried all three hash, merge and loop) and the query returned in seconds. There is something that is causing the sql optimiser to take a long time to compile an inefficient plan.

This is just one example of a query ran by a third party application, so I cannot change the actual code that they run.
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42146 Visits: 20011
richard.austin (7/5/2013)
Thank you, but i am running your revised query and it too is taking a long time. It is worth mentioning that I have since added a join hint to the query (tried all three hash, merge and loop) and the query returned in seconds. There is something that is causing the sql optimiser to take a long time to compile an inefficient plan.

This is just one example of a query ran by a third party application, so I cannot change the actual code that they run.


Can you post the actual plan for the quick version using the join hint? That's interesting...

You should refer them to the WHERE clause of the query. Here it is again:
WHERE  (c."Start Date"<{ts '2013-06-30 12:55:40'} 
AND c."Leaving Date" IS NULL
AND c."Payroll Name"=N'CCF'
OR c."Start Date"<{ts '2013-06-30 12:55:40'}
AND c."Leaving Date">={ts '2013-06-01 12:55:36'}
AND c."Payroll Name"=N'CCF')



Bet you can't guess what it's supposed to mean.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
cunningham
cunningham
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 856
I should mention that the objects being referenced in the initial query are views and not tables. These views use joins too, some quite complex which explains the size of the query plan. See attached for query plan of query executed with join hint.

It is totally different.
Attachments
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42146 Visits: 20011
The join hint restricts the number of possible plans which the optimiser has to evaluate to the point where it has time to pick a suitable plan, hence with the hint you're no longer getting a timeout. It's still a horrible plan - you're reading the same tables again and again. If you post up the view definitions, it might be possible to work through them to generate a cleaner query with less reads of the same tables.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Sean Pearce
Sean Pearce
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4142 Visits: 3436
Both plans are timing out, but one in a second and one in 10 minutes.

<QueryPlan DegreeOfParallelism="0" MemoryGrant="5968" CachedPlanSize="1632" CompileTime="659453" CompileCPU="649780" CompileMemory="715616">

<QueryPlan DegreeOfParallelism="0" MemoryGrant="7296" CachedPlanSize="1720" CompileTime=" 1183" CompileCPU=" 1055" CompileMemory=" 52024">

It is possible the slow one is due to a request for automatic statistics maintenance. If this is the case you could try setting Auto Update Statistics Asynchronously.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
cunningham
cunningham
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 856
ChrisM@Work (7/8/2013)
The join hint restricts the number of possible plans which the optimiser has to evaluate to the point where it has time to pick a suitable plan, hence with the hint you're no longer getting a timeout. It's still a horrible plan - you're reading the same tables again and again. If you post up the view definitions, it might be possible to work through them to generate a cleaner query with less reads of the same tables.


Thanks Chris, I agree the plans are horrible, and some of the joins in the views are horrendous, unfortunately it is a 3rd party application and I am stuck with the queries that are being ran, also the queries work fine on another instance (which is the actual current live system). They also work fine on the slow system when the instance has been restarted.

Sean Pearce (7/8/2013)
Both plans are timing out, but one in a second and one in 10 minutes.

<QueryPlan DegreeOfParallelism="0" MemoryGrant="5968" CachedPlanSize="1632" CompileTime="659453" CompileCPU="649780" CompileMemory="715616">

<QueryPlan DegreeOfParallelism="0" MemoryGrant="7296" CachedPlanSize="1720" CompileTime=" 1183" CompileCPU=" 1055" CompileMemory=" 52024">

It is possible the slow one is due to a request for automatic statistics maintenance. If this is the case you could try setting Auto Update Statistics Asynchronously.


Thanks Sean, what do you mean both plans are timing out? I do get results on both systems. Thank you for your suggestion, I tried setting auto update stats asynchronously, and I also tried turning off auto stats updates but didn't see any results immediately.

I am going to try installing the latest cumulative update for sql server 2008r2 SP2 just in case, at the very least it will rule this out. Though I can't see my specific problem listed in any of the cumulative updates.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search