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 ««123»»

Troubleshooting\Tuning sql query taking more than 40 secs Expand / Collapse
Author
Message
Posted Saturday, August 9, 2014 8:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 11:43 AM
Points: 193, Visits: 746
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

Post #1601421
Posted Saturday, August 9, 2014 9:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #1601439
Posted Saturday, August 9, 2014 10:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1601443
Posted Saturday, August 9, 2014 12:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 11:43 AM
Points: 193, Visits: 746
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
Post #1601450
Posted Saturday, August 9, 2014 1:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1601454
Posted Saturday, August 9, 2014 2:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:17 PM
Points: 5,408, Visits: 7,542
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
Post #1601455
Posted Saturday, August 9, 2014 2:36 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1601456
Posted Saturday, August 9, 2014 2:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:17 PM
Points: 5,408, Visits: 7,542
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
Post #1601460
Posted Monday, August 11, 2014 3:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 11:43 AM
Points: 193, Visits: 746
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



  Post Attachments 
SQLActual2.sqlplan (2 views, 662.24 KB)
Post #1601730
Posted Monday, August 11, 2014 4:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1601750
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse