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.

Answer 2

Clustered Index seeks occur when non-clustered indexes are used and aren't necessarily bad.

Consider the following query:

SELECT s.StuKey, s.Name, s.Address, s.City, s.State FROM stu s WHERE State='TX'

If there is only a clustered index on StuKey, then Sql Server only has 1 option, it must scan the entire table looking for rows where State="TX' and return those rows.

If you add a non-clustered index on State

CREATE INDEX IX_Stu_State on Stu (State)

Now Sql server has a new option. It can choose to seek using the non-clustered index, which will produce the rows where State='TX'. However, in order to get the remaining columns to return in the SELECT, it has to look up those columns by doing a clustered index seek for each row.

If you want to reduce the clustered index seeks, then you can make your index "covering" by including extra columns in it.

 CREATE INDEX IX_Stu_State2 on Stu (State) INCLUDE (name, address, city )

This index now contains all the columns needed to answer the query above. The query will do an index seek to return only the rows where State='TX', and the additional columns can be pulled out of the non-clustered index, so the clustered index seeks go away.

Answer 3

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

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).

Answer 6

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 7

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 8

Check the index statictics.

reCalculating the clustered-index statistics will solve the problem.

in my case, i was looking for 30 records in 40M recored. the execution plan says it's going through the clustered-index but it took about 200ms. and the index wasn't defragmented. after recalculating it's stats, it's getting done under 10ms!

Answer 9

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.