February 5, 2013 at 2:29 pm
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
February 5, 2013 at 4:08 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2013 at 4:26 pm
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.... 🙁
- NOLOCK is required i am aware that i will get extra data(i need it, not a problem)
February 5, 2013 at 4:30 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2013 at 4:43 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 5, 2013 at 5:46 pm
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
:w00t::w00t::w00t:
February 5, 2013 at 8:46 pm
saxena200 (2/5/2013)
hahahaha.... at the end you have to trust DTAwell, 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
:w00t::w00t::w00t:
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.
February 5, 2013 at 8:55 pm
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.
February 6, 2013 at 1:55 am
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
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
February 6, 2013 at 8:14 am
saxena200 (2/6/2013)
@ChrisPFA the execution Plan...
this is not working.... 🙁
You commented out the query hint. Can you run it without the query hint commented out, please?
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
February 6, 2013 at 8:31 am
saxena200 (2/6/2013)
@ChrisPFA the execution Plan...
this is not working.... 🙁
Apologies, I missed a column from the GROUP BY:
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,
cstmr.CNTRCT_DD, -- NEW
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),
cdbf.ar_bhvr_key,
[Total Amount Due]= SUM(CDBF.BLNC),
CDBF.CNTRCT_DD -- NEW
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,
cdbf.AR_BHVR_KEY,
CDBF.CNTRCT_DD -- NEW
) cstmr ON DT.DT_KEY = cstmr.FCT_DT_KEY
Inner join AR_BHVR_DIM ABD
on ABD.AR_BHVR_KEY = cstmr.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)
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply