August 9, 2014 at 12:35 am
[font="Comic Sans MS"]I am trying to troubleshoot/tune a query which is almost running for around more than 40+ mins. Basically, this query is used by high level freaks...sorry but I mean VP, and the likes of a few directors of our org. I looked into the query plan but this plan is kinda confusing to look at and I am not able to figure out the pain point, the other interesting thing I noted was seeing a lot of PAGEIOLATCH_SH waits. Now, we are using Netapp and the files are segregated across 4 different filers across different LUN's, and I don't see any reason to indicate the SAN admins that there is problem with their h/w. I've attached the sql script & the query plan generated by it.
I hope someone can help me refactor or may be rewrite the query in an efficient manner to get rid of those idiotic waits, and get this query running on multiple threads instead of sticking itself on single thread...maybe for performance reasons.
Regards,
Faisal
[/font]
August 9, 2014 at 3:06 am
Can you add the actual execution plan as well?
It displays there's only a single row estimated.
How many rows are returned "in reality"?
August 9, 2014 at 3:23 am
[font="Comic Sans MS"]Thanks! LutzM. However, its kinda difficult for me to run that query again and get the actual exec plan...so for the # of rows returned is around 280093 odd something. In case if there is a pressing need of an actual exec plan then I'll add that in as well.
Regards,
Faisal
[/font]
August 9, 2014 at 5:17 am
The reason I'm asking is fairly simple:
Based on the estimated execution plan there's nothing to indicate the long duration.
A few things can still be improved:
It seems like there are a few views involved (e.g. dbo.RUN_DATE_V).
You might want to look at those views if they refer to tables referenced again in the query. If so, it might be more efficient to use the underlying table instead of the view.
Furthermore, [CDW].[dbo].[RANGE_DIM] seems to be a Heap (missing clustered index). It might help to add a clustered index.
But based on the estimated plan there's not much more to see. How did you measure the 40 seconds? Is this the return time based on Management Studio or is there another application involved?
August 9, 2014 at 5:32 am
Table definitions, index definitions and actual execution plan 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
August 9, 2014 at 7:33 am
[font="Comic Sans MS"]Thanks! LutzM for all your help till this point.
- The views are encapsulated with a whole bunch of other queries referencing some other tables, and again those involve joining two or more tables to get the data needed.
- Yup...this is heap, and I agree that adding a clustering key would help...I'll look into this option as well...maybe there is an identity column in d table definition which can be a perfect candidate for this. However, I'll try and add in the table definition so you can suggest which columns can be included in the index definition.
- I've attached the exec plan, so I hope this helps in understanding things in a better manner.
- This was measured based on the return time from ssms. However, I am not sure how much time front end app might take to pull those results.
P.S: As of my latest run it came out pretty quickly this time, so it took 24 mins to get the results out from ssms but it crossed the usual 15 secs mark for a query to be optimal and quick.
One more thing I'd like to add here is that the query is still running on a single thread even if I force maxdop option to utilize the # of cpu cores, it still isn't parallelizing the query. I thought if I can get this query to run on multiple threads the execution time would reduce.
Regards,
Faisal
[/font]
August 9, 2014 at 7:39 am
GilaMonster (8/9/2014)
Table definitions, index definitions and actual execution plan please.
[font="Comic Sans MS"]Hi Gail,
Thanks! for your prompt response. I'll try and add the table definition & index definition (maybe the schema details) of all the tables, views & indexes involved. I've already added the actual exec plan. I would also request that if some suggestions around refactoring or maybe reordering of joins could be made to retrieve data quickly.
Regards,
Faisal
[/font]
August 9, 2014 at 7:44 am
The root of the problem here is that the row estimations are wildly off. Estimated rows 1, actual rows 8.3 million. No way the optimiser will manage a good job with that.
Run a stats update, with fullscan, on the table [CDW].[dbo].[AGGR_ACTIVE_EE_SUBSCRIPTION_METRICS], run the query again and post the actual execution plan from that
I suspect you're running into this: http://sqlinthewild.co.za/index.php/2009/09/22/estimated-rows-actual-rows-and-execution-count/
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
August 9, 2014 at 7:51 am
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.
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
August 9, 2014 at 7:57 am
[font="Comic Sans MS"]Thanks! Gail. I'll look into it, and post the actual exec plan after updating the stats. It's been quite sometime when we last updated the stats with full scan. Let me ask you something about the plan, may I know why are the estimates even more weird, like estimated guesses it to be 1 rows and actual seems to be 8.3 million, but as a matter of fact the # of rows returned is around 2 lakhs?
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.
Table 'RANGE_DIM'. Scan count 25119537, logical reads 75358611, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'RETAILER_DIM'. Scan count 0, logical reads 16746358, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'GEO_DIM'. Scan count 0, logical reads 33492716, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AFF_LKP'. Scan count 8373179, logical reads 16752403, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PACKAGE_DIM'. Scan count 0, logical reads 33492746, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DATE_DIM'. Scan count 8373179, logical reads 25119537, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AFFILIATE_DIM'. Scan count 8373194, logical reads 50239105, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'EXTENDED_PROTECTION_SEGMENT_DIM'. Scan count 0, logical reads 16746388, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AGGR_ACTIVE_EE_SUBSCRIPTION_METRICS'. Scan count 1, logical reads 8385071, physical reads 42979, read-ahead reads 120, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Regards,
Faisal
[/font]
August 9, 2014 at 8:02 am
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.
[font="Comic Sans MS"]Okay...great. Thanks!
Regards,
Faisal
[/font]
August 9, 2014 at 9:20 am
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?
August 9, 2014 at 10:47 am
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
August 9, 2014 at 12:28 pm
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?
[font="Comic Sans MS"]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
[/font]
August 9, 2014 at 1:50 pm
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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply