March 12, 2010 at 12:14 pm
Hey all,
I have a sql server 2008 standard edition database that i'm reporting from. Up till a few days ago, it was running fine, report returned in less than a minute. This week however I'm having trouble with getting results, the query runs for a LONG time (in excess of 18 minutes when i quit)
Nothing has changed in the database, normal load (inserts/deletes and updates) - no mass changes.. no schema changes that im aware off..
I've ran an update statistics and rebuild indexes but that hasn't helped..
As the query doesn't return I cant get the actual execution plan
Can someone give me some hints on other things i can try?
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
March 12, 2010 at 12:21 pm
Can the query be broken down into smaller pieces to see what's taking so long? Like, if it joins multiple tables, query each table separately and see if one has a poor execution plan or takes forever?
Another thing to definitely check for in this kind of case is an incomplete transaction being held up somewhere. Run the query, see if something is blocking it. I've seen queries hold up for hours because some dev forgot to commit or roll back, or didn't realize that nested transactions don't all commit for a single command.
- 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
March 12, 2010 at 12:24 pm
Hi GSquared,
Thanks for the hint - i'll give it a try.. though ultimately I can't change the sql itself (the sql generated via reportbuilder - and I cant change the report) but knowing where the hold up is will definitely help i guess..
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
March 12, 2010 at 12:26 pm
It might at least give you a hint.
Have you checked for a blocking process?
- 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
March 12, 2010 at 12:32 pm
I hadn't (though i feel pretty stupid for not checking now)
but now running the sql and then running the 'all blocking transactions' report - i get no blocked transactions :\
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
March 12, 2010 at 12:39 pm
I would also look at the query plan. Depending upon the size of the table if suddenly decides to perform a tables scan Vs. some other plan it can result in a very long running query.
You need to answer these questions:
1) has the number of rows grown alot recently?
2) How fragmented is the table?
3) Have you updated statistics?
The probability of survival is inversely proportional to the angle of arrival.
March 12, 2010 at 1:26 pm
Have you checked to see if the transaction is being held up by any wait types?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply