从 .NET 应用程序到 SQL Server 的调用偶尔缓慢

Question

我在 SQL Server 中有一个表，该表是从仍在生产中的遗留系统继承的，该表是根据下面的代码构建的。 我创建了一个 SP 来查询表，如表创建语句下面的代码中所述。 我的问题是，有时，.NET 通过 Enterprise Library 4 和 DataReader 对象调用此 SP 的速度很慢。 SP 通过数据层中的循环结构调用，该循环结构指定进入 SP 的参数以填充用户对象。 值得一提的是，循环结构的每次传递都不会发生缓慢的调用。 通常一天的大部分时间或更长时间都没有问题，然后开始呈现，这使得调试变得非常困难。

该表包含大约 500 万行。 例如，慢速调用平均需要 10 秒，而快速调用平均需要 0 到 10 毫秒。 我在缓慢的调用期间检查了锁定/阻塞事务，但没有发现。 我在数据层创建了一些自定义性能计数器来监控调用时间。 从本质上讲，当性能不佳时，这对于一次调用来说确实很糟糕。 但当它好的时候，它真的很好。 我已经能够在一些不同的开发计算机上重现该问题，但不能在我们的开发和登台数据库服务器上重现该问题，这些服务器当然具有更强大的硬件。 通常，问题可以通过重新启动 SQL Server 服务来解决，但并非总是如此。 表上有我正在查询的字段的索引，但索引比我想要的要多。 但是，我对于删除任何索引或玩具感到犹豫，因为它可能会对遗留系统产生影响。 有没有人以前遇到过这样的问题，或者您有解决方法的建议吗？

CREATE TABLE [dbo].[product_performance_quarterly](
    [performance_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [product_id] [int] NULL,
    [month] [int] NULL,
    [year] [int] NULL,
    [performance] [decimal](18, 6) NULL,
    [gross_or_net] [char](15) NULL,
    [vehicle_type] [char](30) NULL,
    [quarterly_or_monthly] [char](1) NULL,
    [stamp] [datetime] NULL CONSTRAINT [DF_product_performance_quarterly_stamp]  DEFAULT (getdate()),
    [eA_loaded] [nchar](10) NULL,
    [vehicle_type_id] [int] NULL,
    [yearmonth] [char](6) NULL,
    [gross_or_net_id] [tinyint] NULL,
 CONSTRAINT [PK_product_performance_quarterly_4_19_04] PRIMARY KEY CLUSTERED 
(
    [performance_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[product_performance_quarterly]  WITH NOCHECK ADD  CONSTRAINT [FK_product_performance_quarterlyProduct_id] FOREIGN KEY([product_id])
REFERENCES [dbo].[products] ([product_id])
GO
ALTER TABLE [dbo].[product_performance_quarterly] CHECK CONSTRAINT [FK_product_performance_quarterlyProduct_id]

CREATE PROCEDURE [eA.Analytics.Calculations].[USP.GetCalculationData]
(
    @PRODUCTID INT,                     --products.product_id
    @BEGINYEAR INT,                     --year to begin retrieving performance data
    @BEGINMONTH INT,                    --month to begin retrieving performance data
    @ENDYEAR INT,                       --year to end retrieving performance data
    @ENDMONTH INT,                      --month to end retrieving performance data
    @QUARTERLYORMONTHLY VARCHAR(1),     --do you want quarterly or monthly data?
    @VEHICLETYPEID INT,                 --what product vehicle type are you looking for?
    @GROSSORNETID INT                   --are your looking gross of fees data or net of fees data?
)
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @STARTDATE VARCHAR(6),
            @ENDDATE   VARCHAR(6),
            @vBEGINMONTH VARCHAR(2),
            @vENDMONTH VARCHAR(2)   

IF LEN(@BEGINMONTH) = 1 
    SET @vBEGINMONTH = '0' + CAST(@BEGINMONTH AS VARCHAR(1))
ELSE
    SET @vBEGINMONTH = @BEGINMONTH

IF LEN(@ENDMONTH) = 1
    SET @vENDMONTH = '0' + CAST(@ENDMONTH AS VARCHAR(1))
ELSE
    SET @vENDMONTH = @ENDMONTH

SET @STARTDATE = CAST(@BEGINYEAR AS VARCHAR(4)) + @vBEGINMONTH
SET @ENDDATE = CAST(@ENDYEAR AS VARCHAR(4)) + @vENDMONTH

--because null values for gross_or_net_id and vehicle_type_id are represented in 
--multiple ways (true null, empty string, or 0) in the PPQ table, need to account for all possible variations if 
--a -1 is passed in from the .NET code, which represents an enumerated value that
--indicates that the value(s) should be true null.

IF @VEHICLETYPEID = '-1' AND @GROSSORNETID = '-1'
    SELECT
        PPQ.YEARMONTH, PPQ.PERFORMANCE
    FROM PRODUCT_PERFORMANCE_QUARTERLY PPQ
        WITH (NOLOCK)
    WHERE 
        (PPQ.PRODUCT_ID = @PRODUCTID)
        AND (PPQ.YEARMONTH BETWEEN @STARTDATE AND @ENDDATE)
        AND (PPQ.QUARTERLY_OR_MONTHLY = @QUARTERLYORMONTHLY)
        AND (PPQ.VEHICLE_TYPE_ID IS NULL OR PPQ.VEHICLE_TYPE_ID = '0' OR PPQ.VEHICLE_TYPE_ID = '')
        AND (PPQ.GROSS_OR_NET_ID IS NULL OR PPQ.GROSS_OR_NET_ID = '0' OR PPQ.GROSS_OR_NET_ID = '')
    ORDER BY PPQ.YEARMONTH ASC

IF @VEHICLETYPEID <> '-1' AND @GROSSORNETID <> '-1'
    SELECT
        PPQ.YEARMONTH, PPQ.PERFORMANCE
    FROM PRODUCT_PERFORMANCE_QUARTERLY PPQ
        WITH (NOLOCK)
    WHERE 
        (PPQ.PRODUCT_ID = @PRODUCTID)
        AND (PPQ.YEARMONTH BETWEEN @STARTDATE AND @ENDDATE)
        AND (PPQ.QUARTERLY_OR_MONTHLY = @QUARTERLYORMONTHLY)
        AND (PPQ.VEHICLE_TYPE_ID = @VEHICLETYPEID )
        AND (PPQ.GROSS_OR_NET_ID = @GROSSORNETID)
    ORDER BY PPQ.YEARMONTH ASC

IF @VEHICLETYPEID = '-1' AND @GROSSORNETID <> '-1'
    SELECT
        PPQ.YEARMONTH, PPQ.PERFORMANCE
    FROM PRODUCT_PERFORMANCE_QUARTERLY PPQ
        WITH (NOLOCK)
    WHERE 
        (PPQ.PRODUCT_ID = @PRODUCTID)
        AND (PPQ.YEARMONTH BETWEEN @STARTDATE AND @ENDDATE)
        AND (PPQ.QUARTERLY_OR_MONTHLY = @QUARTERLYORMONTHLY)
        AND (PPQ.VEHICLE_TYPE_ID IS NULL OR PPQ.VEHICLE_TYPE_ID = '0' OR PPQ.VEHICLE_TYPE_ID = '')
        AND (PPQ.GROSS_OR_NET_ID = @GROSSORNETID)
    ORDER BY PPQ.YEARMONTH ASC

IF @VEHICLETYPEID <> '-1' AND @GROSSORNETID = '-1'
    SELECT
        PPQ.YEARMONTH, PPQ.PERFORMANCE
    FROM PRODUCT_PERFORMANCE_QUARTERLY PPQ
        WITH (NOLOCK)
    WHERE 
        (PPQ.PRODUCT_ID = @PRODUCTID)
        AND (PPQ.YEARMONTH BETWEEN @STARTDATE AND @ENDDATE)
        AND (PPQ.QUARTERLY_OR_MONTHLY = @QUARTERLYORMONTHLY)
        AND (PPQ.VEHICLE_TYPE_ID = @VEHICLETYPEID)
        AND (PPQ.GROSS_OR_NET_ID IS NULL OR PPQ.GROSS_OR_NET_ID = '0' OR PPQ.GROSS_OR_NET_ID = '')
    ORDER BY PPQ.YEARMONTH ASC

END

Answer 1

听起来好像另一个查询正在后台运行，该查询已锁定表，而您无辜的查询只是在等待它完成

Answer 2

这是一个奇怪的边缘情况，但我最近遇到了它。

如果查询在应用程序中运行的时间比在 Management Studio 中运行的时间长，您可能需要检查以确保 Arithabort 已关闭。 Management Studio 使用的连接参数与 .NET 使用的连接参数不同。

Answer 3

看起来这是两件事之一 - 慢速调用的参数在某种程度上与快速调用不同，并且它们也无法使用索引，或者存在某种类型的锁定争用让您陷入困境向上。 您说您在特定进程挂起时检查了阻塞锁，但没有看到任何阻塞锁 - 这表明它是第一个。 但是 - 您确定您的登台服务器（您无法在其上重现此错误）和开发服务器（您可以在其上重现该错误）具有相同的数据库配置吗？ 例如，也许在生产中启用了“READ COMMITTED SNAPSHOT”，但在开发中未启用，这将导致读取争用问题在生产中消失。

如果是参数上的差异，我建议使用 SQL Profiler 来观察事务并捕获一些事务 - 一些较慢的事务和一些较快的事务，然后在 Management Studio 窗口中，用参数替换上面 SP 中的变量值，然后按“Control-L”获取执行计划。 这将准确地告诉您 SQL Server 希望如何处理您的查询，您可以比较不同参数组合的执行计划，看看一组参数是否存在差异，并从那里开始优化它。

祝你好运！

Answer 4

我已经看到过时的索引会发生这种情况。 这也可能是参数嗅探问题，其中不同的查询计划用于进入存储过程的不同参数。

您应该捕获缓慢调用的参数，并查看每次运行缓慢时它们是否相同。

您还可以尝试运行调整向导并查看它是否推荐任何索引。

您不想担心索引太多，除非您可以证明更新和插入发生得太慢（修改索引所需的时间加上锁定/争用），或者您的磁盘空间不足。