> I heard something about a MS-SQL Server tuning tool that monitors the > activity on a database and then suggests what indexes on the tables > might improve performance. > > Am I hallucinating or is such a thing available?
Gonzo, You're referring to the combination of creating a trace in SQL Profiler and then analyzing that trace in Index Tuning Wizard, but you have to be careful or the results you get will only make things worst in some cases. It looks so simple that it's all too easy to disregard the underlying concepts required to create a proper trace in the first place. I could write an entire book just on the right way to do this, but I'll try to give a few top-level pointers: 1) Make sure you are tracing while the database contains a production-scale set of data, not some dinky sample data set. This is the biggest mistake people make, and it leads to tuning corrections that often make the database a worst performer than it was before the tuning. One reason for this is that small data sets usually lead to full table scans rather than engaging indexes, so the metrics you trace are entirely reverse of what they would be in production. 2) Trace while under a true heterogeneous (does many different seemingly unrelated things) load, driven by a realistic set of control data. For this you'll want a top-end load testing tool like e-TEST Suite from Empirix (what we use), but these tools are very expensive. The free alternatives out there can't handle this without a huge amount of programming. If you don't do this, then all your locking statistics will be completely off. 3) Most importantly, make sure your database is designed properly. Most people assume theirs are, but there are often many mistakes in them that will prevent any real optimization. I hope this helps some. If you need some telephone help on this then feel free to call me at 770-446-8866 and I'll do what I can to help you. Respectfully, Adam Phillip Churvis Member of Team Macromedia http://www.ProductivityEnhancement.com Download Plum and other cool development tools, and get advanced intensive Master-level training: * C# & ASP.NET for ColdFusion Developers * ColdFusion MX Master Class * Advanced Development with CFMX and SQL Server 2000 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:199168 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
