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


SQL 2005 T-SQL Performance tuning


SQL 2005 T-SQL Performance tuning

Author
Message
d_uvarajan
d_uvarajan
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 52
Hi,
I require your help for optimizing/tuning the below query.

This query is a part in a stored procedure and this query alone consumes nearly 15 minutes to complete. It joins nearly 12 tables and all are indexed as mentioned below.

I used the "Execution plan" but could not understand to analyze. Also checked the Trace, there is no bottle-necks.

SELECT
r.rcc_id,
comp.company_code + '_' + comp.company_name AS 'Source Company',
RIGHT(r.rcc_code, CHARINDEX('-', REVERSE(r.rcc_code))-1 ) + '_' + r.rcc_name AS 'Source Cost Centre',
acd.activity_cat_code + '.' + acds.activity_code + '_' + acds.activity_name AS 'Source Sub-activity',
CASE cvc.cc_group
WHEN 0 THEN 'B'
WHEN 1 THEN 'S'
END AS 'Cost Type',
DestComp.company_code + '_' + DestComp.company_name AS 'Destination Company',
pc.profit_centre_code + '_' + pc.profit_centre_name AS 'Destination Profit Center',
cd.channel_code + '_' + cd.channel_name AS 'Destination Channel',
pcds.product_cat_code + '_' + pcds.product_cat_name AS 'Destination Product Category',
cust.customer_level_code + '_' + cust.customer_level_name AS 'Destination Customer',
acd.global_stream_id,
SUM(channel_cost_budg) resource_cost,
SUM(channel_cost_oge) oge_cost,
SUM(channel_cost_globe) globe_cost,
cvc.cc_group,
rcc_moge.common_cost_rcc_id AS 'Common_rcc_id',
rva.rcc_value_attr_name AS 'ValueField'
FROM
#tblChannelCost cvc
INNER JOIN profit_centre pc WITH(NOLOCK)
ON (
pc.profit_centre_id = cvc.profit_centre_id
)
INNER JOIN channel_dataset cd WITH(NOLOCK)
ON (
cd.channel_dataset_id = cvc.channel_dataset_id
)
INNER JOIN product_category_dataset pcds WITH (NOLOCK)
ON (
pcds.product_category_dataset_id = cvc.product_category_dataset_id
)
INNER JOIN activity_dataset acds WITH(NOLOCK)
ON (
cvc.activity_dataset_id = acds.activity_dataset_id
)
INNER JOIN activity_category_dataset acd WITH(NOLOCK)
ON (
acd.activity_category_dataset_id = acds.activity_category_dataset_id
)
INNER JOIN customer_dataset cust WITH(NOLOCK)
ON (
cust.customer_dataset_id = cvc.customer_dataset_id
)
INNER JOIN rcc r WITH(NOLOCK)
ON (
cvc.rcc_id = r.rcc_id
)
INNER JOIN unit u WITH(NOLOCK)
ON (
u.unit_id = r.unit_id
)
INNER JOIN site s WITH(NOLOCK)
ON (
s.site_id = u.site_id
)
INNER JOIN company_dataset comp WITH(NOLOCK)
ON (
comp.company_dataset_id = s.company_dataset_id
)
INNER JOIN company_dataset DestComp WITH(NOLOCK)
ON (
DestComp.company_dataset_id = pc.company_dataset_id
)
INNER JOIN (
SELECT
rcc_id,
common_cost_rcc_id,
cc_group
FROM
#tblRCCCommanMOGEValues
) rcc_moge
ON (
rcc_moge.rcc_id = cvc.rcc_id
AND ISNULL(rcc_moge.common_cost_rcc_id, -1) = ISNULL(cvc.common_cost_rcc_id, -1)
AND ISNULL(rcc_moge.cc_group, 255) = ISNULL(cvc.cc_group, 255)
)
INNER JOIN rcc_value_attr rva
ON (
rva.rcc_value_attr_id = r.rcc_value_attr_id
)
WHERE
cvc.driver_id <> 8
GROUP BY
r.rcc_id,
comp.company_name,
r.rcc_code,
r.rcc_name,
acd.activity_cat_code,
acds.activity_code,
acds.activity_name,
pc.profit_centre_name,
cd.channel_name,
pcds.product_cat_name,
acd.global_stream_id,
comp.company_code,
pc.profit_centre_code,
cd.channel_code,
pcds.product_cat_code,
cvc.cc_group,
DestComp.company_code,
DestComp.company_name,
cust.customer_level_code,
cust.customer_level_name,
rcc_moge.common_cost_rcc_id,
rva.rcc_value_attr_name,
cvc.customer_dataset_id

Rows availability:
select count(*) from #tblChannelCost WHERE driver_id <> 8
-- 923004

select count(profit_centre_id) from profit_centre pc
-- 2037

select count(channel_dataset_id) from channel_dataset cd
-- 2204

select count(product_category_dataset_id) from product_category_dataset
-- 119767

select count(activity_dataset_id) from activity_dataset
-- 10320

select count(activity_category_dataset_id) from activity_category_dataset
-- 8671

select count(customer_dataset_id) from customer_dataset
-- 12736

select count(rcc_id) from rcc
-- 31729

select count(unit_id) from unit
-- 11093

select count(site_id) from site
-- 913

select count(company_dataset_id) from company_dataset
-- 176

select count(*) from company_dataset
-- 176

Index details:
CREATE NONCLUSTERED INDEX idx_tblChannelCost_profit_centre_id ON #tblChannelCost(profit_centre_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_channel_dataset_id ON #tblChannelCost(channel_dataset_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_product_category_dataset_id ON #tblChannelCost(product_category_dataset_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_activity_dataset_id ON #tblChannelCost(activity_dataset_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_customer_dataset_id ON #tblChannelCost(customer_dataset_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_rcc_id ON #tblChannelCost(rcc_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_common_cost_rcc_id ON #tblChannelCost(common_cost_rcc_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_cc_group ON #tblChannelCost(cc_group)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_driver_id ON #tblChannelCost(driver_id)

CREATE CLUSTERED INDEX pk_profit_centre ON profit_centre(profit_centre_id)
CREATE CLUSTERED INDEX pk_channel_dataset ON channel_dataset(channel_dataset_id)
CREATE CLUSTERED INDEX pk_product_category_dataset ON product_category_dataset(product_category_dataset_id)
CREATE CLUSTERED INDEX pk_activity_dataset ON activity_dataset(activity_dataset_id)
CREATE CLUSTERED INDEX pk_activity_category_dataset ON activity_category_dataset(activity_category_dataset_id)
CREATE CLUSTERED INDEX pk_customer_dataset ON customer_dataset(customer_dataset_id)
CREATE CLUSTERED INDEX pk_rcc ON rcc(rcc_id)
CREATE CLUSTERED INDEX pk_unit ON unit(unit_id)
CREATE CLUSTERED INDEX pk_site ON site(site_id)
CREATE CLUSTERED INDEX pk_company_dataset ON company_dataset(company_dataset_id)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86180 Visits: 45229
Please post table definitions and execution plan. Also consider getting rid of those nolocks, they're not go-faster options they can result in incorrect results.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


RatanDeep Saha
RatanDeep Saha
Right there with Babe
Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)

Group: General Forum Members
Points: 770 Visits: 693
You are using almost 15 INNER JOINS that's also not very good idea.
d_uvarajan
d_uvarajan
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 52
How can I save the execution plan also can't take screenshot as it runs more than 1 screen!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86180 Visits: 45229
Right click plan -> save as. Please don't take a screenshot, pictures of the plan are near-useless.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


d_uvarajan
d_uvarajan
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 52
As I mentioned earlier it's a part in the stored procedure and there are lot more queries. I wish to know how to take the Execution plan for this query alone so that I can insert it here.
d_uvarajan
d_uvarajan
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 52
The issue has been resolved! Thanks for everyone! Smile
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3047 Visits: 2766
Can you please post what you did to resolve your issue so that others will get benefit from that if they face similar kind of issse in performance?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
d_uvarajan
d_uvarajan
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 52
Indexing only resolved the issue. It increased the performance by 20%. But it's fine now but even though the expected was 70%.
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