January 25, 2011 at 9:55 am
We have an issue with one of our QA SQL servers that is baffling us. We have a query that runs in 50+ seconds most of the time and then under a second after we manipulate statistics and indexes but then goes back to the slow execution after 10-15 minutes. We have used the tuning advisors and received reccomendations for a number of Stats and Indexes. We have run dbcc freeproccache, updated stats, defraged indexes, used recompile with the stored proc that is running the queries. We have seen the same query plan give us fast and slow results. The same query consistently runs under 2 seconds on the Prod and Dev SQL servers.
Query Plan attached...
Any suggestions will be appreciated...
January 25, 2011 at 10:09 am
Open the plan, right click on the final select in the plan. Then properties. You'll find that the optimizer stopped the optimisation because of a timeout.
That could explain why sometimes the plan is good and then it's not (since you seem to have taken care of parameter sniffing for the most part).
The way to solve this is break the query in smaller steps by using temp tables to save steps of the query.
In the best of worlds, all the plans optimisation should be trivial, full or good enough.
My other idea is to use plan guides but I think there are others options to check out before that one.
January 25, 2011 at 11:54 am
Hey I just had a quick look at the query from the .sqlplan.
Here's another idea to see "where it all goes wrong".
I see you have a lot of subqueries. I'd comment them all out and run the query a few times and see if the optimizer figures out the correct plan.
Then add 1 subquery at a time to see where the tiping point is for the optimizer. Separate and conquer from there.
One more option. Try converting the subqueries to outer applies.
Also try TOP 1 Order by instead of MAX (it's faster than max in all the tests I did).
Another thing to try instead of the subquery is to turn it into a derived table (Select grp, max...) dtMax inner join...
January 25, 2011 at 12:58 pm
another alternative might be
SELECT *
, ROW_NUMBER() OVER ( ORDER BY orgName asc ) AS NUM
FROM (
SELECT TOP 83
o.OrgID
, Name AS OrgName
, Created AS OrgCreated
, LastUpdate AS OrgLastUpdate
, Ord.max_created AS LastOrderDate
, U.max_LastLogin AS LastLogin
, QuoteCount
FROM IPA_AccountManager_Organization_Pending_QuoteCount qc ( nolock )
INNER JOIN uOrganization o ( nolock )
ON qc.OrgID = o.OrgID
left join ( SELECT OrganizationId, MAX(Created) max_created
FROM PCCB2B_Transactions.dbo.Orders WITH ( nolock )
group by OrganizationId
) Ord
on Ord.OrganizationId = o.OrgID
left join ( SELECT OrgID, MAX(LastLogin) max_LastLogin
FROM uUser WITH ( nolock )
group by OrgID
) U
on U.OrgID = o.OrgID
WHERE AccountManagerUserID = '0FA1743E-42B7-4303-8EB2-1544A828DB83'
AND ( WebSite = 'PCCB2B' )
ORDER BY orgName DESC
) AS SUBSET
Did you notice your view consumes 322MB from your ubasket table scan.
Maybe try to avoid the view.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply