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]

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

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

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

Solution 4

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

Solution 5

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?

View more solutions

Comments

[Abe Miessler]

Any suggestions on how much de-fragmentation is too much de-fragmentation?

[Abe Miessler]

In regards to your first question the I made IX_Stu clustered because it is going to be used for the most joins. I thought that would improve performance, am I mixed up here? Second question, I'd rather not post the query up on the internet if I can avoid it. It's very large with eleven joins if that helps you at all... Last question: I created the index through management studio and not by a query. Apparently management studio added the FILLFACTOR 80%. Could that potentially cause problems?

[Abe Miessler]

There could be a couple of other users on but not many. Also this is a DW query. If there are no updates going on in the db then there shouldn't be any locks that would prevent me from reading, right?

[Aaronaught]

Some other process might be doing a SELECT * FROM stu with no filter, retrieving all 8 million rows; that would definitely slow other queries down due to the I/O bottleneck. Unlikely, but possible.

[Abe Miessler]

Interesting. Say that I know that a certain join reduces my set from 6 million rows down to about 500,000. Does it improve performance to have that Join come before the other joins or maybe create a CTE that holds the contents of that Join and then use that going forward?

[Abe Miessler]

Tried rebuilding indexes and stats. no luck though. I might be looking into partitioning the tables next. Is that a good solution for slow seeks?