4

UPDATE: Adding a clustered index to each work table covering all fields reduced the query run times to su-bsecond. I consider this question closed.

Thanks for taking the time to read this post - it's a bit late so hopefully this is probably borderline coherent if I'm lucky.

SQL 2008 on windows 2008.

We've got a 3rd party budgeting application that's part of our new ERP system. Users are running a forecasting process. From what I can tell, there are a set of work tables that are populated. Values from these tables are then inserted into a results table.

The process has been getting slower and slower over the past few weeks. 2 hours has now become a 24 hour run that doesn't end. I've got sp_whoisactive (a augemented sp_who2 of sorts) running and I see CPU and Reads going through the roof. 10 Billion logical reads during one of today's runs - for one query. The entire DB is cached in memory with only a few physical reads. It's been running for 20 min now and is at 500 million reads.

I dug into the INSERT / SELECT statement and started running subsections of it, removing joins and implementing the index recommendations from the showplan. This got everything humming up to a certain point in the process. The work tables were all heaps. Now it's stuck again on a similar SELECT / INSERT and I can't capture the parameters it's currently running with, since I have a trace running with Batch Completed only being captured - at least I don't know a way how to. Wait_Info from sp_whoisactive shows nothing.

So tomorrow morning we are going to run it again and I'll capture those parameters. Hopefully that will shed some light.

Below is my 'test harness' for the query. I grab a batch id from one of the work tables and then use it to build parameters for the query. The app servers are running JAVA using the Microsoft JDBC drivers, which wraps everything in sp prepares and execs, complicating things a bit.

The application vendor has supplied us with a script to insert the same dummy batchID a few thousand times into these temp tables, generate stats and then set them to NORECOMPUTE. However, this wasn't helping and I don't really understand how it would.

We're using the Hallengren maintenance scripts for index and stats maintenance. No other systems are having performance problems running on this server at this time. I ran a stock Index and Stats maintenance plan just to be sure I hadn't misconfigured the Hallengren scripts.

I'm looking for some additional guidance on how to see what this query is actually working on while it is churning away. 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  --All data is uncommitted at this point.

DECLARE @batchid VARCHAR(8000)

SELECT  @batchid = BF_BATC_STUS_EXEC_ID
FROM    dbo.BF_ALOC_WORK_ACTY

SELECT  @batchid



DECLARE @P0 VARCHAR(8000)
DECLARE @P1 DECIMAL(38, 6)
DECLARE @P2 VARCHAR(8000)
DECLARE @P3 VARCHAR(8000)
DECLARE @P4 VARCHAR(8000)
DECLARE @P5 VARCHAR(8000)
DECLARE @P6 VARCHAR(8000)
DECLARE @P7 VARCHAR(8000)
DECLARE @P8 VARCHAR(8000)
DECLARE @P9 VARCHAR(8000)
DECLARE @P10 VARCHAR(8000)
DECLARE @P11 VARCHAR(8000)
DECLARE @P12 VARCHAR(8000)
DECLARE @P13 VARCHAR(8000)
DECLARE @P14 VARCHAR(8000)
DECLARE @P15 VARCHAR(8000)
DECLARE @P16 VARCHAR(8000)
DECLARE @P17 VARCHAR(8000)
DECLARE @P18 VARCHAR(8000)
DECLARE @P19 VARCHAR(8000)

SET @P0 = 'S'
SET @P1 = -0.084125
SET @P2 = @batchid
SET @P3 = @batchid
SET @P4 = @batchid
SET @P5 = @batchid
SET @P6 = @batchid
SET @P7 = @batchid
SET @P8 = @batchid
SET @P9 = @batchid
SET @P10 = @batchid
SET @P11 = @batchid
SET @P12 = @batchid
SET @P13 = @batchid
SET @P14 = @batchid
SET @P15 = @batchid
SET @P16 = @batchid
SET @P17 = @batchid
SET @P18 = 'FINAL_BUD_TOT'
SET @P19 = 'FINAL_BUD_TOT'



INSERT  INTO BF_ALOC_RSLT
        ( BF_ALOC_RSLT.ACTY_CD ,
          BF_ALOC_RSLT.BDOB_CD ,
          BF_ALOC_RSLT.FUND_CD ,
          BF_ALOC_RSLT.ORGN_CD ,
          BF_ALOC_RSLT.PROG_CD ,
          BF_ALOC_RSLT.PROJ_CD ,
          BF_ALOC_RSLT.USER_DM1_CD ,
          BF_ALOC_RSLT.USER_DM2_CD ,
          BF_ALOC_RSLT.USER_DM3_CD ,
          BF_ALOC_RSLT.USER_DM4_CD ,
          BF_ALOC_RSLT.USER_DM5_CD ,
          BF_ALOC_RSLT.USER_DM6_CD ,
          BF_ALOC_RSLT.USER_DM7_CD ,
          BF_ALOC_RSLT.USER_DM8_CD ,
          BF_ALOC_RSLT.TYP ,
          BF_ALOC_RSLT.DATA ,
          BF_ALOC_RSLT.ALOC_LINE_GUID ,
          BF_ALOC_RSLT.BF_BATC_STUS_EXEC_ID
        )
        ( SELECT    BF_ALOC_WORK_ACTY.RSLT ,
                    BF_ALOC_WORK_BDOB.RSLT ,
                    BF_ALOC_WORK_FUND.RSLT ,
                    BF_ALOC_WORK_ORGN.RSLT ,
                    BF_ALOC_WORK_PROG.RSLT ,
                    BF_ALOC_WORK_PROJ.RSLT ,
                    BF_ALOC_WORK_USER_DM1.RSLT ,
                    BF_ALOC_WORK_USER_DM2.RSLT ,
                    BF_ALOC_WORK_USER_DM3.RSLT ,
                    BF_ALOC_WORK_USER_DM4.RSLT ,
                    BF_ALOC_WORK_USER_DM5.RSLT ,
                    BF_ALOC_WORK_USER_DM6.RSLT ,
                    BF_ALOC_WORK_USER_DM7.RSLT ,
                    BF_ALOC_WORK_USER_DM8.RSLT ,
                    @P0 ,
                    ROUND(SUM(BF_DATA.DATA) * @P1, 2) ,
                    @P2 ,
                    @P3
          FROM      BF_ALOC_WORK_ACTY ,
                    BF_ALOC_WORK_BDOB ,
                    BF_ALOC_WORK_FUND ,
                    BF_ALOC_WORK_ORGN ,
                    BF_ALOC_WORK_PROG ,
                    BF_ALOC_WORK_PROJ ,
                    BF_ALOC_WORK_USER_DM1 ,
                    BF_ALOC_WORK_USER_DM2 ,
                    BF_ALOC_WORK_USER_DM3 ,
                    BF_ALOC_WORK_USER_DM4 ,
                    BF_ALOC_WORK_USER_DM5 ,
                    BF_ALOC_WORK_USER_DM6 ,
                    BF_ALOC_WORK_USER_DM7 ,
                    BF_ALOC_WORK_USER_DM8 ,
                    BF_DATA
          WHERE     ( ( ( BF_DATA.BF_ACTY_CD = BF_ALOC_WORK_ACTY.SRC )
                        AND ( BF_ALOC_WORK_ACTY.BF_BATC_STUS_EXEC_ID = @P4 )
                      )
                      AND ( ( BF_DATA.BF_BDOB_CD = BF_ALOC_WORK_BDOB.SRC )
                            AND ( BF_ALOC_WORK_BDOB.BF_BATC_STUS_EXEC_ID = @P5 )
                          )
                      AND ( ( BF_DATA.BF_FUND_CD = BF_ALOC_WORK_FUND.SRC )
                            AND ( BF_ALOC_WORK_FUND.BF_BATC_STUS_EXEC_ID = @P6 )
                          )
                      AND ( ( BF_DATA.BF_ORGN_CD = BF_ALOC_WORK_ORGN.SRC )
                            AND ( BF_ALOC_WORK_ORGN.BF_BATC_STUS_EXEC_ID = @P7 )
                          )
                      AND ( ( BF_DATA.BF_PROG_CD = BF_ALOC_WORK_PROG.SRC )
                            AND ( BF_ALOC_WORK_PROG.BF_BATC_STUS_EXEC_ID = @P8 )
                          )
                      AND ( ( BF_DATA.BF_PROJ_CD = BF_ALOC_WORK_PROJ.SRC )
                            AND ( BF_ALOC_WORK_PROJ.BF_BATC_STUS_EXEC_ID = @P9 )
                          )
                      AND ( ( BF_DATA.BF_USER_DM1_CD = BF_ALOC_WORK_USER_DM1.SRC )
                            AND ( BF_ALOC_WORK_USER_DM1.BF_BATC_STUS_EXEC_ID = @P10 )
                          )
                      AND ( ( BF_DATA.BF_USER_DM2_CD = BF_ALOC_WORK_USER_DM2.SRC )
                            AND ( BF_ALOC_WORK_USER_DM2.BF_BATC_STUS_EXEC_ID = @P11 )
                          )
                      AND ( ( BF_DATA.BF_USER_DM3_CD = BF_ALOC_WORK_USER_DM3.SRC )
                            AND ( BF_ALOC_WORK_USER_DM3.BF_BATC_STUS_EXEC_ID = @P12 )
                          )
                      AND ( ( BF_DATA.BF_USER_DM4_CD = BF_ALOC_WORK_USER_DM4.SRC )
                            AND ( BF_ALOC_WORK_USER_DM4.BF_BATC_STUS_EXEC_ID = @P13 )
                          )
                      AND ( ( BF_DATA.BF_USER_DM5_CD = BF_ALOC_WORK_USER_DM5.SRC )
                            AND ( BF_ALOC_WORK_USER_DM5.BF_BATC_STUS_EXEC_ID = @P14 )
                          )
                      AND ( ( BF_DATA.BF_USER_DM6_CD = BF_ALOC_WORK_USER_DM6.SRC )
                            AND ( BF_ALOC_WORK_USER_DM6.BF_BATC_STUS_EXEC_ID = @P15 )
                          )
                      AND ( ( BF_DATA.BF_USER_DM7_CD = BF_ALOC_WORK_USER_DM7.SRC )
                            AND ( BF_ALOC_WORK_USER_DM7.BF_BATC_STUS_EXEC_ID = @P16 )
                          )
                      AND ( ( BF_DATA.BF_USER_DM8_CD = BF_ALOC_WORK_USER_DM8.SRC )
                            AND ( BF_ALOC_WORK_USER_DM8.BF_BATC_STUS_EXEC_ID = @P17 )
                          )
                      AND ( ( BF_DATA.BF_TM_PERD_CD = @P18 )
                            OR ( BF_DATA.BF_TM_PERD_CD IN (
                                 SELECT BF_TM_PERD_RLUP.BF_TM_PERD_CHLD_CD
                                 FROM   BF_TM_PERD_RLUP
                                 WHERE  ( BF_TM_PERD_RLUP.BF_TM_PERD_PARN_CD = @P19 ) ) )
                          )
                    )
          GROUP BY  BF_ALOC_WORK_ACTY.RSLT ,
                    BF_ALOC_WORK_BDOB.RSLT ,
                    BF_ALOC_WORK_FUND.RSLT ,
                    BF_ALOC_WORK_ORGN.RSLT ,
                    BF_ALOC_WORK_PROG.RSLT ,
                    BF_ALOC_WORK_PROJ.RSLT ,
                    BF_ALOC_WORK_USER_DM1.RSLT ,
                    BF_ALOC_WORK_USER_DM2.RSLT ,
                    BF_ALOC_WORK_USER_DM3.RSLT ,
                    BF_ALOC_WORK_USER_DM4.RSLT ,
                    BF_ALOC_WORK_USER_DM5.RSLT ,
                    BF_ALOC_WORK_USER_DM6.RSLT ,
                    BF_ALOC_WORK_USER_DM7.RSLT ,
                    BF_ALOC_WORK_USER_DM8.RSLT
        )

