SQL Server:过程、视图和函数的临时超时

Question

在我们的数据库中，我们有不同的函数、过程和视图，它们在我们的应用程序中抛出超时异常。即使在 SSMS 中，脚本也非常慢。问题只发生在清晨，早上 7 点到 9 点之间，尤其是在周一早上。其中一些脚本非常短，下午的持续时间不到一秒。

运行缓慢的会话不会被任何其他会话阻止。在此期间运行一些计划的代理作业，但这些作业也在下午运行。由于视图也会受到影响，因此不能进行参数探查，也不能对查询计划产生任何其他问题。我们不知道是什么导致了这些波动。

其中一个受影响的视图如下所示：

create view [dbo].[View01]
as
select
 A.Id                                                                                                               as Id
,A.Client_Id                                                                                                        as Client_Id
,A.Status                                                                                                           as Status
,(select count(Id) from [dbo].[Table01] where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable01
,(select count(Id) from [dbo].[Table02] where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable02
,(select count(Id) from [dbo].[Table03] where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable03
,(select count(Id) from [dbo].[Table04] where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable04
,(select count(Id) from [dbo].[Table05] where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable05
,(select count(Id) from [dbo].[Table06] where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable06
,(select count(Id) from [dbo].[Table07] where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable07
,(select count(Id) from [dbo].[Table08] where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable08
from [dbo].[Address] as A;

此视图上查询的默认持续时间约为 0.2 秒。

编辑：

在表上设置了以下索引：

Table01     nonclustered index on (Client_Id, Status) include (Adress_Id)
Table02     nonclustered index on (Client_Id, Status) include (Adress_Id)
Table03     nonclustered index on (Client_Id, Status) include (Adress_Id)
Table04     nonclustered index on (Client_Id, Adress_Id)
Table05     nonclustered index on (Client_Id, Status) include (Adress_Id)
Table06     no index
Table07     nonclustered index on (Client_Id, Status) include (Adress_Id)
Table08     nonclustered index on (Client_Id, Adress_Id)

Answer 1

这可能是由于锁定。尝试添加表提示："with (nolock("到您的查询中，如果可以进行脏读。例如：

create view [dbo].[View01]
as
select
 A.Id                                                                                                               as Id
,A.Client_Id                                                                                                        as Client_Id
,A.Status                                                                                                           as Status
,(select count(PK) from [dbo].[Table01] with (nolock) where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable01
,(select count(PK) from [dbo].[Table02] with (nolock) where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable02
,(select count(PK) from [dbo].[Table03] with (nolock) where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable03
,(select count(PK) from [dbo].[Table04] with (nolock) where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable04
,(select count(PK) from [dbo].[Table05] with (nolock) where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable05
,(select count(PK) from [dbo].[Table06] with (nolock) where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable06
,(select count(PK) from [dbo].[Table07] with (nolock) where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable07
,(select count(PK) from [dbo].[Table08] with (nolock) where Client_Id = A.Client_Id and Adress_Id = A.Id and Status = A.Status)   as CountTable08
from [dbo].[Address] as A with (nolock);

<</div> div class="one_answers">

您应该在"缓慢"期间使用sys.dm_os_waiting_tasks以查看哪个会话正在等待什么。

一旦你找到了你的等待，你可以在这里查阅它们的含义：SQL Server等待类型库。

请使用此 DMV 的结果更新您的问题，以便我们能够找到原因。