any general approaches to troubleshooting a sudden long running query?

Question

Post reply

any general approaches to troubleshooting a sudden long running query?

Greg Johnson-186398

SSCommitted

Points: 1703
More actions
June 2, 2005 at 8:00 am

#110241

I've got a view that gets called often and until yesterday took about 1.5 seconds to run (barely acceptable). Yesterday, 1 in every 5 or so calls to this view took varying amounts of time between 2 seconds and 30 seconds.
I've ran a perfmon on the box, and there seems to be no bottlenecks. There are no other processes running on the machine. I'm guessing it's another call to the database that is locking a resource that the view uses, but how do I track it down? dbcc showcontig looks good for all tables involved. Index Wizard has no suggestions. I've been trying to use the "Current Activity" monitor in EM, but it's tough to get in there and get a refresh in while the problem is happening.
Is there a checklist somewhere I can go through to make sure I'm not overlooking something? Anybody want more details on the issue?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Are the stats up to date?

sp_updatestats

Are the index fragmented?

Greg Johnson-186398 SSCommitted Points: 1703 More actions · Answer 2

I ran sp_updatestats and saw no improvement. There is negligible fragmentation on the tables and nonclustered indexes.

Rick Phillips-220796 SSC Eights! Points: 967 More actions · Answer 3

Are you using the "with (NOLOCK)" optimizer hint on the tables within the query?

What is the reason for the normally poor performance (1.5 Seconds)? Is the view scanning large tables and/or indexes?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Have you check for a bottleneck on the execution plan?

One query in a while could also be caused by a lock. Have you run the profiler to find such locks?

Greg Johnson-186398 SSCommitted Points: 1703 More actions · Answer 5

Are you using the "with (NOLOCK)" optimizer hint on the tables within the query?

DIDN'T SEEM TO HELP.

What is the reason for the normally poor performance (1.5 Seconds)? Is the view scanning large tables and/or indexes?

THE VIEW INVOLVES ABOUT 12 TABLES AND A TABLE VALUED FUNCTION IN THE FROM CLAUSE

Have you check for a bottleneck on the execution plan?

NO SINGLE STEP TAKES UP MORE THAN 20% (THE TABLE VALUED FUNCTION CALL)

One query in a while could also be caused by a lock. Have you run the profiler to find such locks?

I'M SEEING A LOT OF BENIGN LOCKS LIKE SHARE OR INTENT-SHARE. ALL LOCKS ARE SHOWING A DURATION OF 0. (IS THAT EVEN A VALID COLUMN FOR A LOCK PROFILE?)

Greg Johnson-186398 SSCommitted Points: 1703 More actions · Answer 6

also, I put the "WITH (NOLOCK)" hint on all the tables in the view. this is where you where I should be putting them, right?

Greg Johnson-186398 SSCommitted Points: 1703 More actions · Answer 7

actually, now that I'm looking closer, I'm seeing hundreds (thousands?) of locks, aquired and released on the same millisecond in time around the same time that a select to my view took a long time (6 secs).

why so much locking activity?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Can you paste the query in here... maybe we'll be able to spot something you haven't thaught of yet. Also the execution plan would hurt .

Greg Johnson-186398 SSCommitted Points: 1703 More actions · Answer 9

I really appreciate the help...

ALTER view vwPositionByBizStrategy

as

select

min(S.BizSubBusinessUnitName) as BizSubBusinessUnitName,

min(BU.BizBusinessUnitName) as BizBusinessUnitName,

D.BizStrategyName,

I.SevernTicker,

min(I.SecurityName) as SecurityName,

min(I.CommonTicker) as CommonTicker,

min(IT.InstID) as InstID,

-- added fields for manual use by Gil Song, may be able to remove later

min(IT.CUSIP) as CUSIP,

min(IT.ISIN) as ISIN,

sum(P.Quantity) as SRTradeQuantity,

min(M.Price) as Price,

min(M.UnderlyerPrice) as UnderlyerPrice,

min(PM.Price) as PreviousPrice,

min(PM.UnderlyerPrice) as PreviousUnderlyerPrice,

min(PM7.Price) as Previous7Price,

min(PM7.UnderlyerPrice) as Previous7UnderlyerPrice,

min(M.Delta) as Delta,

min(M.CreditSpread) as CreditSpread,

min(M.Volatility) as Volatility,

min(M.[RecoveryRate]) as [RecoveryRate],

min(M.DateFrom) as DateFrom,

min(RMIO.TheoDelta) as TheoDelta,

min(RMIO.TheoVega) as TheoVega,

min(RMIO.TheoRho) as TheoRho,

min(M.CreditRhoWeight) as CreditRhoWeight,

min(M.InterestRateRhoWeight) as InterestRateRhoWeight,

min(M.EquityFactor) as EquityFactor,

min(right(C.InstCurrencyName, 3)) as InstCurrencyName,

min(right(UC.InstCurrencyName, 3)) as UnderlyerCurrencyName,

min(I.Coupon) / 100 as Coupon,

min(I.PaymentFrequency) as CouponFrequency,

min(I.TerminationDate) as TerminationDate,

min(I.FirstSettleDate) as FirstSettleDate,

min(I.FirstCouponDate) as FirstCouponDate,

min(I.PriceFactor) as PriceFactor,

min(I.ConversionRatio) as ConversionRatio,

min(I.StrikePrice) as StrikePrice,

min(TYPE.EnumName) as InstTypeEnumName,

min(UTYPE.EnumName) as UnderlyerInstTypeEnumName,

min(IT.BBTicker) as BBTicker,

min(UIT.BBTicker) as UnderlyerBBTicker,

min(I.FixedExchangeRate) as FixedExchangeRate,

min(I.ParAmount) as ParAmount,

case when min(TYPE.EnumName) = 'as' then min(UM.Price) else null end as aswPrice,

case when min(TYPE.EnumName) = 'as' then min(UM.UnderlyerPrice) else null end as aswUnderlyerPrice,

case when min(TYPE.EnumName) = 'as' then min(UM.Delta) else null end as aswDelta,

case when min(TYPE.EnumName) = 'as' then min(UI.ConversionRatio) else null end as aswConversionRatio,

