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


query tuning (PFA, execution plan and indexed view)- need help to enhance performance


query tuning (PFA, execution plan and indexed view)- need help to enhance performance

Author
Message
saxena200
saxena200
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 168
well, guys using the following query also created indexed view and statistics as advised by DTA
--------------------------------------------------------------------------------------------

declare @FromDate datetime = '1-march-2012'
declare @ToDate datetime = '31-march-2012'

SELECT DT.MO_OF_YR_NM [Month],
DT.MO_OF_YR [month_No],
DT.YR [Year],
DT.MIC_WK_OF_MO_NM [Week],
DT.DT [Date],
CD.CSTMR_NM [Organisation],cd.CSTMR_KEY,
PD.BS_LN_NM [Business Line],
ED.EMPE_NM [Sales Executive],
COUNT(DISTINCT CDBF.CSTMR_KEY) [Total no of Lines],
(CASE WHEN SUM(CDBF.BLNC)>0 then COUNT(DISTINCT CDBF.CSTMR_KEY) else 0 END) [Total No Of Unpaid Lines],

SUM(CDBF.BLNC) [Total Amount Due]

FROM DT_DIM DT with (NOLOCK)
INNER JOIN CSTMR_DUE_BLNC_FCT CDBF with (NOLOCK) ON DT.DT_KEY=CDBF.FCT_DT_KEY
Inner Join CSTMR_DIM CD with (NOLOCK) ON CD.CSTMR_KEY=CDBF.CSTMR_KEY
Inner join AR_BHVR_DIM ABD with (NOLOCK) on ABD.AR_BHVR_KEY = CDBF.AR_BHVR_KEY
Inner join PD_DIM PD with (NOLOCK) on PD.PD_KEY = ABD.PD_KEY
Inner Join EMPE_DIM ED with (NOLOCK) ON ED.EMPE_KEY=ABD.EMPE_KEY

WHERE DT.DT>=@FromDate AND DT.DT<=@ToDate
--AND CD.CSTMR_NM IN (@Organisation)
--AND PD.BS_LN_NM IN (@BusinessLine)
--AND ED.EMPE_NM IN (@SalesExecutive)

GROUP BY
DT.MO_OF_YR_NM,
DT.MO_OF_YR,
DT.MIC_WK_OF_MO_NM,
DT.DT,
DT.YR,
CD.CSTMR_NM,
PD.BS_LN_NM,
ED.EMPE_NM,
--CDBF.FCT_DT_KEY,
CDBF.AR_BHVR_KEY,
CDBF.CNTRCT_DD,cd.CSTMR_KEY
ORDER BY DT.MO_OF_YR
Attachments
Execution_Plan.zip (11 views, 9.00 KB)
Indexed_View_Statistics.zip (6 views, 738 bytes)
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39456 Visits: 32630
You've got a two second compile time on a query that's not all that complex. But, you're referencing six tables and the optimizer is only pulling data from four (plus that odd table spool). So, it looks like it's going through some simplification for you and eliminating some of the tables you have referenced. First tuning suggestion, figure out what that is and why and eliminate it from the query. Next up, estimated cost is 49.3946. That's not terribly high. Granted, SQL Server thinks it's moving 100k rows, but that's not that much. Maybe test out with the cost threshold for parallelism set to 50. Are the statistics up to date? When was the last time you had a full scan on them? I ask because that nested loop join looks odd considering the amount of data we're moving and a clustered index seek for 100k rows, that's a very strong indication that you're looking at out of date statistics, which makes the whole plan suspect. Also, if you created an indexed view, that's not being used, instead of all those nolock hints (you do know that can lead to missing/extra rows, right?), try a noexpand hint and run the query directly against the indexed view (which may still need to be rewritten since not all the tables are actually used).

That's a quick pass. I'm sure there are more details to pull out of it. For example, why a table spool?

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
saxena200
saxena200
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 168
Thanks for ur reply

- all 6 tables are required can't remove any of them
- Statistics are up to date, i have checked it
- Indexed view is used, table seek can be seen in the plan
- my join on ar_bhvr_dim is really heavy that is causing problem.... Sad
- NOLOCK is required i am aware that i will get extra data(i need it, not a problem)
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39456 Visits: 32630
The optimizer doesn't believe that all six tables are needed in the query. Just saying. It's using a subset of what you defined.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7833 Visits: 7145
If possible, you need to get rid of the massive GROUP BY (and also reduce the number of expensive joins required); that means using a subquery on CDBF.

I've taken my best stab at it below, but you will likely have to touch it up to get it to work properly.

Btw, you do know that "DTA" stands for Don't Trust Anything it says, don't you :-)



DECLARE @FromDate datetime = '1-march-2012'
DECLARE @ToDate datetime = '31-march-2012'

