SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DTA Stopped unexpectedly


DTA Stopped unexpectedly

Author
Message
Brandie Tarvin
Brandie Tarvin
SSC Guru
SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)

Group: General Forum Members
Points: 160629 Visits: 9869

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.



Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSC Guru
SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)

Group: General Forum Members
Points: 160629 Visits: 9869
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...

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Joel Ewald
Joel Ewald
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5767 Visits: 1880
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.
Brandie Tarvin
Brandie Tarvin
SSC Guru
SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)

Group: General Forum Members
Points: 160629 Visits: 9869
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"?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Cliff Jones
Cliff Jones
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9877 Visits: 3648
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.
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48221 Visits: 715
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

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Cliff Jones
Cliff Jones
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9877 Visits: 3648
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.
Marios Philippopoulos
Marios Philippopoulos
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54164 Visits: 3784
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
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Cliff Jones
Cliff Jones
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9877 Visits: 3648
Yes, I agree. I can only get it to work with certain queries.
Marios Philippopoulos
Marios Philippopoulos
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54164 Visits: 3784
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
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search