September 12, 2007 at 8:38 am
Anyone know where I can find details on why my DTA stopped? It keeps halting on the very first part of the process, "Submitting Configuration Information". And the only error message it's giving me is "Tuning Process exited unexpectedly.(DTAEngine)".
The first time around, I thought it was because of the time limit set on the DTA, so I moved the "Limit Tuning Time". This time, though, it stopped well before the time limit hit, and on the exact same step.
Unfortunately, I can see anything in the SQL Server or SQL Server Agent Logs that gives me a clue on why it's choking on my tuning workload. My original workload isn't terribly huge. It's a trace file based off the Tuning template in Profiler and is less than 19 MB in size.
This is my first time using the DTA, so thoughts would be appreciated. I did have to add the procs and tables to MSDB before I could get DTA to work (for some reason, they're missing in all our instances) and I'm running 2k5 SP1 + hotfix.
November 9, 2007 at 8:23 am
Has anyone ever encountered the problem I've described in the post above? Any information on DTA would be greatly appreciated.
I don't even know why it didn't install properly in the first place, but I have all the stuff installed now and it still won't work.
Thanks in advance to anyone with any suggestions. I'm desperate because I can't run a tuning load and I know for a fact that the DB I'm trying to tune has some crap indexes. I just don't know which are the good and which are the bad...
November 9, 2007 at 10:28 am
did the statement you were trying to tune have a temp (#) table in it?
I have seen the advisor error when trying to tune a procedure using one.
November 9, 2007 at 11:05 am
Joel,
I honestly didn't check. I just ran a normal tuning Profiler trace against our production DB during business hours and then ran the DTA against that. I wasn't trying to tune one specific query. Just "everything".
I'm pretty sure we do use temp tables in a lot of our procs. But doesn't that defeat the purpose of the Tuning Advisor if you can't use it against just "any ole query"?
March 27, 2008 at 12:49 pm
Brandie,
Did you ever determine what was causing this problem? I am suddenly having this problem as well. DTA runs for about a half hour on the 'Submitting Configuration Information' step and then throws the same error you reported. This occurs on the simplest of queries but it seems to hang before it gets to the workload. It was working fine on Feb 12 and then stopped working on Feb 21 so I know that my installation was fine. I installed CU 4 and it still has the same problem.
March 27, 2008 at 3:10 pm
I've found the DTA will fail around 50% of the time I use it. I've never given it much thought and my general view is that you can extract the same info from the dmvs as you can from the DTA , stop your server to clear the dmvs, run your query and then see what missing indexes are suggested by the dmvs
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 27, 2008 at 3:15 pm
Thank you for the response. And yes I employ that method as well. I use the DMV's more than DTA but sometimes it is handy to be able to point the DTA at a workload when it is not feasable to bounce the service.
March 27, 2008 at 4:03 pm
I think the DTA still needs a lot of work on behalf of the SQL Server dev team.
I have only been able to get it to work on individual queries with no temp tables and no parameters - just hard-coded values!
Still useful if you have a gigantic query and you want to get index recommendations on it, but still not nearly enough!
The claim that the DTA can analyze trace workloads is simple bogus, at least in my experience.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 27, 2008 at 4:14 pm
Yes, I agree. I can only get it to work with certain queries.
March 27, 2008 at 4:14 pm
Since DTA stops unexpectedly, run the following statement to ensure there are no hypothetical indexes left behind:
SELECT *
FROM sys.indexes
WHERE is_hypothetical = 1
Drop any such indexes from your database.
Hypothetical indexes are created by the DTA in the process of its analysis. Provided that the DTA completes gracefully, these indexes are dropped automatically. However, an abrupt DTA stop can cause these indexes to linger and litter your database, even posing a performance hazard!
See:
http://www.graytechnology.com/Blog/post.aspx?id=e21cbab0-8ae2-478e-a027-1b3b14e7d0b9
http://weblogs.sqlteam.com/mladenp/archive/2007/07/16/60257.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 28, 2008 at 7:08 am
I have never figured out what the problem was. Even posted on Microsoft and got a lukewarm response. THough someone suggested the issue had to do with Temp tables, which I find utterly ridiculous. Granted, they may be correct that it's temp tables causing the problems, but why won't the DTA handle them?
I asked that question and never got a response. It's seriously annoying. The DMVs haven't been quite as useful to me as I want them to be so I really wanted to see what the DTA said.
I've never been able to use it since the main thing I want to troubleshoot is the one set of queries which causes the stupid thing to bomb out. And every time I ask a "SQL Expert" what the problem might be, their eyes glaze and they change the subject.
Bleargh!
March 28, 2008 at 7:29 am
Mine hangs on the simplest of queries (select * from table) and then eventually throws the generic error that you mentioned.
Since it hangs at "Submitting Configuration Information" I don't think it ever gets far enough to start working on the workload and making hypothetical indexes. I checked however and none exist. I ran profiler and the only call I ever saw from DTA was the call to sp_DTA_help_session. At least that proved to me that it was connecting to my database.
I agree with Colin that it is very finicky and only works occassionally. It definitely needs work and doesn't seem to be production quality.
March 28, 2008 at 7:43 am
Did yours get installed properly to begin with or did you have to manually install it like I did?
March 28, 2008 at 7:49 am
Mine was installed properly to begin with and was working. It suddenly stopped working. The only thing I know that changed was a Microsoft Windows update. We then installed CU4 but it still does not work.
Quest's Toad for SQL Server is a pretty good tool for this purpose but I like the price of the DTA.
March 28, 2008 at 5:10 pm
don't you have another server to use?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply