How to improve performance on a clustered index seek

Question

I'm trying to improve the performance on a query that is running very slowly. After going through the Actual Execution Plan; I found that a Clustered Index Seek was taking up 82%. Is there any way for me to improve the performance on an Index Seek?

Index:

/****** Object:  Index [IX_Stu]    Script Date: 12/28/2009 11:11:43 ******/ CREATE CLUSTERED INDEX [IX_Stu] ON [dbo].[stu]  (  [StuKey] ASC )WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] 

Table (some columns omitted for brevity):

CREATE TABLE [dbo].[stu](  [StuCertKey] [int] IDENTITY(1,1) NOT NULL,  [StuKey] [int] NULL  CONSTRAINT [PK_Stu] PRIMARY KEY NONCLUSTERED  (  [StuCertKey] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] 

Answer 1

I'm generalizing here, but...

A clustered index seek is, for the most part, the best-case scenario. The only ways I can think of to improve performance would be:

• Update the query to return fewer rows/columns, if possible;
• Defragment or rebuild the index;
• Partition the index across multiple disks/servers.

If it's only returning 138 rows, and it's that slow... maybe it's being blocked by some other process? Are you testing this in isolation, or are other users/processes online at the same time? Or maybe it's even a hardware problem, like a disk failure.