SQL Server 2005 Execution Plan

Question

I am attempting to troubleshoot a slow running stored procedure in SQL Server 2005. I am analyzing the execution plan and see a SORT that is 45%, but I am not using an ORDER clauses. What would be causing this.

UPDATE SP (cleaned up, and made change on OR's)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Rpt_D]
@AsOfDate datetime,

@LEA int,
@SchoolName varchar(max),

@Grade varchar(8000),
@Gender varchar(8000),
@Race varchar(8000),
@UserID int
AS

SET NOCOUNT ON

Declare
@AsOfMonth int,
@AsOfDay int

SET @AsOfMonth = DATEPART(MONTH, @AsOfDate)
SET @AsOfDay = DATEPART(DAY, @AsOfDate)

CREATE TABLE #TempSchool
(
 SchoolESIID int,
 LEAESIID int
)
CREATE TABLE #TempRace
(
 Race varchar(60)
)
CREATE TABLE #TempGender
(
 GenderCode char(1)
)
CREATE TABLE #TempGrade
(
 GradeCode char(2)
)

DECLARE 
 @UserLevel nvarchar(10),
 @ESILEAList nvarchar(max),
 @ESISchoolList nvarchar(max)

EXEC Staging.dbo.GetUserFilter @userId = @userid, @userLevel = @Userlevel out, @ESILEAList = @ESILEAList out, @ESISchoolList = @ESISchoolList out

-- Parse parameters into tables
INSERT INTO #TempSchool
SELECT ParsedValue, PrntESIID 
FROM dbo.tfnParseStringIntoTable(@SchoolName, ',')
 INNER JOIN dbo.CALPADSOrg co ON ParsedValue = ChESIID

INSERT INTO #TempRace
SELECT ParsedValue FROM dbo.tfnParseStringIntoTable(@Race, ',')

INSERT INTO #TempGender
SELECT ParsedValue FROM dbo.tfnParseStringIntoTable(@Gender, ',')

INSERT INTO #TempGrade
SELECT ParsedValue FROM dbo.tfnParseStringIntoTable(@Grade, ',')


 SELECT DISTINCT
  ser.ESIIDLeaRptng AS LEARptngEsiID,
  ser.EsiIDSchlAtndnc AS SchoolESIID, 
  resi.CDSEttyCode AS SchlAtndncCode,
  resi.CDSEttyName AS SchlAtndncName,
  ser.StuKey,
  s.StuIDStwdCal,
  ISNULL(sdr.StuLastOrSrnmLgl,'') + ', ' + ISNULL(sdr.StuFstNameLgl,'') + ' ' + ISNULL(sdr.StuMdlNameLgl,'') AS StudentName,
  ser.StuIDLcl,
  rg.GndrCode AS GndrCode,
  ISNULL(
   CASE
    WHEN sdr.StuHspncEnctyIndctr = 'Y' 
     THEN 'Hispanic'
    WHEN sdr.StuEnctyMsngIndctr = 'Y' OR sdr.StuRaceMsngIndctr = 'Y' 
     THEN 'Missing'
    WHEN srr.RaceCatg2Code IS NOT NULL
     THEN 'Multiple'
    ELSE srr.FedEnctyRaceCatgCode
   END, 'Missing') AS RaceEnthnicity,
  rgl.GrdLvlCode AS GrdLvlCode,
  ISNULL(
   CASE relass.EngLangAcqstnStatStCode
    WHEN 'EL' 
     THEN 'Y'
    ELSE 'N'
   END, 'N') AS EnglishLearner,
  ISNULL(
   CASE
    WHEN ISNULL(sdr.StuIneligSnorImgrntIndctr, 'Y') = 'N' 
      AND ISNULL(ssr.StuEnrldUSSchlLessThanThreCumltvYrsIndctr, 'N') = 'Y' 
      AND ISNULL(sdr.rptCntryCode, 'US') != 'US' 
      AND ISNULL(res.EnrlmtStatCode, '0') = '10' 
      AND ISNULL(
       CASE 
        WHEN sdr.StuBirMonth < @AsOfMonth
         THEN DATEDIFF(YEAR, sdr.StuBirDate, @AsOfDate)
        WHEN sdr.StuBirMonth = @AsOfMonth  AND sdr.StuBirDay <= @AsOfDay
         THEN DATEDIFF(YEAR, sdr.StuBirDate, @AsOfDate)
        ELSE DATEDIFF(YEAR, sdr.StuBirDate, GETDATE()) -1
       END,0) BETWEEN 3 AND 21 
      AND ISNULL(rgl.GrdLvlCode, 'AD') != 'AD'
     THEN 'Y'
    ELSE 'N'
   END, 'N') AS TitleIIIEligibleImmigrantFlag,
  ISNULL(
   CASE
    WHEN ISNULL(rep.EduPgmCode, 000) = 175 OR ISNULL(rhel.HighstEduLvlCode, 0) = 14
     THEN 'Y' 
    ELSE 'N'
   END, 'N') AS SocioEconomicallyDisadvantagedFlag,
  ISNULL(
   CASE
    WHEN relass.EngLangAcqstnStatStCode IN ('EL', 'RFEP') AND relatp.EngLangArtsTestProfcyCode = 'N' 
     THEN 'Y'
    ELSE 'N'
   END, 'N') AS LimitedEnglishProficientFlag,
  ISNULL(
   CASE rep.EduPgmCode
    WHEN '135' 
     THEN 'Y'
    ELSE 'N'
   END, 'N') AS TitleIPartCMigrantFlag,
  ISNULL(
   CASE rep.EduPgmCode
    WHEN '144' 
     THEN 'Y'
    ELSE 'N'
   END, 'N') AS SpecialEducationFlag ,
  ISNULL(
   CASE rep.EduPgmCode
    WHEN '127' 
     THEN 'Y'
    ELSE 'N'
   END, 'N') AS GiftedAndTalentedFlag
 From 
  dbo.StuEnrlmt ser
  INNER JOIN dbo.Stu s ON ser.StuKey = s.StuKey
  INNER JOIN #TempSchool ts ON ser.EsiIDSchlAtndnc = ts.SchoolESIID 
          AND (ser.EsiIDLEARptng = @LEA)
  INNER JOIN RefEductlSrvcInstn resi ON ts.SchoolESIID = resi.ESIID
  INNER JOIN dbo.RefEnrlmtStat res ON ser.EnrlmtStatKey = res.EnrlmtStatKey
  LEFT JOIN dbo.RefStuExitCatg rsec ON ser.StuExitCatgKey = rsec.StuExitCatgKey
  LEFT JOIN dbo.StuEngLangArt selar ON ser.StuKey = selar.StuKey 
          AND (selar.EfctvStartDate <= @AsOfDate)
          AND ((selar.EfctvEndDate IS NULL) OR (selar.EfctvEndDate >= @AsOfDate))
  LEFT JOIN dbo.RefEngLangAcqstnStatSt relass ON selar.EngLangAcqstnStatStKey = relass.EngLangAcqstnStatStKey
  LEFT JOIN dbo.RefEngLangArtsTestProfcy relatp ON selar.ElaTestProfcyDsgntnKey = relatp.EngLangArtsTestProfcyKey
  INNER JOIN dbo.StuDemo sdr ON ser.StuKey = sdr.StuKey
  INNER JOIN dbo.RefGndr rg ON sdr.GndrCodeKey = rg.GndrCodeKey
  LEFT JOIN dbo.StuStat ssr ON ser.StuKey = ssr.StuKey 
          AND (ssr.EfctvStartDate <= @AsOfDate)
          AND ((ssr.EfctvEndDate IS NULL) OR (ssr.EfctvEndDate >= @AsOfDate))
  INNER JOIN dbo.StuGrdLvl sglr ON ser.StuKey = sglr.StuKey
  INNER JOIN dbo.RefGrdLvl rgl ON sglr.GrdLvlKey = rgl.GrdLvlKey
  LEFT JOIN dbo.StuPgm spr ON ser.StuKey = spr.StuKey 
          AND (spr.StuEduPgmMbrshpCatgStartDate <= @AsOfDate )
          AND ((spr.StuEduPgmMbrshpCatgEndDate IS NULL) OR (spr.StuEduPgmMbrshpCatgEndDate >= @AsOfDate))
  LEFT JOIN dbo.RefEduPgm rep ON spr.EduPgmCodeKey = rep.EduPgmCodeKey
  LEFT JOIN dbo.StuPrntOrGrdn spogr ON ser.StuKey = spogr.StuKey 
          AND (spogr.EfctvStartDate <= @AsOfDate)
          AND ((spogr.EfctvEndDate IS NULL) OR (spogr.EfctvEndDate >= @AsOfDate))
  LEFT JOIN dbo.RefHighstEduLvl rhel ON spogr.PrntOrGrdnHighstEduLvlCodeKey = rhel.HighstEduLvlKey
  LEFT JOIN dbo.vwStuRaceRptng srr ON ser.StuKey = srr.StuKey 
          AND (srr.EfctvStartDate <= @AsOfDate)
          AND ((srr.EfctvEndDate IS NULL) OR (srr.EfctvEndDate >= @AsOfDate))
  INNER JOIN #TempRace tr ON 
   ISNULL(
     CASE
     WHEN sdr.StuHspncEnctyIndctr = 'Y' 
      THEN 'Hispanic'
     WHEN sdr.StuEnctyMsngIndctr = 'Y' OR sdr.StuRaceMsngIndctr = 'Y' 
      THEN 'Missing'
     WHEN srr.RaceCatg2Code IS NOT NULL
      THEN 'Multiple'
     ELSE srr.FedEnctyRaceCatgCode
    END, 'Missing') = tr.Race  
  INNER JOIN #TempGender tg ON rg.GndrCode = tg.GenderCode 
  INNER JOIN #TempGrade tgr ON rgl.GrdLvLCode = tgr.GradeCode  

 WHERE (ser.StuEsiRltnspExpctdSchlStartDate <= @AsOfDate)
  AND ((ser.WithdrlDate IS NULL) OR (ser.WithdrlDate >= @AsOfDate))
  AND (res.EnrlmtStatCode = '10')
  AND (ISNULL(rsec.StuExitCatgCode, 'N/A') != 'N470')
  AND (sdr.EfctvStartDate <= @AsOfDate)
  AND ((sdr.EfctvEndDate IS NULL) OR (sdr.EfctvEndDate >= @AsOfDate))
  AND (sglr.EfctvStartDate <= @AsOfDate)
  AND ((sglr.EfctvEndDate IS NULL) OR (sglr.EfctvEndDate >= @AsOfDate))
  AND ((spr.DeleteFlag IS NULL) OR (spr.DeleteFlag = 'N'))
  AND ((spogr.DeleteFlag IS NULL) OR (spogr.DeleteFlag = 'N'))
  AND ((sglr.DeleteFlag IS NULL) OR (sglr.DeleteFlag = 'N'))
  AND ((selar.DeleteFlag IS NULL) OR (selar.DeleteFlag = 'N'))
  AND ((sdr.DeleetFlag IS NULL) OR (sdr.DeleetFlag = 'N'))
  AND ((ser.DeleteFlag IS NULL) OR (ser.DeleteFlag = 'N'))

DROP TABLE #TempSchool
DROP TABLE #TempRace
DROP TABLE #TempGender
DROP TABLE #TempGrade

vwStuRaceRptng View

SELECT     sr.StuRaceKey, sr.StuKey, rr1.RaceCatgCode, rr1.RaceCatgName, rferc1.FedEnctyRaceCatgKey, rferc1.FedEnctyRaceCatgCode, rferc1.FedEnctyRaceCatgName, 
                      rr2.RaceCatgCode AS RaceCatg2Code, rr2.RaceCatgName AS RaceCatg2Name, rferc2.FedEnctyRaceCatgKey AS FedEnctyRaceCatg2Key, 
                      rferc2.FedEnctyRaceCatgCode AS FedEnctyRaceCatg2Code, rferc2.FedEnctyRaceCatgName AS FedEnctyRaceCatg2Name, rr3.RaceCatgCode AS RaceCatg3Code, 
                      rr3.RaceCatgName AS RaceCatg3Name, rferc3.FedEnctyRaceCatgKey AS FedEnctyRaceCatg3Key, rferc3.FedEnctyRaceCatgCode AS FedEnctyRaceCatg3Code, 
                      rferc3.FedEnctyRaceCatgName AS FedEnctyRaceCatg3Name, rr4.RaceCatgCode AS RaceCatg4Code, rr4.RaceCatgName AS RaceCatg4Name, 
                      rferc4.FedEnctyRaceCatgKey AS FedEnctyRaceCatg4Key, rferc4.FedEnctyRaceCatgCode AS FedEnctyRaceCatg4Code, 
                      rferc4.FedEnctyRaceCatgName AS FedEnctyRaceCatg4Name, rr5.RaceCatgCode AS RaceCatg5Code, rr5.RaceCatgName AS RaceCatg5Name, 
                      rferc5.FedEnctyRaceCatgKey AS FedEnctyRaceCatg5Key, rferc5.FedEnctyRaceCatgCode AS FedEnctyRaceCatg5Code, 
                      rferc5.FedEnctyRaceCatgName AS FedEnctyRaceCatg5Name, sr.EfctvStartDate, sr.EfctvEndDate
FROM         dbo.StuRace AS sr INNER JOIN
                      dbo.RefRace AS rr1 ON sr.RaceCatgKey = rr1.RaceCatgKey LEFT OUTER JOIN
                      dbo.RefRace AS rr2 ON sr.RaceCatg2Key = rr2.RaceCatgKey LEFT OUTER JOIN
                      dbo.RefRace AS rr3 ON sr.RaceCatg3Key = rr3.RaceCatgKey LEFT OUTER JOIN
                      dbo.RefRace AS rr4 ON sr.RaceCatg4Key = rr4.RaceCatgKey LEFT OUTER JOIN
                      dbo.RefRace AS rr5 ON sr.RaceCatg5Key = rr5.RaceCatgKey LEFT OUTER JOIN
                      dbo.RefFedEnctyRaceCatg AS rferc1 ON rr1.FedEnctyRaceCatgKey = rferc1.FedEnctyRaceCatgKey LEFT OUTER JOIN
                      dbo.RefFedEnctyRaceCatg AS rferc2 ON rr2.FedEnctyRaceCatgKey = rferc2.FedEnctyRaceCatgKey LEFT OUTER JOIN
                      dbo.RefFedEnctyRaceCatg AS rferc3 ON rr3.FedEnctyRaceCatgKey = rferc3.FedEnctyRaceCatgKey LEFT OUTER JOIN
                      dbo.RefFedEnctyRaceCatg AS rferc4 ON rr4.FedEnctyRaceCatgKey = rferc4.FedEnctyRaceCatgKey LEFT OUTER JOIN
                      dbo.RefFedEnctyRaceCatg AS rferc5 ON rr5.FedEnctyRaceCatgKey = rferc5.FedEnctyRaceCatgKey
WHERE     (ISNULL(sr.DeleteFlag, 'N') = 'N')

OLD SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATEPROCEDURE [dbo].[Rpt_D]
@AsOfDate datetime,
@LEA int,
@SchoolName varchar(max),
@Grade varchar(8000),
@Gender varchar(8000),
@Race varchar(8000),
@UserID int
AS
Declare
@AsOfMonth int,
@AsOfDay int

select @AsOfMonth = datepart(month, @AsOfDate)
select @AsOfDay = datepart(day, @AsOfDate)

Create table #TempSchool
(
 SchoolESIID int,
 LEAESIID int
)
Create table #TempRace
(
 Race varchar(60)
)
Create table #TempGender
(
 GenderCode char(1)
)
Create table #TempGrade
(
 GradeCode char(2)
)

declare 
 @UserLevel nVarchar(10),
 @ESILEAList nvarchar(max),
 @ESISchoolList nvarchar(max)

exec Staging.dbo.GetUserFilter @userId=@userid,@userLevel=@Userlevel out,@ESILEAList=@ESILEAList out,@ESISchoolList=@ESISchoolList out

-- Parse parameters into tables
Insert into #TempSchool
select 
 ParsedValue, PrntESIID 
from 
   dbo.tfnParseStringIntoTable(@SchoolName, ',')
inner join dbo.CALPADSOrg co on ParsedValue = ChESIID

Insert into #TempRace
select ParsedValue from dbo.tfnParseStringIntoTable(@Race, ',')

Insert into #TempGender
select ParsedValue from dbo.tfnParseStringIntoTable(@Gender, ',')

Insert into #TempGrade
select ParsedValue from dbo.tfnParseStringIntoTable(@Grade, ',')




 Select DISTINCT
  ser.ESIIDLeaRptng as LEARptngEsiID,
  ser.EsiIDSchlAtndnc as SchoolESIID, 
  resi.CDSEttyCode as SchlAtndncCode,
  resi.CDSEttyName as SchlAtndncName,
  ser.StuKey,
  s.StuIDStwdCal,
  isnull(sdr.StuLastOrSrnmLgl,'') + ', ' + isnull(sdr.StuFstNameLgl,'') + ' ' + isnull(sdr.StuMdlNameLgl,'') as StudentName,
  ser.StuIDLcl,
  rg.GndrCode as GndrCode,
  isnull(case
   when sdr.StuHspncEnctyIndctr = 'Y' then 'Hispanic'
   when sdr.StuEnctyMsngIndctr = 'Y' or sdr.StuRaceMsngIndctr = 'Y' then 'Missing'
   when srr.RaceCatg2Code is not null then 'Multiple'
   else srr.FedEnctyRaceCatgCode
  end, 'Missing') as RaceEnthnicity,
  rgl.GrdLvlCode as GrdLvlCode,
  isnull(
   case relass.EngLangAcqstnStatStCode
    when 'EL' then 'Y'
    else 'N'
   end, 'N') as EnglishLearner,
  isnull(
   case
   when 
    -- if a value is null, set it to any value that will evaluate to false in the expression
    -- only students with valid information should be counted as Title III Eligible Immigrants
    isnull(sdr.StuIneligSnorImgrntIndctr, 'Y') = 'N' AND
    isnull(ssr.StuEnrldUSSchlLessThanThreCumltvYrsIndctr, 'N') = 'Y' AND
    isnull(sdr.rptCntryCode, 'US') != 'US' AND
    isnull(res.EnrlmtStatCode, '0') = '10' AND
    -- Calculate age from birth date
    isnull(case 
    when sdr.StuBirMonth < @AsOfMonth
     then datediff(year, sdr.StuBirDate, @AsOfDate)
    when sdr.StuBirMonth = @AsOfMonth and sdr.StuBirDay <= @AsOfDay
     then datediff(year, sdr.StuBirDate, @AsOfDate)
    else datediff(year, sdr.StuBirDate, getdate()) -1
    end,0) between 3 and 21 AND
    isnull(rgl.GrdLvlCode, 'AD') != 'AD'
   then 'Y'
   else 'N'
   end, 'N') as TitleIIIEligibleImmigrantFlag,
  isnull(
   case
    when 
     isnull(rep.EduPgmCode, 000) = 175 OR
     isnull(rhel.HighstEduLvlCode, 0) = 14
    then 'Y' 
    else 'N'
   end, 'N') as SocioEconomicallyDisadvantagedFlag,
  isnull(case
     when relass.EngLangAcqstnStatStCode in ('EL', 'RFEP')  AND relatp.EngLangArtsTestProfcyCode = 'N' then 'Y'
     else 'N'
    end, 'N') as LimitedEnglishProficientFlag,
  isnull(case rep.EduPgmCode
     when '135' then 'Y'
     else 'N'
    end, 'N') as TitleIPartCMigrantFlag,
  isnull(case rep.EduPgmCode
     when '144' then 'Y'
     else 'N'
    end, 'N') as SpecialEducationFlag ,
  isnull(case rep.EduPgmCode
     when '127' then 'Y'
     else 'N'
    end, 'N') as GiftedAndTalentedFlag
 From 
  dbo.StuEnrlmt ser
  inner join dbo.Stu s on 
   ser.StuKey = s.StuKey
  inner join #TempSchool ts on 
   ser.EsiIDSchlAtndnc = ts.SchoolESIID and
   ser.EsiIDLEARptng = @LEA
  inner join RefEductlSrvcInstn resi on 
   ts.SchoolESIID = resi.ESIID
  inner join dbo.RefEnrlmtStat res on 
   ser.EnrlmtStatKey = res.EnrlmtStatKey
  left join dbo.RefStuExitCatg rsec on
   ser.StuExitCatgKey = rsec.StuExitCatgKey
  left join dbo.StuEngLangArt selar on 
   ser.StuKey = selar.StuKey and
   selar.EfctvStartDate <= @AsOfDate AND (selar.EfctvEndDate is null OR selar.EfctvEndDate >= @AsOfDate)
  left join dbo.RefEngLangAcqstnStatSt relass on 
   selar.EngLangAcqstnStatStKey = relass.EngLangAcqstnStatStKey
  left join dbo.RefEngLangArtsTestProfcy relatp on 
   selar.ElaTestProfcyDsgntnKey = relatp.EngLangArtsTestProfcyKey
  inner join dbo.StuDemo sdr on
   ser.StuKey = sdr.StuKey
  inner join dbo.RefGndr rg on 
   sdr.GndrCodeKey = rg.GndrCodeKey
  left join dbo.StuStat ssr on
   ser.StuKey = ssr.StuKey and
   ssr.EfctvStartDate   <= @AsOfDate AND (ssr.EfctvEndDate   is null OR ssr.EfctvEndDate >= @AsOfDate)   
  inner join dbo.StuGrdLvl sglr on
   ser.StuKey = sglr.StuKey
  inner join dbo.RefGrdLvl rgl on 
   sglr.GrdLvlKey = rgl.GrdLvlKey
  left join dbo.StuPgm spr on
   ser.StuKey = spr.StuKey AND
   spr.StuEduPgmMbrshpCatgStartDate   <= @AsOfDate AND (spr.StuEduPgmMbrshpCatgEndDate   is null OR spr.StuEduPgmMbrshpCatgEndDate >= @AsOfDate)  
  left join dbo.RefEduPgm rep on 
   spr.EduPgmCodeKey = rep.EduPgmCodeKey
  left join dbo.StuPrntOrGrdn spogr on 
   ser.StuKey = spogr.StuKey And
   spogr.EfctvStartDate <= @AsOfDate AND (spogr.EfctvEndDate is null OR spogr.EfctvEndDate >= @AsOfDate) 
  left join dbo.RefHighstEduLvl rhel on 
   spogr.PrntOrGrdnHighstEduLvlCodeKey = rhel.HighstEduLvlKey
  left join dbo.vwStuRaceRptng srr on
   ser.StuKey = srr.StuKey  and 
   srr.EfctvStartDate   <= @AsOfDate AND (srr.EfctvEndDate   is null OR srr.EfctvEndDate >= @AsOfDate)
  inner join #TempRace tr on 
   isnull(
   case
    when sdr.StuHspncEnctyIndctr = 'Y' then 'Hispanic'
    when sdr.StuEnctyMsngIndctr = 'Y' or sdr.StuRaceMsngIndctr = 'Y' then 'Missing'
    when srr.RaceCatg2Code is not null then 'Multiple'
    else srr.FedEnctyRaceCatgCode
   end, 'Missing') = tr.Race  
  inner join #TempGender tg on
   rg.GndrCode = tg.GenderCode 
  inner join #TempGrade tgr on 
   rgl.GrdLvLCode = tgr.GradeCode  

 Where
  -- Enrollments
  ser.StuEsiRltnspExpctdSchlStartDate <= @AsOfDate AND (ser.WithdrlDate is null OR ser.WithdrlDate >= @AsOfDate) AND
  res.EnrlmtStatCode = '10'  AND
  isnull(rsec.StuExitCatgCode, 'N/A') != 'N470' AND -- no shows are not considered in active enrollment numbers
  -- Effective date Comparisions. As of date should be between Effective Start and End date (end date can be null)
  sdr.EfctvStartDate   <= @AsOfDate AND (sdr.EfctvEndDate   is null OR sdr.EfctvEndDate >= @AsOfDate)   AND
  sglr.EfctvStartDate  <= @AsOfDate AND (sglr.EfctvEndDate  is null OR sglr.EfctvEndDate >= @AsOfDate)  AND
  -- Filter deleted records out
  (spr.DeleteFlag is null OR spr.DeleteFlag = 'N') AND
  (spogr.DeleteFlag is null OR spogr.DeleteFlag = 'N')  AND
  (sglr.DeleteFlag is null OR sglr.DeleteFlag = 'N') AND
  (selar.DeleteFlag is null OR selar.DeleteFlag = 'N') AND
  (sdr.DeleetFlag is null OR sdr.DeleetFlag = 'N') AND
  (ser.DeleteFlag is null OR ser.DeleteFlag = 'N') 


Drop table #TempSchool
Drop table #TempRace
Drop table #TempGender
Drop table #TempGrade

Thanks for any help. Just as an FYI, the tfnParseStringIntoTable Function returns a table bases on a comma seperated list of values.

Answer 1

CTE Version

;WITH ser as
( SELECT DISTINCT
  *
 From 
  dbo.StuEnrlmt ser
 WHERE (ser.StuEsiRltnspExpctdSchlStartDate <= @AsOfDate)
  AND ((ser.DeleteFlag IS NULL) OR (ser.DeleteFlag = 'N'))
)
--Main select replacing FROM dbo.StueEnrlmt with FROM ser
Select DISTINCT
  ser.ESIIDLeaRptng as LEARptngEsiID,
  ser.EsiIDSchlAtndnc as SchoolESIID, 
  resi.CDSEttyCode as SchlAtndncCode,
  resi.CDSEttyName as SchlAtndncName,
  ser.StuKey,
  s.StuIDStwdCal,
  isnull(sdr.StuLastOrSrnmLgl,'') + ', ' + isnull(sdr.StuFstNameLgl,'') + ' ' + isnull(sdr.StuMdlNameLgl,'') as StudentName,
  ser.StuIDLcl,
  rg.GndrCode as GndrCode,
  isnull(case
   when sdr.StuHspncEnctyIndctr = 'Y' then 'Hispanic'
   when sdr.StuEnctyMsngIndctr = 'Y' or sdr.StuRaceMsngIndctr = 'Y' then 'Missing'
   when srr.RaceCatg2Code is not null then 'Multiple'
   else srr.FedEnctyRaceCatgCode
  end, 'Missing') as RaceEnthnicity,
  rgl.GrdLvlCode as GrdLvlCode,
  isnull(
   case relass.EngLangAcqstnStatStCode
    when 'EL' then 'Y'
    else 'N'
   end, 'N') as EnglishLearner,
  isnull(
   case
   when 
    -- if a value is null, set it to any value that will evaluate to false in the expression
    -- only students with valid information should be counted as Title III Eligible Immigrants
    isnull(sdr.StuIneligSnorImgrntIndctr, 'Y') = 'N' AND
    isnull(ssr.StuEnrldUSSchlLessThanThreCumltvYrsIndctr, 'N') = 'Y' AND
    isnull(sdr.rptCntryCode, 'US') != 'US' AND
    isnull(res.EnrlmtStatCode, '0') = '10' AND
    -- Calculate age from birth date
    isnull(case 
    when sdr.StuBirMonth < @AsOfMonth
     then datediff(year, sdr.StuBirDate, @AsOfDate)
    when sdr.StuBirMonth = @AsOfMonth and sdr.StuBirDay <= @AsOfDay
     then datediff(year, sdr.StuBirDate, @AsOfDate)
    else datediff(year, sdr.StuBirDate, getdate()) -1
    end,0) between 3 and 21 AND
    isnull(rgl.GrdLvlCode, 'AD') != 'AD'
   then 'Y'
   else 'N'
   end, 'N') as TitleIIIEligibleImmigrantFlag,
  isnull(
   case
    when 
     isnull(rep.EduPgmCode, 000) = 175 OR
     isnull(rhel.HighstEduLvlCode, 0) = 14
    then 'Y' 
    else 'N'
   end, 'N') as SocioEconomicallyDisadvantagedFlag,
  isnull(case
     when relass.EngLangAcqstnStatStCode in ('EL', 'RFEP')  AND relatp.EngLangArtsTestProfcyCode = 'N' then 'Y'
     else 'N'
    end, 'N') as LimitedEnglishProficientFlag,
  isnull(case rep.EduPgmCode
     when '135' then 'Y'
     else 'N'
    end, 'N') as TitleIPartCMigrantFlag,
  isnull(case rep.EduPgmCode
     when '144' then 'Y'
     else 'N'
    end, 'N') as SpecialEducationFlag ,
  isnull(case rep.EduPgmCode
     when '127' then 'Y'
     else 'N'
    end, 'N') as GiftedAndTalentedFlag
 From ser
  inner join dbo.Stu s on 
   ser.StuKey = s.StuKey
  inner join #TempSchool ts on 
   ser.EsiIDSchlAtndnc = ts.SchoolESIID and
   ser.EsiIDLEARptng = @LEA
  inner join RefEductlSrvcInstn resi on 
   ts.SchoolESIID = resi.ESIID
  inner join dbo.RefEnrlmtStat res on 
   ser.EnrlmtStatKey = res.EnrlmtStatKey
  left join dbo.RefStuExitCatg rsec on
   ser.StuExitCatgKey = rsec.StuExitCatgKey
  left join dbo.StuEngLangArt selar on 
   ser.StuKey = selar.StuKey and
   selar.EfctvStartDate <= @AsOfDate AND (selar.EfctvEndDate is null OR selar.EfctvEndDate >= @AsOfDate)
  left join dbo.RefEngLangAcqstnStatSt relass on 
   selar.EngLangAcqstnStatStKey = relass.EngLangAcqstnStatStKey
  left join dbo.RefEngLangArtsTestProfcy relatp on 
   selar.ElaTestProfcyDsgntnKey = relatp.EngLangArtsTestProfcyKey
  inner join dbo.StuDemo sdr on
   ser.StuKey = sdr.StuKey
  inner join dbo.RefGndr rg on 
   sdr.GndrCodeKey = rg.GndrCodeKey
  left join dbo.StuStat ssr on
   ser.StuKey = ssr.StuKey and
   ssr.EfctvStartDate   <= @AsOfDate AND (ssr.EfctvEndDate   is null OR ssr.EfctvEndDate >= @AsOfDate)   
  inner join dbo.StuGrdLvl sglr on
   ser.StuKey = sglr.StuKey
  inner join dbo.RefGrdLvl rgl on 
   sglr.GrdLvlKey = rgl.GrdLvlKey
  left join dbo.StuPgm spr on
   ser.StuKey = spr.StuKey AND
   spr.StuEduPgmMbrshpCatgStartDate   <= @AsOfDate AND (spr.StuEduPgmMbrshpCatgEndDate   is null OR spr.StuEduPgmMbrshpCatgEndDate >= @AsOfDate)  
  left join dbo.RefEduPgm rep on 
   spr.EduPgmCodeKey = rep.EduPgmCodeKey
  left join dbo.StuPrntOrGrdn spogr on 
   ser.StuKey = spogr.StuKey And
   spogr.EfctvStartDate <= @AsOfDate AND (spogr.EfctvEndDate is null OR spogr.EfctvEndDate >= @AsOfDate) 
  left join dbo.RefHighstEduLvl rhel on 
   spogr.PrntOrGrdnHighstEduLvlCodeKey = rhel.HighstEduLvlKey
  left join dbo.vwStuRaceRptng srr on
   ser.StuKey = srr.StuKey  and 
   srr.EfctvStartDate   <= @AsOfDate AND (srr.EfctvEndDate   is null OR srr.EfctvEndDate >= @AsOfDate)
  inner join #TempRace tr on 
   isnull(
   case
    when sdr.StuHspncEnctyIndctr = 'Y' then 'Hispanic'
    when sdr.StuEnctyMsngIndctr = 'Y' or sdr.StuRaceMsngIndctr = 'Y' then 'Missing'
    when srr.RaceCatg2Code is not null then 'Multiple'
    else srr.FedEnctyRaceCatgCode
   end, 'Missing') = tr.Race  
  inner join #TempGender tg on
   rg.GndrCode = tg.GenderCode 
  inner join #TempGrade tgr on 
   rgl.GrdLvLCode = tgr.GradeCode  

 Where
  -- Enrollments
  res.EnrlmtStatCode = '10'  AND
  isnull(rsec.StuExitCatgCode, 'N/A') != 'N470' AND -- no shows are not considered in active enrollment numbers
  -- Effective date Comparisions. As of date should be between Effective Start and End date (end date can be null)
  sdr.EfctvStartDate   <= @AsOfDate AND (sdr.EfctvEndDate   is null OR sdr.EfctvEndDate >= @AsOfDate)   AND
  sglr.EfctvStartDate  <= @AsOfDate AND (sglr.EfctvEndDate  is null OR sglr.EfctvEndDate >= @AsOfDate)  AND
  -- Filter deleted records out
  (spr.DeleteFlag is null OR spr.DeleteFlag = 'N') AND
  (spogr.DeleteFlag is null OR spogr.DeleteFlag = 'N')  AND
  (sglr.DeleteFlag is null OR sglr.DeleteFlag = 'N') AND
  (selar.DeleteFlag is null OR selar.DeleteFlag = 'N') AND
  (sdr.DeleetFlag is null OR sdr.DeleetFlag = 'N')    

some ideas

You don't need to make all those temp tables, your function will return an in memory table which will not go to disk, should be faster. For example

change

INNER JOIN #TempRace tr ON 

to

INNER JOIN dbo.tfnParseStringIntoTable(@Race, ',') tr ON 

and remove all references to #TempRace. You can do the same for #TempGender and #TempGrade

You can sometimes gain a lot of speed by selecting the main table's elements of interest before doing all the joins. To see what I mean, try the following and see if it works:

;WITH ser as
( SELECT DISTINCT
  *
 From 
  dbo.StuEnrlmt ser
 WHERE (ser.StuEsiRltnspExpctdSchlStartDate <= @AsOfDate)
  AND ((ser.WithdrlDate IS NULL) OR (ser.WithdrlDate >= @AsOfDate))
  AND ((spr.DeleteFlag IS NULL) OR (spr.DeleteFlag = 'N'))
  AND ((sdr.DeleetFlag IS NULL) OR (sdr.DeleetFlag = 'N'))
  AND ((ser.DeleteFlag IS NULL) OR (ser.DeleteFlag = 'N'))
)
--Main select replacing FROM dbo.StueEnrlmt with FROM ser

Also: You might want to join in the other elements in the where of the main select into this CTE so your main select has NO where clause (res, rsec, sdr, sglr, spr, spogr, selar), then you will be doing a lot of the slow stuff - that is case statements and joins only for records you are actually going to use.

original before query code

One thing to note is the estimated number of rows was 3 and the actual number of rows was 11601.

Also it says order by RaceCatg2Key -- are you joining to this table? It might be ordering it so it can do the join.

If you show the whole query we might have some ways to speed it up.

Answer 2

From the small snippet of the execution plan shown I can see that the sort (although slow) is not the source of your problems - the estimated number of rows is 3, however the actual number of rows is ~11,000.

The real problem is that SQL server has hugely misjudged the number of records that it will be sorting and as a result it is executing things in the wrong order and sorting far more rows than it should be.

There are a number of reasons why SQL server might mis-judge the number of rows (for example out-of-date statistics, parameter sniffing, use of SQL functions etc...) without looking at the rest of the query / execution plan its not really possible to tell what the culprit, although I'm sure if you post the full query + execution plan someone will be able to spot it! :-)

Update: Looking at your SQL I think that the reason may be the bit:

WHERE (ISNULL(sr.DeleteFlag, 'N') = 'N')

Try replacing this with this - I think this is equivalent, but my brain has gone to mush! :-)

WHERE sr.DeleteFlag IS NULL

The reason being that although SQL server keeps statistics on the DeleteFlag column, it might not be clever enough to anticipate what the value of this more complex expression is.

TBH its a bit of a long shot, but at the moment I can't see any other potential culprits - this is the best I have! :-)

Answer 3

    (spr.DeleteFlag is null OR spr.DeleteFlag = 'N') AND 
    (spogr.DeleteFlag is null OR spogr.DeleteFlag = 'N')  AND 
    (sglr.DeleteFlag is null OR sglr.DeleteFlag = 'N') AND 
    (selar.DeleteFlag is null OR selar.DeleteFlag = 'N') AND 
    (sdr.DeleetFlag is null OR sdr.DeleetFlag = 'N') AND 
    (ser.DeleteFlag is null OR ser.DeleteFlag = 'N')  

This will always be inefficient code. You need to to stop allowing nulls in your delete flag. It should have a default value of 'N'. This will speed up all the queries on your site that exclude deleted records (well once you rewrite them) so should be should very high priority to fix.

Incidentally I don't see any reference to the table the part of the execution plan is using call StuRace. Therefore I'm figuring it is either in one of the table values functions or a view or even worse a view that calls a view.