So I'm looking to assist the developers at my employer, who have been working on an SQL based application. The problem is, they don't know indexing (and I'm still enough of a newb to know I don't know everything I should know) and haven't put any indexes in other than a clustered index on a automatically incremental "KeyID" column (data type Int.)
Now, I've seen a few things that might help (such as a covering index on some fields that store names and acct numbers, a full text index on some description fields that get searched,) but I'd like to be able to more specifically target improvements. My thoughts thus led naturally to thinking of setting up a SQL Profiler run to generate a workload, then feeding this into the DTA.
Now, I just want to confirm a couple things on this, before I go making the suggestion:
1. Optimally, the Profiler run will be on a production system, and run for several hours.
2. The Profiler process will have some impact on the performance during this time (thankfully these aren't OLTP with thousands of transactions a second.)
3. The best option to then use DTA would be a backed up copy of the production DB, on a separate system, to "consume" the workload information, without further impacting the production system.
4. Then I / we should review the DTA suggestions before implementing anything.
Sound like a good plan? Or at least the outline of a plan?
PS. If you're wondering why the ostensible DBA isn't the one completely responsible for the DB, it's a combination of office politics, institutional inertia (the devs were also the ones responsible for the Foxpro-based system and its tables), and a lack of understanding of what a DBA is intended to do (which to me seems to be almost equal parts developer and administrator.)