sql - 如何适应不经常运行但资源密集型的 Azure SQL 查询

Question

注意:我在此处提供了我的 Azure 设置的详细信息，但我不确定该解决方案是否是基于 Azure 的解决方案。这可能是一个可以在 C#、 Entity Framework 或 SQL 级别解决的问题。

我有一个在 Azure 应用服务上运行的 .NET Web 应用程序，使用 Entity Framework 以定价级别标准 S1 (20 DTU) 访问 Azure SQL DB。 99% 的情况下，应用程序在 SQL DB 上使用的 DTU 不足 1%。但是，当有人登录应用程序的管理门户并运行特定报告时，它会执行一个资源密集型查询，并且需要很长时间(一分钟多)，这是我们无法忍受的。该报告每周仅运行几次。我尝试过扩展 SQL DB，并且毫不奇怪地发现，在更高的计划下，执行时间会达到某种合理的水平。在标准 S4 (200 DTU) 下，执行时间下降到 20 秒，这并不理想，但我现在可以忍受。然而，当 99% 的时间只使用 DTU 的一小部分时，为 S4 级别付费是没有意义的。关于如何减少查询执行时间或仅在需要时扩展的任何想法？

用于此报告的 Entity Framework 代码是:

class MyAppModelContainer : DbContext 
{
    public virtual ObjectResult<GetOrganizationList_Result> GetOrganizationList()
    {
        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<GetOrganizationList_Result>("GetOrganizationList");
    }
}

用于检索结果的模型是:

public partial class GetOrganizationList_Result
{
    public int id { get; set; }
    public string Name { get; set; }
    public Nullable<int> DeviceCounts { get; set; }
    public Nullable<int> EmailCounts { get; set; }
}

存储过程是:

CREATE PROCEDURE [dbo].[GetOrganizationList]
AS
BEGIN
    SELECT o.Id,o.Name,COUNT(distinct s.DeviceId) as DeviceCounts, COUNT(distinct d.userid) as EmailCounts
    FROM Sessions s
    INNER JOIN Devices d on d.Id = s.DeviceId
    RIGHT OUTER JOIN Organizations o on o.id=s.OrganizationId
    GROUP BY o.Id,Name
END

每个连接表中的大致行数: session 表:200 万行设备表:166,000 行用户表:88,000 行

以下是表定义和索引:

CREATE TABLE [dbo].[Sessions] (
    [Id]             INT      IDENTITY (1, 1) NOT NULL,
    [DeviceId]       INT      NULL,
    [StartTime]      DATETIME NOT NULL,
    [OrganizationId] INT      NOT NULL,
    CONSTRAINT [PK_Sessions] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_DeviceSession] FOREIGN KEY ([DeviceId]) REFERENCES [dbo].[Devices] ([Id]),
    CONSTRAINT [FK_OrganizationSession] FOREIGN KEY ([OrganizationId]) REFERENCES [dbo].[Organizations] ([Id])
);

CREATE NONCLUSTERED INDEX [IX_FK_DeviceSession]
    ON [dbo].[Sessions]([DeviceId] ASC);

CREATE NONCLUSTERED INDEX [IX_FK_OrganizationSession]
    ON [dbo].[Sessions]([OrganizationId] ASC);

CREATE NONCLUSTERED INDEX [IX_Sessions_OrganizationId_Include_DeviceId]
    ON [dbo].[Sessions]([OrganizationId] ASC)
    INCLUDE([DeviceId]);    

CREATE NONCLUSTERED INDEX [IX_Sessions_OrganizationId_DeviceId] ON [dbo].[Sessions]
(
    [DeviceId] ASC,
    [OrganizationId] ASC,
    [StartTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE TABLE [dbo].[Devices] (
    [Id]         INT        IDENTITY (1, 1) NOT NULL,
    [UserId]     INT        NULL,
    [MACAddress] NCHAR (12) NOT NULL,
    CONSTRAINT [PK_Devices] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_UserDevice] FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([Id]),
    CONSTRAINT [IX_Unique_MACAddress] UNIQUE NONCLUSTERED ([MACAddress] ASC)
);

CREATE NONCLUSTERED INDEX [IX_FK_UserDevice]
    ON [dbo].[Devices]([UserId] ASC);

CREATE TABLE [dbo].[Users] (
    [Id]    INT            IDENTITY (1, 1) NOT NULL,
    [Email] NVARCHAR (250) NOT NULL,
    [Sex]   TINYINT        NOT NULL,
    [Age]   SMALLINT       NOT NULL,
    [PhoneNumber] NCHAR (10)     NOT NULL DEFAULT '' ,
    [Name] NVARCHAR(100) NOT NULL DEFAULT '', 
    CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [IX_Unique_Email_PhoneNumber] UNIQUE NONCLUSTERED ([Email] ASC, [PhoneNumber] ASC)
);

我每周都会重建索引并更新统计数据。 Azure SQL DB 没有性能建议。

关于如何在不简单地投入更多 Azure 硬件的情况下解决这个问题，有什么想法吗？我对任何事情都持开放态度，包括 Azure 级别的更改、SQL 更改、代码更改。 Azure SQL DB 似乎没有定价的消费模型，如果存在的话可能会对我有所帮助。

Answer 1

我建议创建以下索引或将缺少的列添加到现有索引中。

CREATE NONCLUSTERED INDEX [NIX_Session_Device_OrganizationId]
ON [dbo].[Sessions] ([DeviceId] , [OrganizationId]);


CREATE NONCLUSTERED INDEX [NIX_Device_ID_UserID]
ON [dbo].[Devices] ([Id], [userid]);


CREATE NONCLUSTERED INDEX [NIX_Organizations]
ON [dbo].[Organizations] ([Id] , [Name]);

200 个 DTU 并不是一个大数字，2oo 个 DTU 意味着您已经处于 S4 服务级别，任何以上都会使您处于 S6 服务级别。

首先尝试使用适当的索引调整您的查询，一旦完成，然后开始查看 DTU，实际上对于任务关键型系统，我更愿意使用 vCore 定价模型，而不是与DTU 的黑匣子。