What would cause timeouts on a single table

I am getting the following error ONLY for queries run on the ReportingPeriod table (see below). This happens even when I comment-out the related LEFT JOIN on another table. There aren't more than 200 records in the table. Additionally, it doesn't matter if I run the query outright or run it using the stored procedure. Lastly, other tables & procedures run fine.

Any idea what may cause this?

VERSION

Sql-Server 2005

ERROR:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

RAW QUERY:

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

TABLE DEFINITION:

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开发者_如何学JAVA [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]

SYSTEM STATS

There are 2 sets for object_id #875866187

reserved_page_count = 17 used_page_count = 11 row_count = 306

reserved_page_count = 2 used_page_count = 2 row_count = 306

---

Have you tried checking for open transactions that may be locking the table?

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

---

Check if the query is being blocked, note the sessionID (seen at the bottom of query window) of the SQL statement or proc and then fire below query, check if BlkBy column is not empty for your sessionID

EXEC SP_WHO2 

Also execute your query in one window and then in another window execute below query to see if any other processes are running that are having a lock on the table

 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) 

Also test and change the transaction isolation level allowing dirty reads as a final option as below:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Now you can also check for any currently running transactions using this DMV and look for request_mode column for any Xclusive locks on the table concerned.

SELECT * FROM SYS.DM_TRAN_LOCKS

---

Something most likely has a lock on one of the tables in your select. You can test whether your proc is being blocked by running the below query while you proc is executing

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

Hope this helps.

---

Unless you are worried about dirty reads from the table tReportingPeriod you could just use:

FROM ReportingPeriod WITH (NOLOCK)

in your query.