title: SQL Server procedure timing out
body: <p>I'm having intermediate timeouts when calling this stored procedure from ASP.NET frontend environment on a production site. it returns the following sql exception:</p>
  
  <blockquote>
    <p>Exception Details: System.Data.SqlClient.SqlException: Timeout
    expired.  The timeout period elapsed prior to completion of the
    operation or the server is not responding.</p>
  </blockquote>
  
  <p>Funny thing is, when executing this procedure on the server, or from a remote pc using Management Studio, it executes in 6s. But yet, sometimes times out from when executed from the ASP.NET application? Can this query be improved? Or is this issue related to something else? Anyone who can help? I;ve read some threads about increasing the timeout and enabling pooling on the connectionstring in my web.config, but haven't tried that yet.</p>
  
  <pre><code> ALTER PROCEDURE [dbo].[Report_Activity]
      (
          @StartDate      DATETIME
      ,   @EndDate        DATETIME
      ,   @TotalActions INT OUTPUT
      )
  
      AS
      BEGIN 
  
          SELECT      @TotalActions = COUNT(EventHistoryId)
          FROM        dbo.SessionEventHistory
          WHERE       DateCreated BETWEEN @StartDate AND @EndDate
  
          SELECT      EventDescription, COUNT(EventHistoryId) AS EventCount           
          FROM        dbo.SessionEventHistory
          WHERE       DateCreated BETWEEN @StartDate AND @EndDate
          GROUP BY    EventDescription
          ORDER BY    EventDescription
  </code></pre>
  
  <p>SQL SCHEMAS:</p>
  
  <pre><code>CREATE TABLE [dbo].[SessionEventHistory](
      [EventHistoryID] [int] IDENTITY(1,1) NOT NULL,
      [SessionHistoryID] [int] NOT NULL,
      [CategoryID] [int] NULL,
      [UserName] [nvarchar](50) NULL,
      [IPAddress] [nvarchar](20) NOT NULL,
      [EventDescription] [nvarchar](1000) NOT NULL,
      [EventData] [varbinary](max) NULL,
      [DateCreated] [datetime] NOT NULL,
   CONSTRAINT [PK_UserEventHistory] PRIMARY KEY CLUSTERED 
  (
      [EventHistoryID] 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].[SessionEventHistory] ADD  CONSTRAINT [DF_UserEventHistory_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
  GO
  </code></pre>
  
  <p>Table has 3 indexes:</p>
  
  <pre><code>/****** Object:  Index [IX_SessionEventHistory_SessionHistoryId_CategoryId]    Script Date: 07/04/2012 10:47:06 ******/
  CREATE NONCLUSTERED INDEX [IX_SessionEventHistory_SessionHistoryId_CategoryId] ON [dbo].[SessionEventHistory] 
  (
      [SessionHistoryID] ASC,
      [CategoryID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  
  
  /****** Object:  Index [IX_SessionEventHistory_UserName_DateCreated]    Script Date: 07/04/2012 10:47:09 ******/
  CREATE NONCLUSTERED INDEX [IX_SessionEventHistory_UserName_DateCreated] ON [dbo].[SessionEventHistory] 
  (
      [UserName] ASC,
      [DateCreated] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  
  
  /****** Object:  Index [PK_UserEventHistory]    Script Date: 07/04/2012 10:47:14 ******/
  ALTER TABLE [dbo].[SessionEventHistory] ADD  CONSTRAINT [PK_UserEventHistory] PRIMARY KEY CLUSTERED 
  (
      [EventHistoryID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>I added the following Index, does it look okay?</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX [IX_SessionEventHistory_DateCreated] ON [dbo].[SessionEventHistory] 
  (
      [DateCreated] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  GO
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/PKKeg.jpg" alt="enter image description here"></p>