Need 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? Below is an image of the problem Index Seek from the execution plan as well as the index and table it is using.

index:

GO
/****** 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

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?

Answer 2

rebuild the index, and calculate stats?

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

Answer 3

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.

Answer 4

Thoughts...

• Why is IX_Stu clustered? Internally, SQL Server adds a 4 byte "uniqueifier" to non-unique clustered indexes. What is the justification? This also bloats your PK too

• What is the actual query you are running?

• Finally, why FILLFACTOR 80%?

Edit:

• A "normal" FILLFACTOR would be 90%, but this is a rule of thumb only

• An 11 join query? That's most likely your problem. What are your JOINs, WHERE clauses etc? What is the full text plan?

Answer 5

What happens if you hard-code your WHERE criteria, like this:

SELECT StuCertKey, StuKey FROM stu WHERE stuKey in (/* list 50 values of StuKey here */)

If it's still very slow, you have an internal problem of some kind. If it's faster, then the index isn't your bottleneck, it's the JOINs that you're doing to create the WHERE filter.

Note that SELECT * can be very slow if there are many large columns, and especially if there are BLOBs.

Answer 6

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.

Answer 7

Some general advice: when I have to do query optimization, I start by writing out what I think the execution plan should be.

Once I've decided what I think the execution plan should be, I try to make the actual query fit this plan. The techniques to do this are different for each DBMS, and do not necessarily transfer from one to the other, or even, sometimes, between different versions of the DBMS.

The thing to keep in mind is that the DBMS can only execute one join at a time: it starts with two initial tables, joins those, and then takes the result of that operation and joins it to the next table. The goal at each step is to minimize the number of rows in the intermediate result set (more correctly, to minimize the number of blocks that have to be read to produce the intermediate results, but this generally means fewest rows).