Index Tuning Wizard

I've been finding out that a lot of VB people out there are getting stuck with the role of the DBA.  They can create the tables, data integrity stuff, stored procedures and all that just fine.  They might even pick up some security and backup knowledge.  But something that many just don't have the time to learn proper is indexes and the index architecture.  Some really cool stuff in there, but many people are too busy creating apps to worry about creating an index that might perform a little better than what we have out there already.

Well, there is a tool in SQL2k called the index tuning wizard.  What you do is you start SQL Profiler, connect to your server, and have choose a "SQLProfilerTuning" trace.  It will sit there keeping track of what queries are going into your database.  You let that guy run for a while, long enough to get a good sampling of your database.  Now, if Scott was going to run it on dotnetweblogs.com, he could probably just leave it running for an hour and check out how it is used over that period.  That usage really doesn't change over time.  However, If I was to only run the trace in the morning on our data, I'd get a pretty high level of reads as our sales people hop on their database in the morning.  I'd also miss out on the writes in tthe afternoon as they start entering scheduling, and client notes.  Plus the end of day reports.  I should probably run it for a whole day.  Now, you should run the trace for a two hours, four hours, a day, or a whole week.  Only you know how long is appropriate for your data.  Just make sure you have enough space to write the trace to :)

After you have your trace data, you need to run the Index Tuning wizard.  You can find it in your Tools menu of SQL Profiler (it's also in the Wizard in Enterprise Manager).  This will examine the queries being ran, and then give you some "strategies" on how to improve performance.  Pretty neat stuff.

Now, this wizard of course can never "out index" someone with a good understanding of the index architecture, and their data.  But I also understand not ever VB developer is thrilled at learning the rather complex index architecture of SQL Server.  It is some pretty cool stuff though :)