November 29, 2007 at 9:37 am
Please see the attached query. In SQL Server 2000, this query ran in ~1 sec. In SQL Server 2005 it takes 40 seconds!!!! Yikes. I had a look through the execution plan and in 2005 it is bringing back 100,000s of rows where in 2000 it was only find 1 row. The indexes and statistics were all rebuilt on import from 2005 to 2000. I have also run it through the DTA but and applied the suggestions but it didn't make any difference under 2005. Please help.
ALTER PROCEDURE [dbo].[spSelectCancelledTitlesByTitleNumber]
@titleNumber varchar(13),
@cancelledAfter datetime = null
AS
Declare @Level smallint
Declare @MaxLevel smallint
Declare @FurtherBreakdown smallint
Declare @SKeyCanCS as uniqueidentifier
Declare @SKeyCanTi as uniqueidentifier
Declare @SKeyCanNP as uniqueidentifier
Declare @NameCanCS as varchar(60)
Declare @NameCanTi as varchar(60)
Declare @NameCanNP as varchar(60)
Select @SKeyCanCS = SKey from ArticleType where HCKey = 'CancelledCondoPlan'
Select @SKeyCanTi = SKey from ArticleType where HCKey = 'CancelledTitle'
Select @SKeyCanNP = SKey from ArticleType where HCKey = 'CancelledNonPatent'
Select @NameCanCS = [Name] from ArticleType where HCKey = 'CancelledCondoPlan'
Select @NameCanTi = [Name] from ArticleType where HCKey = 'CancelledTitle'
Select @NameCanNP = [Name] from ArticleType where HCKey = 'CancelledNonPatent'
Set @Level = 0
If substring(@titlenumber,10,1) = '+'
and not exists (Select *
from ALTA_Staging..vwTITL TITL
inner join vwMinLTOXHTOXLINC LTOXHTOXLINC on LTOXHTOXLINC.TITLE_REFRNC_NBR = TITL.DTI_TITLE_REFRNC_NBR
where DTI_TITLE_REFRNC_NBR = @titleNumber
and DTI_TITLE_STATUS_IND in ('R','V')
and (TITL.DTI_EXPIRY_DATE is null or TITL.DTI_EXPIRY_DATE >= @cancelledAfter or @cancelledAfter is null)) begin
select @titlenumber = left(@titlenumber,9) + left('000', 13 - len(@titlenumber)) + substring(@titlenumber,11,999)
end
Select Distinct
TITL.DTI_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR,
LTOXHTOXLINC.PROPRTY_PARCEL_ID,
LTOXHTOXLINC.LINC_NBR,
LTOXHTOXLINC.RIGHTS_IND,
LTOXHTOXLINC.NON_PATENT_IND,
LTOXHTOXLINC.LINCCount,
LTOXHTOXLINC.MUNC_CODE,
LTOXHTOXLINC.LTOXHTOX,
@Level as Level
into #TempTITLHist
from ALTA_Staging..vwTITL TITL
inner join vwMinLTOXHTOXLINC LTOXHTOXLINC on LTOXHTOXLINC.TITLE_REFRNC_NBR = TITL.DTI_TITLE_REFRNC_NBR
where DTI_TITLE_REFRNC_NBR = @titleNumber
and DTI_TITLE_STATUS_IND in ('R','V')
and (TITL.DTI_EXPIRY_DATE is null or TITL.DTI_EXPIRY_DATE >= @cancelledAfter or @cancelledAfter is null)
exec sp2SelectCancelledTitlesByTitleNumber @Level, @cancelledAfter
Select @MaxLevel = Max(Level) from #TempTITLHist
SelectLevel,
#TempTITLHist.TITLE_REFRNC_NBR as TitleNumber,
min(Case
when PROPRTY_PARCEL_ID like '%;CS' then @NameCanCS
when NON_PATENT_IND = 'N' then @NameCanTi
when NON_PATENT_IND = 'Y' then @NameCanNP
else null
end) as TitleType,
min(LINC_NBR) as LINCNumber,
min(LincCount) as LINCCount,
min(PROPRTY_PARCEL_ID) as ShortLegalDescription,
min(MUNC_CODE) as Municipality,
min(PropertyRightsType.[Name]) as RightsType,
min(TITL.DTI_CREATE_DATE) as RegistrationDate,
min(TITL.DTI_EXPIRY_DATE) as TitleChangeDate,
min(#TempTITLHist.TITLE_REFRNC_NBR) as ArticleID,
Case
when PROPRTY_PARCEL_ID like '%;CS' then @SKeyCanCS
when NON_PATENT_IND = 'N' then @SKeyCanTi
when NON_PATENT_IND = 'Y' then @SKeyCanNP
else null
end as ArticleType,
Sum(Case
when Level = @MaxLevel then
Case
when REFTITL.DTI_EXPIRY_DATE >= @cancelledAfter
or (REFTITL.DTI_EXPIRY_DATE is not null and @cancelledAfter is null) then 1
else 0
end
else null
end) as FurtherBreakdown
from #TempTitlHist
Inner join ALTA_Staging..vwTITL TITL on TITL.DTI_TITLE_REFRNC_NBR = #TempTITLHist.TITLE_REFRNC_NBR
left outer join ALTA_Staging..vwTITL REFTITL on REFTITL.DTI_TITLE_REFRNC_NBR = TITL.DRE_TITLE_REFRNC_NBR
left outer join Spin_II..PropertyRightsType PropertyRightsType on PropertyRightsType.HCKey = #TempTITLHist.RIGHTS_IND
where (LTOXHTOX = 'HTOX' or TITL.DTI_EXPIRY_DATE is not null)
and TITL.DTI_TITLE_STATUS_IND in ('R','V')
and (TITL.DTI_EXPIRY_DATE >= @cancelledAfter or @cancelledAfter is null)
Group by Level,
#TempTITLHist.TITLE_REFRNC_NBR,
Case
when PROPRTY_PARCEL_ID like '%;CS' then @SKeyCanCS
when NON_PATENT_IND = 'N' then @SKeyCanTi
when NON_PATENT_IND = 'Y' then @SKeyCanNP
else null
end
Order by Level, #TempTITLHist.TITLE_REFRNC_NBR
drop table #TempTITLHist
The above stored proc calls sp2SelectCancelledTitlesByTitleNumber. Details of that one are:
Declare @NextLevel smallint
Declare @titleNumber varchar(12)
-- This is done because it gives better performance than a join to #TempTITLHist
-- Works because there is only one title at this level or SP is not executed
Select @titleNumber = TITLE_REFRNC_NBR from #TempTITLHist where Level = @Level
Set @NextLevel = @Level + 1
Insert into #TempTITLHist
SelectTITL.DRE_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR ,
LTOXHTOXLINC.PROPRTY_PARCEL_ID,
LTOXHTOXLINC.LINC_NBR,
LTOXHTOXLINC.RIGHTS_IND,
LTOXHTOXLINC.NON_PATENT_IND,
LTOXHTOXLINC.LINCCount,
LTOXHTOXLINC.MUNC_CODE,
LTOXHTOXLINC.LTOXHTOX,
@NextLevel as Level
from ALTA_Staging..vwTITL TITL
inner join vwMinLTOXHTOXLINC LTOXHTOXLINC on LTOXHTOXLINC.TITLE_REFRNC_NBR = TITL.DRE_TITLE_REFRNC_NBR
where TITL.DTI_TITLE_REFRNC_NBR = @titleNumber
and DTI_TITLE_STATUS_IND in ('R','V')
and (TITL.DTI_EXPIRY_DATE is null or TITL.DTI_EXPIRY_DATE >= @cancelledAfter or @cancelledAfter is null)
If (Select count(distinct TITLE_REFRNC_NBR) from #TempTITLHist where #TempTITLHist.Level = @NextLevel) = 1 and @NextLevel <= 30 Begin
Exec sp2SelectCancelledTitlesByTitleNumber @NextLevel, @cancelledAfter
End
It seems to really bog down in this part of the SP:
SelectTITL.DRE_TITLE_REFRNC_NBR as TITLE_REFRNC_NBR ,
LTOXHTOXLINC.PROPRTY_PARCEL_ID,
LTOXHTOXLINC.LINC_NBR,
LTOXHTOXLINC.RIGHTS_IND,
LTOXHTOXLINC.NON_PATENT_IND,
LTOXHTOXLINC.LINCCount,
LTOXHTOXLINC.MUNC_CODE,
LTOXHTOXLINC.LTOXHTOX,
@NextLevel as Level
from ALTA_Staging..vwTITL TITL
inner join vwMinLTOXHTOXLINC LTOXHTOXLINC on LTOXHTOXLINC.TITLE_REFRNC_NBR = TITL.DRE_TITLE_REFRNC_NBR
where TITL.DTI_TITLE_REFRNC_NBR = @titleNumber
and DTI_TITLE_STATUS_IND in ('R','V')
and (TITL.DTI_EXPIRY_DATE is null or TITL.DTI_EXPIRY_DATE >= @cancelledAfter or @cancelledAfter is null)
Please help as this is preventing our users from accomplishing certain business functionality!!! Of course. 🙂
November 29, 2007 at 10:05 am
It sounds like you have both a 2000 box and a seperate 2005 box.
Are they equivalent spec (proc, memory, disk subsystem) and under equivalent load?
November 29, 2007 at 10:11 am
They are seperate boxes.
The 2005 box is supposedly much bigger and faster. More ram and faster processeors.
Thanks.
November 29, 2007 at 10:11 am
Sorry...yes....the load is the same.
November 29, 2007 at 10:51 am
Got this from another DBA:
The problem appears to be that the tables DB_HTXTITL and DB_LTXTITL are scanning an index instead of seeking on based on the DRE_TITLE_REFRNC_NBR column of the DB_REFTITL table. This ends up generating millions of rows instead of 1.
Does anyone know how to force it to seek instead of scan? Is that even possible?
November 29, 2007 at 10:59 am
OK so I guess we both believe the 2005 box should be faster.
How did the DB get transferred?
November 29, 2007 at 11:03 am
We ran full backups of the database in 2000 and then recovered those backups into 2005.
November 29, 2007 at 11:16 am
are scanning an index instead of seeking on based on the DRE_TITLE_REFRNC_NBR column
Sounds like the stats are not up to date, like the optimiser does not consider that index to be good.
If you are using the graphical estimated query plan look for any messages in red.
November 29, 2007 at 11:21 am
agree with allen...after restoring 2000 db to 2005 ....its must to reorganize stats...
use dbcc updateusage.....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
November 29, 2007 at 11:27 am
I ran dbcc updateusage on the two databases accessed in the query. No impact. The query continues to run high and CPU utlization is excessive while the query runs.
November 29, 2007 at 11:50 am
I'm not sure if DBCC UPDATEUSAGE and UPDATE STATISTICS do the same thing (anyone comment?).
It may be that stats dont exist you may need to run CREATE STATISTICS
November 29, 2007 at 12:17 pm
DBCC UPDATEUSAGE has nothing to do with statistics.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2007 at 12:25 pm
Bingo (I hope) try CREATE STATISTICS and see if the execution plan changes
November 29, 2007 at 12:35 pm
Jessica (11/29/2007)
Got this from another DBA:The problem appears to be that the tables DB_HTXTITL and DB_LTXTITL are scanning an index instead of seeking on based on the DRE_TITLE_REFRNC_NBR column of the DB_REFTITL table. This ends up generating millions of rows instead of 1.
Does anyone know how to force it to seek instead of scan? Is that even possible?
I might be going blind - but I don't even see these objects in the query.... Do they really play in this query? How?
Also - if your temp table joins millions of rows - it needs a proper set of indexes.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 29, 2007 at 12:36 pm
You should run UPDATE STATISTICS on all tables after restoring a database from 2000 to a 2005 server.
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply