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.
Best Answer
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.