Database Indexing - SQL Server Tools
Proper Database Indexing is important with any application. It is even more important with the applications that I am involved with due to the large amount of data, number of transactions, and number of users of the application that I am typically involved with. As I have been working on my Web Search Engine with .NET, I have learned about the importance of proper database indexing once again. First, let's look at the tools with Sql Server. Sql Server comes with two really good tools for performance tuning:
Sql Profiler. Sql Profiler gives you information about the operations that are being sent to your computer. It provides you with info about the command itself, the number of reads and writes performed, and most importantly to me, the duration of the operation. As I first worked on the Spider part of my Web Search Engine, I saw from using the Sql Profiler, that my database would quickly become a bottleneck. Operations that initially took 0-10 msec, were taking 2000 msec and more after the database tables had any significant amount of data in them. I began by looking at the commands that took the longest and wrote some indexes that were optimized for those operations. After I applied each index, the commands took less and less time. That's great, but what if you don't want to write your own indexes? Well, MS has a tool for you.
Index Tuning Wizard. Given the data that Sql Profiler generates, the Index Tuning Wizard can generate a set of indexes for a given workload. Trust me, it works great. I used it to verify that my indexes that I created were good, which the indexes were.
Using the Sql Profiler and Index Tuning Wizard, I have been able to create operations that run against millions of records in 0-10 msec as oppossed to 2,000, 4,000, or 40,000 msec against those same millions of records.