SELECT
DT.MO_OF_YR_NM [Month],
DT.MO_OF_YR [month_No],
DT.YR [Year],
DT.MIC_WK_OF_MO_NM [Week],
DT.DT [Date],
CD.CSTMR_NM [Organisation],cd.CSTMR_KEY,
PD.BS_LN_NM [Business Line],
ED.EMPE_NM [Sales Executive],
CDBF.[Total no of Lines],
CDBF.[Total No Of Unpaid Lines],
CDBF.[Total Amount Due]
FROM DT_DIM DT with (NOLOCK)
INNER JOIN (
SELECT
CDBF2.FCT_DT_KEY, CDBF2.AR_BHVR_KEY, CDBF2.CNTRCT_DD, CDBF2.CSTMR_KEY,
COUNT(DISTINCT CDBF.CSTMR_KEY) [Total no of Lines],
(CASE WHEN SUM(CDBF.BLNC)>0 THEN COUNT(DISTINCT CDBF.CSTMR_KEY) ELSE 0 END) [Total No Of Unpaid Lines],
SUM(CDBF.BLNC) [Total Amount Due]
FROM CSTMR_DUE_BLNC_FCT CDBF2 WITH (NOLOCK)
INNER JOIN DT_DIM DT2 WITH (NOLOCK) ON
DT2.DT_KEY = CDBF2.FCT_DT_KEY
GROUP BY
CDBF2.FCT_DT_KEY, CDBF2.AR_BHVR_KEY, CDBF2.CNTRCT_DD, CDBF2.CSTMR_KEY
) AS CDBF ON DT.DT_KEY=CDBF.FCT_DT_KEY
Inner Join CSTMR_DIM CD with (NOLOCK) ON CD.CSTMR_KEY=CDBF.CSTMR_KEY
Inner join AR_BHVR_DIM ABD with (NOLOCK) on ABD.AR_BHVR_KEY = CDBF.AR_BHVR_KEY
Inner join PD_DIM PD with (NOLOCK) on PD.PD_KEY = ABD.PD_KEY
Inner Join EMPE_DIM ED with (NOLOCK) ON ED.EMPE_KEY=ABD.EMPE_KEY

WHERE DT.DT>=@FromDate AND DT.DT<=@ToDate
--AND CD.CSTMR_NM IN (@Organisation)
--AND PD.BS_LN_NM IN (@BusinessLine)
--AND ED.EMPE_NM IN (@SalesExecutive)
ORDER BY DT.MO_OF_YR



SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
saxena200
saxena200
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 168
hahahaha.... at the end you have to trust DTA

well, inner query is not helping at all, Not getting any concrete solution for tuning this....
execution plan is not showing anything extremely costly i am confused

w00tw00tw00t
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39097 Visits: 38518
saxena200 (2/5/2013)
hahahaha.... at the end you have to trust DTA

well, inner query is not helping at all, Not getting any concrete solution for tuning this....
execution plan is not showing anything extremely costly i am confused

w00tw00tw00t


No, no you don't. Anything that DTA provides should be thoroughly tested before it is implemented in a production environment.

Been there, done that.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39097 Visits: 38518
One thing that your plan indicates is that there are no statistics on the the columns _col_1 or _col_2 on your indexed view. You might start there.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16125 Visits: 19534
That plan S a RA, T&S.

Out of interest, try this, and please post the actual plan:

SELECT      DT.MO_OF_YR_NM          [Month],
DT.MO_OF_YR [month_No],
DT.YR [Year],
DT.MIC_WK_OF_MO_NM [Week],
DT.DT [Date],
cstmr.[Organisation],
cstmr.CSTMR_KEY,
PD.BS_LN_NM [Business Line],
ED.EMPE_NM [Sales Executive],
cstmr.[Total no of Lines],
[Total No Of Unpaid Lines] = CASE WHEN cstmr.[Total Amount Due] > 0 THEN cstmr.[Total no of Lines] ELSE 0 END,
cstmr.[Total Amount Due]
FROM DT_DIM DT

INNER JOIN (
SELECT
CDBF.FCT_DT_KEY,
[Organisation] = CD.CSTMR_NM,
cd.CSTMR_KEY,
[Total no of Lines] = COUNT(DISTINCT CDBF.CSTMR_KEY),
[Total Amount Due] = SUM(CDBF.BLNC)
FROM CSTMR_DUE_BLNC_FCT CDBF
INNER JOIN CSTMR_DIM CD
ON CD.CSTMR_KEY=CDBF.CSTMR_KEY --
GROUP BY CDBF.FCT_DT_KEY, CD.CSTMR_NM, cd.CSTMR_KEY
) cstmr ON DT.DT_KEY = cstmr.FCT_DT_KEY

Inner join AR_BHVR_DIM ABD
on ABD.AR_BHVR_KEY = CDBF.AR_BHVR_KEY
Inner join PD_DIM PD
on PD.PD_KEY = ABD.PD_KEY
Inner Join EMPE_DIM ED
ON ED.EMPE_KEY=ABD.EMPE_KEY

WHERE DT.DT>=@FromDate AND DT.DT<=@ToDate
/*
GROUP BY
DT.MO_OF_YR_NM,
DT.MO_OF_YR,
DT.MIC_WK_OF_MO_NM,
DT.DT,
DT.YR,
CD.CSTMR_NM,
PD.BS_LN_NM,
ED.EMPE_NM,
--CDBF.FCT_DT_KEY,
CDBF.AR_BHVR_KEY,
CDBF.CNTRCT_DD,cd.CSTMR_KEY
*/
ORDER BY DT.MO_OF_YR
OPTION (EXPAND VIEWS) -- don't use the indexed view



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
saxena200
saxena200
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 168
@Chris

PFA the execution Plan...
this is not working.... Sad
Attachments
EXECUTION_PLAN_TEST.sqlplan (10 views, 115.00 KB)
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