6

I'm currently using Microsoft SQL Azure (RTM) - 12.0.2000.8 as the database. The database currently has 10 DTUs.

The idea here is that I want to make the query based on the view. This view contains of simple SELECT() syntax with 5 table joins. This view give output roughly about 253K rows in about 6 seconds.

CREATE VIEW [dbo].[TopAdsDisplaySumaryView]
AS
SELECT  
        client.Id AS ClientId,      -- (PK, int, not null)
        client.PartnerId,           -- (FK, int, not null)
        adsPict.Id AS AdsPictureId, -- (PK, int, not null)
        adsPict.ImageName,          -- (nvarchar(max), null)
        displayAds.DisplayTo,       -- (datetime, not null)
        displayAds.DisplayFrom      -- (datetime, not null)
FROM      
        dbo.Machines AS machine 
        INNER JOIN dbo.MachineGroups AS machineGroups ON machineGroups.Id = machine.MachineGroupId 
        INNER JOIN dbo.Clients AS client ON client.Id = machineGroups.ClientId 
        INNER JOIN dbo.DisplyadAds AS displayAds ON displayAds.MachineId = machine.Id 
        INNER JOIN dbo.AdsPictures AS adsPict ON adsPict.Id = displayAds.AdsPictureId

enter image description here

Notice that I'm only taking the column from tables clients, displayAds, and adsPictures, but I have to join tables machines and machineGroups in order to get data from clients. displayAds table has the most data.

From here I want to generate the top 5 data with the most data count. This is the query:

SELECT TOP 5
    adsSum.PartnerId as PartnerId,
    adsSum.ClientId as ClientId,
    adsSum.AdsPictureId as AdsPictureId,
    adsSum.ImageName as ImageName,
    count(*) as TotalDisplay
FROM [dbo].[TopAdsDisplaySumaryView] adsSum
GROUP BY adsSum.PartnerId, adsSum.ClientId, adsSum.AdsPictureId, adsSum.ImageName
ORDER BY TotalDisplay DESC

Here's the actual execution plan for this query: https://pastebin.com/CcR9cC5e

When this query is executed, it takes about 1 minute 15 seconds. I'm trying to find out why this query takes a lot of time to execute, while executing the view only takes 6 seconds.

Here's my attempts so far:

  1. Created an index for displayAds table. This gives me ~1 second improvement, no big change.

    CREATE NONCLUSTERED INDEX 
    [MachineIdIndexIncludeAdsPictureIdDisplayFromDisplayTo] ON [dbo].[DisplyadAds]
    (
        [MachineId] ASC
    )
    INCLUDE 
    (   
        [AdsPictureId],
        [DisplayFrom],
        [DisplayTo]
    )
    GO
    
  2. Created an index for adsPictures table. Nothing changed.

    CREATE NONCLUSTERED INDEX [IX_AdsPictures_IncludeImageName] ON [dbo].[AdsPictures]
    (
       [Id] ASC
    )
    INCLUDE ( [ImageName])
    GO
    
  3. Tried to remove column adsPict.ImageName in the query, the execution time was greatly improved to only 3 seconds (~72 sec. improvement!). I suspect that column is the problem, but I need it to show.

The maximum record length of ImageName is 36. Mostly around 11~20. There are 55 rows in AdsPictures table.

Any idea how to resolve this problem? Any help would be greatly appreciated.

  • Also, looks like you might be dealing with out of date statistics, estimated rows 12k, actual 254k. – Grant Fritchey Jun 7 '18 at 10:57
  • @jean, The types of each column are already added in the view. I marked them in comments. – Reynaldi Jun 7 '18 at 12:36
  • Nop you don't I talking about things like ON machineGroups.Id = machine.MachineGroupId – jean Jun 7 '18 at 12:53
3

Here's what I see in the XML for your actual plan:

<WaitStats>
<Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="66883" WaitCount="1649" />
<Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="56086" WaitCount="3980" />
</WaitStats>
<QueryTimeStats CpuTime="6665" ElapsedTime="73522" />

You can also see this information in the root node of the plan in SSMS if you don't like looking at XML:

enter image description here

You can read about the SOS_SCHEDULER_YIELD wait here. A good layman's explanation is that your query was only able to execute on a CPU for 6639 ms. It spent 66883 ms on a queue waiting to get on a CPU. Based on your description of the problem ("executing the view only takes 6 seconds"), you have a server performance issue instead of a query performance issue. Of course, there may be ways to make the query use even less CPU, but I would approach this problem by verifying that the server has the correct number of CPU cores/DTUs/whatever and by looking at the queries that use the most CPU on the server. Your query could run almost 12X faster if there was less load on the server.

| improve this answer | |
1

This is just the same as what you have.

Yes the view runs in 6 seconds but the query is making many calls to the view.

The solution may be indexed views. Put an index on the group by columns.

CREATE VIEW [dbo].[TopAdsDisplaySumaryView] AS
SELECT  client.Id AS ClientId,      -- (PK, int, not null)
        client.PartnerId,           -- (FK, int, not null)
        adsPict.Id AS AdsPictureId, -- (PK, int, not null)
        adsPict.ImageName           -- (nvarchar(max), null)
FROM    dbo.Machines AS machine 
INNER JOIN dbo.MachineGroups AS machineGroups 
        ON machineGroups.Id = machine.MachineGroupId 
INNER JOIN dbo.Clients AS client 
        ON client.Id = machineGroups.ClientId 
INNER JOIN dbo.DisplyadAds AS displayAds 
        ON displayAds.MachineId = machine.Id 
INNER JOIN dbo.AdsPictures AS adsPict 
        ON adsPict.Id = displayAds.AdsPictureId  

SELECT TOP 5
       adsSum.PartnerId as PartnerId,          -- client
       adsSum.ClientId as ClientId,            -- client
       adsSum.AdsPictureId as AdsPictureId,    -- adsPict
       adsSum.ImageName as ImageName,          -- adsPict
       count(*) as TotalDisplay
FROM [dbo].[TopAdsDisplaySumaryView] adsSum
GROUP BY adsSum.PartnerId, adsSum.ClientId, adsSum.AdsPictureId, adsSum.ImageName
ORDER BY TotalDisplay DESC
| improve this answer | |
  • Thanks for the suggestion, but it seems impossible to create an index in a view, since this view may return multiple rows of PartnerId, ClientId, and AdsPictureId. – Reynaldi Jun 11 '18 at 2:17
0

Try add indexes that will assist in the Grouping.

Start with:

CREATE NONCLUSTERED INDEX idx ON dbo.Clients(PartnerId)

Check out this article by Erik Darling it shows how to deal with the expensive Hash Match Aggregate that is costing 34% on your plan.

| improve this answer | |
  • I tried this, but it didn't make any changes. – Reynaldi Jun 7 '18 at 11:59

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.