Extremely high database load after upgrading to 3.8 - did i do something wrong? Need help ASAP

I know im spamming this thread, but i really need help, and i would like to add as much information as i can.

http://i.imgur.com/cjHynO0.png

This is the database over the past month. I upgraded it right AFTER it increased in avarage DTU.

before update this method is running alot:

INSERT INTO #FilterableSpecs ([ProductId], [SpecificationAttributeOptionId])      SELECT DISTINCT [psam].ProductId, [psam].SpecificationAttributeOptionId      FROM [Product_SpecificationAttribute_Mapping] [psam] with (NOLOCK)      WHERE [psam].[ProductId] IN (SELECT [pi].ProductId FROM #PageIndex [pi])

after upgradering this method running alot more than the previous one:

INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
          SELECT DISTINCT [psam].SpecificationAttributeOptionId
          FROM [Product_SpecificationAttribute_Mapping] [psam] WITH (NOLOCK)
              WHERE [psam].[AllowFiltering] = 1
              AND [psam].[ProductId] IN (
  SELECT p.Id
  FROM
    Product p with (NOLOCK)
    LEFT JOIN Product_Category_Mapping pcm with (NOLOCK)
      ON p.Id = pcm.ProductId
  WHERE
    p.Deleted = 0
    AND pcm.CategoryId IN (SELECT CategoryId FROM #FilteredCategoryIds)
    AND pcm.IsFeaturedProduct = 0
    AND p.VisibleIndividually = 1
      AND p.Published = 1
    AND p.Deleted = 0
    AND (getutcdate() BETWEEN ISNULL(p.AvailableStartDateTimeUtc, '1/1/1900') and ISNULL(p.AvailableEndDateTimeUtc, '1/1/2999'))
    AND (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = 'Product' and [sm].StoreId=1
      )))

lintho wrote:
Disable all the Scheduled Tasks, and see if any impact

Done.

Awaiting results.

Thanks for reply!

I disabled all Scheduled Tasks on one site, and restarted it.

After a couple of minutes the database requests are going insane again.


You can see where i restarted. Top of the graph is 99%.

I used analytics while waiting, and i was the ONLY visitor.

I checked the sql query insight and this is the queries running still:



And one of the queries with the highest runtime and querycount is still this one:




Another site ive created has about 1% average dtu usage and is visited alot more than the one in the screenshots.

1. Is it possible test it out with version 3.70? If yes, please let me know if it works fine
2. Try to test it locally. Download and restore database backup on local computer. Does it also take 100% CPU?

a.m. wrote:

1. Testing 3.7 will take some time. I merged the 3.8 update to master and removed the branch. Stupid.

2.The CPU is actually fine on azure. But the DTU is high. After some more testing today, and replacing all files for 3.8 once more i've figured out that the site has no problem on a test slot, with only me navigating on the site. As soon as i swap it to production the database goes ham with requests. I've checked the HTTP requests on the server, and that isn't excessively high.

Maybe get a support ticket opened with Microsoft to figure out the issue, if its urgent?

Support ticket added. I hope it helps.

What i know so far:
There seems to be problems only with databases that has over 2000 products.

We have 8 stores at the moment, where 7 of them are on 3.8.
3 of them have very high load, which does not drop even with no users on the site. Those 3 has 2000+ products. 1 has 8000. There is also a lot of attribute combinations for these 3 databases.

The rest of the databases has less than 300 products each, and they have no problems.

The last database has 3000 products and is on 3.7. That database has no problems as well.

Here is a picture of 6 databases and their metric. Only 2 of them has visitors at the moment, and thats not the ones with high load.

Update:

The only difference i have found so far on a one working database and one with problems are:

The database with 5% cpu usage
Low number of products
Azure database compability_level on 120

The database with 100% cpu usage
High count of products
Azure database compability_level on 100


About the same amount of visitors on both sites.

The query you mentioned above... is that something custom being done inside a 3rd party extension?

This is what I see in the installation script and 3.7 to 3.8 upgrade script, which looks little different when compared to your posted query.

  --prepare filterable specification attribute option identifier (if requested)
  IF @LoadFilterableSpecificationAttributeOptionIds = 1
  BEGIN    
    CREATE TABLE #FilterableSpecs
    (
      [SpecificationAttributeOptionId] int NOT NULL
    )
    INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
    SELECT DISTINCT [psam].SpecificationAttributeOptionId
    FROM [Product_SpecificationAttribute_Mapping] [psam] with (NOLOCK)
    WHERE [psam].[AllowFiltering] = 1
    AND [psam].[ProductId] IN (SELECT [pi].ProductId FROM #PageIndex [pi])

    --build comma separated list of filterable identifiers
    SELECT @FilterableSpecificationAttributeOptionIds = COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '') + CAST(SpecificationAttributeOptionId as nvarchar(4000))
    FROM #FilterableSpecs

    DROP TABLE #FilterableSpecs
  END