2

I have a query in my data warehouse load that occasionally hangs. Traditionally, it takes only a few minutes to run, however sometimes it can take a few hours.

I'm unsure whether this is the query taking a different plan and doing something different or what. When it hangs, restarting the job fixes it.

I've just checked my TEST server (identical specs) and have noticed that the query has been running for over 3 days!

What can I do right now to try and determine the cause? I've got a copy of the query plan from when I run it manually..compared to what it currently is running (followed instructions http://www.fulltablescan.com/index.php?/archives/149-Viewing-the-Execution-Plan-of-a-Running-Query-in-SQL-Server.html).

They do look different but I am out of my depth when it comes to query plans.

The sqlservr.exe process is currently using all 32GB assigned to it, it is using almost 100% CPU but doesn't appear to be writing anything to disk (apart from system_health_log occasionally).

Nobody else logged in, nothing else running on the server (only this and my own session currently open).

Here is the current sessions: enter image description here

The query is:

INSERT INTO dbo.FactProductPrice (
  PriceDate,
  ProductId,
  SalesPriceSchemeId,
  CurrentPrice,
  OriginalPrice
)
SELECT
  CurrentPrice.PriceDate,
  CurrentPrice.ProductId,
  CurrentPrice.SalesPriceSchemeId,
  COALESCE(CurrentPrice.CurrentPrice, 0) AS CurrentPrice,
  COALESCE(OriginalPrice.OriginalPrice, 0) AS OriginalPrice
FROM
(
  SELECT
    PricesByStyle.CAPTURE_DATE AS PriceDate,
    DimProduct.Id AS ProductId,
    DimSalesPriceScheme.Id AS SalesPriceSchemeId,
    CASE
      WHEN PricesByStyle.PRICEBY = 0 THEN PricesByStyle.PRICE
      WHEN PricesByStyle.PRICEBY = 1 THEN PricesByColour.PRICE
      WHEN PricesByStyle.PRICEBY = 2 THEN PricesBySize.PRICE
    END AS CurrentPrice,
    ROW_NUMBER() OVER (PARTITION BY DimProduct.Id, PricesByStyle.CAPTURE_DATE, DimSalesPriceScheme.Id ORDER BY PricesByStyle.DUEF DESC, PricesByStyle.SPIDX DESC) AS RowNumber
  FROM
    DimProduct
  INNER JOIN
    DataWarehouseRawData.dbo.SPRICE_STYLE AS PricesByStyle ON
    PricesByStyle.STYLEIDX = DimProduct.StyleSourceKey
  LEFT OUTER JOIN
    DataWarehouseRawData.dbo.SPRICE_CLRSKU AS PricesByColour ON
    PricesByColour.SPIDX = PricesByStyle.SPIDX
    AND PricesByColour.CAPTURE_DATE = PricesByStyle.CAPTURE_DATE
    AND PricesByStyle.PRICEBY = 1 AND PricesByColour.CLRIDX = DimProduct.StyleColourSourceKey
  LEFT OUTER JOIN
    DataWarehouseRawData.dbo.SPRICE_CLRSKU AS PricesBySize ON
    PricesBySize.SPIDX = PricesByStyle.SPIDX
    AND PricesBySize.CAPTURE_DATE = PricesByStyle.CAPTURE_DATE
    AND PricesByStyle.PRICEBY = 2 AND PricesBySize.SKUIDX = DimProduct.SourceKey
  INNER JOIN
    dbo.DimSalesPriceScheme ON
    DimSalesPriceScheme.SourceKey = PricesByStyle.RCIDX
    AND DimSalesPriceScheme.IsActive = 1
  WHERE
    PricesByStyle.ACTIVE = 1
    AND PricesByStyle.DUEF <= PricesByStyle.CAPTURE_DATE
    AND PricesByStyle.DUET >= PricesByStyle.CAPTURE_DATE
    --AND PricesByStyle.CAPTURE_DATE = '20160601'
    AND NOT EXISTS (
      SELECT *
      FROM dbo.FactProductPrice
      WHERE FactProductPrice.PriceDate = PricesByStyle.CAPTURE_DATE
    ) --Only records that we don't currently have
) AS CurrentPrice
LEFT OUTER JOIN
(
  SELECT
    PricesByStyle.CAPTURE_DATE AS PriceDate,
    DimProduct.Id AS ProductId,
    DimSalesPriceScheme.Id AS SalesPriceSchemeId,
    CASE
      WHEN PricesByStyle.PRICEBY = 0 THEN PricesByStyle.PRICE
      WHEN PricesByStyle.PRICEBY = 1 THEN PricesByColour.PRICE
      WHEN PricesByStyle.PRICEBY = 2 THEN PricesBySize.PRICE
    END AS OriginalPrice,
    ROW_NUMBER() OVER (PARTITION BY DimProduct.Id, PricesByStyle.CAPTURE_DATE, DimSalesPriceScheme.Id ORDER BY PricesByStyle.SPIDX DESC) AS RowNumber
  FROM
    DimProduct
  INNER JOIN
    DataWarehouseRawData.dbo.SPRICE_STYLE AS PricesByStyle ON
    PricesByStyle.STYLEIDX = DimProduct.StyleSourceKey
  LEFT OUTER JOIN
    DataWarehouseRawData.dbo.SPRICE_CLRSKU AS PricesByColour ON
    PricesByColour.SPIDX = PricesByStyle.SPIDX
    AND PricesByColour.CAPTURE_DATE = PricesByStyle.CAPTURE_DATE
    AND PricesByStyle.PRICEBY = 1 AND PricesByColour.CLRIDX = DimProduct.StyleColourSourceKey
  LEFT OUTER JOIN
    DataWarehouseRawData.dbo.SPRICE_CLRSKU AS PricesBySize ON
    PricesBySize.SPIDX = PricesByStyle.SPIDX
    AND PricesBySize.CAPTURE_DATE = PricesByStyle.CAPTURE_DATE
    AND PricesByStyle.PRICEBY = 2 AND PricesBySize.SKUIDX = DimProduct.SourceKey
  INNER JOIN
    dbo.DimSalesPriceScheme ON
    DimSalesPriceScheme.SourceKey = PricesByStyle.RCIDX
    AND DimSalesPriceScheme.IsActive = 1
  WHERE
    PricesByStyle.ACTIVE = 1
    AND PricesByStyle.DUEF = '17530101'
    AND PricesByStyle.DUET = '99991231'
) AS OriginalPrice ON
  OriginalPrice.ProductId = CurrentPrice.ProductId
  AND OriginalPrice.SalesPriceSchemeId = CurrentPrice.SalesPriceSchemeId
  AND OriginalPrice.PriceDate = CurrentPrice.PriceDate
  AND OriginalPrice.RowNumber = 1
WHERE
  CurrentPrice.RowNumber = 1
  AND NOT (
    COALESCE(CurrentPrice.CurrentPrice, 0) = 0
    AND COALESCE(OriginalPrice.OriginalPrice, 0) = 0
  )

Here is the query plan that is currently running (has been over 5 days now) Query Plan

  • Have you tried using the old optimizer TF 9481 and 4199 (you can use query hint - option (querytraceon 9481). I suspect the problem might be due to the new optimizer in 2014. Also try to use temp tables & create indexes instead of bigger joins- that will help as well. What is maxdop and cost threshold of parallelism. You can upload the query plan at brentozar.com/pastetheplan – Kin Shah Jul 28 '17 at 13:25
  • 1
    Now I'm not going to delve too deep into this, because the last time I mentioned this as a possible solution I got my hand smacked. Have you tried deleting the (clustered-)index on the target table before you insert the data? I have personally experienced an issue, where inserting a lot of data into a star table via SSIS, that having any index on the table resulted in a very long insert duration. Removing the index before the insert and then re-creating the index resulted in faster INSERTS. – hot2use Jul 28 '17 at 13:27
  • @hot2use deleting the clustered index would mean the table is not accessible... I wouldn't be able to load my data. I don't have any non clustered indexes if that's that you meant (only 5 or so columns) – Lock Jul 28 '17 at 22:19
  • You can delete the CI. It returns the table into a HEAP. 1. Drop CI 2. Load Data 3. Recreate CI. – hot2use Jul 29 '17 at 9:09

Your Answer

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

Browse other questions tagged or ask your own question.