Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query plan changes over time, query performance severely impacted Expand / Collapse
Author
Message
Posted Thursday, July 4, 2013 2:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 61, Visits: 469
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


  Post Attachments 
FastExecutionPlan.sqlplan (8 views, 3.46 MB)
SlowExecutionPlan.sqlplan (10 views, 1.74 MB)
Post #1470509
Posted Thursday, July 4, 2013 5:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1470537
Posted Friday, July 5, 2013 3:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 61, Visits: 469
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.
Post #1470641
Posted Friday, July 5, 2013 4:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 7,230, Visits: 13,709
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
Post #1470662
Posted Friday, July 5, 2013 7:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 61, Visits: 469
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.
Post #1470737
Posted Friday, July 5, 2013 8:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 7,230, Visits: 13,709
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
Post #1470743
Posted Friday, July 5, 2013 8:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 61, Visits: 469
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.


  Post Attachments 
001executionplanQuickWithLoopHint.sqlplan (9 views, 1.23 MB)
Post #1470780
Posted Monday, July 8, 2013 1:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 7,230, Visits: 13,709
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
Post #1471059
Posted Monday, July 8, 2013 3:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:13 AM
Points: 883, Visits: 2,804
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
Post #1471104
Posted Monday, July 8, 2013 12:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 9:12 AM
Points: 61, Visits: 469
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.
Post #1471308
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse