title: What would cause timeouts on a single table
body: <p>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 &amp; procedures run fine.</p>
  
  <p>Any idea what may cause this?</p>
  
  <p><strong>VERSION</strong><br />
  Sql-Server 2005</p>
  
  <p><strong>ERROR:</strong><br />
  Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.</p>
  
  <p><strong>RAW QUERY:</strong><br /></p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>TABLE DEFINITION:</strong><br /></p>
  
  <pre><code>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]
  </code></pre>
  
  <p><strong>SYSTEM STATS</strong><br />
  There are 2 sets for object_id #875866187</p>
  
  <p>reserved_page_count = 17
  used_page_count = 11
  row_count = 306</p>
  
  <p>reserved_page_count = 2
  used_page_count = 2
  row_count = 306</p>