会导致单个表超时的原因

Question

我收到以下错误，仅适用于在ReportingPeriod表上运行的查询(请参阅下文(。即使我在另一个表上注释掉相关的LEFT JOIN，也会发生这种情况。表中的记录不超过200条。此外，无论是直接运行查询还是使用存储过程运行查询都无关紧要。最后，其他表格&程序运行良好。

知道是什么原因造成的吗？

版本
Sql Server 2005

错误：
超时已过期。在操作完成或服务器没有响应之前经过的超时时间。

原始查询：

DECLARE @ProjectsKey INT
SET @ProjectsKey = 1234
-----------------------------
    SELECT 
        ReportingPeriodKey
        ,ReportingPeriod.ProjectsKey
        --,Phase.PhaseKey AS PhaseKey
        --,Phase.Name AS PhaseName
        ,[Type]
        ,ReportingPeriodStart
        ,ReportingPeriodEnd
    FROM   
        ReportingPeriod
--  LEFT JOIN
--  (
--      SELECT
--          PhaseKey
--          ,ProjectsKey
--          ,Name
--      FROM dbo.Phase
--  ) AS Phase ON Phase.PhaseKey = dbo.ReportingPeriod.PhaseKey
    WHERE
        ((@ProjectsKey IS NOT NULL AND ReportingPeriod.ProjectsKey = @ProjectsKey) OR @ProjectsKey IS NULL)
    ORDER BY
        ReportingPeriodStart

表格定义：

SET ANSI_NULLS ON GO SET
QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[ReportingPeriod](
    [ReportingPeriodKey] [int] IDENTITY(1,1) NOT NULL,  
    [ProjectsKey] [int] NOT NULL,   
    [PhaseKey] [int] NOT NULL, [Type] [nvarchar](250) NOT NULL,     
    [ReportingPeriodStart] [datetime] NOT NULL CONSTRAINT [DF_ReportPeriod_Start]  DEFAULT (getdate()),     
    [ReportingPeriodEnd] [datetime] NOT NULL CONSTRAINT [DF_ReportPeriod_End]  DEFAULT(getdate()),  
    [CreatedBy] [nvarchar](100) NOT NULL,
    [CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_ReportingPeriod_CreatedDate] DEFAULT (getdate()),  
    [ModifiedBy] [nvarchar](100) NULL,  
    [ModifiedDate] [datetime] NULL,  
    CONSTRAINT [PK_ReportPeriod] PRIMARY KEY CLUSTERED  ( [ReportingPeriodKey] ASC )WITH (PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]
GO 
ALTER TABLE [dbo].[ReportingPeriod] WITH CHECK 
ADD  CONSTRAINT [FK_ReportingPeriod_attrReportingPeriodType]
FOREIGN KEY([Type]) REFERENCES [dbo].[attrReportingPeriodType] ([FullName]) 
GO 
ALTER TABLE [dbo].[ReportingPeriod]  
CHECK CONSTRAINT [FK_ReportingPeriod_attrReportingPeriodType]
GO 
ALTER TABLE [dbo].[ReportingPeriod] WITH CHECK 
ADD  CONSTRAINT [FK_ReportingPeriod_Phase] 
FOREIGN KEY([PhaseKey]) 
REFERENCES [dbo].[Phase] ([PhaseKey]) 
GO 
ALTER TABLE [dbo].[ReportingPeriod] 
CHECK CONSTRAINT [FK_ReportingPeriod_Phase]
GO 
ALTER TABLE [dbo].[ReportingPeriod] WITH CHECK 
ADD  CONSTRAINT [FK_ReportingPeriod_Projects] 
FOREIGN KEY([ProjectsKey]) 
REFERENCES [dbo].[Projects] ([ProjectsKey]) 
GO
ALTER TABLE [dbo].[ReportingPeriod]
CHECK CONSTRAINT
[FK_ReportingPeriod_Projects]

系统状态
object_id#875866187 有2个集合

reserved_page_count=17used_page_count=11row_count=306

reserved_page_count=2used_page_count=2row_count=306

Answer 1

您是否尝试过检查可能锁定表的打开事务？

dbcc opentran
exec sp_who 69
-- Where the id is the SPID from DBCC OPENTRAN
exec sp_lock 69
-- Where the id is the SPID from DBCC OPENTRAN
select * from sys.objects where object_id = 2089058478
-- Where the id is the ObjID from sp_lock

Answer 2

检查查询是否被阻止，记下SQL语句或进程的sessionID(位于查询窗口底部(，然后激发下面的查询，检查sessionID的BlkBy列是否为空

EXEC SP_WHO2 

还要在一个窗口中执行您的查询，然后在另一个窗口执行下面的查询，看看是否有其他进程正在运行，这些进程锁定了表

 select text, session_id,
start_time, status, db_name(database_id) as DBName, blocking_session_id,
wait_type, wait_resource
from sys.dm_Exec_requests der
cross apply
sys.dm_exec_sql_text (der.sql_handle) 

同时测试并更改允许脏读取的事务隔离级别作为最终选项，如下所示：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

现在，您还可以使用这个DMV检查任何当前正在运行的事务，并在相关表上查找任何Xcluster锁的request_mode列。

SELECT * FROM SYS.DM_TRAN_LOCKS

Answer 3

某些东西很可能锁定了您选择的表中的一个表。您可以通过在proc执行时运行以下查询来测试proc是否被阻止

select spid, blocked, login_time, nt_username, hostname, program_name 
from sys.sysprocesses

希望这能有所帮助。

Answer 4

除非您担心从表tReportingPeriod中读取脏数据，否则您可以使用：

FROM ReportingPeriod WITH (NOLOCK)

在您的查询中。