case when min(TYPE.EnumName) = 'as' then min(UI.PriceFactor) else null end as aswFactor,

case when min(TYPE.EnumName) = 'as' then min(UUIT.BBTicker) else null end as aswUnderlyerBBTicker,

case when min(TYPE.EnumName) = 'as' then min(right(UUIC.InstCurrencyName, 3)) else null end as aswUnderlyerCurrencyName,

min(IR.InstRegionName) as InstRegionName,

min(IP.PutDate) as NextPutDate,

-- convert(datetime, '1/1/2999') as NextPutDate,

min(ISEC.InstIndustrySectorName) as InstIndustrySectorName,

convert(bit, min(convert(int,I.IsDefaulted))) as IsDefaulted,

min(TC.TransCounterpartyName) as TransCounterpartyName,

min(CP.BBTicker) as MarkRefCurvePointBBTicker

from

(select * from dbo.fnPositionMarkS(null)) P

join tbInstTranche IT on IT.InstTrancheID = P.InstTrancheID

join tbBizStrategy D on D.BizStrategyID = P.BizStrategyID

left join tbInstRegion IR on IR.InstRegionID = D.InstRegionID

join tbBizSubBusinessUnit S on S.BizSubBusinessUnitID = D.BizSubBusinessUnitID

join tbBizBusinessUnit BU on BU.BizBusinessUnitID = S.BizBusinessUnitID

join tbInst I on I.InstID = IT.InstID

join tbEnum TYPE on TYPE.EnumID = I.InstTypeEnumID

left join tbInstCurrency C on C.InstCurrencyID = I.InstCurrencyID

left join tbInst UI on UI.InstID = I.UnderlyerInstID

left join tbEnum UTYPE on UTYPE.EnumID = UI.InstTypeEnumID

left join tbInstCurrency UC on UC.InstCurrencyID = UI.InstCurrencyID

left join tbInstTranche UIT on UIT.InstID = I.UnderlyerInstID

and UIT.InstTrancheTypeEnumID = dbo.fnEnumS('InstTrancheType', '')

left join tbMarkInput M on M.InstID = IT.InstID

and M.DateThru is null

left join tbMarkInput UM on UM.InstID = UI.InstID

and UM.DateThru is null

left join tbRiskManualInstOutput RMIO on RMIO.InstID = M.InstID

and RMIO.DateThru is null

left join tbMark PM on PM.DatasetID = dbo.fnLatestDatasetS ('MEOD', getdate()-1,'P')

and PM.InstID = I.InstID

left join tbMark PM7 on PM7.DatasetID = dbo.fnLatestDatasetS ('MEOD',getdate()-7,'P')

and PM7.InstID = I.InstID

left join tbInst UUI on UUI.InstID = UI.UnderlyerInstID

left join tbInstTranche UUIT on UUIT.InstID = UUI.InstID

and UUIT.InstTrancheTypeEnumID = dbo.fnEnumS('InstTrancheType', '')

left join tbInstCurrency UUIC on UUIC.InstCurrencyID = UUI.InstCurrencyID

left join tbInstCompany IC on IC.InstCompanyID = I.InstCompanyID

left join tbInstIndustrySubgroup ISG on ISG.InstIndustrySubgroupID = IC.InstIndustrySubgroupID

left join tbInstIndustryGroup IG on IG.InstIndustryGroupID = ISG.InstIndustryGroupID

left join tbInstIndustrySector ISEC on ISEC.InstIndustrySectorID = IG.InstIndustrySectorID

left join tbInstPut IP on IP.InstID = I.InstID and IP.PutDate =

( select min(IPX.PutDate) from tbInstPut IPX

where IPX.InstID = I.InstID and IPX.PutDate >= dbo.fnUtilTruncTime(getdate()) )

left join tbTrans T on T.TransID = I.TransID

left join tbTransBroker BR on BR.TransBrokerID = T.TransBrokerID

left join tbTransCounterparty TC on TC.TransCounterpartyID = BR.TransBrokerCounterpartyID

left join tbCurvePoint CP on CP.CurvePointID = I.MarkRefCurvePointID

where

I.SevernTicker not like 'cy%'

--I.InstTypeEnumID != dbo.fnEnumS('InstType', 'cy')

group by

D.BizStrategyName, I.SevernTicker

-----------------------------

how do I get a text based query plan for you?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

go in Results in text mode then run this :

SET SHOWPLAN_ALL ON

GO

Select * from dbo.YourView

GO

SET SHOWPLAN_ALL OFF

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Also are all those left joins necessary?? You know that you force the server to do a lot more work when using outer join than inner joins?

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 12

On a side note, if you're having problems getting into Current Activity in EM, you can always run sp_who/sp_who2 from QA. They're usually much more responsive.

Greg Johnson-186398 SSCommitted Points: 1703 More actions · Answer 13

this is going to be tought to read, but...

StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------ -------- ------------------------

Select * from vwPositionByBizStrategy 5993 1 0 NULL NULL 1 NULL 1.0 NULL NULL NULL 0.19837749 NULL NULL SELECT 0 NULL

|--Compute Scalar(DEFINE[Expr1118]=[Expr1091]/100, [Expr1119]=If ([Expr1099]='as') then [Expr1105] else NULL, [Expr1120]=If ([Expr1099]='as') then [Expr1106] else NULL, [Expr1121]=If ([Expr1099]='as') then [Expr1107] else NULL, [Expr1122]=If ([Expr1099 5993 2 1 Compute Scalar Compute Scalar DEFINE[Expr1118]=[Expr1091]/100, [Expr1119]=If ([Expr1099]='as') then [Expr1105] else NULL, [Expr1120]=If ([Expr1099]='as') then [Expr1106] else NULL, [Expr1121]=If ([Expr1099]='as') then [Expr1107] else NULL, [Expr1122]=If ([Expr1099]='as') then [Expr11 [Expr1118]=[Expr1091]/100, [Expr1119]=If ([Expr1099]='as') then [Expr1105] else NULL, [Expr1120]=If ([Expr1099]='as') then [Expr1106] else NULL, [Expr1121]=If ([Expr1099]='as') then [Expr1107] else NULL, [Expr1122]=If ([Expr1099]='as') then [Expr1108] else 1.0 0.0 0.0000001 2254 0.19837749 [tbBizStrategy].[BizStrategyName], [tbInst].[SevernTicker], [Expr1064], [Expr1065], [Expr1066], [Expr1067], [Expr1068], [Expr1069], [Expr1070], [Expr1071], [Expr1072], [Expr1073], [Expr1074], [Expr1075], [Expr1076], [Expr1077], [Expr1078], [Expr1079], [Exp NULL PLAN_ROW 0 1.0

|--Compute Scalar(DEFINE[Expr1071]=If ([Expr1183]=0) then NULL else [Expr1184])) 5993 3 2 Compute Scalar Compute Scalar DEFINE[Expr1071]=If ([Expr1183]=0) then NULL else [Expr1184]) [Expr1071]=If ([Expr1183]=0) then NULL else [Expr1184] 1.0 0.0 7.6000001E-6 2257 0.19837739 [tbBizStrategy].[BizStrategyName], [tbInst].[SevernTicker], [Expr1064], [Expr1065], [Expr1066], [Expr1067], [Expr1068], [Expr1069], [Expr1070], [Expr1071], [Expr1072], [Expr1073], [Expr1074], [Expr1075], [Expr1076], [Expr1077], [Expr1078], [Expr1079], [Exp NULL PLAN_ROW 0 1.0

|--Stream Aggregate(GROUP BY[tbInst].[SevernTicker], [tbBizStrategy].[BizStrategyName]) DEFINE[Expr1064]=MIN([tbBizSubBusinessUnit].[BizSubBusinessUnitName]), [Expr1065]=MIN([tbBizBusinessUnit].[BizBusinessUnitName]), [Expr1066]=MIN([tbInst 5993 4 3 Stream Aggregate Aggregate GROUP BY[tbInst].[SevernTicker], [tbBizStrategy].[BizStrategyName]) [Expr1064]=MIN([tbBizSubBusinessUnit].[BizSubBusinessUnitName]), [Expr1065]=MIN([tbBizBusinessUnit].[BizBusinessUnitName]), [Expr1066]=MIN([tbInst].[SecurityName]), [Expr1067]=MIN([tbInst].[CommonTicker]), [Expr1068]=MIN([tbInstTranche].[InstID]), [Expr106 1.0 0.0 7.6000001E-6 2257 0.19837739 [tbBizStrategy].[BizStrategyName], [tbInst].[SevernTicker], [Expr1064], [Expr1065], [Expr1066], [Expr1067], [Expr1068], [Expr1069], [Expr1070], [Expr1072], [Expr1073], [Expr1074], [Expr1075], [Expr1076], [Expr1077], [Expr1078], [Expr1079], [Expr1080], [Exp NULL PLAN_ROW 0 1.0

|--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInst].[MarkRefCurvePointID])) 5993 5 4 Nested Loops Left Outer Join OUTER REFERENCES[tbInst].[MarkRefCurvePointID]) NULL 1.0 0.0 0.00001254 6157 0.19836979 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

|--Nested Loops(Left Outer Join, OUTER REFERENCES[tbTransBroker].[TransBrokerCounterpartyID])) 5993 6 5 Nested Loops Left Outer Join OUTER REFERENCES[tbTransBroker].[TransBrokerCounterpartyID]) NULL 1.0 0.0 0.00001254 6097 0.19483647 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbTrans].[TransBrokerID])) 5993 7 6 Nested Loops Left Outer Join OUTER REFERENCES[tbTrans].[TransBrokerID]) NULL 1.0 0.0 0.00001254 6072 0.19130315 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInst].[TransID])) 5993 8 7 Nested Loops Left Outer Join OUTER REFERENCES[tbInst].[TransID]) NULL 1.0 0.0 0.00001254 5919 0.18776982 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInstTranche].[InstID])) 5993 9 8 Nested Loops Left Outer Join OUTER REFERENCES[tbInstTranche].[InstID]) NULL 1.0 0.0 0.00001254 5550 0.18111151 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInstIndustryGroup].[InstIndustrySectorID])) 5993 10 9 Nested Loops Left Outer Join OUTER REFERENCES[tbInstIndustryGroup].[InstIndustrySectorID]) NULL 1.0 0.0 0.00001254 5538 0.17757758 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInstIndustrySubgroup].[InstIndustryGroupID])) 5993 11 10 Nested Loops Left Outer Join OUTER REFERENCES[tbInstIndustrySubgroup].[InstIndustryGroupID]) NULL 1.0 0.0 0.00001254 5505 0.17404425 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInstCompany].[InstIndustrySubgroupID])) 5993 12 11 Nested Loops Left Outer Join OUTER REFERENCES[tbInstCompany].[InstIndustrySubgroupID]) NULL 1.0 0.0 0.00001254 5352 0.17051093 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInst].[InstCompanyID])) 5993 13 12 Nested Loops Left Outer Join OUTER REFERENCES[tbInst].[InstCompanyID]) NULL 1.0 0.0 0.00001254 5198 0.16697761 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInst].[InstCurrencyID])) 5993 14 13 Nested Loops Left Outer Join OUTER REFERENCES[tbInst].[InstCurrencyID]) NULL 1.0 0.0 0.00001254 4912 0.16344428 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | |--Bookmark Lookup(BOOKMARK[Bmk1038]), OBJECT[Proprietary].[dbo].[tbInstTranche])) 5993 15 14 Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1038]), OBJECT[Proprietary].[dbo].[tbInstTranche]) [tbInstTranche].[BBTicker] 1.0 3.1249749E-3 0.0000011 4691 0.15991096 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInst].[InstID])) 5993 16 15 Nested Loops Left Outer Join OUTER REFERENCES[tbInst].[InstID]) NULL 1.0 0.0 0.00001254 3911 0.15678489 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInst].[UnderlyerInstID])) 5993 17 16 Nested Loops Left Outer Join OUTER REFERENCES[tbInst].[UnderlyerInstID]) NULL 1.0 0.0 0.00001254 3882 0.15325154 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInstTranche].[InstID])) 5993 18 17 Nested Loops Left Outer Join OUTER REFERENCES[tbInstTranche].[InstID]) NULL 1.0 0.0 0.00001254 3127 0.14659324 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInstTranche].[InstID])) 5993 19 18 Nested Loops Left Outer Join OUTER REFERENCES[tbInstTranche].[InstID]) NULL 1.0 0.0 0.00001254 3081 0.13993493 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | | |--Bookmark Lookup(BOOKMARK[Bmk1030]), OBJECT[Proprietary].[dbo].[tbRiskManualInstOutput])) 5993 20 19 Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1030]), OBJECT[Proprietary].[dbo].[tbRiskManualInstOutput]) [tbRiskManualInstOutput].[TheoDelta], [tbRiskManualInstOutput].[TheoVega], [tbRiskManualInstOutput].[TheoRho] 1.0 3.1249749E-3 0.0000011 3034 0.13327661 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbMarkInput].[InstID])) 5993 21 20 Nested Loops Left Outer Join OUTER REFERENCES[tbMarkInput].[InstID]) NULL 1.0 0.0 0.00001254 3005 0.13015053 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | | | |--Bookmark Lookup(BOOKMARK[Bmk1028]), OBJECT[Proprietary].[dbo].[tbMarkInput])) 5993 22 21 Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1028]), OBJECT[Proprietary].[dbo].[tbMarkInput]) [tbMarkInput].[Price], [tbMarkInput].[UnderlyerPrice], [tbMarkInput].[Delta] 1.0 3.1249749E-3 0.0000011 2965 0.12349222 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInst].[InstID])) 5993 23 22 Nested Loops Left Outer Join OUTER REFERENCES[tbInst].[InstID]) NULL 1.0 0.0 0.00001254 2883 0.12036614 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | | | | |--Bookmark Lookup(BOOKMARK[Bmk1026]), OBJECT[Proprietary].[dbo].[tbMarkInput])) 5993 24 23 Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1026]), OBJECT[Proprietary].[dbo].[tbMarkInput]) [tbMarkInput].[InstID], [tbMarkInput].[Price], [tbMarkInput].[UnderlyerPrice], [tbMarkInput].[Delta], [tbMarkInput].[CreditSpread], [tbMarkInput].[Volatility], [tbMarkInput].[RecoveryRate], [tbMarkInput].[DateFrom], [tbMarkInput].[CreditRhoWeight], [tbMark 1.0 3.1249749E-3 0.0000011 2843 0.11370783 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInstTranche].[InstID])) 5993 25 24 Nested Loops Left Outer Join OUTER REFERENCES[tbInstTranche].[InstID]) NULL 1.0 0.0 0.00001254 2762 0.11058176 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | | | | | |--Bookmark Lookup(BOOKMARK[Bmk1024]), OBJECT[Proprietary].[dbo].[tbInstTranche])) 5993 26 25 Bookmark Lookup Bookmark Lookup BOOKMARK[Bmk1024]), OBJECT[Proprietary].[dbo].[tbInstTranche]) [tbInstTranche].[BBTicker] 1.0 3.1249749E-3 0.0000011 2721 0.10392345 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInst].[UnderlyerInstID])) 5993 27 26 Nested Loops Left Outer Join OUTER REFERENCES[tbInst].[UnderlyerInstID]) NULL 1.0 0.0 0.00001254 1942 0.10079737 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInst].[InstCurrencyID])) 5993 28 27 Nested Loops Left Outer Join OUTER REFERENCES[tbInst].[InstCurrencyID]) NULL 1.0 0.0 0.00001254 1914 9.7264029E-2 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInst].[InstTypeEnumID])) 5993 29 28 Nested Loops Left Outer Join OUTER REFERENCES[tbInst].[InstTypeEnumID]) NULL 1.0 0.0 0.00001254 1693 9.3730703E-2 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInst].[UnderlyerInstID])) 5993 30 29 Nested Loops Left Outer Join OUTER REFERENCES[tbInst].[UnderlyerInstID]) NULL 1.0 0.0 0.00001254 1410 9.0197384E-2 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

| | | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tbInst].[InstCurrencyID])) 5993 31 30 Nested Loops Left Outer Join OUTER REFERENCES[tbInst].[InstCurrencyID]) NULL 1.0 0.0 0.00001254 655 8.3539084E-2 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

Nested Loops(Left Outer Join, OUTER REFERENCES[tbBizStrategy].[InstRegionID])) 5993 32 31 Nested Loops Left Outer Join OUTER REFERENCES[tbBizStrategy].[InstRegionID]) NULL 1.0 0.0 0.00001254 556 8.0005758E-2 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[BizStrategyName], [tbInstRegion].[InstRegionName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[Biz NULL PLAN_ROW 0 1.0

Nested Loops(Inner Join, OUTER REFERENCES[tbInst].[InstTypeEnumID])) 5993 33 32 Nested Loops Inner Join OUTER REFERENCES[tbInst].[InstTypeEnumID]) NULL 1.0 0.0 0.00001254 402 7.6472431E-2 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[InstRegionID], [tbBizStrategy].[BizStrategyName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[BizB NULL PLAN_ROW 0 1.0

|--Sort(ORDER BY[tbInst].[SevernTicker] ASC, [tbBizStrategy].[BizStrategyName] ASC)) 5993 35 33 Sort Sort ORDER BY[tbInst].[SevernTicker] ASC, [tbBizStrategy].[BizStrategyName] ASC) NULL 1.0 1.1261261E-2 1.00239E-4 239 7.2939113E-2 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[InstRegionID], [tbBizStrategy].[BizStrategyName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[BizB NULL PLAN_ROW 0 1.0

| |--Nested Loops(Inner Join, OUTER REFERENCES[tbInstTranche].[InstID])) 5993 36 35 Nested Loops Inner Join OUTER REFERENCES[tbInstTranche].[InstID]) NULL 1.0 0.0 0.00001254 1033 6.1577611E-2 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[InstRegionID], [tbBizStrategy].[BizStrategyName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[BizB NULL PLAN_ROW 0 1.0

| |--Nested Loops(Inner Join, OUTER REFERENCES[InstTrancheID])) 5993 37 36 Nested Loops Inner Join OUTER REFERENCES[InstTrancheID]) NULL 1.0 0.0 0.00001254 616 5.4916371E-2 [Quantity], [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker], [tbBizStrategy].[InstRegionID], [tbBizStrategy].[BizStrategyName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[BizB NULL PLAN_ROW 0 1.0

| | |--Nested Loops(Inner Join, OUTER REFERENCES[tbBizSubBusinessUnit].[BizBusinessUnitID])) 5993 38 37 Nested Loops Inner Join OUTER REFERENCES[tbBizSubBusinessUnit].[BizBusinessUnitID]) NULL 1.0 0.0 0.00001254 156 4.8258074E-2 [InstTrancheID], [Quantity], [tbBizStrategy].[InstRegionID], [tbBizStrategy].[BizStrategyName], [tbBizSubBusinessUnit].[BizSubBusinessUnitName], [tbBizBusinessUnit].[BizBusinessUnitName] NULL PLAN_ROW 0 1.0

| | | |--Nested Loops(Inner Join, OUTER REFERENCES[tbBizStrategy].[BizSubBusinessUnitID])) 5993 39 38 Nested Loops Inner Join OUTER REFERENCES[tbBizStrategy].[BizSubBusinessUnitID]) NULL 1.0 0.0 0.00001254 123 4.4724748E-2 [InstTrancheID], [Quantity], [tbBizStrategy].[InstRegionID], [tbBizStrategy].[BizStrategyName], [tbBizSubBusinessUnit].[BizBusinessUnitID], [tbBizSubBusinessUnit].[BizSubBusinessUnitName] NULL PLAN_ROW 0 1.0

| | | | |--Nested Loops(Inner Join, OUTER REFERENCES[BizStrategyID])) 5993 40 39 Nested Loops Inner Join OUTER REFERENCES[BizStrategyID]) NULL 1.0 0.0 0.00001254 76 4.1191425E-2 [InstTrancheID], [Quantity], [tbBizStrategy].[InstRegionID], [tbBizStrategy].[BizSubBusinessUnitID], [tbBizStrategy].[BizStrategyName] NULL PLAN_ROW 0 1.0

| | | | | |--Table Scan(OBJECTdbo.fnPositionMarkS)) 5993 41 40 Table Scan Table Scan OBJECTdbo.fnPositionMarkS) [InstTrancheID], [BizStrategyID], [Quantity] 1.0 3.7578501E-2 7.9600002E-5 31 3.7658099E-2 [InstTrancheID], [BizStrategyID], [Quantity] NULL PLAN_ROW 0 1.0

| | | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbBizStrategy].[PK_BizStrategy]), SEEK[tbBizStrategy].[BizStrategyID]=[BizStrategyID]) ORDERED FORWARD) 5993 42 40 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbBizStrategy].[PK_BizStrategy]), SEEK[tbBizStrategy].[BizStrategyID]=[BizStrategyID]) ORDERED FORWARD [tbBizStrategy].[InstRegionID], [tbBizStrategy].[BizSubBusinessUnitID], [tbBizStrategy].[BizStrategyName] 1.0 3.2034749E-3 7.9603E-5 53 3.520784E-3 [tbBizStrategy].[InstRegionID], [tbBizStrategy].[BizSubBusinessUnitID], [tbBizStrategy].[BizStrategyName] NULL PLAN_ROW 0 3.0

| | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbBizSubBusinessUnit].[PK_BizSubBusinessUnit]), SEEK[tbBizSubBusinessUnit].[BizSubBusinessUnitID]=[tbBizStrategy].[BizSubBusinessUnitID]) ORDERED FORWARD) 5993 43 39 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbBizSubBusinessUnit].[PK_BizSubBusinessUnit]), SEEK[tbBizSubBusinessUnit].[BizSubBusinessUnitID]=[tbBizStrategy].[BizSubBusinessUnitID]) ORDERED FORWARD [tbBizSubBusinessUnit].[BizBusinessUnitID], [tbBizSubBusinessUnit].[BizSubBusinessUnitName] 1.0 3.2034749E-3 7.9603E-5 55 3.520784E-3 [tbBizSubBusinessUnit].[BizBusinessUnitID], [tbBizSubBusinessUnit].[BizSubBusinessUnitName] NULL PLAN_ROW 0 3.0

| | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbBizBusinessUnit].[PK_BizBusinessUnit]), SEEK[tbBizBusinessUnit].[BizBusinessUnitID]=[tbBizSubBusinessUnit].[BizBusinessUnitID]) ORDERED FORWARD) 5993 44 38 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbBizBusinessUnit].[PK_BizBusinessUnit]), SEEK[tbBizBusinessUnit].[BizBusinessUnitID]=[tbBizSubBusinessUnit].[BizBusinessUnitID]) ORDERED FORWARD [tbBizBusinessUnit].[BizBusinessUnitName] 1.0 3.2034749E-3 7.9603E-5 42 3.520784E-3 [tbBizBusinessUnit].[BizBusinessUnitName] NULL PLAN_ROW 0 3.0

| | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbInstTranche].[PK_InstTranche]), SEEK[tbInstTranche].[InstTrancheID]=[InstTrancheID]) ORDERED FORWARD) 5993 45 37 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbInstTranche].[PK_InstTranche]), SEEK[tbInstTranche].[InstTrancheID]=[InstTrancheID]) ORDERED FORWARD [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker] 1.0 3.2034749E-3 7.9603E-5 468 6.6457591E-3 [tbInstTranche].[InstID], [tbInstTranche].[CUSIP], [tbInstTranche].[ISIN], [tbInstTranche].[BBTicker] NULL PLAN_ROW 0 3.0

| |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbInst].[PK_Inst]), SEEK[tbInst].[InstID]=[tbInstTranche].[InstID]), WHERENOT(like([tbInst].[SevernTicker], 'cy%', NULL))) ORDERED FORWARD) 5993 46 36 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbInst].[PK_Inst]), SEEK[tbInst].[InstID]=[tbInstTranche].[InstID]), WHERENOT(like([tbInst].[SevernTicker], 'cy%', NULL))) ORDERED FORWARD [tbInst].[InstTypeEnumID], [tbInst].[InstCurrencyID], [tbInst].[UnderlyerInstID], [tbInst].[InstCompanyID], [tbInst].[TransID], [tbInst].[MarkRefCurvePointID], [tbInst].[SecurityName], [tbInst].[CommonTicker], [tbInst].[Coupon], [tbInst].[PaymentFrequency] 1.0 3.2034749E-3 7.9603E-5 427 6.6457591E-3 [tbInst].[InstTypeEnumID], [tbInst].[InstCurrencyID], [tbInst].[UnderlyerInstID], [tbInst].[InstCompanyID], [tbInst].[TransID], [tbInst].[MarkRefCurvePointID], [tbInst].[SecurityName], [tbInst].[CommonTicker], [tbInst].[Coupon], [tbInst].[PaymentFrequency] NULL PLAN_ROW 0 3.0

|--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbEnum].[PK_Enum]), SEEK[tbEnum].[EnumID]=[tbInst].[InstTypeEnumID]) ORDERED FORWARD) 5993 50 33 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbEnum].[PK_Enum]), SEEK[tbEnum].[EnumID]=[tbInst].[InstTypeEnumID]) ORDERED FORWARD [tbEnum].[EnumName] 1.0 3.2034749E-3 7.9603E-5 171 3.520784E-3 [tbEnum].[EnumName] NULL PLAN_ROW 0 3.0

Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbInstRegion].[PK_InstRegion]), SEEK[tbInstRegion].[InstRegionID]=[tbBizStrategy].[InstRegionID]) ORDERED FORWARD) 5993 51 32 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbInstRegion].[PK_InstRegion]), SEEK[tbInstRegion].[InstRegionID]=[tbBizStrategy].[InstRegionID]) ORDERED FORWARD [tbInstRegion].[InstRegionName] 1.0 3.2034749E-3 7.9603E-5 163 3.520784E-3 [tbInstRegion].[InstRegionName] NULL PLAN_ROW 0 3.0

Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbInstCurrency].[PK_InstCurrency]), SEEK[tbInstCurrency].[InstCurrencyID]=[tbInst].[InstCurrencyID]) ORDERED FORWARD) 5993 52 31 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbInstCurrency].[PK_InstCurrency]), SEEK[tbInstCurrency].[InstCurrencyID]=[tbInst].[InstCurrencyID]) ORDERED FORWARD [tbInstCurrency].[InstCurrencyName] 1.0 3.2034749E-3 7.9603E-5 108 3.520784E-3 [tbInstCurrency].[InstCurrencyName] NULL PLAN_ROW 0 3.0

| | | | | | | | | | | | | | | | | | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbInst].[PK_Inst]), SEEK[tbInst].[InstID]=[tbInst].[Un 5993 53 30 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbInst].[PK_Inst]), SEEK[tbInst].[InstID]=[tbInst].[UnderlyerInstID]) ORDERED FORWARD [tbInst].[InstTypeEnumID], [tbInst].[InstCurrencyID], [tbInst].[InstID], [tbInst].[UnderlyerInstID], [tbInst].[ConversionRatio], [tbInst].[PriceFactor] 1.0 3.2034749E-3 7.9603E-5 765 6.6457591E-3 [tbInst].[InstTypeEnumID], [tbInst].[InstCurrencyID], [tbInst].[InstID], [tbInst].[UnderlyerInstID], [tbInst].[ConversionRatio], [tbInst].[PriceFactor] NULL PLAN_ROW 0 3.0

| | | | | | | | | | | | | | | | | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbEnum].[PK_Enum]), SEEK[tbEnum].[EnumID]=[tbInst].[InstTyp 5993 54 29 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbEnum].[PK_Enum]), SEEK[tbEnum].[EnumID]=[tbInst].[InstTypeEnumID]) ORDERED FORWARD [tbEnum].[EnumName] 1.0 3.2034749E-3 7.9603E-5 291 3.520784E-3 [tbEnum].[EnumName] NULL PLAN_ROW 0 3.0

| | | | | | | | | | | | | | | | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbInstCurrency].[PK_InstCurrency]), SEEK[tbInstCurrency].[InstCu 5993 55 28 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbInstCurrency].[PK_InstCurrency]), SEEK[tbInstCurrency].[InstCurrencyID]=[tbInst].[InstCurrencyID]) ORDERED FORWARD [tbInstCurrency].[InstCurrencyName] 1.0 3.2034749E-3 7.9603E-5 230 3.520784E-3 [tbInstCurrency].[InstCurrencyName] NULL PLAN_ROW 0 3.0

| | | | | | | | | | | | | | | | | |--Index Seek(OBJECT[Proprietary].[dbo].[tbInstTranche].[UQ_InstTranche]), SEEK[tbInstTranche].[InstID]=[tbInst].[Underl 5993 56 27 Index Seek Index Seek OBJECT[Proprietary].[dbo].[tbInstTranche].[UQ_InstTranche]), SEEK[tbInstTranche].[InstID]=[tbInst].[UnderlyerInstID] AND [tbInstTranche].[InstTrancheTypeEnumID]=[dbo].[fnEnumS]('InstTrancheType', '')) ORDERED FORWARD [Bmk1024] 1.0 3.2034749E-3 7.9609003E-5 37 3.5208019E-3 [Bmk1024] NULL PLAN_ROW 0 3.0

| | | | | | | | | | | | | | | | |--Index Seek(OBJECT[Proprietary].[dbo].[tbMarkInput].[UQ_MarkInput2]), SEEK[tbMarkInput].[InstID]=[tbInstTranche].[InstID] AND [t 5993 57 25 Index Seek Index Seek OBJECT[Proprietary].[dbo].[tbMarkInput].[UQ_MarkInput2]), SEEK[tbMarkInput].[InstID]=[tbInstTranche].[InstID] AND [tbMarkInput].[DateThru]=NULL) ORDERED FORWARD [Bmk1026] 1.0 3.2034749E-3 7.9607002E-5 49 6.6457712E-3 [Bmk1026] NULL PLAN_ROW 0 3.0

| | | | | | | | | | | | | | | |--Index Seek(OBJECT[Proprietary].[dbo].[tbMarkInput].[UQ_MarkInput2]), SEEK[tbMarkInput].[InstID]=[tbInst].[InstID] AND [tbMarkInput].[Date 5993 58 23 Index Seek Index Seek OBJECT[Proprietary].[dbo].[tbMarkInput].[UQ_MarkInput2]), SEEK[tbMarkInput].[InstID]=[tbInst].[InstID] AND [tbMarkInput].[DateThru]=NULL) ORDERED FORWARD [Bmk1028] 1.0 3.2034749E-3 7.9607002E-5 49 6.6457712E-3 [Bmk1028] NULL PLAN_ROW 0 3.0

| | | | | | | | | | | | | | |--Index Seek(OBJECT[Proprietary].[dbo].[tbRiskManualInstOutput].[UQ_RiskManualInstOutput2]), SEEK[tbRiskManualInstOutput].[InstID]=[tbMarkInput].[Ins 5993 59 21 Index Seek Index Seek OBJECT[Proprietary].[dbo].[tbRiskManualInstOutput].[UQ_RiskManualInstOutput2]), SEEK[tbRiskManualInstOutput].[InstID]=[tbMarkInput].[InstID] AND [tbRiskManualInstOutput].[DateThru]=NULL) ORDERED FORWARD [Bmk1030] 1.0 3.2034749E-3 7.9607002E-5 49 6.6457712E-3 [Bmk1030] NULL PLAN_ROW 0 3.0

| | | | | | | | | | | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbMark].[PK_Mark]), SEEK[tbMark].[DatasetID]=[dbo].[fnLatestDatasetS]('MEOD', getdate()-'Jan 2 1900 12:00AM' 5993 60 19 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbMark].[PK_Mark]), SEEK[tbMark].[DatasetID]=[dbo].[fnLatestDatasetS]('MEOD', getdate()-'Jan 2 1900 12:00AM', 'P') AND [tbMark].[InstID]=[tbInstTranche].[InstID]) ORDERED FORWARD [tbMark].[Price], [tbMark].[UnderlyerPrice] 1.0 3.2034749E-3 7.9609999E-5 55 6.6457801E-3 [tbMark].[Price], [tbMark].[UnderlyerPrice] NULL PLAN_ROW 0 3.0

| | | | | | | | | | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbMark].[PK_Mark]), SEEK[tbMark].[DatasetID]=[dbo].[fnLatestDatasetS]('MEOD', getdate()-'Jan 8 1900 12:00AM', 'P' 5993 61 18 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbMark].[PK_Mark]), SEEK[tbMark].[DatasetID]=[dbo].[fnLatestDatasetS]('MEOD', getdate()-'Jan 8 1900 12:00AM', 'P') AND [tbMark].[InstID]=[tbInstTranche].[InstID]) ORDERED FORWARD [tbMark].[Price], [tbMark].[UnderlyerPrice] 1.0 3.2034749E-3 7.9609999E-5 55 6.6457801E-3 [tbMark].[Price], [tbMark].[UnderlyerPrice] NULL PLAN_ROW 0 3.0

| | | | | | | | | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbInst].[PK_Inst]), SEEK[tbInst].[InstID]=[tbInst].[UnderlyerInstID]) ORDERED FORWARD) 5993 62 17 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbInst].[PK_Inst]), SEEK[tbInst].[InstID]=[tbInst].[UnderlyerInstID]) ORDERED FORWARD [tbInst].[InstID], [tbInst].[InstCurrencyID] 1.0 3.2034749E-3 7.9603E-5 765 6.6457591E-3 [tbInst].[InstID], [tbInst].[InstCurrencyID] NULL PLAN_ROW 0 3.0

| | | | | | | | | | |--Index Seek(OBJECT[Proprietary].[dbo].[tbInstTranche].[UQ_InstTranche]), SEEK[tbInstTranche].[InstID]=[tbInst].[InstID] AND [tbInstTranche].[InstTrancheTypeEnumID]=[dbo].[fn 5993 63 16 Index Seek Index Seek OBJECT[Proprietary].[dbo].[tbInstTranche].[UQ_InstTranche]), SEEK[tbInstTranche].[InstID]=[tbInst].[InstID] AND [tbInstTranche].[InstTrancheTypeEnumID]=[dbo].[fnEnumS]('InstTrancheType', '')) ORDERED FORWARD [Bmk1038] 1.0 3.2034749E-3 7.9609003E-5 37 3.5208019E-3 [Bmk1038] NULL PLAN_ROW 0 3.0

| | | | | | | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbInstCurrency].[PK_InstCurrency]), SEEK[tbInstCurrency].[InstCurrencyID]=[tbInst].[InstCurrencyID]) ORDERED FORWARD) 5993 64 14 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbInstCurrency].[PK_InstCurrency]), SEEK[tbInstCurrency].[InstCurrencyID]=[tbInst].[InstCurrencyID]) ORDERED FORWARD [tbInstCurrency].[InstCurrencyName] 1.0 3.2034749E-3 7.9603E-5 230 3.520784E-3 [tbInstCurrency].[InstCurrencyName] NULL PLAN_ROW 0 3.0

| | | | | | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbInstCompany].[PK_InstCompany]), SEEK[tbInstCompany].[InstCompanyID]=[tbInst].[InstCompanyID]) ORDERED FORWARD) 5993 65 13 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbInstCompany].[PK_InstCompany]), SEEK[tbInstCompany].[InstCompanyID]=[tbInst].[InstCompanyID]) ORDERED FORWARD [tbInstCompany].[InstIndustrySubgroupID] 1.0 3.2034749E-3 7.9603E-5 295 3.520784E-3 [tbInstCompany].[InstIndustrySubgroupID] NULL PLAN_ROW 0 3.0

| | | | | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbInstIndustrySubgroup].[PK_InstIndustrySubgroup]), SEEK[tbInstIndustrySubgroup].[InstIndustrySubgroupID]=[tbInstCompany].[InstIndustrySubgroupI 5993 66 12 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbInstIndustrySubgroup].[PK_InstIndustrySubgroup]), SEEK[tbInstIndustrySubgroup].[InstIndustrySubgroupID]=[tbInstCompany].[InstIndustrySubgroupID]) ORDERED FORWARD [tbInstIndustrySubgroup].[InstIndustryGroupID] 1.0 3.2034749E-3 7.9603E-5 162 3.520784E-3 [tbInstIndustrySubgroup].[InstIndustryGroupID] NULL PLAN_ROW 0 3.0

| | | | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbInstIndustryGroup].[PK_InstIndustryGroup]), SEEK[tbInstIndustryGroup].[InstIndustryGroupID]=[tbInstIndustrySubgroup].[InstIndustryGroupID]) ORDERED 5993 67 11 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbInstIndustryGroup].[PK_InstIndustryGroup]), SEEK[tbInstIndustryGroup].[InstIndustryGroupID]=[tbInstIndustrySubgroup].[InstIndustryGroupID]) ORDERED FORWARD [tbInstIndustryGroup].[InstIndustrySectorID] 1.0 3.2034749E-3 7.9603E-5 162 3.520784E-3 [tbInstIndustryGroup].[InstIndustrySectorID] NULL PLAN_ROW 0 3.0

| | | | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbInstIndustrySector].[PK_InstIndustrySector]), SEEK[tbInstIndustrySector].[InstIndustrySectorID]=[tbInstIndustryGroup].[InstIndustrySectorID]) ORDERED FO 5993 68 10 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbInstIndustrySector].[PK_InstIndustrySector]), SEEK[tbInstIndustrySector].[InstIndustrySectorID]=[tbInstIndustryGroup].[InstIndustrySectorID]) ORDERED FORWARD [tbInstIndustrySector].[InstIndustrySectorName] 1.0 3.2034749E-3 7.9603E-5 41 3.520784E-3 [tbInstIndustrySector].[InstIndustrySectorName] NULL PLAN_ROW 0 3.0

| | | | |--Top(1) 5993 70 9 Top Top TIE COLUMNS[tbInstPut].[PutDate]) NULL 1.0 0.0 0.0000001 41 3.521099E-3 [tbInstPut].[PutDate] NULL PLAN_ROW 0 3.0

| | | | |--Index Seek(OBJECT[Proprietary].[dbo].[tbInstPut].[IX_InstPutInstIDPutDate]), SEEK[tbInstPut].[InstID]=[tbInstTranche].[InstID] AND [tbInstPut].[PutDate] >= [dbo].[fnUtilTruncTime](Convert(getdate()))) O 5993 71 70 Index Seek Index Seek OBJECT[Proprietary].[dbo].[tbInstPut].[IX_InstPutInstIDPutDate]), SEEK[tbInstPut].[InstID]=[tbInstTranche].[InstID] AND [tbInstPut].[PutDate] >= [dbo].[fnUtilTruncTime](Convert(getdate()))) ORDERED FORWARD [tbInstPut].[PutDate] 1.0 3.2034749E-3 7.9607998E-5 41 3.5207991E-3 [tbInstPut].[PutDate] NULL PLAN_ROW 0 3.0

| | | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbTrans].[PK_Trans]), SEEK[tbTrans].[TransID]=[tbInst].[TransID]) ORDERED FORWARD) 5993 73 8 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbTrans].[PK_Trans]), SEEK[tbTrans].[TransID]=[tbInst].[TransID]) ORDERED FORWARD [tbTrans].[TransBrokerID] 1.0 3.2034749E-3 7.9603E-5 378 6.6457591E-3 [tbTrans].[TransBrokerID] NULL PLAN_ROW 0 3.0

| | |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbTransBroker].[PK_TransBroker]), SEEK[tbTransBroker].[TransBrokerID]=[tbTrans].[TransBrokerID]) ORDERED FORWARD) 5993 74 7 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbTransBroker].[PK_TransBroker]), SEEK[tbTransBroker].[TransBrokerID]=[tbTrans].[TransBrokerID]) ORDERED FORWARD [tbTransBroker].[TransBrokerCounterpartyID] 1.0 3.2034749E-3 7.9603E-5 162 3.520784E-3 [tbTransBroker].[TransBrokerCounterpartyID] NULL PLAN_ROW 0 3.0

| |--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbTransCounterparty].[PK_TransCounterparty]), SEEK[tbTransCounterparty].[TransCounterpartyID]=[tbTransBroker].[TransBrokerCounterpartyID]) ORDERED FORWARD) 5993 75 6 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbTransCounterparty].[PK_TransCounterparty]), SEEK[tbTransCounterparty].[TransCounterpartyID]=[tbTransBroker].[TransBrokerCounterpartyID]) ORDERED FORWARD [tbTransCounterparty].[TransCounterpartyName] 1.0 3.2034749E-3 7.9603E-5 34 3.520784E-3 [tbTransCounterparty].[TransCounterpartyName] NULL PLAN_ROW 0 3.0

|--Clustered Index Seek(OBJECT[Proprietary].[dbo].[tbCurvePoint].[PK_CurvePoint]), SEEK[tbCurvePoint].[CurvePointID]=[tbInst].[MarkRefCurvePointID]) ORDERED FORWARD) 5993 76 5 Clustered Index Seek Clustered Index Seek OBJECT[Proprietary].[dbo].[tbCurvePoint].[PK_CurvePoint]), SEEK[tbCurvePoint].[CurvePointID]=[tbInst].[MarkRefCurvePointID]) ORDERED FORWARD [tbCurvePoint].[BBTicker] 1.0 3.2034749E-3 7.9603E-5 69 3.520784E-3 [tbCurvePoint].[BBTicker] NULL PLAN_ROW 0 3.0

(70 row(s) affected)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

I don't see anything out of the ordinary... but then again it's hard to see anything in this context

However there see to have a few bookmark lookups that might slow this thing down a wee bit, and also the scan on the fonction.

How about you rerun the query with

SET STATISTICS IO ON (this might expose easier which table is causing you grief trying to read through this plan.)