How to improve performance on a clustered index seek

Asked 2009-12-28T19:15:08.683

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]

Edit Report

3 Answers

A clustered index range seek that returns 138 rows is not your problem.

Technically you can improve the seek performance by making the clustered index narrower:

evict all varlenght into a separate allocation unit by setting 'large value types out of row' to 1 and recreating the table from scratch).
enable page compression (SQL 2008 EE only).

Both can have quite a dramatic impact on range seek time, as they reduce the IO and the need to hit physical reads. Of course, as usually, the result will vary on a big number of other factors, like what columns do you project (evicting a projected column into BLOB allocation unit may actually have adverse effects on certain queries). As a side note, usually fragmentation will have only a marginal impact on such a short range scan. Again, it depends.

But as I say, I highly doubt this is your true problem. You have only posted selected parts of the plan and the results of your own analysis. The true root cause may lay completely elsewhere.

answered 2009-12-28T19:59:59.767

Have you tried some maintenance on this index? Like defrag it? Seems really strange that it costs THAT much (120.381). Index seek is the fastest index operation, shouldn't take that long. Can you post the query?

answered 2009-12-28T19:20:28.000

Rebuild the index, and calculate stats?

The only other way that I can think to speed it up is to partition the table, which may or may not be possible.

answered 2009-12-28T19:22:26.473

How to improve performance on a clustered index seek

3 Answers

Your Answer