0

I have this query and it runs less than 2 minutes on Test but on Prod it takes 10 minutes.

Both are the same version,CPU,RAM,OS the same.

I have four tempdb files on Prod but one one Test.

I listed the time it took when I run each query one at a time. When I created missing indexes(2), it made it worse on test but reduce the execution time by half on prod.

I also see two different exec plans on Prod and Test.

select  distinct a11.APPLN_CODE  APPLN_CODE
into #ZZMQ00
from    BI_EDW.dbo.DIM_APPLICATION  a11
where   a11.APPLN_DATE_KEY >=  99990000 

Prod rows 390233 , 0 sec

Test rows 390178, 0 sec

select  distinct a12.APPLN_CODE  APPLN_CODE
into #ZZMQ01
from    BI_EDW.dbo.DIM_APPLICATION  a12
    join    BI_EDW.dbo.DIM_APPLICATION_STATUS   a13
      on    (a12.APPLN_STATUS_KEY = a13.STATUS_KEY)
where   ((not ((a12.APPLN_CODE)
 in (select ps21.APPLN_CODE
    from    #ZZMQ00 ps21)))
 and a13.STATUS_CD in ('DEP', 'SWD', 'SDE', 'SPD', 'MS')) 

Prod 49758 , 1 Sec

Test 49742 , 1 sec

select  a16.SCHOOL_KEY  SCHOOL_KEY,
    a15.DATE_KEY  DATE_KEY,
    a13.DGR_ADM_CAT_KEY  DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY  ACAD_REGULAR_TERM_KEY,
    a13.APPLN_ACAD_LVL_KEY  OBJ_KEY,
    count(distinct a11.APPLT_KEY)  WJXBFS1
into #ZZMD02
from    BI_EDW.dbo.DIM_APPLICATION  a11
    join    #ZZMQ01 pa12
      on    (a11.APPLN_CODE = pa12.APPLN_CODE)
    join    BI_EDW.dbo.DIM_APPLICATION  a13
      on    (a11.APPLN_KEY = a13.APPLN_KEY)
    join    BI_EDW.dbo.DIM_ACAD_TERM    a14
      on    (a11.APPLN_ACAD_TERM_KEY = a14.ACAD_TERM_KEY)
    join    BI_EDW.dbo.TRANS_TERM_STATUS_EFF_DATE_TO_DATE   a15
      on    (CONVERT(VARCHAR,a11.ROW_EFF_START_DTTM,112) = a15.EFFSTARTDATEKEY and 
    a11.APPLN_ACAD_TERM_KEY = a15.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_DEPARTMENT   a16
      on    (a11.APPLN_DEPT_KEY = a16.DEPT_KEY)
    join    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    a17
      on    (a14.ACAD_REGULAR_TERM_KEY = a17.ACAD_REGULAR_TERM_KEY)
    join    BI_EDW.dbo.DIM_APPLICATION_STATUS   a18
      on    (a13.APPLN_STATUS_KEY = a18.STATUS_KEY)
where   (((a14.ACAD_REGULAR_TERM_KEY)
 in (select r11.ACAD_REGULAR_TERM_KEY
    from    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    r11
    where   r11.ACTIVE in ('1')))
 and a17.ACTIVE in ('1')
 and a18.STATUS_CD in ('DEP', 'SWD', 'SDE', 'SPD', 'MS')
 and a15.DATE_KEY <=  convert(varchar,a11.ROW_EFF_END_DTTM,112)
 and ((a15.DATE_KEY between LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 0921 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END and LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 1001 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END
 and a17.ACAD_SEASON_SNAME = 'F')
 or (a17.ACAD_SEASON_SNAME = 'X'
 and a15.DATE_KEY between LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0621 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END and LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0701 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END)
 or (a17.ACAD_SEASON_SNAME = 'S'
 and a15.DATE_KEY between CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE()-9,112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0428 END and CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE(),112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0507 END)))
group by    a16.SCHOOL_KEY,
    a15.DATE_KEY,
    a13.DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY,
    a13.APPLN_ACAD_LVL_KEY 

Prod 4851 rows, 27 sec prod,

test 4859 rows , 38 Sec

select  a17.SCHOOL_KEY  SCHOOL_KEY,
    (a12.DATE_KEY + 10000)  DATE_KEY,
    a16.DGR_ADM_CAT_KEY  DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY  ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY  OBJ_KEY,
    count(distinct a11.APPLT_KEY)  WJXBFS1
into #ZZMD03
from    BI_EDW.dbo.DIM_APPLICATION  a11
    join    BI_EDW.dbo.TRANS_TERM_STATUS_EFF_DATE_TO_DATE   a12
      on    (CONVERT(VARCHAR,a11.ROW_EFF_START_DTTM,112) = a12.EFFSTARTDATEKEY and 
    a11.APPLN_ACAD_TERM_KEY = a12.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_TERM    a13
      on    (a11.APPLN_ACAD_TERM_KEY = a13.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    a14
      on    (a13.ACAD_REGULAR_TERM_KEY = (a14.ACAD_REGULAR_TERM_KEY - 100))
    join    #ZZMQ01 pa15
      on    (a11.APPLN_CODE = pa15.APPLN_CODE)
    join    BI_EDW.dbo.DIM_APPLICATION  a16
      on    (a11.APPLN_KEY = a16.APPLN_KEY)
    join    BI_EDW.dbo.DIM_DEPARTMENT   a17
      on    (a11.APPLN_DEPT_KEY = a17.DEPT_KEY)
    join    BI_EDW.dbo.DIM_APPLICATION_STATUS   a18
      on    (a16.APPLN_STATUS_KEY = a18.STATUS_KEY)
where   (((a14.ACAD_REGULAR_TERM_KEY)
 in (select r11.ACAD_REGULAR_TERM_KEY
    from    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    r11
    where   r11.ACTIVE in ('1')))
 and a14.ACTIVE in ('1')
 and a18.STATUS_CD in ('DEP', 'SWD', 'SDE', 'SPD', 'MS')
 and a12.DATE_KEY <=  (convert(varchar,a11.ROW_EFF_END_DTTM,112) - 10000)
 and ((a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 0921 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 10000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 1001 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 10000)
 and a14.ACAD_SEASON_SNAME = 'F')
 or (a14.ACAD_SEASON_SNAME = 'X'
 and a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0621 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 10000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0701 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 10000))
 or (a14.ACAD_SEASON_SNAME = 'S'
 and a12.DATE_KEY between (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE()-9,112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0428 END - 10000) and (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE(),112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0507 END - 10000))))
group by    a17.SCHOOL_KEY,
    (a12.DATE_KEY + 10000),
    a16.DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY 

Prod 4591 rows ,2 mintues:38 Seconds,

Test 4591 rows ,16 Sec

select  a17.SCHOOL_KEY  SCHOOL_KEY,
    (a12.DATE_KEY + 20000)  DATE_KEY,
    a16.DGR_ADM_CAT_KEY  DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY  ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY  OBJ_KEY,
    count(distinct a11.APPLT_KEY)  WJXBFS1
into #ZZMD04
from    BI_EDW.dbo.DIM_APPLICATION  a11
    join    BI_EDW.dbo.TRANS_TERM_STATUS_EFF_DATE_TO_DATE   a12
      on    (CONVERT(VARCHAR,a11.ROW_EFF_START_DTTM,112) = a12.EFFSTARTDATEKEY and 
    a11.APPLN_ACAD_TERM_KEY = a12.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_TERM    a13
      on    (a11.APPLN_ACAD_TERM_KEY = a13.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    a14
      on    (a13.ACAD_REGULAR_TERM_KEY = (a14.ACAD_REGULAR_TERM_KEY - 200))
    join    #ZZMQ01 pa15
      on    (a11.APPLN_CODE = pa15.APPLN_CODE)
    join    BI_EDW.dbo.DIM_APPLICATION  a16
      on    (a11.APPLN_KEY = a16.APPLN_KEY)
    join    BI_EDW.dbo.DIM_DEPARTMENT   a17
      on    (a11.APPLN_DEPT_KEY = a17.DEPT_KEY)
    join    BI_EDW.dbo.DIM_APPLICATION_STATUS   a18
      on    (a16.APPLN_STATUS_KEY = a18.STATUS_KEY)
where   (((a14.ACAD_REGULAR_TERM_KEY)
 in (select r11.ACAD_REGULAR_TERM_KEY
    from    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    r11
    where   r11.ACTIVE in ('1')))
 and a14.ACTIVE in ('1')
 and a18.STATUS_CD in ('DEP', 'SWD', 'SDE', 'SPD', 'MS')
 and a12.DATE_KEY <=  (convert(varchar,a11.ROW_EFF_END_DTTM,112) - 20000)
 and ((a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 0921 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 20000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 1001 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 20000)
 and a14.ACAD_SEASON_SNAME = 'F')
 or (a14.ACAD_SEASON_SNAME = 'X'
 and a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0621 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 20000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0701 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 20000))
 or (a14.ACAD_SEASON_SNAME = 'S'
 and a12.DATE_KEY between (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE()-9,112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0428 END - 20000) and (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE(),112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0507 END - 20000))))
group by    a17.SCHOOL_KEY,
    (a12.DATE_KEY + 20000),
    a16.DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY

Prod 3752 rows, 2 minutes 17 Seconds

Test 3752 rows, 14 sec

select  a17.SCHOOL_KEY  SCHOOL_KEY,
    (a12.DATE_KEY + 30000)  DATE_KEY,
    a16.DGR_ADM_CAT_KEY  DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY  ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY  OBJ_KEY,
    count(distinct a11.APPLT_KEY)  WJXBFS1
into #ZZMD05
from    BI_EDW.dbo.DIM_APPLICATION  a11
    join    BI_EDW.dbo.TRANS_TERM_STATUS_EFF_DATE_TO_DATE   a12
      on    (CONVERT(VARCHAR,a11.ROW_EFF_START_DTTM,112) = a12.EFFSTARTDATEKEY and 
    a11.APPLN_ACAD_TERM_KEY = a12.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_TERM    a13
      on    (a11.APPLN_ACAD_TERM_KEY = a13.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    a14
      on    (a13.ACAD_REGULAR_TERM_KEY = (a14.ACAD_REGULAR_TERM_KEY - 300))
    join    #ZZMQ01 pa15
      on    (a11.APPLN_CODE = pa15.APPLN_CODE)
    join    BI_EDW.dbo.DIM_APPLICATION  a16
      on    (a11.APPLN_KEY = a16.APPLN_KEY)
    join    BI_EDW.dbo.DIM_DEPARTMENT   a17
      on    (a11.APPLN_DEPT_KEY = a17.DEPT_KEY)
    join    BI_EDW.dbo.DIM_APPLICATION_STATUS   a18
      on    (a16.APPLN_STATUS_KEY = a18.STATUS_KEY)
where   (((a14.ACAD_REGULAR_TERM_KEY)
 in (select r11.ACAD_REGULAR_TERM_KEY
    from    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    r11
    where   r11.ACTIVE in ('1')))
 and a14.ACTIVE in ('1')
 and a18.STATUS_CD in ('DEP', 'SWD', 'SDE', 'SPD', 'MS')
 and a12.DATE_KEY <=  (convert(varchar,a11.ROW_EFF_END_DTTM,112) - 30000)
 and ((a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 0921 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 30000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 1001 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 30000)
 and a14.ACAD_SEASON_SNAME = 'F')
 or (a14.ACAD_SEASON_SNAME = 'X'
 and a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0621 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 30000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0701 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 30000))
 or (a14.ACAD_SEASON_SNAME = 'S'
 and a12.DATE_KEY between (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE()-9,112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0428 END - 30000) and (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE(),112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0507 END - 30000))))
group by    a17.SCHOOL_KEY,
    (a12.DATE_KEY + 30000),
    a16.DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY 

Prod 2880 rows, 1 minutes and 38 seconds

Test 2880 rows, 12 Seconds

select  a17.SCHOOL_KEY  SCHOOL_KEY,
    (a12.DATE_KEY + 40000)  DATE_KEY,
    a16.DGR_ADM_CAT_KEY  DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY  ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY  OBJ_KEY,
    count(distinct a11.APPLT_KEY)  WJXBFS1
into #ZZMD06
from    BI_EDW.dbo.DIM_APPLICATION  a11
    join    BI_EDW.dbo.TRANS_TERM_STATUS_EFF_DATE_TO_DATE   a12
      on    (CONVERT(VARCHAR,a11.ROW_EFF_START_DTTM,112) = a12.EFFSTARTDATEKEY and 
    a11.APPLN_ACAD_TERM_KEY = a12.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_TERM    a13
      on    (a11.APPLN_ACAD_TERM_KEY = a13.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    a14
      on    (a13.ACAD_REGULAR_TERM_KEY = (a14.ACAD_REGULAR_TERM_KEY - 400))
    join    #ZZMQ01 pa15
      on    (a11.APPLN_CODE = pa15.APPLN_CODE)
    join    BI_EDW.dbo.DIM_APPLICATION  a16
      on    (a11.APPLN_KEY = a16.APPLN_KEY)
    join    BI_EDW.dbo.DIM_DEPARTMENT   a17
      on    (a11.APPLN_DEPT_KEY = a17.DEPT_KEY)
    join    BI_EDW.dbo.DIM_APPLICATION_STATUS   a18
      on    (a16.APPLN_STATUS_KEY = a18.STATUS_KEY)
where   (((a14.ACAD_REGULAR_TERM_KEY)
 in (select r11.ACAD_REGULAR_TERM_KEY
    from    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    r11
    where   r11.ACTIVE in ('1')))
 and a14.ACTIVE in ('1')
 and a18.STATUS_CD in ('DEP', 'SWD', 'SDE', 'SPD', 'MS')
 and a12.DATE_KEY <=  (convert(varchar,a11.ROW_EFF_END_DTTM,112) - 40000)
 and ((a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 0921 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 40000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 1001 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 40000)
 and a14.ACAD_SEASON_SNAME = 'F')
 or (a14.ACAD_SEASON_SNAME = 'X'
 and a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0621 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 40000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0701 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 40000))
 or (a14.ACAD_SEASON_SNAME = 'S'
 and a12.DATE_KEY between (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE()-9,112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0428 END - 40000) and (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE(),112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0507 END - 40000))))
group by    a17.SCHOOL_KEY,
    (a12.DATE_KEY + 40000),
    a16.DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY 

Prod 2029 rows , 1:01 minutes,

Test 2029 rows , 10 Seconds

  • On Prod Table 'DIM_APPLICATION'. Scan count 9, logical reads 231976486 On Test Table 'DIM_APPLICATION'. Scan count 18, logical reads 43896 – user42148 Jul 21 '14 at 20:34
  • Production SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 1701 ms, elapsed time = 3398 ms. SQL Server Execution Times: CPU time = 470471 ms, elapsed time = 110008 ms. (2029 row(s) affected) – user42148 Jul 21 '14 at 20:40
  • Test server SQL Server parse and compile time: CPU time = 1607 ms, elapsed time = 1611 ms. SQL Server Execution Times: CPU time = 43100 ms, elapsed time = 11570 ms. (2029 row(s) affected) – user42148 Jul 21 '14 at 20:41
  • @user42148 What is the max memory on test and PROD ? Are all sys.configuration settings same on test and PROD ? – Kin Shah Jul 21 '14 at 21:05
  • @kin, Both 8 Core CPU, Prod 32GB RAM and Test 24GB RAM. Yes the sys configuration is the same. – user42148 Jul 22 '14 at 14:57
1

Folks, I was able to resolve the issue by updating the statistics . I believe it is related to outdated stats and so the query optimizer getting not so efficient execution plan. Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.