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 12»»

query tuning (PFA, execution plan and indexed view)- need help to enhance performance Expand / Collapse
Author
Message
Posted Tuesday, February 5, 2013 2:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 2:11 PM
Points: 34, Visits: 100
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


  Post Attachments 
Execution_Plan.zip (7 views, 9.88 KB)
Indexed_View_Statistics.zip (2 views, 738 bytes)
Post #1416107
Posted Tuesday, February 5, 2013 4:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 14,022, Visits: 28,400
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1416144
Posted Tuesday, February 5, 2013 4:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 2:11 PM
Points: 34, Visits: 100
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)
Post #1416155
Posted Tuesday, February 5, 2013 4:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 14,022, Visits: 28,400
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1416158
Posted Tuesday, February 5, 2013 4:43 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:06 AM
Points: 2,268, Visits: 3,425
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1416162
Posted Tuesday, February 5, 2013 5:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 2:11 PM
Points: 34, Visits: 100
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

Post #1416181
Posted Tuesday, February 5, 2013 8:46 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:55 AM
Points: 20,804, Visits: 32,734
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



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.



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)
Post #1416213
Posted Tuesday, February 5, 2013 8:55 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:55 AM
Points: 20,804, Visits: 32,734
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.



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)
Post #1416215
Posted Wednesday, February 6, 2013 1:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 6,868, Visits: 14,175
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
Post #1416312
Posted Wednesday, February 6, 2013 7:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 2:11 PM
Points: 34, Visits: 100
@Chris

PFA the execution Plan...
this is not working....


  Post Attachments 
EXECUTION_PLAN_TEST.sqlplan (6 views, 115.77 KB)
Post #1416514
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse