sql - 什么会导致单表超时

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有2组#875866187

保留页数 = 17使用页数 = 11行数 = 306

保留页数 = 2使用页数 = 2行数 = 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