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”
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:email@example.com
hei hei I an in 季庄
[…] I blogged about using Database Engine Tuning Advisor (DTA) to analyze your workload here. […]