improving sql server query response time.

Question

I have a table which contains around 4 lakh 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.

Q1. I have indexed the fields involved in the 'where' clause. Is there a way to improve the response time any more? Q2. 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 definiton 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 jsut there to shown on the page. There are some fields (5-6) which are currently not used but I have left them in teh 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] CL ON u.WorkForceCd= CL.WorkForceCd AND u.CareerLevelCd = CL.LevelCd WHERE u.PeopleKey =

**[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

How large is your DB and how much RAM do you have on that box?

You mention indexes on the fields involved in the 'where' clause... are your index columns set in the same order as your where clause columns?

If you run the query execution plan in sql management studio do you see any table scans (you want seeks). If you see scans you might be missing indexes.

What's your primary key / clustered index? Can you switch your lookups to use the clustered index? This is always your fastest route to your data.

Hope that helps!

Answer 2

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

Answer 3

Take a look at the SQL Server Performance Dashboard. If you have obvious issues they will show up there.

Also, you shouldn't just add indexes without analyzing the queries execution plan (and see what it is looking for).

Answer 4

Join Left On - all these columns: ON u.[CareerCounselorPeopleKey] = cc.PersonnelNbr Left JOIN [CareerLevel] CL ON u.WorkForceCd= CL.WorkForceCd AND u.CareerLevelCd = CL.LevelCd

Like your WHERE clause the ON clause was for obvious key relations !

So the fat marked columns must be indexed also ...