SQL Server 2008 indexes - performance gain on queries vs. loss on INSERT/UPDATE

How can you determine if the performance gained on a SELECT by indexing a column will outweigh the p开发者_JAVA百科erformance loss on an INSERT in the same table? Is there a "tipping-point" in the size of the table when the index does more harm than good?

I have table in SQL Server 2008 with 2-3 million rows at any given time. Every time an insert is done on the table, a lookup is also done on the same table using two of its columns. I'm trying to determine if it would be beneficial to add indexes to the two columns used in the lookup.

---

Like everything else SQL-related, it depends:

• What kind of fields are they? Varchar? Int? Datetime?
• Are there other indexes on the table?
• Will you need to include additional fields?
• What's the clustered index?
• How many rows are inserted/deleted in a transaction?

The only real way to know is to benchmark it. Put the index(es) in place and do frequent monitoring, or run a trace.

---

This depends on your workload and your requirements. Sometimes data is loaded once and read millions of times, but sometimes not all loaded data is ever read.

Sometimes reads or writes must complete in certain time.

---

case 1: If table is static and is queried heavily (eg: item table in Shopping Cart application) then indexes on the appropriate fields is highly beneficial.

case 2: If table is highly dynamic and not a lot of querying is done on a daily basis (eg: log tables used for auditing purposes) then indexes will slow down the writes.

If above two cases are the boundary cases, then to build indexes or not to build indexes on a table depends on which case above does the table in contention comes closest to.

If not leave it to the judgement of Query tuning advisor. Good luck.