Edits:

SHOWPLAN Text

  |--Table Insert(OBJECT:([PB].[dbo].[BF_ALOC_RSLT]), OBJECT:([PB].[dbo].[BF_ALOC_RSLT].[XIE1_ALOC_RSLT]), SET:([PB].[dbo].[BF_ALOC_RSLT].[ACTY_CD] = [PB].[dbo].[BF_ALOC_WORK_ACTY].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[BDOB_CD] = [PB].[dbo].[BF_ALOC_WORK_BDOB].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[FUND_CD] = [PB].[dbo].[BF_ALOC_WORK_FUND].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[ORGN_CD] = [PB].[dbo].[BF_ALOC_WORK_ORGN].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[PROG_CD] = [PB].[dbo].[BF_ALOC_WORK_PROG].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[PROJ_CD] = [PB].[dbo].[BF_ALOC_WORK_PROJ].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM1_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM1].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM2_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM2].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM3_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM3].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM4_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM4].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM5_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM5].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM6_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM6].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM7_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM7].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[USER_DM8_CD] = [PB].[dbo].[BF_ALOC_WORK_USER_DM8].[RSLT],[PB].[dbo].[BF_ALOC_RSLT].[TYP] = RaiseIfNullInsert([Expr1067]),[PB].[dbo].[BF_ALOC_RSLT].[DATA] = RaiseIfNullInsert([Expr1068]),[PB].[dbo].[BF_ALOC_RSLT].[ALOC_LINE_GUID] = RaiseIfNullInsert([Expr1069]),[PB].[dbo].[BF_ALOC_RSLT].[BF_BATC_STUS_EXEC_ID] = RaiseIfNullInsert([Expr1070]),[PB].[dbo].[BF_ALOC_RSLT].[DIV_CD] = NULL,[PB].[dbo].[BF_ALOC_RSLT].[PATN_CD] = NULL))
   |--Compute Scalar(DEFINE:([Expr1067]=CONVERT_IMPLICIT(char(1),[@P0],0), [Expr1068]=CONVERT_IMPLICIT(numeric(27,6),round([Expr1066]*[@P1],(2)),0), [Expr1069]=CONVERT_IMPLICIT(varchar(32),[@P2],0), [Expr1070]=CONVERT_IMPLICIT(varchar(32),[@P3],0)))
        |--Top(ROWCOUNT est 0)
             |--Stream Aggregate(GROUP BY:([PB].[dbo].[BF_ALOC_WORK_ACTY].[RSLT], [PB].[dbo].[BF_ALOC_WORK_BDOB].[RSLT], [PB].[dbo].[BF_ALOC_WORK_FUND].[RSLT], [PB].[dbo].[BF_ALOC_WORK_ORGN].[RSLT], [PB].[dbo].[BF_ALOC_WORK_PROG].[RSLT], [PB].[dbo].[BF_ALOC_WORK_PROJ].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM1].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM2].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM3].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM4].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM5].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM6].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM7].[RSLT], [PB].[dbo].[BF_ALOC_WORK_USER_DM8].[RSLT]) DEFINE:([Expr1066]=SUM([PB].[dbo].[BF_DATA].[DATA])))
                  |--Sort(ORDER BY:([PB].[dbo].[BF_ALOC_WORK_ACTY].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_BDOB].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_FUND].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_ORGN].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_PROG].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_PROJ].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM1].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM2].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM3].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM4].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM5].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM6].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM7].[RSLT] ASC, [PB].[dbo].[BF_ALOC_WORK_USER_DM8].[RSLT] ASC))
                       |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_DATA].[BF_ACTY_CD])=([PB].[dbo].[BF_ALOC_WORK_ACTY].[SRC]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_ACTY_CD]=[PB].[dbo].[BF_ALOC_WORK_ACTY].[SRC]))
                            |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_DATA].[BF_USER_DM2_CD])=([PB].[dbo].[BF_ALOC_WORK_USER_DM2].[SRC]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM2_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM2].[SRC]))
                            |    |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_BDOB].[SRC])=([PB].[dbo].[BF_DATA].[BF_BDOB_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_BDOB_CD]=[PB].[dbo].[BF_ALOC_WORK_BDOB].[SRC]))
                            |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1008]))
                            |    |    |    |--Index Seek(OBJECT:([PB].[dbo].[BF_ALOC_WORK_BDOB].[IX_COM_BF_ALOC_WORK_BDOB]), SEEK:([PB].[dbo].[BF_ALOC_WORK_BDOB].[BF_BATC_STUS_EXEC_ID]=[@P5]) ORDERED FORWARD)
                            |    |    |    |--RID Lookup(OBJECT:([PB].[dbo].[BF_ALOC_WORK_BDOB]), SEEK:([Bmk1008]=[Bmk1008]) LOOKUP ORDERED FORWARD)
                            |    |    |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM3].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM3_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM3_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM3].[SRC]))
                            |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1036]))
                            |    |         |    |--Index Seek(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM3].[IX_COM_BF_ALOC_WORK_USER_DM3_EXEC_ID]), SEEK:([PB].[dbo].[BF_ALOC_WORK_USER_DM3].[BF_BATC_STUS_EXEC_ID]=[@P12]) ORDERED FORWARD)
                            |    |         |    |--RID Lookup(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM3]), SEEK:([Bmk1036]=[Bmk1036]) LOOKUP ORDERED FORWARD)
                            |    |         |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM1].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM1_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM1_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM1].[SRC]))
                            |    |              |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1028]))
                            |    |              |    |--Index Seek(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM1].[IX_COM_BF_ALOC_WORK_USER_DM1_EXEC_ID]), SEEK:([PB].[dbo].[BF_ALOC_WORK_USER_DM1].[BF_BATC_STUS_EXEC_ID]=[@P10]) ORDERED FORWARD)
                            |    |              |    |--RID Lookup(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM1]), SEEK:([Bmk1028]=[Bmk1028]) LOOKUP ORDERED FORWARD)
                            |    |              |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM8].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM8_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM8_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM8].[SRC]))
                            |    |                   |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM8]), WHERE:([PB].[dbo].[BF_ALOC_WORK_USER_DM8].[BF_BATC_STUS_EXEC_ID]=[@P17]))
                            |    |                   |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_FUND].[SRC])=([PB].[dbo].[BF_DATA].[BF_FUND_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_FUND_CD]=[PB].[dbo].[BF_ALOC_WORK_FUND].[SRC]))
                            |    |                        |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_FUND]), WHERE:([PB].[dbo].[BF_ALOC_WORK_FUND].[BF_BATC_STUS_EXEC_ID]=[@P6]))
                            |    |                        |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM6].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM6_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM6_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM6].[SRC]))
                            |    |                             |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM6]), WHERE:([PB].[dbo].[BF_ALOC_WORK_USER_DM6].[BF_BATC_STUS_EXEC_ID]=[@P15]))
                            |    |                             |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM5].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM5_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM5_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM5].[SRC]))
                            |    |                                  |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM5]), WHERE:([PB].[dbo].[BF_ALOC_WORK_USER_DM5].[BF_BATC_STUS_EXEC_ID]=[@P14]))
                            |    |                                  |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_PROJ].[SRC])=([PB].[dbo].[BF_DATA].[BF_PROJ_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_PROJ_CD]=[PB].[dbo].[BF_ALOC_WORK_PROJ].[SRC]))
                            |    |                                       |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_PROJ]), WHERE:([PB].[dbo].[BF_ALOC_WORK_PROJ].[BF_BATC_STUS_EXEC_ID]=[@P9]))
                            |    |                                       |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM4].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM4_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM4_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM4].[SRC]))
                            |    |                                            |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM4]), WHERE:([PB].[dbo].[BF_ALOC_WORK_USER_DM4].[BF_BATC_STUS_EXEC_ID]=[@P13]))
                            |    |                                            |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_USER_DM7].[SRC])=([PB].[dbo].[BF_DATA].[BF_USER_DM7_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_USER_DM7_CD]=[PB].[dbo].[BF_ALOC_WORK_USER_DM7].[SRC]))
                            |    |                                                 |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM7]), WHERE:([PB].[dbo].[BF_ALOC_WORK_USER_DM7].[BF_BATC_STUS_EXEC_ID]=[@P16]))
                            |    |                                                 |--Hash Match(Inner Join, HASH:([PB].[dbo].[BF_ALOC_WORK_PROG].[SRC])=([PB].[dbo].[BF_DATA].[BF_PROG_CD]), RESIDUAL:([PB].[dbo].[BF_DATA].[BF_PROG_CD]=[PB].[dbo].[BF_ALOC_WORK_PROG].[SRC]))
                            |    |                                                      |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_PROG]), WHERE:([PB].[dbo].[BF_ALOC_WORK_PROG].[BF_BATC_STUS_EXEC_ID]=[@P8]))
                            |    |                                                      |--Nested Loops(Inner Join, OUTER REFERENCES:([PB].[dbo].[BF_ALOC_WORK_ORGN].[SRC]))
                            |    |                                                           |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_ORGN]), WHERE:([PB].[dbo].[BF_ALOC_WORK_ORGN].[BF_BATC_STUS_EXEC_ID]=[@P7]))
                            |    |                                                           |--Sort(DISTINCT ORDER BY:([PB].[dbo].[BF_DATA].[BF_ACTY_CD] ASC, [PB].[dbo].[BF_DATA].[BF_FUND_CD] ASC, [PB].[dbo].[BF_DATA].[BF_PROG_CD] ASC, [PB].[dbo].[BF_DATA].[BF_PROJ_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM1_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM2_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM3_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM4_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM5_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM6_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM7_CD] ASC, [PB].[dbo].[BF_DATA].[BF_USER_DM8_CD] ASC, [PB].[dbo].[BF_DATA].[BF_BDOB_CD] ASC, [PB].[dbo].[BF_DATA].[BF_TM_PERD_CD] ASC))
                            |    |                                                                |--Concatenation
                            |    |                                                                     |--Nested Loops(Inner Join, OUTER REFERENCES:([PB].[dbo].[BF_DATA].[BF_ORGN_CD], [PB].[dbo].[BF_DATA].[BF_ACTY_CD], [PB].[dbo].[BF_DATA].[BF_PROG_CD], [PB].[dbo].[BF_DATA].[BF_PROJ_CD], [PB].[dbo].[BF_DATA].[BF_BDOB_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM1_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM2_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM3_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM4_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM5_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM6_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM7_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM8_CD], [PB].[dbo].[BF_DATA].[BF_FUND_CD], [PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]))
                            |    |                                                                     |    |--Index Seek(OBJECT:([PB].[dbo].[BF_DATA].[XIF18_DATA]), SEEK:([PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]=[@P18]),  WHERE:([PB].[dbo].[BF_DATA].[BF_ORGN_CD]=[PB].[dbo].[BF_ALOC_WORK_ORGN].[SRC]) ORDERED FORWARD)
                            |    |                                                                     |    |--Clustered Index Seek(OBJECT:([PB].[dbo].[BF_DATA].[XPK_DATA]), SEEK:([PB].[dbo].[BF_DATA].[BF_ACTY_CD]=[PB].[dbo].[BF_DATA].[BF_ACTY_CD] AND [PB].[dbo].[BF_DATA].[BF_FUND_CD]=[PB].[dbo].[BF_DATA].[BF_FUND_CD] AND [PB].[dbo].[BF_DATA].[BF_ORGN_CD]=[PB].[dbo].[BF_DATA].[BF_ORGN_CD] AND [PB].[dbo].[BF_DATA].[BF_PROG_CD]=[PB].[dbo].[BF_DATA].[BF_PROG_CD] AND [PB].[dbo].[BF_DATA].[BF_PROJ_CD]=[PB].[dbo].[BF_DATA].[BF_PROJ_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM1_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM1_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM2_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM2_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM3_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM3_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM4_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM4_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM5_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM5_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM6_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM6_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM7_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM7_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM8_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM8_CD] AND [PB].[dbo].[BF_DATA].[BF_BDOB_CD]=[PB].[dbo].[BF_DATA].[BF_BDOB_CD] AND [PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]=[PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]) LOOKUP ORDERED FORWARD)
                            |    |                                                                     |--Nested Loops(Inner Join, OUTER REFERENCES:([PB].[dbo].[BF_DATA].[BF_ORGN_CD], [PB].[dbo].[BF_DATA].[BF_ACTY_CD], [PB].[dbo].[BF_DATA].[BF_PROG_CD], [PB].[dbo].[BF_DATA].[BF_PROJ_CD], [PB].[dbo].[BF_DATA].[BF_BDOB_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM1_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM2_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM3_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM4_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM5_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM6_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM7_CD], [PB].[dbo].[BF_DATA].[BF_USER_DM8_CD], [PB].[dbo].[BF_DATA].[BF_FUND_CD], [PB].[dbo].[BF_DATA].[BF_TM_PERD_CD], [Expr1077]) WITH UNORDERED PREFETCH)
                            |    |                                                                          |--Nested Loops(Inner Join, OUTER REFERENCES:([PB].[dbo].[BF_TM_PERD_RLUP].[BF_TM_PERD_CHLD_CD]))
                            |    |                                                                          |    |--Index Seek(OBJECT:([PB].[dbo].[BF_TM_PERD_RLUP].[XIF1_TM_PERD_RLUP]), SEEK:([PB].[dbo].[BF_TM_PERD_RLUP].[BF_TM_PERD_PARN_CD]=[@P19]) ORDERED FORWARD)
                            |    |                                                                          |    |--Index Seek(OBJECT:([PB].[dbo].[BF_DATA].[XIF18_DATA]), SEEK:([PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]=[PB].[dbo].[BF_TM_PERD_RLUP].[BF_TM_PERD_CHLD_CD]),  WHERE:([PB].[dbo].[BF_DATA].[BF_ORGN_CD]=[PB].[dbo].[BF_ALOC_WORK_ORGN].[SRC]) ORDERED FORWARD)
                            |    |                                                                          |--Clustered Index Seek(OBJECT:([PB].[dbo].[BF_DATA].[XPK_DATA]), SEEK:([PB].[dbo].[BF_DATA].[BF_ACTY_CD]=[PB].[dbo].[BF_DATA].[BF_ACTY_CD] AND [PB].[dbo].[BF_DATA].[BF_FUND_CD]=[PB].[dbo].[BF_DATA].[BF_FUND_CD] AND [PB].[dbo].[BF_DATA].[BF_ORGN_CD]=[PB].[dbo].[BF_DATA].[BF_ORGN_CD] AND [PB].[dbo].[BF_DATA].[BF_PROG_CD]=[PB].[dbo].[BF_DATA].[BF_PROG_CD] AND [PB].[dbo].[BF_DATA].[BF_PROJ_CD]=[PB].[dbo].[BF_DATA].[BF_PROJ_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM1_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM1_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM2_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM2_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM3_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM3_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM4_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM4_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM5_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM5_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM6_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM6_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM7_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM7_CD] AND [PB].[dbo].[BF_DATA].[BF_USER_DM8_CD]=[PB].[dbo].[BF_DATA].[BF_USER_DM8_CD] AND [PB].[dbo].[BF_DATA].[BF_BDOB_CD]=[PB].[dbo].[BF_DATA].[BF_BDOB_CD] AND [PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]=[PB].[dbo].[BF_DATA].[BF_TM_PERD_CD]) LOOKUP ORDERED FORWARD)
                            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1032], [Expr1078]) WITH UNORDERED PREFETCH)
                            |         |--Index Seek(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM2].[IX_COM_BF_ALOC_WORK_USER_DM2_EXEC_ID]), SEEK:([PB].[dbo].[BF_ALOC_WORK_USER_DM2].[BF_BATC_STUS_EXEC_ID]=[@P11]) ORDERED FORWARD)
                            |         |--RID Lookup(OBJECT:([PB].[dbo].[BF_ALOC_WORK_USER_DM2]), SEEK:([Bmk1032]=[Bmk1032]) LOOKUP ORDERED FORWARD)
                            |--Table Scan(OBJECT:([PB].[dbo].[BF_ALOC_WORK_ACTY]), WHERE:([PB].[dbo].[BF_ALOC_WORK_ACTY].[BF_BATC_STUS_EXEC_ID]=[@P4]))

