This case was raised to me by my colleagues in the company. I thought it would be nice to write this on my weblog. This isn’t a technical article but rather a reflection on when I think is the right time to create indexes based from the experiences I have been through every time I design databases and use them. I am not sure if this is a good practice but it has worked for me ever since I got hooked with SQL Server since version 6.5.
So when do I start creating indexes? Read on …
When I layout my DB objects, I seldom define indexes right away. But of course I make notes as to where and what kind of indexes I’ll be creating later on. I let the database drag on for quite sometime during the development stage without my custom indexes. I let myself write queries without the benefit of indexes. I also allow for sometime that the database gets populated with enough data. Most often, I start creating indexes when the system is almost feature complete and most of the complex queries were written and run against some amount of data. We usually call this phase the load testing phase.
Why do I do this? Here are my two major reasons:
- Indexes can improve your query performance drastically. During the query writing phase, having indexes can give you an impression that your scripts are blazingly fast. This can be worsen by the fact that typically, your database contains just a token amount of data.
- Since we all create optimal indexes based on the kind of queries sql server will often encounter and process, creating indexes too early could potentially give you skewed performance impression.
Both reasons can potentially affect your capability to detect query performance problems.
By having the indexes created at a later stage of the development phase, you subject your database to work harder without the benefits of indexes. Performance problems will begin to appear if you have crappy queries. Still, at this point, resist creating an index. Exhaust your means to optimize the query without the benefit of indexes. If you want to create indexes at this point for the sake of testing, just be sure to delete them after the testing.
Doing this will give you a certain level of assurance that you are writing optimized queries without the benefit of indexes. You will even appreciate this practice when there are too many in your team writing so many queries. Once you have the indexes, your queries will be lightning fast.
So how about you? When do you create your indexes?
**************************************************
Toto Gamboa is a consultant specializing on databases, Microsoft SQL Server and software development operating in the Philippines. He is currently a member and one of the leaders of Philippine SQL Server Users Group, a Professional Association for SQL Server (PASS) chapter and is one of Microsoft’s MVP for SQL Server in the Philippines. You may reach him by sending an email to totogamboa@gmail.com
totogamboa.wordpress.com 