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

Hi.

My azure database is kneeling. The following query is running constantly, and for a long time.

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')))

As far as i know this is a temp table created by a stored procedure. But, is it my plugin, or is this code in nopcommerce? The problems started with 4 of my sites after upgrading to 3.8.

Does anyone know what this is? Could it be a problem with my nop templates ajax filter?

Hi,

How many products and specification attributes do you have?

lintho wrote:
You can easily test it out. Just  disable this plugin and see whether anything is changed

Seems to be the case without the plugin to.

We have 2278 products and 4 specifiaction attributes. There is however ALOT of attributecombinations.

The database is now using a S3 standard pool by itself and its at 99.9% dtu.

This is very critical. Have i done anything wrong when updating?

I did change the files in your post from august 22.


Screenshot of the database metric: http://i.imgur.com/aPobirc.png

Screenshot of queries count:  http://i.imgur.com/1y4YnCn.png

The 2 with insane count are these:

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__linq__0 int)SELECT TOP (1)
    [Project1].[Id] AS [Id],
    [Project1].[PictureBinary] AS [PictureBinary],
    [Project1].[MimeType] AS [MimeType],
    [Project1].[SeoFilename] AS [SeoFilename],
    [Project1].[AltAttribute] AS [AltAttribute],
    [Project1].[TitleAttribute] AS [TitleAttribute],
    [Project1].[IsNew] AS [IsNew]
    FROM ( SELECT
        [Extent1].[Id] AS [Id],
        [Extent1].[PictureBinary] AS [PictureBinary],
        [Extent1].[MimeType] AS [MimeType],
        [Extent1].[SeoFilename] AS [SeoFilename],
        [Extent1].[AltAttribute] AS [AltAttribute],
        [Extent1].[TitleAttribute] AS [TitleAttribute],
        [Extent1].[IsNew] AS [IsNew],
        [Extent2].[DisplayOrder] AS [DisplayOrder]
        FROM  [dbo].[Picture] AS [Extent1]
        INNER JOIN [dbo].[Product_Picture_Mapping] AS [Extent2] ON [Extent1].[Id] = [Extent2].[PictureId]
        WHERE [Extent2].[ProductId] = @p__linq__0
    )  AS [Project1]
    ORDER BY [Project1].[DisplayOrder] ASC


the one with 7800 is this one:

INSERT INTO #DisplayOrderTmp ([ProductId])
  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.Id in (select psam.ProductId from [Product_SpecificationAttribute_Mapping] psam with (NOLOCK) where psam.AllowFiltering = 1 and psam.SpecificationAttributeOptionId IN (SELECT SpecificationAttributeOptionId FROM #FilteredSpecsWithAttributes WHERE SpecificationAttributeId = 5))
        AND p.Id in (select psam.ProductId from [Product_SpecificationAttribute_Mapping] psam with (NOLOCK) where psam.AllowFiltering = 1 and psam.SpecificationAttributeOptionId IN (SELECT SpecificationAttributeOptionId FROM #FilteredSpecsWithAttributes WHERE SpecificationAttributeId = 6))
        AND p.Id in (select psam.ProductId from [Product_SpecificationAttribute_Mapping] psam with (NOLOCK) where psam.AllowFiltering = 1 and psam.SpecificationAttributeOptionId IN (SELECT SpecificationAttributeOptionId FROM #FilteredSpecsWithAttributes WHERE SpecificationAttributeId = 8))
  ORDER BY pcm.DisplayOrder ASC,  p.[Name] ASC



Does this make any sense to you Andrei?

Check the Execution Plan.  Is it using indexes?  (did you possibly lose them during upgrade?)

Will do.


By the way. I allowed azure to  create table indexes to    increase performance. I did not allow it to drop indexes automaticlly.

Could possibly creating indexes for some tables cause this?

I will add a screenshot of all the actions azure did when i get to work!

Could this have anything to do with it?

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

Even when the site has no users, the DTU is at 90%. Something is running and isnt stopping.

Any idea what it could be?

I noticed that my app service plan has increased the number of instances to 2 instead of 1.

I have 9 sites, and not a single one is set up to use webfarms in web.config.

Could that be the reason for these problems?

I have installed 1 clean nopcommerce solution with sampledata from the installation.

Ive set it up on a single S3 database and 1 serviceplan with 1 large webserver.

Testing results with 20 concurrent users for 3 minutes:

http://i.imgur.com/2Y4bkWX.png

While the test was going the database monitor shows the following data:

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

Is this normal behaviour? Isnt there any caching in memory out of the box? Why is it doing so many requests to the database? This test was going to 1 single category-view only.

This does not explain why my other 2 sites have constant requests to the database with ANY users on the site. Its as if a request is looping.



Here is a screenshot of the other database. there was NO users on the site during this hour.
http://i.imgur.com/xIrnVlf.png

1st screenshot: I feel like 5 requests per second is very low.