title: improving sql server query response time
body: <p>I have a table which contains around 400 000 records and which gets called on the homepage of the intranet website. At peak times we can have 300-400 concurrent users. The SQL Profiler tool gives the following output.</p>
  
  <ul>
  <li>CPU: 406</li>
  <li>Reads: 32446</li>
  <li>Duration: 397</li>
  </ul>
  
  <p>I have indexed the fields involved in the 'where' clause. Is there a way to improve the response time any more?</p>
  
  <p>What needs to be done to reduce the disk reads?</p>
  
  <p>Server configuration: 
  Windows 2003 64bit, SQL Server 2005 64bit SP2, .NET 2.0.</p>
  
  <p>Added the query and table definition below. There are some 40 more fields which I have not added here for sake of simplicity. Those fields which are mostly varchar are not used in the where clause. They are just there to shown on the page. There are some fields (5-6) which are currently not used but I have left them in the query because they will be required later. Should I take them off now then? would that improve the response time? </p>
  
  <p><strong>Query</strong></p>
  
  <pre><code>SELECT 
      u.[PeopleKey], 
      u.[EnterpriseId],   
      u.[PersonnelNbr],   
      u.LastName,   
      u.FirstName,   
      u.MiddleName,    
      cc.WorkForceCd AS CareerCounselorWorkForceCd,                     
      cc.WorkForceDesc AS CareerCounselorWorkForceDesc,                    
      cc.WorkGroupCd AS CareerCounselorWorkGroupCd,                   
      cc.WorkGroupDesc AS CareerCounselorWorkGroupDesc,                 
      cc.CareerLevelCd As CareerCounselorCareerLevelCd,               
      cc.CareerLevelDesc AS CareerCounselorCareerLevel,             
      CL.NextLevelCD as  nextCareerLevelCd
  FROM 
      [User] u 
      LEFT JOIN [User] cc ON 
          u.[CareerCounselorPeopleKey] = cc.PersonnelNbr  
      Left JOIN [CareerLevel] CLON 
          u.WorkForceCd= CL.WorkForceCd AND 
          u.CareerLevelCd = CL.LevelCd
  WHERE 
      u.PeopleKey = &lt;integer&gt;
  </code></pre>
  
  <p><strong>[CareerLevel]</strong></p>
  
  <pre><code>ID  int 4 [Primary Key - clustered index]
  Description varchar 150
  WorkforceCd varchar 4
  LevelCD varchar     10
  NextLevelCD varchar 10
  </code></pre>
  
  <p><strong>[User]</strong></p>
  
  <pre><code>PeopleKey   int 4 [Primary Key - clustered index]
  EnterpriseId    varchar 50 [non clustered index]
  PersonnelNbr    varchar 8 [non clustered index]
  FirstName   varchar 40
  LastName    varchar 40
  MiddleName  varchar 40
  CareerCounselorPeopleKey    int 4
  CareerCounselorPersonnelNbr varchar 8
  CareerCounselorName varchar 50
  CapabilityCd    varchar 5
  CapabilityDesc  varchar 25
  WorkforceCd varchar 4
  WorkForceDesc   varchar 40
  WorkGroupCd varchar 4
  WorkGroupDesc   varchar 50
  CareerLevelCd   varchar 10
  CareerLevelDesc varchar 50
  </code></pre>