One other note, the query plan shows early termination due to plan timeout.

Improve this question
8
  • 2
    Cheese and fries! That's way up there on the list of worst written queries I've ever seen. My condolences for having to deal with this stuff. I would seriously recommend switching vendors because these guys have no clue what they are doing. It's probably getting slower and slower because the data it's processing keeps increasing. That thing has to be chewing up your buffer pool.
    – Robert L Davis
    Commented Jan 27, 2013 at 6:29
  • That wasn't much help. I think you are on the right track and diligent query tuning is your best option. I would recommend restoring the database onto a non-production server, capture the workload with SQL Trace, and then run DTA (Database Engine Tuning Advisor) on it.
    – Robert L Davis
    Commented Jan 27, 2013 at 6:31
  • How nice - I have all that ready to go. Running now...
    – Sam
    Commented Jan 27, 2013 at 6:42
  • 1
    Can you post the execution plan?
    – Martin Smith
    Commented Jan 27, 2013 at 9:40
  • @MartinSmith - Posted
    – Sam
    Commented Jan 27, 2013 at 13:47

1 Answer 1

Reset to default
1

I followed some 'basic query tuning' steps as explained in this article: http://www.simple-talk.com/sql/performance/simple-query-tuning-with-statistics-io-and-execution-plans/

I used sp_whoisactive, SET STATISTICS IO ON to find where the reads were happening and then added indexes based on the explain plan.

This resulted in adding covering indexes to each work table. A couple queries are taking about 2 seconds, but the majority are sub second and sub tenth of a sec.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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