Index Performance

[bobbo]

I have a table that has a primary key (clustered) on column 1and no other indexes. After some analysis, I decided to create another index on column2+column3+column4+column5. To test, I restored a copy of my database (on the same server) and added the index on the copy.
If I run a stored proc that uses this table with 'include actual execution plan' turned on I can see quite dramatic results. Where before I had a clustered index scan (62%) I now have an Index seek (1%) and a key lookup (5%). This is pretty much in line with what I had hoped for. Also, the total 'estimated subtree cost' has gone from 1.08172 to 0.371683. Great.
My problem is that when I try to time the execution of the stored procedures running multiple times, I'm getting pretty much the same performance from both databases (around 700ms if I run the proc 20 times). Is there any good reason for this?

[skhanal]

Once you have the data cache, it may be influencing the performance as there is no physical IO.

You can flush buffer cache and try to see the difference. Or you can see statistics IO for logical vs physical reads.

[DBA_support]

Hi, the key locckups indicates that you index missing some columns, the best choice is that you use DTA to analize the sentence and select the best index of your query.

Regards,

[bobbo]

DBA_Support, you are taking a rather simple view. The DTA will recommend MANY indexes which will hurt insert and update performance. Therefore I must decide on the best one or two indexes that will cover as much as possible.

skhanal, I have tried the following (repeated 10 times):
checkpoint 10
dbcc dropcleanbuffers
print 'start 1: ' + convert(varchar(23), getdate(), 121)
exec mySP N'2595,10199','12/12/2003',3,75
print 'end 1: ' + convert(varchar(23), getdate(), 121)

should this give me the results I expect? would it make any difference if I changed the params each time? I don't want to clear the procedure cache each time as I don't believe this reflects what will happen in the production environment.

[rmiao]

Set statistics io on then compare io on both.

[skhanal]

It should show you better info, also turn on stats io.