SQL Server Performance

Query using 100% CPU

Discussion in 'Performance Tuning for DBAs' started by BoltonDBA, Sep 2, 2005.

  1. BoltonDBA New Member

    Hi<br /><br />I've been wrestling with this query for a couple of days and need a fresh perspective [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />This query uses up 100% cpu whenever its run on the production server, but is fine (about 10% on the development server). Saying that, devept isnt running much else but the Prod server normally runs at about 6 - 15% CPU usage.<br /><br />NB: It runs in about 11 seconds. <br /><br />Query below:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />INSERT INTO st.dbo.ProductDiscount WITH (ROWLOCK) (DiscountId, ShortCode, Price, OriginalPrice, LastUpdated)<br />SELECT DISTINCT sd.DiscountId, sp.ShortCode, (sda.DiscountValue* sl.ExchangeRate), sp.SellPrice, Getdate()<br />FROM st.dbo.StoreGroupProduct ssgp WITH (NOLOCK)<br />INNER JOIN st.dbo.Product sp WITH (NOLOCK) ON ssgp.ShortCode = sp.ShortCode<br />INNER JOIN st.dbo.Discount sd WITH (NOLOCK) ON ssgp.StoreGroupId = sd.StoreGroupId AND sd.Pattern = '0000000000'<br />INNER JOIN st.dbo.DiscountAccount sda WITH (NOLOCK) ON sd.Pattern = sda.Mask AND sda.AccountTypeValue = sd.AccountTypeValue AND sd.DiscountTypeId = 2<br />INNER JOIN gl.dbo.Account ga WITH (NOLOCK) ON sda.SalesId = ga.SalesId <br />INNER JOIN st.dbo.StoreGroup ssg WITH (NOLOCK) ON ga.StoreGroupId = ssg.StoreGroupId<br />INNER JOIN st.dbo.Locale sl WITH (NOLOCK) ON ssg.Locale = sl.Locale <br />LEFT OUTER JOIN st.dbo.ProductDiscount spd WITH (NOLOCK) ON sd.DiscountId = spd.DiscountId AND sp.ShortCode = spd.ShortCode<br />WHERE spd.DiscountId IS NULL AND sda.DiscountType = 'S'<br /></font id="code"></pre id="code"><br /><br />My execution plan:<br /><pre id="code"><font face="courier" size="2" id="code"><br />INSERT INTO st.dbo.ProductDiscount WITH (ROWLOCK) (DiscountId, ShortCode, Price, OriginalPrice, LastUpdated) SELECT DISTINCT sd.DiscountId, sp.ShortCode, (sda.DiscountValue* sl.ExchangeRate), sp.SellPrice, Getdate() FROM st.db<br /> |--Clustered Index Insert(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[ProductDiscount].[PK_ProductDiscount]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ProductDiscount].[LastUpdated]=[Expr1009], [ProductDiscount].[OriginalPrice]=[sp].[SellPrice], [ProductDiscount].[PriceAdjusted]=, [ProductDiscou<br /> |--Table Spool<br /> |--Top(ROWCOUNT est 0)<br /> |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1008]=Convert([Expr1012]), [Expr1009]=Convert([Expr1014])))<br /> |--Sort(DISTINCT ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId] ASC, [sp].[ShortCode] ASC, [Expr1012] ASC, [Expr1014] ASC))<br /> |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1012]=[sda].[DiscountValue]*[sl].[ExchangeRate], [Expr1014]=getdate()))<br /> |--Parallelism(Gather Streams)<br /> |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssg].[Locale]))<br /> |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ga].[StoreGroupId]))<br /> | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[SalesId]))<br /> | | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1003]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[DiscountAccount] AS [sda]))<br /> | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[AccountTypeValue]))<br /> | | | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[spd].[DiscountId]=))<br /> | | | | |--Hash Match(Right Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[spd].[DiscountId], [spd].[ShortCode])=([sd].[DiscountId], [sp].[ShortCode]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId]=[spd].[DiscountId] AND [sp].[ShortCode]=[spd]<br /> | | | | |--Parallelism(Repartition Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[spd].[DiscountId], [spd].[ShortCode]))<br /> | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[ProductDiscount].[PK_ProductDiscount] AS [spd]))<br /> | | | | |--Parallelism(Repartition Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId], [sp].[ShortCode]))<br /> | | | | |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[StoreGroupId])=([ssgp].[StoreGroupId]))<br /> | | | | |--Parallelism(Broadcast)<br /> | | | | | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1002]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Discount] AS [sd]))<br /> | | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Discount].[IX_Discount_1] AS [sd]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[Pattern]='0000000000'), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountTypeId]=2) ORDERED FORW<br /> | | | | |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssgp].[ShortCode])=([sp].[ShortCode]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssgp].[ShortCode]=[sp].[ShortCode]))<br /> | | | | |--Parallelism(Repartition Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssgp].[ShortCode]))<br /> | | | | | |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[StoreGroupProduct].[PK_StoreGroupProduct] AS [ssgp]))<br /> | | | | |--Parallelism(Repartition Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sp].[ShortCode]))<br /> | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Product].[PK_Product] AS [sp]))<br /> | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[DiscountAccount].[IX_DiscountAccount] AS [sda]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[AccountTypeValue]=[sd].[AccountTypeValue]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[DiscountType]='S' AND [sda<br /> | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[gl].[dbo].[Account].[PK_Account] AS [ga]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ga].[SalesId]=[sda].[SalesId]) ORDERED FORWARD)<br /> | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[StoreGroup].[PK_StoreGroup] AS [ssg]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssg].[StoreGroupID]=[ga].[StoreGroupId]) ORDERED FORWARD)<br /> |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Locale].[PK_Locale] AS [sl]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sl].[Locale]=[ssg].[Locale]) ORDERED FORWARD)<br /></font id="code"></pre id="code"><br /><br />What have you done today to make you feel proud ? - Heather Small
  2. mmarovic Active Member

    Can you post index definitions at least on Discount and DiscountAccount tables? How many rows do you have there? How many rows there meets the criteria:
    sda.DiscountType = 'S'
    and
    sd.DiscountTypeId = 2 AND sd.Pattern = '0000000000'
  3. BoltonDBA New Member

    Hi,

    I've posted these below, thanks very much for looking at this:



    select count(*) from discountaccount sda where sda.discounttype = 'S' - 336 rows
    select count(*) from discountaccount sda inner join discount sd on sd.Pattern = sda.Mask AND sda.AccountTypeValue = sd.AccountTypeValue AND sd.DiscountTypeId = 2 where sd.DiscountTypeId = 2 AND sd.Pattern = '0000000000' - 185 rows
    select count(*) from discountaccount sda inner join discount sd on sd.Pattern = sda.Mask AND sda.AccountTypeValue = sd.AccountTypeValue AND sd.DiscountTypeId = 2
    where sda.DiscountType = 'S'
    and
    (sd.DiscountTypeId = 2 AND sd.Pattern = '0000000000') = 0 rows

    and the index definitions



    TableNameIndexNamedpagesusedrowcnt
    DiscountPK_DiscountDiscountId313212
    DiscountIX_DiscountStoreGroupIdDiscountTypeIdReferralCodePatternAccountTypeValue24212
    DiscountIX_Discount_1PatternAccountTypeValueDiscountTypeId24212
    DiscountAccountPK_DiscountAccountSalesIdMaskDiscountType13291675
    DiscountAccountIX_DiscountAccountAccountTypeValue681675
    DiscountAccountIX_DiscountAccount_1Mask461675
    ProductPK_ProductShortCode2590346592002
    ProductIX_ProductCatID25225492002
    ProductIX_Product_1MfrID20921192002
    ProductIX_Product_2ProductOwner39139692002
    ProductDiscountPK_ProductDiscountDiscountIdShortCode878883117179



    What have you done today to make you feel proud ? - Heather Small
  4. mmarovic Active Member

    Are you sure that:
    select count(*) from discountaccount sda inner join discount sd on sd.Pattern = sda.Mask AND sda.AccountTypeValue = sd.AccountTypeValue AND sd.DiscountTypeId = 2where sda.DiscountType = 'S'and(sd.DiscountTypeId = 2 AND sd.Pattern = '0000000000')
    returns zero? In that case your original query should return no row too, if i haven't missed something.

    Can you also run:
    Select count(*)
    from discountaccount
    where DiscountTypeId = 2 AND Pattern = '0000000000'
  5. mmarovic Active Member

    Sorry, I mean:
    Select count(*)
    from discountaccount
    where DiscountType = 'S' AND Mask = '0000000000'
  6. mmarovic Active Member

    If you add discountType as a second column of ix_discountAccount_1 query will be slightly faster but it will not solve your problem.
  7. mmarovic Active Member

    See if this helps:
    INSERT INTO st.dbo.ProductDiscount WITH (ROWLOCK) (DiscountId, ShortCode, Price, OriginalPrice, LastUpdated)
    SELECT DISTINCT sd.DiscountId, sp.ShortCode, sda.DiscountValue * sl.ExchangeRate, sp.SellPrice, Getdate()
    FROM st.dbo.DiscountAccount sda WITH (NOLOCK)
    JOIN st.dbo.Discount sdWITH (NOLOCK) ON sd.Pattern = sda.Mask AND sda.AccountTypeValue = sd.AccountTypeValue
    inner loop JOIN st.dbo.StoreGroupProduct ssgpWITH (NOLOCK) ON ssgp.StoreGroupId = sd.StoreGroupId
    inner loop JOIN st.dbo.Product sp WITH (NOLOCK) ON ssgp.ShortCode = sp.ShortCode
    JOIN gl.dbo.Account ga WITH (NOLOCK) ON sda.SalesId = ga.SalesId
    JOIN st.dbo.StoreGroup ssg WITH (NOLOCK) ON ga.StoreGroupId = ssg.StoreGroupId
    JOIN st.dbo.Locale sl WITH (NOLOCK) ON ssg.Locale = sl.Locale
    LEFT JOIN st.dbo.ProductDiscount spd WITH (NOLOCK) ON sd.DiscountId = spd.DiscountId AND sp.ShortCode = spd.ShortCode
    WHERE spd.DiscountId IS NULL AND
    sda.DiscountType = 'S' AND
    sd.DiscountTypeId = 2 AND
    sd.Pattern = '0000000000' and
    sda.Mask = '0000000000'
    option (force order)
    Btw, can you avoid SELECT DISTINCT?
  8. BoltonDBA New Member

    Sorry, should have mentioned it returns 0 now because I've populated the table, in normal situation, it would return approx 92,000 records BUT I will delete the entry in the destination table and get the actual figure. <br /><br />Though it has no records to insert, the query still runs at 100%. I put the distinct in to avoid duplication but I'll see if I can drop it out with no effects. Will have to log into work to try your suggestions, but will do so ASAP. Many thanks for your help so far. I will run the above with the addition to the index you've suggested and post back <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />What have you done today to make you feel proud ? - Heather Small
  9. BoltonDBA New Member

    Good morning

    I was unable to re-connect till this morning, apologies for the delay. I have tried the alterations but they are still causing the 100% CPU spike and now taking 5mins to run. I have re-sampled the data and when a new record is created that is a type 'S', about 74000 records will be added to the destination table. Otherwise, when a new product is added, about 46 a day, these will be added to the destination table. So quite a wide range of records. I am currently looking to fine-tune this and possible drop some records out. Will let you know if that works.

    What have you done today to make you feel proud ? - Heather Small
  10. mmarovic Active Member

    Ok, it looks like that in this case parallelism really helps and that the range of records after selection is narrowed based on the condition in where clause is to wide for nested loop to be effective.
    I'm afraid without access to your sample and structure I am not capable of finding better solution. Especially when parallelism is involved.

  11. mmarovic Active Member

    Can you please post execution plan for query I posted, I doubt it is not one I expected?
  12. BoltonDBA New Member

    Hello<br /><br />I've re-run the query - 3mins this time but still using 100% CPU and the execution plan is below:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />INSERT INTO st.dbo.ProductDiscount WITH (ROWLOCK) (DiscountId, ShortCode, Price, OriginalPrice, LastUpdated) SELECT DISTINCT sd.DiscountId, sp.ShortCode, sda.DiscountValue * sl.ExchangeRate, sp.SellPrice, Getdate() FROM DabsDataStagi<br /> |--Clustered Index Insert(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[ProductDiscount].[PK_ProductDiscount]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ProductDiscount].[LastUpdated]=[Expr1009], [ProductDiscount].[OriginalPrice]=[sp].[SellPrice], [ProductDiscount].[PriceAdjusted]=NULL, [ProductDiscou<br /> |--Table Spool<br /> |--Top(ROWCOUNT est 0)<br /> |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1008]=Convert([Expr1012]), [Expr1009]=Convert([Expr1014])))<br /> |--Sort(DISTINCT ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId] ASC, [sp].[ShortCode] ASC, [Expr1012] ASC, [Expr1014] ASC))<br /> |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1012]=[sda].[DiscountValue]*[sl].[ExchangeRate], [Expr1014]=getdate()))<br /> |--Parallelism(Gather Streams)<br /> |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssg].[Locale]))<br /> |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ga].[StoreGroupId]))<br /> | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[SalesId]))<br /> | | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[spd].[DiscountId]=NULL))<br /> | | | |--Hash Match(Left Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId], [sp].[ShortCode])=([spd].[DiscountId], [spd].[ShortCode]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId]=[spd].[DiscountId] AND [sp].[ShortCode]=[spd].[ShortCode<br /> | | | |--Parallelism(Repartition Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId], [sp].[ShortCode]))<br /> | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssgp].[ShortCode]) WITH PREFETCH)<br /> | | | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssgp].[ShortCode] ASC))<br /> | | | | | |--Nested Loops(Inner Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssgp].[StoreGroupId]=[sd].[StoreGroupId]))<br /> | | | | | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1001]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Discount] AS [sd]))<br /> | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[AccountTypeValue]))<br /> | | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[DiscountAccount].[PK_DiscountAccount] AS [sda]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[DiscountType]='C' AND [sda].[Mask]='00000000<br /> | | | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Discount].[IX_Discount_1] AS [sd]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[Pattern]='0000000000' AND [sd].[AccountTypeValue]=[sda].[AccountTypeV<br /> | | | | | |--Table Spool<br /> | | | | | |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[StoreGroupProduct].[PK_StoreGroupProduct] AS [ssgp]))<br /> | | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Product].[PK_Product] AS [sp]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sp].[ShortCode]=[ssgp].[ShortCode]) ORDERED FORWARD)<br /> | | | |--Parallelism(Repartition Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[spd].[DiscountId], [spd].[ShortCode]))<br /> | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[ProductDiscount].[PK_ProductDiscount] AS [spd]))<br /> | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[gl].[dbo].[Account].[PK_Account] AS [ga]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ga].[SalesId]=[sda].[SalesId]) ORDERED FORWARD)<br /> | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[StoreGroup].[PK_StoreGroup] AS [ssg]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssg].[StoreGroupID]=[ga].[StoreGroupId]) ORDERED FORWARD)<br /> |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Locale].[PK_Locale] AS [sl]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sl].[Locale]=[ssg].[Locale]) ORDERED FORWARD)<br /><br /></font id="code"></pre id="code"><br /><br />What have you done today to make you feel proud ? - Heather Small
  13. BoltonDBA New Member

    Also without distinct 1:31mins<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />RowsExecutesStmt<br />01INSERT INTO st.dbo.ProductDiscount WITH (ROWLOCK) (DiscountId, ShortCode, Price, OriginalPrice, LastUpdated) SELECT sd.DiscountId, sp.ShortCode, sda.DiscountValue * sl.ExchangeRate, sp.SellPrice, Getdate() FROM st.dbo.D<br />01 |--Clustered Index Insert(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[ProductDiscount].[PK_ProductDiscount]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ProductDiscount].[LastUpdated]=Convert(getdate()), [ProductDiscount].[OriginalPrice]=[sp].[SellPrice], [ProductDiscount].[PriceAdjusted]=NULL, [Produ<br />01 |--Top(ROWCOUNT est 0)<br />01 |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1008]=Convert([sda].[DiscountValue]*[sl].[ExchangeRate]), [ConstExpr1013]=Convert(getdate())))<br />01 |--Parallelism(Gather Streams)<br />04 |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssg].[Locale]))<br />04 |--Table Spool<br />04 | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ga].[StoreGroupId]))<br />04 | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[SalesId]))<br />04 | | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[spd].[DiscountId]=NULL))<br />80682004 | | | |--Hash Match(Left Outer Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId], [sp].[ShortCode])=([spd].[DiscountId], [spd].[ShortCode]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId]=[spd].[DiscountId] AND [sp].[ShortCode]=[spd].[ShortCode]))<br />80682004 | | | |--Parallelism(Repartition Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId], [sp].[ShortCode]))<br />80682004 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssgp].[ShortCode]) WITH PREFETCH)<br />80682004 | | | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssgp].[ShortCode] ASC))<br />80682004 | | | | | |--Nested Loops(Inner Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssgp].[StoreGroupId]=[sd].[StoreGroupId]))<br />1754 | | | | | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1001]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Discount] AS [sd]))<br />1754 | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[AccountTypeValue]))<br />1754 | | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[DiscountAccount].[PK_DiscountAccount] AS [sda]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[DiscountType]='C' AND [sda].[Mask]='0000000000'))<br />175175 | | | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Discount].[IX_Discount_1] AS [sd]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[Pattern]='0000000000' AND [sd].[AccountTypeValue]=[sda].[AccountTypeValue] AND <br />9890825175 | | | | | |--Table Spool<br />2260764 | | | | | |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[StoreGroupProduct].[PK_StoreGroupProduct] AS [ssgp]))<br />80682008068200 | | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Product].[PK_Product] AS [sp]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sp].[ShortCode]=[ssgp].[ShortCode]) ORDERED FORWARD)<br />1232174 | | | |--Parallelism(Repartition Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[spd].[DiscountId], [spd].[ShortCode]))<br />1232174 | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[ProductDiscount].[PK_ProductDiscount] AS [spd]))<br />00 | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[gl].[dbo].[Account].[PK_Account] AS [ga]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ga].[SalesId]=[sda].[SalesId]) ORDERED FORWARD)<br />00 | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[StoreGroup].[PK_StoreGroup] AS [ssg]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssg].[StoreGroupID]=[ga].[StoreGroupId]) ORDERED FORWARD)<br />00 |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Locale].[PK_Locale] AS [sl]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sl].[Locale]=[ssg].[Locale]) ORDERED FORWARD)<br /><br /></font id="code"></pre id="code"><br /><br />What have you done today to make you feel proud ? - Heather Small
  14. mmarovic Active Member

    You have condition discountType = 'C' here instead of discountType = 'S'. Can yuo run exactly the same query please?
  15. BoltonDBA New Member

    Hi<br /><br />Thats worked now thanks. Though it has no records at present. I will find out what a typical sample of records for 'S' is and attempt to get condition 'C' working as well. Many thanks again.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />RowsExecutesStmt<br />01INSERT INTO st.dbo.ProductDiscount WITH (ROWLOCK) (DiscountId, ShortCode, Price, OriginalPrice, LastUpdated) SELECT sd.DiscountId, sp.ShortCode, sda.DiscountValue * sl.ExchangeRate, sp.SellPrice, Getdate() FROM st.dbo.D<br />01 |--Clustered Index Insert(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[ProductDiscount].[PK_ProductDiscount]), SET<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ProductDiscount].[LastUpdated]=[ConstExpr1013], [ProductDiscount].[OriginalPrice]=[sp].[SellPrice], [ProductDiscount].[PriceAdjusted]=NULL, [ProductD<br />01 |--Top(ROWCOUNT est 0)<br />01 |--Parallelism(Gather Streams, ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId] ASC, [sp].[ShortCode] ASC))<br />04 |--Table Spool<br />04 |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId] ASC, [sp].[ShortCode] ASC))<br />04 |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1008]=Convert([sda].[DiscountValue]*[sl].[ExchangeRate]), [ConstExpr1013]=Convert(getdate())))<br />04 |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssg].[Locale])=([sl].[Locale]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sl].[Locale]=[ssg].[Locale]))<br />04 |--Parallelism(Repartition Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssg].[Locale]))<br />04 | |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ga].[StoreGroupId])=([ssg].[StoreGroupID]))<br />04 | |--Parallelism(Repartition Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ga].[StoreGroupId]))<br />04 | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[SalesId]) WITH PREFETCH)<br />04 | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[SalesId] ASC))<br />04 | | | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[spd].[DiscountId]=NULL))<br />04 | | | |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId], [sp].[ShortCode])=([spd].[DiscountId], [spd].[ShortCode]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId]=[spd].[DiscountId] AND [sp].[Qui<br />04 | | | |--Parallelism(Repartition Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId], [ssgp].[ShortCode]), ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId] ASC, [ssgp].[ShortCode] ASC))<br />04 | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssgp].[ShortCode]) WITH PREFETCH)<br />04 | | | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[DiscountId] ASC, [ssgp].[ShortCode] ASC))<br />04 | | | | | |--Nested Loops(Inner Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssgp].[StoreGroupId]=[sd].[StoreGroupId]))<br />04 | | | | | |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1001]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Discount] AS [sd]))<br />04 | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[AccountTypeValue]))<br />04 | | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[DiscountAccount].[PK_DiscountAccount] AS [sda]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sda].[DiscountType]='S' AND [sda].[Mask]<br />00 | | | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Discount].[IX_Discount_1] AS [sd]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sd].[Pattern]='0000000000' AND [sd].[AccountTypeValue]=[sda].[Ac<br />00 | | | | | |--Table Spool<br />00 | | | | | |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[StoreGroupProduct].[PK_StoreGroupProduct] AS [ssgp]))<br />00 | | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Product].[PK_Product] AS [sp]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sp].[ShortCode]=[ssgp].[ShortCode]) ORDERED FORWARD)<br />44 | | | |--Parallelism(Repartition Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[spd].[DiscountId], [spd].[ShortCode]), ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[spd].[DiscountId] ASC, [spd].[ShortCode] ASC))<br />540994 | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[ProductDiscount].[PK_ProductDiscount] AS [spd]), ORDERED FORWARD)<br />00 | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[gl].[dbo].[Account].[PK_Account] AS [ga]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ga].[SalesId]=[sda].[SalesId]) ORDERED FORWARD)<br />04 | |--Parallelism(Distribute Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ssg].[StoreGroupID]))<br />21 | |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[StoreGroup].[IX_StoreGroup_1] AS [ssg]))<br />04 |--Parallelism(Distribute Streams, PARTITION COLUMNS<img src='/community/emoticons/emotion-6.gif' alt=':(' />[sl].[Locale]))<br />21 |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[st].[dbo].[Locale].[PK_Locale] AS [sl]))<br /><br /></font id="code"></pre id="code"><br /><br />What have you done today to make you feel proud ? - Heather Small
  16. mmarovic Active Member

    Ah, that's the reason, ok. I'll look at it later today, but I don't know how much I can help. Maybe you can add maxdop = 1 to my query to see how does non-parallel execution plan work. How much time the original query takes with condition DiscountType = 'C'?
  17. BoltonDBA New Member

    Hi<br /><br />I will try your suggestion in the morning. It takes 1min31secs with condition C. I think I might need to look at splitting the updates up and do try to get to get it working that way <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />What have you done today to make you feel proud ? - Heather Small

Share This Page