July 15, 2014 at 8:13 am
I ran database tuning adviser and really bad things started to happen. First, I used a trace file that I had created from the profiler and I set the 'Database for Workload Analysis' to the production database that I was trying to learn more about the indexes that need to be added. I uncheck 'Limit Tuning time' and I kept the PDS as Indexes, NO partitioning and keep all existing PDS. So all I wanted was recommendations. It was taking the DTA a long time so I tried to stop the process. Later I looked at the activity monitor and there were spids trying to drop indexes. I compared the index name with what was actually on production and they were not the same. These spids caused locks and hindered production jobs. I tried to kill the spid but it kept creating another. I did this about ten times. Finally, I had to stop the sqlservr.exe and a few other processes on my machine. It finally died! I did not know that DTA could be so lethal!
Has anyone ever had this to happen to them? Is there another product out there that performs better and use less resources than DTA?
July 15, 2014 at 8:31 am
Never use DTA against a production database, it's incredibly dangerous. It's testing workloads so it will put a lot of load on whatever server it's pointing at. If you have to use it at all, take a trace from production and run it against a test server, get it to recommend only, not implement anything and test every single recommendation it makes as most will be unnecessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply