[SQL 2000] To Index or Not to Index

Question

I have a database that I used specifically for logging actions of users. The database has a few small tables that are aimed at specific types of actions. This data is rarely searched, but the rowcounts of the tables are starting to climb into multi-millions. I haven't noticed a large slow down, but I want to know if indexing the table for searching will hinder or help the performance of inserts. Inserts are performed constantly but searches don't happen so often, and the tables are just going to keep growing.

Should I create indexes on these tables? Why or why not?

Answer 1

Rather than indexes, I think you should consider having no indexes on the table where you insert the rows into, and then replicate the table(s) (and possibly apply indexes) to use specifically for querying.

Answer 2

This all depends on your empirical research. Take a copy of the database onto a different environment and run a profiler while running searches and inserts with and without indexes. Measure the performance and see what helps. :)

Answer 3

As Ray says, it's all dependent on the situation, and the only way to tell is to try it under load.

From a theoretical perspective: yes, adding indexes to a table will slow down inserts, because the DBMS has to maintain all the indexes with every insert. But will you notice? Will it matter to observed performance? Maybe not. Indexes are generally kept in B+ Tree structures, which can be inserted into in O(log n) time, which is quite good, not to mention all the disk caching, etc. So the only way to know for sure is to try it both ways and see what the difference is.

Answer 4

I'm not a Sql Server expert but I worked with a Microsoft Senior Engineer on the performance of one of our systems. According to him, the way MSSS finds a page to insert a new row into is via a "Free space scan"... a scan of every page looking for a page with space to insert the row.

If you add a clustered index to the table you force data to go in one specific place. Say you add an autonumber column to this table and make it a clustered index. Now MSSS doesn't scan for a free block, it knows that 1000 MUST go right after 999; so it does a walk of the index a instead.

I would give that a shot. Shouldn't take too long to try with 4 or 5 million rows.