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

Poor performing query on Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 5:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:15 AM
Points: 66, Visits: 487
mayurkb I switched parametrization to forced on the offending database, and now when I run the query (after it's initial 10 minute run into the cache), even if I change it slightly by adding/removing spaces it returns in seconds.

This is definitely a problem with the compilation of the query, but what would be causing this extra long compilation time, does compilation utilise CPU heavily? there are 8 cores on the server, but it is a virtual server, see any problems here?

The query takes seconds to compile on the test and current live servers.

Thanks for all your help in this guys, getting me on the right track i think.

Rich
Post #1464567
Posted Tuesday, June 18, 2013 7:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:22 PM
Points: 83, Visits: 175
Glad I could help.
We were also having some CPU issues with our virtual servers running on vSphere. We did not see those issues while running on hyperv.
Also, keep in mind this is a short term solution. For long term solution, you will have to tune your query by fixing query, views and indexes.
Post #1464638
Posted Tuesday, June 18, 2013 1:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:15 AM
Points: 66, Visits: 487
update on this, restored the backups to another sql server with exactly the same OS and SQL server version and SP, and the query returns in seconds, even on the intial compile. So that's 3 servers handling the queries with ease, but the super awesome production server can't deal with it!

I can only think it's something in the configuration somewhere, Need to find why it is using a different execution plan on the slowly running server.

Post #1464850
Posted Thursday, June 20, 2013 4:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:15 AM
Points: 66, Visits: 487
FYI, solved this by adding 7 extra tempdb files to match the 8 cores, then restarted the sql server. Now the query plans match exactly, and the query returns in no time.
Post #1465573
Posted Tuesday, July 9, 2013 1:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:15 AM
Points: 66, Visits: 487
just to let you know the tempdbs didn't sort it, the restart sorted it temporarily but then the issue returned. Eventually tracked it down to trace flag T8780 being turned on. This trace flag increases the timeime the optimiser can search for the best plan before a timeout occurs. Because the query was so complex this was taking 10minutes and STILL timing out.

I turned the trace flag off and the query now runs in seconds, all be it with a sub optimal plan.

Just need to figure out what is turning the trace flag on.
Post #1471813
Posted Tuesday, July 9, 2013 10:39 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
First, thanks a bunch for the updates on this. We're getting close to doing the same thing (update) and this will help with some of the things to watch for

richard.austin (7/9/2013)
I turned the trace flag off and the query now runs in seconds, all be it with a sub optimal plan.


BWAAA-HAAA!!!! Considering what you just went through, I'm thinking that the sub optimal plan isn't so bad.


--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 #1471942
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse