Anyone know where I can find details on why my DTA stopped? It keeps haltin
g
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.
Catadmin
--
MCDBA, MCSA, MCTS
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)Try focusing in on just a tiny part of the database to begin with. For
example, find a long running query that hits several tables. Turn on the
profiler, let it run. Now run the long running query about three times. Go
back to the profiler and save the trace. Open the trace in the tuning
advisor. Browse to the trace file. Select the database(s) that have the
tables you tested in the long running query. Select only those tables and
use only one of the databases. If you have more than one database you can
reuse the same trace. Let it run against those smallest of parameters.
Generally you will get results. Sometimes there isn't enough information in
the trace to kick off the process. At least that's my take on it.
--
Regards,
Jamie
"Catadmin" wrote:
> Anyone know where I can find details on why my DTA stopped? It keeps halt
ing
> on the very first part of the process, "Submitting Configuration
> Information". And the only error message it's giving me is "Tuning Proces
s
> exited unexpectedly.(DTAEngine)".
> The first time around, I thought it was because of the time limit set on t
he
> DTA, so I moved the "Limit Tuning Time". This time, though, it stopped we
ll
> before the time limit hit, and on the exact same step.
> Unfortunately, I can see anything in the SQL Server or SQL Server Agent Lo
gs
> that gives me a clue on why it's choking on my tuning workload. My origin
al
> 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 wor
k
> (for some reason, they're missing in all our instances) and I'm running 2k
5
> SP1 + hotfix.
> Catadmin
> --
> MCDBA, MCSA, MCTS
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
'
> @.=)|||Be VERY leery of using DTA. It does REALLY bad things sometimes - like
creating indexes that contain 80% of the columns in the table, multiple
indexes with nothing more than additional columns added to the set, etc. I
advise my clients to not use it.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:179FC3C7-2370-47CF-BF72-6F574B0280E9@.microsoft.com...
> 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.
> Catadmin
> --
> MCDBA, MCSA, MCTS
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the
> back?
> @.=)|||Jamie,
Hmm. Not sure why it wouldn't have enough information since the trace ran
off of Production during business hours, but I'll do as you suggest in a tes
t
DB. See if that makes a difference.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"thejamie" wrote:
[vbcol=seagreen]
> Try focusing in on just a tiny part of the database to begin with. For
> example, find a long running query that hits several tables. Turn on the
> profiler, let it run. Now run the long running query about three times.
Go
> back to the profiler and save the trace. Open the trace in the tuning
> advisor. Browse to the trace file. Select the database(s) that have the
> tables you tested in the long running query. Select only those tables and
> use only one of the databases. If you have more than one database you ca
n
> reuse the same trace. Let it run against those smallest of parameters.
> Generally you will get results. Sometimes there isn't enough information
in
> the trace to kick off the process. At least that's my take on it.
> --
> Regards,
> Jamie
>
> "Catadmin" wrote:
>|||Kevin,
I'm trying to run this offline, so I can consolidate or get rid of indexes.
Believe me, the last thing I'm going to do is run it online and allow it to
change stuff without me approving it.
Problem is, I can't even get it to run to begin with... GRRR. Oh, well.
I'll try Jamie's suggestion on a test DB and see if it runs that way.
Catadmin
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"TheSQLGuru" wrote:
> Be VERY leery of using DTA. It does REALLY bad things sometimes - like
> creating indexes that contain 80% of the columns in the table, multiple
> indexes with nothing more than additional columns added to the set, etc.
I
> advise my clients to not use it.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
> news:179FC3C7-2370-47CF-BF72-6F574B0280E9@.microsoft.com...
>
>
