SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Troubleshooting\Tuning sql query taking more than 40 secs


Troubleshooting\Tuning sql query taking more than 40 secs

Author
Message
ffarouqi
ffarouqi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1140 Visits: 1412
GilaMonster (8/9/2014)
faisalfarouqi (8/9/2014)
I would also request that if some suggestions around refactoring or maybe reordering of joins could be made to retrieve data quickly.


Reordering of joins won't do a thing, the order of joins does not matter. Refactoring's not currently indicated, the obvious problem needs to be addressed first.


Okay...great. Thanks!

Regards,
Faisal


LutzM
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24389 Visits: 13559
You've mentioned to expect between 10 and 50 rows as the result set.
However, the actual execution plan returned more than 280.000 rows.
Are those rows really returned?
Or is there usually a WHERE clause applied that you didn't use when runnning the query to get the actual execution plan?



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231387 Visits: 46350
faisalfarouqi (8/9/2014)
I've also added the io stats o/p...maybe these high logical reads is some kind of a problem, and hence it might be the reason I am observing those PAGEIOLATCH_SH waits.


The high logical reads are going to be because of the inappropriate execution plan, caused by the out-of-date statistics.

I would suggest a job to regularly update stats. Stale stats cause all sorts of performance problems

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ffarouqi
ffarouqi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1140 Visits: 1412
LutzM (8/9/2014)
You've mentioned to expect between 10 and 50 rows as the result set.
However, the actual execution plan returned more than 280.000 rows.
Are those rows really returned?
Or is there usually a WHERE clause applied that you didn't use when runnning the query to get the actual execution plan?

Nopes that was my mistake I assumed it to return 10 to 50 rows but when I reran the query it outputted around 2 lakh rows, and yes those rows are actual rows...nopes no where clause needed the query is as I've attached. As you reqd. the actual exec plan I've attached d same...so request you to kindly provide your valuable inputs

Regards,
Faisal

GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231387 Visits: 46350
Update the stats, run the query again and post the new actual exec plan please. Currently the incorrect row estimations are messing everything up, it's not going to be possible to provide useful advice until that glaring problem is fixed and the execution plan is sensible for the rows involved.

According to the plan you posted, the query returned 280093 rows in total. That 8.3 million was the actual rows on the index seek and the number across the plan before the aggregation.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21349 Visits: 7660
GilaMonster (8/9/2014)
faisalfarouqi (8/9/2014)
I would also request that if some suggestions around refactoring or maybe reordering of joins could be made to retrieve data quickly.


Reordering of joins won't do a thing, the order of joins does not matter. Refactoring's not currently indicated, the obvious problem needs to be addressed first.


I know this isn't supposed to matter, but I recently had to deal with a query where this actually did matter. The plan said it was FULL (instead of TIMEOUT), but I doubt it. The only difference was moving an INNER JOIN around in the FROM clause and the query plan came out wildly different, as did the timings on the query (10 seconds vs. 5 minutes).


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231387 Visits: 46350
Your move of the join changed the way the optimiser 'wandered' through the search space, resulting in it finding a different 'good enough' plan.

I say not to do it, because it's in no way deterministic, you could change something else (swap two columns in the group by) and have the same effect. If the optimiser did a complete search of the plan space, this would never happen, but since no one wants to spend days waiting for a plan, it can.

Remember the optimiser is NEVER looking for the best plan, it's looking for a good plan fast. In one case if found a good plan that was actually crap, in the other it found a good plan that was good. In that case, I'd be looking over the query to see if there's anything in it which is prone to cause the optimiser problems.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21349 Visits: 7660
GilaMonster (8/9/2014)

Remember the optimiser is NEVER looking for the best plan, it's looking for a good plan fast. In one case if found a good plan that was actually crap, in the other it found a good plan that was good. In that case, I'd be looking over the query to see if there's anything in it which is prone to cause the optimiser problems.


An excellent reminder, thanks. Due to the nature of the concern I actually took the query apart completely and rebuilt it using a temp table mid-point with indexes to control the optimizations and expectations.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
ffarouqi
ffarouqi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1140 Visits: 1412
GilaMonster (8/9/2014)
Update the stats, run the query again and post the new actual exec plan please. Currently the incorrect row estimations are messing everything up, it's not going to be possible to provide useful advice until that glaring problem is fixed and the execution plan is sensible for the rows involved.

According to the plan you posted, the query returned 280093 rows in total. That 8.3 million was the actual rows on the index seek and the number across the plan before the aggregation.


Hi Gail, As per your suggestion I've updated the stats for that particular table and I've seen tremendous improvements. The estimates are now pretty good, and the query is returned in almost a minute whereas it used to take around 21 minutes prior to updating stats. I've attached the actual exec plan after updating the stats. Now, the other problem is I cannot frequently update stats for this db, as these approx 5 TB in size and is busy with transactions. Is there a way I can deal with this VLDB, so I can keep stats up to date and defrag the indexes.

Regards,
Faisal


Attachments
SQLActual2.sqlplan (6 views, 662.00 KB)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231387 Visits: 46350
You *need* to run frequent stats updates. There's no way around that, you can either run frequent stats updates or have terrible performance. Index defrags, not such a critical issue, but the stats updates are essential. Daily would be good. Is the server active 24x7?

Do you want the query tuned further or is the minute execution acceptable?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search