December 4, 2009 at 2:49 am
Hi
I don't quite understand the function (but it's worked).
I have 2 servers one my reporting Web Server running SSRS (1) and the other the for a better word of it the Datawarehouse (2).
Running an SSRS report on (1) triggers a Sproc on (2) however one report in particular took 5 mins to run. Machine (1) is a single processor Machine (2) dual processor 16GB ram. They are both SQL2008 running on 2003 Servers (both virtual VMware environments)
Looking at the processes that went on when this report ran I decided to use the following in my Sproc running on (2)
option(maxdop 1)
Am a right in assuming that I have forced that Sproc not to try to run in parrallisms over the two processor? And that is why I have seen a dramatic increase in speed (and I mean dramatic).
What I am saying is this is at the edge of my knowledge at this moment and would like to fully understand the reason and implications of the
option(maxdop 1) statement.
Thanks
Doug
December 4, 2009 at 6:33 am
douglas.allison-1136597 (12/4/2009)
Am a right in assuming that I have forced that Sproc not to try to run in parrallisms over the two processor?
Correct. MAXDOP sets the maximum degree of parallelism, the number of processor cores that a query operator may use. By setting it to 1, you're saying don't parallel.
Sometimes there are problems with queries (inadequate indexes or non-optimal code) that's a minor problem on 1 core but, if the query parallels the duration goes way up, usually due to inter-thread waits.
Want to post the query and have it looked up? Or are you happy with the maxdop patch?
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
December 4, 2009 at 7:26 am
USE [dw_reports]
GO
/****** Object: StoredProcedure [dbo].[s_rpt_SLA_code_enquiry] Script Date: 12/04/2009 14:25:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[s_rpt_SLA_code_enquiry]
@codeset varchar(20) ,
@code varchar(20) ,
@fiscal_year varchar(20)
as
/*
exec s_rpt_SLA_code_enquiry 'code_sla_2009_2010', 'CN301AN', '2009/10'
*/
select st.source_table ,
d.source_table__id ,
t.month ,
t.encoding_criteria_id , -- not needed
t.code ,
activity_date = convert(varchar(11),t.activity_date,106) ,
d.entity_value ,
d.date_of_birth ,
d.sex ,
d.nhs_number ,
d.activity__1_userdesc ,
d.activity__2_userdesc ,
d.activity__3_userdesc ,
d.format_userdesc ,
d.location_userdesc ,
d.contact_staff_userdesc ,
d.new_ongoing_closed ,
d.face_to_face ,
d.specialty ,
d.team ,
d.service ,
d.staff_name ,
d.pct_code
from rpt_sla_code_enquiry t
left join rpt_sla_code_enquiry_data d
on t.source_table_id = d.source_table_id
and t.source_table__id = d.source_table__id
left join source_table st
on st.source_table_id = t.source_table_id
where t.codeset = @codeset
and t.code = @code
and t.fiscal_year = @fiscal_year
order by t.activity_date
option(maxdop 1)
--------------------------------------------------------------------
Sproc
lost the indentation 😀
December 4, 2009 at 7:37 am
Also I always check the costs of any script on our development server and make sure it's properly indexed and the subtree costs are to a minimum before transferring to our live environment.
December 4, 2009 at 7:42 am
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
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
December 4, 2009 at 8:07 am
Thanks for this please find attached a copy of my execution plan.
Doug
December 4, 2009 at 8:19 am
Table and index definitions?
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
December 4, 2009 at 9:01 am
Whenever I see a query with OUTER JOIN's I my first question is do you really need OUTER JOIN's or are INNER JOIN's acceptable? IN your case specifically the join to the source_table table, since source_table_id is your join criteria I'm assuming there is some kind of referential integrity between the tables.
As Gail has already asked, there isn't a whole lot more anyone can suggest without Table and Index Definitions.
Could you also post the parallel execution plan as this one is not parallelized and the suggestions made for the parallel plan may be different than the ones made for this plan.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 4, 2009 at 9:07 am
It looks to me like the thing slowing the query down is the clustered index scan on dbo.rpt_sla_code_enquiry_data.
You might be able to add an index with source_table_id and source_table__id as the leading edge, and get an improvement out of that. On the other hand, that may just result in heavy bookmark lookups, unless you make it a covering index, and there are a lot of columns in that table to cover.
I'd definitely look into a covering index for that table for this query. Would be better in SQL 2005/2008, since you could use the Include clause for most of the columns, but in SQL 2000, you may or may not get what you want from an index on all those columns.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 4, 2009 at 12:49 pm
GSquared (12/4/2009)
Would be better in SQL 2005/2008, since you could use the Include clause for most of the columns, but in SQL 2000, you may or may not get what you want from an index on all those columns.
Think this is just posted in wrong forum. Initial post mentions SQL 2008
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
December 4, 2009 at 12:52 pm
GilaMonster (12/4/2009)
GSquared (12/4/2009)
Would be better in SQL 2005/2008, since you could use the Include clause for most of the columns, but in SQL 2000, you may or may not get what you want from an index on all those columns.This this is just posted in wrong forum. Initial post mentions SQL 2008
In that case, I'd definitely look into a covering index for that table for the query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply