improving sql server query response time

Question

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.

• CPU: 406
• Reads: 32446
• Duration: 397

I have indexed the fields involved in the 'where' clause. Is there a way to improve the response time any more?

What needs to be done to reduce the disk reads?

Server configuration: Windows 2003 64bit, SQL Server 2005 64bit SP2, .NET 2.0.

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?

Query

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 = <integer>

[CareerLevel]

ID  int 4 [Primary Key - clustered index]
Description varchar 150
WorkforceCd varchar 4
LevelCD varchar     10
NextLevelCD varchar 10

[User]

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

Answer 1

Can you publish the table schema and the index definitions? What do you queries look like?

The amount of Reads would hint at massive I/O operations - so maybe due to your table and index setups and/or the way you query, even though you have indices, they're not being used.

Do you regularly run maintenance plans on the database? E.g. do your indices get reorganized and/or rebuilt when index fragmentation gets too high?

You could always use SQL's Database Tuning Advisor to try and get hints about how to optimize certain queries - or better yet, collect some real-life usage data using a SQL Trace and analyze that for potential tuning steps.

Marc