DTA works against Sql Server 2000 databases

Index Tuning Wizard morphed into Database Engine Tuning Advisor (DTA) in Sql Server 2005. The idea is still the same. You feed it with some profiling results, and it will make some recommendations.

Sql Server Profiler 2005 still has the same look and feel of Profiler 2000. It does have some enhancement when picking event columns to capture. Please note that you can open a Sql Server 2000 trace file with Profiler 2005, but not the other way around.

I recently used Sql Server 2005 Profiler against a Sql Server 2000 database. I used the Tuning template and saved the results to files, allowing file rollover and setting maximum file size to 15 MB.

I then fed the results into DTA. DTA gave me some recommendations on indexes and statistics. You do not need to apply those recommendations right away. You may want to go to Actions menu and choose Save Recommendations… This will give you a nice script that you can review.

One advice: you probably want to run DTA on a more powerful machine, if you can. I ran it first on my workstation, with Intel Centeno 2.1 GHz and 2 GB of memory. I fed DTA with one 15 MB of trace file. After 10 minutes or so, only 1% of the file was analyzed. I then transferred the file to a 64-bit Opteron server with a lot of memory and did the analysis there. The whole thing took less than 1 minute. Really impressive.

5 responses to “DTA works against Sql Server 2000 databases”

  1. my name is Ji Jialiang,in xu zhou of jiang su.I see your blog.I am a pragramer.I hope that I can with you a friend.I am in nan jing .learn C# , Oracle,SQL Server 2005.Now I have an question,hope you can help.Thanks.this is my Email:feidragon319@163.com

  2. 你好,季家亮!哈,在网上遇到本家了!


  3. 那次我在网上偶然的机会,看到你的网址,发现是远亲,才留的言,这次在网上搜索东西才又看到的.这个是我的邮件,发邮件吧,我也很少能看到你的这个网页.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.