July 14, 2011 at 6:46 am
I am encountering a strange problem in SQL Server 2008. I have a query which at one time returns me data in 2 sec, while some other time it takes 3 minutes to return the same data. I compared the execution plan in both these scenarios and found that the nested loops in one execution plan were changed to Hash join. These hash join were the main culprit behind this undesired query execution time.
I have reindexed and also updated the statistics for the DB. But its of no use. As the load on the DB server increase, i believe somehow indexes are getting outdated and the optimizer chose different execution plan which is not optimized. Because of this, it brings huge amount of data which results in Hash join.
As a temp fix, i am just getting the top 1 in the query, which solves my scenario. But still the root cause is unknown.
Can any one please help me in finding the root cause of why the indexes are getting outdated so early? Why there are two different execution plan for the same query?
Any suggestion will be appreciated.
July 14, 2011 at 7:15 am
Does the query have any input parameters or variables in it, or is it returning the exact same data every time?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2011 at 7:18 am
The query is using hardcoded ID as the only parameter.(where ID=123). There are inner join and left join in it. Select statement involve some case statement as well.
Its returning the exact data all the time
July 14, 2011 at 7:20 am
And the execution plan changes from time to time without the data changing?
Sounds like you may have buffer issues. How's the memory pressure on the server?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2011 at 7:44 am
The DB Server is having 8 GB RAM, SQL Server is allowed to take as much ram it can take. As the load increases on Server, it takes upto 7.81 GB RAM, out of which SQL server uses around 6.7GB. Noticed there was a jump in CPU usage as well while executing the query.
July 14, 2011 at 8:06 am
How are the stats on buffer hits and plan reuse?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 19, 2011 at 12:35 am
Buffer hits were 99% when i saw it from performance monitor. But how to see the stats on plan reuse.
Moreover i am getting 'Appdomain unloaded due to memory pressure' as well. is that anything to do with?
July 19, 2011 at 1:53 am
Can you post both execution plans please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2011 at 2:18 am
Attached the execution plan for both the cases. in one plan ,the query uses nested loop and in other plan it uses hash join for the same query.
July 19, 2011 at 3:30 am
These 2 plans are from two different DBs.
One of them probably needs to have statistics updated. (the _srinavas one, I'm guessing).
Craig Outcalt
July 19, 2011 at 3:56 am
Sorry for the previous reply.The plan were indeed from different databases. I have corrected it. One plan is taken after some time when the indexing on DB was complete, while the other one was taken after a long time after the indexing is over.
July 20, 2011 at 7:18 am
Those two queries are NOT the same - one is parameterized and the other is hard-coded. This will absolutely lead to different plans.
Also, the after-some-time plan seems to be estimated, not actual.
Key point: check out the estimated rowcounts for both plans. They are different by 5-8 orders of magnitude!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 21, 2011 at 6:20 am
Different queries, different plans. Simple as that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 22, 2011 at 7:56 am
Why not just use optimizer hint to force the plan that performs better?
July 25, 2011 at 6:16 am
stolbovoy (7/22/2011)
Why not just use optimizer hint to force the plan that performs better?
Because most of the time, that's a really bad idea, maybe?
When you force an execution plan, you're assuming your data will never change.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply