Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
druer
Constraint Violating Yak Guru
314 Posts |
 Posted - 2006-08-31 : 10:24:06
|
| Background: I have a situation where I have a very highly active that currently has 1 index supplied by the vendor. I've found that by a mile the most active command in the system does a select against this table that requires a bookmark lookup for 1 extra field not included in their index. However, their index (unclustered) happens to be unique. So I can't just replace that index or add the 1 extra field needed to cover it to that same index. So I need to add another index.New Index: I know that adding a new index will cost SQL Server some amount of time to constantly maintain it due to the fact that the table is constantly changing inserts/updates/deletes as it is recording who is in the system, what screen they are on and what they are doing (saving data etc.). Thought Process: I happen to have three distinct drives that I can utilize. Meaning right now the data file itself is on 1 drive, and the 1 existing index is on a different drive. I can create the new index on the remaining drive. So my thought is that the net negative impact will likely not even be felt since it could write to the existing index and the new index at that the same time. Thus the overall impact could only be improvement since the select command could be covered and avoid the bookmark lookup by using just this covering index and it is called about 10 times more often than changes actually occur.Any thoughts, comments, opinions?Thanks,Dalton |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-31 : 10:45:45
|
| This is why v2005 allows you to include columns in the index without indexing them.Bit surprised that this table has a lot more reads than updates if it is for recording what people are doing.Not sure if the extra drive will make much difference as I would expect this to be cached but it won't hurt.The only real way to find out is to try it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-08-31 : 11:51:07
|
| I was so bogged down in the "reads" themselves I hadn't even brought my head up to distinquish the logical from the physical and realize that since this table is only about 400 rows most of the time, and due to frequency of access the entire thing would likely remain in cache and not really go back to the table on disk.The check is in the mail :)Thanks,Dalton |
|
|
|
|
|
|
Indexing Performance OPINION/FACT