What is Proper Database Indexing?

Much like “Beauty is in the eye of the beholder,“ “Proper database indexing is in the eye of the workload.” 

To understand how to properly index a database table, you must first know what the workload of the table is.  For example, indexing every column of a table is probably a bad idea if all you are doing is inserting rows into a table.  Indexing more than is necessary will result in an application that is slower than optimal and may result in an application that causes the database server to freeze under load.  The other extreme is to not use any indexes or have any primary keys.  While this typically allows for inserts to be performed as quickly as possible, it will result in other operations (select, delete, and update) being verrrrryyyyy slowwwwww.  Lack of proper indexing may result in a database performing the dreaded table scan, which you want to avoid as much as possible.  So, somewhere in between the two extremes is the proper set of indexes.

My ultimate suggestion is to use the proper set of indexes for your application by knowing what your application is doing with regards to the database and using the MS Sql Profiler and Index Tuning Wizard mentioned in a previous post.

Wally

2 Comments

  • Table scans are not always bad. Sometimes they are necessary depending on the SQL statement (i.e., joins often cause them). And sometimes they are faster than looking up stuff in an index, depending on the size of the table. As always, it depends.

  • In addition to having a system indexed approriately is knowing why you have an index. Interested in reading an article on .net dev from MS that said when code is changed it is verified that it has not effected the performance of the code.

    My ideal world is that I have a system that holds the indexes, and the SPs that use the indexes and the benefits to that SP. this will then allow you to assess the benefit of changing an index and adding or removing one.



    Would be great to have a system that did the same as the MS performance stuff but for SPs.

Comments have been disabled for this content.