SQL 2005 Performance issue after a migration

Question

Post reply

SQL 2005 Performance issue after a migration

Karl McIntyre

SSC Journeyman

Points: 99
More actions
November 24, 2010 at 7:17 am

#243208

I have recently migrated a datawarehouse from sql server 2000 to sql 2005. After the upgrade we have seen greately improved perfomance in most areas. however a few areas have suffered. The old sql 200 warehouse is still available for comparison although the data is now a few weeks old. One of the biggest issues i seem to be having is that queries written by the DW team that include a subquery (even if it is a simple subquery) is degrading performance severely. This issue does not exist in sql 2000.
The query is below:
SELECT TOP 1
tbl_provider_spell.hospital_provider_spell_number,
tbl_patient.pasid,
tbl_patient.nhs_number,
convert(datetime,convert(char(10),tbl_provider_spell.admission_date_dt,120),120),
convert(datetime,convert(char(10),tbl_provider_spell.discharge_date_dt,120),120),
count(distinct provider_spell_measures.hospital_provider_spell_number),
tbl_provider_ipact.provider_code,
tbl_admis_method_ipact.national_code,
tbl_pat_class.national_code
FROM
tbl_admis_method tbl_admis_method_ipact INNER JOIN tbl_provider_spell ON (tbl_provider_spell.fk_admission_method=tbl_admis_method_ipact.pk_admis_method AND tbl_provider_spell.delete_flag='N')
INNER JOIN tbl_consultant_episode ON (tbl_provider_spell.pk_provider_spell=tbl_consultant_episode.fk_provider_spell AND tbl_provider_spell.delete_flag='N' AND tbl_consultant_episode.delete_flag='N')
INNER JOIN tbl_purchaser ON (tbl_consultant_episode.delete_flag='N' AND tbl_consultant_episode.fk_purchaser=tbl_purchaser.pk_purchaser)
INNER JOIN tbl_pat_class ON (tbl_provider_spell.fk_patient_classification=tbl_pat_class.pk_pat_class AND tbl_provider_spell.delete_flag='N')
INNER JOIN tbl_calendar Discharge_Date_Calendar ON (tbl_provider_spell.delete_flag='N' AND tbl_provider_spell.fk_cal_discharge_date_dt=Discharge_Date_Calendar.pk_calendar)
INNER JOIN tbl_provider_spell provider_spell_measures ON (tbl_provider_spell.delete_flag='N' AND tbl_provider_spell.pk_provider_spell=provider_spell_measures.pk_provider_spell)
INNER JOIN tbl_provider tbl_provider_ipact ON (tbl_provider_ipact.pk_provider=tbl_provider_spell.fk_provider AND tbl_provider_spell.delete_flag='N')
INNER JOIN tbl_patient ON (tbl_provider_spell.delete_flag='N' AND tbl_patient.pk_patient=tbl_provider_spell.fk_patient AND tbl_patient.delete_flag = 'N')
INNER JOIN tbl_gp ON (tbl_gp.pk_gp=tbl_patient.fk_gp AND tbl_patient.delete_flag = 'N')
INNER JOIN tbl_pct tbl_pct_gp ON (tbl_pct_gp.pk_pct=tbl_gp.fk_pct)
INNER JOIN tbl_pct tbl_pct_patient ON (tbl_pct_patient.pk_pct=tbl_patient.fk_pct AND tbl_patient.delete_flag = 'N')
WHERE
(
Discharge_Date_Calendar.fy_year In ( '2008/09' )
AND
convert(datetime,convert(char(10),tbl_provider_spell.discharge_date_dt,120),120) BETWEEN '01/01/2009 00:0:0' AND '28/02/2009 00:0:0'
AND
( tbl_consultant_episode.episode_number = 1 and tbl_consultant_episode.end_date_cons_episode_dt is not null )
AND
( CASE
WHEN tbl_purchaser.purchaser_code IN ('VPP00', 'TDH00', 'YDD82') THEN tbl_purchaser.purchaser_code
WHEN NOT(ISNULL(tbl_pct_gp.pct_code, 'X9800') IN ('X9800', 'Q9900')) THEN tbl_pct_gp.pct_code
WHEN NOT(ISNULL(tbl_pct_patient.pct_code, 'X9800') IN ('X9800', 'Q9900')) THEN tbl_pct_patient.pct_code
ELSE tbl_purchaser.purchaser_code END In ( '5NM00','5J300','5J100' ) )
AND
( tbl_admis_method_ipact.national_code IN ('21', '22', '23', '24', '28')
AND EXISTS (
SELECT 1
FROM dbo.tbl_provider_spell ps_1
INNER JOIN dbo.tbl_patient pat_1
ON pat_1.pk_patient = ps_1.fk_patient
INNER JOIN dbo.tbl_patient pat_2
ON ISNULL(pat_2.nhs_number,pat_2.pasid) = ISNULL(pat_1.nhs_number,pat_1.pasid)
INNER JOIN dbo.tbl_provider_spell ps_2
ON ps_2.fk_patient = pat_2.pk_patient
WHERE ps_1.pk_provider_spell = tbl_provider_spell.pk_provider_spell
AND ps_1.pk_provider_spell <> ps_2.pk_provider_spell
AND ps_1.admission_date_dt BETWEEN ps_2.discharge_date_dt AND DATEADD(d, 3, ps_2.discharge_date_dt)
AND (CASE
WHEN pat_2.pasid = pat_1.pasid THEN ps_1.pk_provider_spell
ELSE ps_2.pk_provider_spell
END) >= ps_2.pk_provider_spell
)
)
)
GROUP BY
tbl_provider_spell.hospital_provider_spell_number,
tbl_patient.pasid,
tbl_patient.nhs_number,
convert(datetime,convert(char(10),tbl_provider_spell.admission_date_dt,120),120),
convert(datetime,convert(char(10),tbl_provider_spell.discharge_date_dt,120),120),
tbl_provider_ipact.provider_code,
tbl_admis_method_ipact.national_code,
tbl_pat_class.national_code
this takes two minutes on the 2000 box and runs for hours without completing on the 2005 box.
can anyone give me any ideas

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

have you updated the stats post upgrade?

Also generating execution plan can give you more option.

As well running query with 'set statistics IO on' will give you information on read and write, which is another information you will get to judge the slowness.

----------
Ashish

Karl McIntyre SSC Journeyman Points: 99 More actions · Answer 2

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.4311.00">

<BatchSequence>

<Batch>

<Statements>

<StmtSimple StatementText="SELECT TOP 1 tbl_provider_spell.hospital_provider_spell_number, tbl_patient.pasid, tbl_patient.nhs_number, convert(datetime,convert(char(10),tbl_provider_spell.admission_date_dt,120),120), convert(datetime,convert(char(10),tbl_provider_spell.discharge_date_dt,120),120), count(distinct provider_spell_measures.hospital_provider_spell_number), tbl_provider_ipact.provider_code, tbl_admis_method_ipact.national_code, tbl_pat_class.national_code FROM tbl_admis_method tbl_admis_method_ipact INNER JOIN tbl_provider_spell ON (tbl_provider_spell.fk_admission_method=tbl_admis_method_ipact.pk_admis_method AND tbl_provider_spell.delete_flag='N') INNER JOIN tbl_consultant_episode ON (tbl_provider_spell.pk_provider_spell=tbl_consultant_episode.fk_provider_spell AND tbl_provider_spell.delete_flag='N' AND tbl_consultant_episode.delete_flag='N') INNER JOIN tbl_purchaser ON (tbl_consultant_episode.delete_flag='N' AND tbl_consultant_episode.fk_purchaser=tbl_purchaser.pk_purchaser) INNER JOIN tbl_pat_class ON (tbl_provider_spell.fk_patient_classification=tbl_pat_class.pk_pat_class AND tbl_provider_spell.delete_flag='N') INNER JOIN tbl_calendar Discharge_Date_Calendar ON (tbl_provider_spell.delete_flag='N' AND tbl_provider_spell.fk_cal_discharge_date_dt=Discharge_Date_Calendar.pk_calendar) INNER JOIN tbl_provider_spell provider_spell_measures ON (tbl_provider_spell.delete_flag='N' AND tbl_provider_spell.pk_provider_spell=provider_spell_measures.pk_provider_spell) INNER JOIN tbl_provider tbl_provider_ipact ON (tbl_provider_ipact.pk_provider=tbl_provider_spell.fk_provider AND tbl_provider_spell.delete_flag='N') INNER JOIN tbl_patient ON (tbl_provider_spell.delete_flag='N' AND tbl_patient.pk_patient=tbl_provider_spell.fk_patient AND tbl_patient.delete_flag = 'N') INNER JOIN tbl_gp ON (tbl_gp.pk_gp=tbl_patient.fk_gp AND tbl_patient.delete_flag = 'N') INNER JOIN tbl_pct tbl_pct_gp ON (tbl_pct_gp.pk_pct=tbl_gp.fk_pct) INNER JOIN tbl_pct tbl_pct_patient ON (tbl_pct_patient.pk_pct=tbl_patient.fk_pct AND tbl_patient.delete_flag = 'N') WHERE ( Discharge_Date_Calendar.fy_year In ( '2008/09' ) AND convert(datetime,convert(char(10),tbl_provider_spell.discharge_date_dt,120),120) BETWEEN '01/01/2009 00:0:0' AND '28/02/2009 00:0:0' AND ( tbl_consultant_episode.episode_number = 1 and tbl_consultant_episode.end_date_cons_episode_dt is not null ) AND ( CASE WHEN tbl_purchaser.purchaser_code IN ('VPP00', 'TDH00', 'YDD82') THEN tbl_purchaser.purchaser_code WHEN NOT(ISNULL(tbl_pct_gp.pct_code, 'X9800') IN ('X9800', 'Q9900')) THEN tbl_pct_gp.pct_code WHEN NOT(ISNULL(tbl_pct_patient.pct_code, 'X9800') IN ('X9800', 'Q9900')) THEN tbl_pct_patient.pct_code ELSE tbl_purchaser.purchaser_code END In ( '5NM00','5J300','5J100' ) ) AND ( tbl_admis_method_ipact.national_code IN ('21', '22', '23', '24', '28') AND EXISTS ( SELECT 1 FROM dbo.tbl_provider_spell ps_1 INNER JOIN dbo.tbl_patient pat_1 ON pat_1.pk_patient = ps_1.fk_patient INNER JOIN dbo.tbl_patient pat_2 ON ISNULL(pat_2.nhs_number,pat_2.pasid) = ISNULL(pat_1.nhs_number,pat_1.pasid) INNER JOIN dbo.tbl_provider_spell ps_2 ON ps_2.fk_patient = pat_2.pk_patient WHERE ps_1.pk_provider_spell = tbl_provider_spell.pk_provider_spell AND ps_1.pk_provider_spell <> ps_2.pk_provider_spell AND ps_1.admission_date_dt BETWEEN ps_2.discharge_date_dt AND DATEADD(d, 3, ps_2.discharge_date_dt) AND (CASE WHEN pat_2.pasid = pat_1.pasid THEN ps_1.pk_provider_spell ELSE ps_2.pk_provider_spell END) >= ps_2.pk_provider_spell ) ) ) GROUP BY tbl_provider_spell.hospital_provider_spell_number, tbl_patient.pasid, tbl_patient.nhs_number, convert(datetime,convert(char(10),tbl_provider_spell.admission_date_dt,120),120), convert(datetime,convert(char(10),tbl_provider_spell.discharge" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="264.152" StatementEstRows="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut">

<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false"/>

<QueryPlan CachedPlanSize="344" CompileTime="4318" CompileCPU="4318" CompileMemory="96976">

<RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="81" EstimatedTotalSubtreeCost="264.152" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">

<OutputList>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_admis_method]" Alias="[tbl_admis_method_ipact]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider_spell]" Column="hospital_provider_spell_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_pat_class]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider]" Alias="[tbl_provider_ipact]" Column="provider_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="nhs_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="pasid"/>

<ColumnReference Column="Expr1040"/>

<ColumnReference Column="Expr1041"/>

<ColumnReference Column="Expr1042"/>

</OutputList>

<Top RowCount="0" IsPercent="0" WithTies="0">

<TopExpression>

<ScalarOperator ScalarString="(1)">

<Const ConstValue="(1)"/>

</ScalarOperator>

</TopExpression>

<RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="6.32222e-005" AvgRowSize="81" EstimatedTotalSubtreeCost="264.152" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">

<OutputList>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_admis_method]" Alias="[tbl_admis_method_ipact]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider_spell]" Column="hospital_provider_spell_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_pat_class]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider]" Alias="[tbl_provider_ipact]" Column="provider_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="nhs_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="pasid"/>

<ColumnReference Column="Expr1040"/>

<ColumnReference Column="Expr1041"/>

<ColumnReference Column="Expr1042"/>

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1042"/>

<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1080],0)">

<Convert DataType="int" Style="0" Implicit="1">

<ScalarOperator>

<Identifier>

<ColumnReference Column="Expr1080"/>

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<RelOp NodeId="2" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="6.32222e-005" AvgRowSize="81" EstimatedTotalSubtreeCost="264.152" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">

<OutputList>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_admis_method]" Alias="[tbl_admis_method_ipact]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider_spell]" Column="hospital_provider_spell_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_pat_class]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider]" Alias="[tbl_provider_ipact]" Column="provider_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="nhs_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="pasid"/>

<ColumnReference Column="Expr1040"/>

<ColumnReference Column="Expr1041"/>

<ColumnReference Column="Expr1080"/>

</OutputList>

<StreamAggregate>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1080"/>

<ScalarOperator ScalarString="Count(*)">

<Aggregate AggType="countstar" Distinct="0"/>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<GroupBy>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider_spell]" Column="hospital_provider_spell_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="pasid"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="nhs_number"/>

<ColumnReference Column="Expr1040"/>

<ColumnReference Column="Expr1041"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider]" Alias="[tbl_provider_ipact]" Column="provider_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_admis_method]" Alias="[tbl_admis_method_ipact]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_pat_class]" Column="national_code"/>

</GroupBy>

<RelOp NodeId="3" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="5.74748e-005" AvgRowSize="88" EstimatedTotalSubtreeCost="264.152" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">

<OutputList>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_admis_method]" Alias="[tbl_admis_method_ipact]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider_spell]" Column="hospital_provider_spell_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_pat_class]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider_spell]" Alias="[provider_spell_measures]" Column="hospital_provider_spell_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider]" Alias="[tbl_provider_ipact]" Column="provider_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="nhs_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="pasid"/>

<ColumnReference Column="Expr1040"/>

<ColumnReference Column="Expr1041"/>

</OutputList>

<StreamAggregate>

<DefinedValues/>

<GroupBy>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider_spell]" Column="hospital_provider_spell_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="pasid"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="nhs_number"/>

<ColumnReference Column="Expr1040"/>

<ColumnReference Column="Expr1041"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider]" Alias="[tbl_provider_ipact]" Column="provider_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_admis_method]" Alias="[tbl_admis_method_ipact]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_pat_class]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider_spell]" Alias="[provider_spell_measures]" Column="hospital_provider_spell_number"/>

</GroupBy>

<RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="0.00381235" AvgRowSize="88" EstimatedTotalSubtreeCost="264.152" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">

<OutputList>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_admis_method]" Alias="[tbl_admis_method_ipact]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider_spell]" Column="hospital_provider_spell_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_pat_class]" Column="national_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider_spell]" Alias="[provider_spell_measures]" Column="hospital_provider_spell_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_provider]" Alias="[tbl_provider_ipact]" Column="provider_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="nhs_number"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_patient]" Column="pasid"/>

<ColumnReference Column="Expr1040"/>

<ColumnReference Column="Expr1041"/>

</OutputList>

<NestedLoops Optimized="0" WithOrderedPrefetch="1">

<OuterReferences>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_purchaser]" Column="purchaser_code"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_gp]" Column="fk_pct"/>

<ColumnReference Database="[nhs_data_warehouse]" Schema="[dbo]" Table="[tbl_pct]" Alias="[tbl_pct_patient]" Column="pct_code"/>

<ColumnReference Column="Expr1079"/>

</OuterReferences>

<RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="15.8686" EstimateIO="0" EstimateCPU="0.00381235" AvgRowSize="104" EstimatedTotalSubtreeCost="264.11" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">