i ran an analysis of a database using the tunning adviser. I pointed
it to some trace files I took. It provided me with a list of new
indexes along with the various reports.
what i have not found is the linkage between the most costly queries
from the trace and the new index recommendations.
did i miss this? Let's say i only want to implement the top 3 index
recommendations based on their ability to minimize the most expensive
query operations...
does the tuning advisor provide this? or do i have to "connect the
dot" between the 2 myself?
Thanks.
Hi
The tuning advisor provides recommendation using the sample provided. If you
only want to have the specific queries tuned then you could modify the
sample, although only tuning specific queries may have a detremental effect
elsewhere.
If you had a standard benchmark test then you can judge the effects of your
query tuning by running the test before and after the indexes have been
applied.
John
"kent@.kaeservices.com" wrote:
> i ran an analysis of a database using the tunning adviser. I pointed
> it to some trace files I took. It provided me with a list of new
> indexes along with the various reports.
> what i have not found is the linkage between the most costly queries
> from the trace and the new index recommendations.
> did i miss this? Let's say i only want to implement the top 3 index
> recommendations based on their ability to minimize the most expensive
> query operations...
> does the tuning advisor provide this? or do i have to "connect the
> dot" between the 2 myself?
> Thanks.
>
Showing posts with label advisor. Show all posts
Showing posts with label advisor. Show all posts
Tuesday, February 14, 2012
database tunning advisor 2005 question
i ran an analysis of a database using the tunning adviser. I pointed
it to some trace files I took. It provided me with a list of new
indexes along with the various reports.
what i have not found is the linkage between the most costly queries
from the trace and the new index recommendations.
did i miss this? Let's say i only want to implement the top 3 index
recommendations based on their ability to minimize the most expensive
query operations...
does the tuning advisor provide this? or do i have to "connect the
dot" between the 2 myself?
Thanks.Hi
The tuning advisor provides recommendation using the sample provided. If you
only want to have the specific queries tuned then you could modify the
sample, although only tuning specific queries may have a detremental effect
elsewhere.
If you had a standard benchmark test then you can judge the effects of your
query tuning by running the test before and after the indexes have been
applied.
John
"kent@.kaeservices.com" wrote:
> i ran an analysis of a database using the tunning adviser. I pointed
> it to some trace files I took. It provided me with a list of new
> indexes along with the various reports.
> what i have not found is the linkage between the most costly queries
> from the trace and the new index recommendations.
> did i miss this? Let's say i only want to implement the top 3 index
> recommendations based on their ability to minimize the most expensive
> query operations...
> does the tuning advisor provide this? or do i have to "connect the
> dot" between the 2 myself?
> Thanks.
>
it to some trace files I took. It provided me with a list of new
indexes along with the various reports.
what i have not found is the linkage between the most costly queries
from the trace and the new index recommendations.
did i miss this? Let's say i only want to implement the top 3 index
recommendations based on their ability to minimize the most expensive
query operations...
does the tuning advisor provide this? or do i have to "connect the
dot" between the 2 myself?
Thanks.Hi
The tuning advisor provides recommendation using the sample provided. If you
only want to have the specific queries tuned then you could modify the
sample, although only tuning specific queries may have a detremental effect
elsewhere.
If you had a standard benchmark test then you can judge the effects of your
query tuning by running the test before and after the indexes have been
applied.
John
"kent@.kaeservices.com" wrote:
> i ran an analysis of a database using the tunning adviser. I pointed
> it to some trace files I took. It provided me with a list of new
> indexes along with the various reports.
> what i have not found is the linkage between the most costly queries
> from the trace and the new index recommendations.
> did i miss this? Let's say i only want to implement the top 3 index
> recommendations based on their ability to minimize the most expensive
> query operations...
> does the tuning advisor provide this? or do i have to "connect the
> dot" between the 2 myself?
> Thanks.
>
Database Tuning Advisor question (against sql server 2000 trace fi
Hi,
I caught all RPC:Completed, SP:StmtCompleted, and SQL:BatchCompleted events
that occured in the system (based on prepared statements, so that's why I
included statement level of events) during one day, and I ran DTA against
this trace file.
Surprisingly it ran only 5 minutes, regardless of the fact that there is
around 700,000 events (real sql statements) in the trace file and that I did
not limit tuning time. When I reviewed session summary, I noticed that it
reported only 8,553 events in the workload, number of events tuned 8,553, and
number of statements tuned 517!
What the heck happened there? Again, I did not limit tuning time, and after
running it against 3 different trace files, each time it ran and reported
running time of only 5 minutes.
Does anybody have an idea what's going on, and how to resolve it?
Thanks,
Pedja
Do you see anything in the event log? Did you get any recommendation
back? What kind of workload is this?
An event represents a batch,a stored procedure or a single statement.
Statement represents the tuneable query. A batch or SP can potentially
map to multiple statements.However it can also be "non-tuneable"
example select@.@. version
Manoj
On Jan 13, 12:48 pm, Pedja <P...@.discussions.microsoft.com> wrote:
> Hi,
> I caught all RPC:Completed, SP:StmtCompleted, and SQL:BatchCompleted events
> that occured in the system (based on prepared statements, so that's why I
> included statement level of events) during one day, and I ran DTA against
> this trace file.
> Surprisingly it ran only 5 minutes, regardless of the fact that there is
> around 700,000 events (real sql statements) in the trace file and that I did
> not limit tuning time. When I reviewed session summary, I noticed that it
> reported only 8,553 events in the workload, number of events tuned 8,553, and
> number of statements tuned 517!
> What the heck happened there? Again, I did not limit tuning time, and after
> running it against 3 different trace files, each time it ran and reported
> running time of only 5 minutes.
> Does anybody have an idea what's going on, and how to resolve it?
> Thanks,
> Pedja
|||I got recommendation back, but it was based on extreemly small sample of the
trace. Therefore I couldn't accept its results... I did analysis of the
trace, and from around 700,000 events, around 100,000 were tunable (select
statements against database tables). So I still didn't figure out what
happened there.
Workload is whatever happened on the system that day, application is based
on prepared statements...
"_manoj@.yahoo.com" wrote:
> Do you see anything in the event log? Did you get any recommendation
> back? What kind of workload is this?
> An event represents a batch,a stored procedure or a single statement.
> Statement represents the tuneable query. A batch or SP can potentially
> map to multiple statements.However it can also be "non-tuneable"
> example select@.@. version
> Manoj
> On Jan 13, 12:48 pm, Pedja <P...@.discussions.microsoft.com> wrote:
>
|||Scoping down to your actual post
"Surprisingly it ran only 5 minutes, regardless of the fact that there
is
around 700,000 events (real sql statements) in the trace file and that
I did
not limit tuning time. When I reviewed session summary, I noticed that
it
reported only 8,553 events in the workload, number of events tuned
8,553, and
number of statements tuned 517! "
a) Did you get any recommendation with this case (not the sample you
mention in the previous post).If so what is the expected percentage
improvement?
b) Did you get any errors in the tuning log?
c) Are you pointing to the right database for workload analysis (via
the user interface option)?
d) Are you choosing the right databases to tune?
The behavior you mention is not abnormal - DTA relies on the right
database context and if this is incorrect DTA might not do any useful
work. Also if your workload comprises vaild non-tuneable statements
(select @.@.version ; select SERVER_PROPERTY(...)) etc it can scan thru
fast and hence the question about the nature of the workload
Thanks
Manoj
I caught all RPC:Completed, SP:StmtCompleted, and SQL:BatchCompleted events
that occured in the system (based on prepared statements, so that's why I
included statement level of events) during one day, and I ran DTA against
this trace file.
Surprisingly it ran only 5 minutes, regardless of the fact that there is
around 700,000 events (real sql statements) in the trace file and that I did
not limit tuning time. When I reviewed session summary, I noticed that it
reported only 8,553 events in the workload, number of events tuned 8,553, and
number of statements tuned 517!
What the heck happened there? Again, I did not limit tuning time, and after
running it against 3 different trace files, each time it ran and reported
running time of only 5 minutes.
Does anybody have an idea what's going on, and how to resolve it?
Thanks,
Pedja
Do you see anything in the event log? Did you get any recommendation
back? What kind of workload is this?
An event represents a batch,a stored procedure or a single statement.
Statement represents the tuneable query. A batch or SP can potentially
map to multiple statements.However it can also be "non-tuneable"
example select@.@. version
Manoj
On Jan 13, 12:48 pm, Pedja <P...@.discussions.microsoft.com> wrote:
> Hi,
> I caught all RPC:Completed, SP:StmtCompleted, and SQL:BatchCompleted events
> that occured in the system (based on prepared statements, so that's why I
> included statement level of events) during one day, and I ran DTA against
> this trace file.
> Surprisingly it ran only 5 minutes, regardless of the fact that there is
> around 700,000 events (real sql statements) in the trace file and that I did
> not limit tuning time. When I reviewed session summary, I noticed that it
> reported only 8,553 events in the workload, number of events tuned 8,553, and
> number of statements tuned 517!
> What the heck happened there? Again, I did not limit tuning time, and after
> running it against 3 different trace files, each time it ran and reported
> running time of only 5 minutes.
> Does anybody have an idea what's going on, and how to resolve it?
> Thanks,
> Pedja
|||I got recommendation back, but it was based on extreemly small sample of the
trace. Therefore I couldn't accept its results... I did analysis of the
trace, and from around 700,000 events, around 100,000 were tunable (select
statements against database tables). So I still didn't figure out what
happened there.
Workload is whatever happened on the system that day, application is based
on prepared statements...
"_manoj@.yahoo.com" wrote:
> Do you see anything in the event log? Did you get any recommendation
> back? What kind of workload is this?
> An event represents a batch,a stored procedure or a single statement.
> Statement represents the tuneable query. A batch or SP can potentially
> map to multiple statements.However it can also be "non-tuneable"
> example select@.@. version
> Manoj
> On Jan 13, 12:48 pm, Pedja <P...@.discussions.microsoft.com> wrote:
>
|||Scoping down to your actual post
"Surprisingly it ran only 5 minutes, regardless of the fact that there
is
around 700,000 events (real sql statements) in the trace file and that
I did
not limit tuning time. When I reviewed session summary, I noticed that
it
reported only 8,553 events in the workload, number of events tuned
8,553, and
number of statements tuned 517! "
a) Did you get any recommendation with this case (not the sample you
mention in the previous post).If so what is the expected percentage
improvement?
b) Did you get any errors in the tuning log?
c) Are you pointing to the right database for workload analysis (via
the user interface option)?
d) Are you choosing the right databases to tune?
The behavior you mention is not abnormal - DTA relies on the right
database context and if this is incorrect DTA might not do any useful
work. Also if your workload comprises vaild non-tuneable statements
(select @.@.version ; select SERVER_PROPERTY(...)) etc it can scan thru
fast and hence the question about the nature of the workload
Thanks
Manoj
Database Tuning Advisor problems
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?
@.=)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 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?
> @.=)|||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 test
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:
> 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 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?
> > @.=)|||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...
> > 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?
> > @.=)
>
>
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 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?
> @.=)|||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 test
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:
> 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 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?
> > @.=)|||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...
> > 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?
> > @.=)
>
>
Database Tuning Advisor problems
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?
@.=)
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 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?
> @.=)
|||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 test
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 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:
|||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...
>
>
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 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?
> @.=)
|||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 test
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 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:
|||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...
>
>
Database Tuning Advisor problems
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...
>
>
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...
>
>
Database Tuning Advisor DTA Import Session Results?
Hi,
I have ran the Tuning Advisor (DTA) on a .trc file. Recommendations and reports came back.
I would like to take my exported session results and import that file into a DTA on a different machine with the SQL Server 2005 developer tools installed.
The file is the following Job010704SessionResultsExported.xml
I see how to import a session definition. I do not see how to import 'session results.'
Any ideas oh how to do that?
Thanks,
AIMDBA
DTA does not have such feature. You can file a feature request here.https://connect.microsoft.com/sqlserver
Database Tuning Advisor doubt.
Hi,
After processing my workload, DTA answers to create 2 Index (and
more), the first one with columns id_key1 and id_key2 from table A and
the second index with id_key1 and includes id_key2.
I though than the first index really covered the included index.
- Is that ok?
- Why does it recommends the include one?On 12 nov, 19:17, GeneroDu...@.gmail.com wrote:
> Hi,
> After processing my workload, DTA answers to create 2 Index (and
> more), the first one with columns id_key1 and id_key2 from table A and
> the second index with id_key1 and includes id_key2.
> I though than the first index really covered the included index.
> - Is that ok?
> - Why does it recommends the include one?
NO one knows?|||1) I tell my clients to avoid DTA like the plague. I have yet to see it do
anything but harm (BAD harm in some cases) in the hands of an unskilled DBA.
And if you are a skilled DBA you don't need it anyway.
2) Yes, you are correct - first index and second index will both cover the
same queries, although they are not identical in nature since included
columns are different from actual indexed columns.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
<GeneroDudas@.gmail.com> wrote in message
news:1194946569.668426.54180@.k79g2000hse.googlegroups.com...
> On 12 nov, 19:17, GeneroDu...@.gmail.com wrote:
>> Hi,
>> After processing my workload, DTA answers to create 2 Index (and
>> more), the first one with columns id_key1 and id_key2 from table A and
>> the second index with id_key1 and includes id_key2.
>> I though than the first index really covered the included index.
>> - Is that ok?
>> - Why does it recommends the include one?
> NO one knows?
>|||Well then,
But id like to have an automatic way to tune the most querys from the
database through diferent workloads as there are several diferent
databases each with many tables. I can go through the longest querys
and give them a separate look, but for the global map you dont
recomend the use of the DTA?
Im DBA, id like to be Skilled in SQL Server, but as always... the more
i learn the less i know. I simply document my decisions and look for
second opinions.
Could you add some explanation about DTA harms? I wanna know if i must
go through the whole index recomendation it does to analyze as I
cannot trust it. I m sure the ones that builded it know far much more
than i...|||My approach to indexing has worked pretty well for me. First I put on
the "obvious" indexes - the Primary Key, any alternate keys, and any
foreign keys that are selective. Then I look for specific performance
issues and see if indexing is the way to correct them.
I have tried DTA a few times when a performance issue persisted, but
for me it has never given any useful suggestions.
Roy Harvey
Beacon Falls, CT
On Wed, 14 Nov 2007 04:12:03 -0800, GeneroDudas@.gmail.com wrote:
>Well then,
>But id like to have an automatic way to tune the most querys from the
>database through diferent workloads as there are several diferent
>databases each with many tables. I can go through the longest querys
>and give them a separate look, but for the global map you dont
>recomend the use of the DTA?
>Im DBA, id like to be Skilled in SQL Server, but as always... the more
>i learn the less i know. I simply document my decisions and look for
>second opinions.
>Could you add some explanation about DTA harms? I wanna know if i must
>go through the whole index recomendation it does to analyze as I
>cannot trust it. I m sure the ones that builded it know far much more
>than i...|||I know that approach (and used it before), but with many hundred
million rows tables, I find many IOs in my querys, and that affects
performance seriously. I get Querys with similar columns and try to
get indexes to try to cover all them in order. But i find than the
optimizer don't use them as id like and sometimes i have to force with
hints.
Of course primary and alternate keys have their index. But with other
columns (FOREIGN) indexes, for example, have 6 cols, and i use to save
the clustered ones to grouping querys where possible.
The approach DTA shows are big Indexes (100GB six indexes) and very
low IOs. but i cannot use/compare now that first approach as the
database follows this way since before i arrived...
=BFIs really better the one-col-one-index way?
On 14 nov, 13:38, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
wrote:
> My approach to indexing has worked pretty well for me. First I put on
> the "obvious" indexes - the Primary Key, any alternate keys, and any
> foreign keys that are selective. Then I look for specific performance
> issues and see if indexing is the way to correct them.
> I have tried DTA a few times when a performance issue persisted, but
> for me it has never given any useful suggestions.
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 14 Nov 2007 04:12:03 -0800, GeneroDu...@.gmail.com wrote:
> >Well then,
> >But id like to have an automatic way to tune the most querys from the
> >database through diferent workloads as there are several diferent
> >databases each with many tables. I can go through the longest querys
> >and give them a separate look, but for the global map you dont
> >recomend the use of the DTA?
> >Im DBA, id like to be Skilled in SQL Server, but as always... the more
> >i learn the less i know. I simply document my decisions and look for
> >second opinions.
> >Could you add some explanation about DTA harms? I wanna know if i must
> >go through the whole index recomendation it does to analyze as I
> >cannot trust it. I m sure the ones that builded it know far much more
> >than i...- Ocultar texto de la cita -
> - Mostrar texto de la cita -
After processing my workload, DTA answers to create 2 Index (and
more), the first one with columns id_key1 and id_key2 from table A and
the second index with id_key1 and includes id_key2.
I though than the first index really covered the included index.
- Is that ok?
- Why does it recommends the include one?On 12 nov, 19:17, GeneroDu...@.gmail.com wrote:
> Hi,
> After processing my workload, DTA answers to create 2 Index (and
> more), the first one with columns id_key1 and id_key2 from table A and
> the second index with id_key1 and includes id_key2.
> I though than the first index really covered the included index.
> - Is that ok?
> - Why does it recommends the include one?
NO one knows?|||1) I tell my clients to avoid DTA like the plague. I have yet to see it do
anything but harm (BAD harm in some cases) in the hands of an unskilled DBA.
And if you are a skilled DBA you don't need it anyway.
2) Yes, you are correct - first index and second index will both cover the
same queries, although they are not identical in nature since included
columns are different from actual indexed columns.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
<GeneroDudas@.gmail.com> wrote in message
news:1194946569.668426.54180@.k79g2000hse.googlegroups.com...
> On 12 nov, 19:17, GeneroDu...@.gmail.com wrote:
>> Hi,
>> After processing my workload, DTA answers to create 2 Index (and
>> more), the first one with columns id_key1 and id_key2 from table A and
>> the second index with id_key1 and includes id_key2.
>> I though than the first index really covered the included index.
>> - Is that ok?
>> - Why does it recommends the include one?
> NO one knows?
>|||Well then,
But id like to have an automatic way to tune the most querys from the
database through diferent workloads as there are several diferent
databases each with many tables. I can go through the longest querys
and give them a separate look, but for the global map you dont
recomend the use of the DTA?
Im DBA, id like to be Skilled in SQL Server, but as always... the more
i learn the less i know. I simply document my decisions and look for
second opinions.
Could you add some explanation about DTA harms? I wanna know if i must
go through the whole index recomendation it does to analyze as I
cannot trust it. I m sure the ones that builded it know far much more
than i...|||My approach to indexing has worked pretty well for me. First I put on
the "obvious" indexes - the Primary Key, any alternate keys, and any
foreign keys that are selective. Then I look for specific performance
issues and see if indexing is the way to correct them.
I have tried DTA a few times when a performance issue persisted, but
for me it has never given any useful suggestions.
Roy Harvey
Beacon Falls, CT
On Wed, 14 Nov 2007 04:12:03 -0800, GeneroDudas@.gmail.com wrote:
>Well then,
>But id like to have an automatic way to tune the most querys from the
>database through diferent workloads as there are several diferent
>databases each with many tables. I can go through the longest querys
>and give them a separate look, but for the global map you dont
>recomend the use of the DTA?
>Im DBA, id like to be Skilled in SQL Server, but as always... the more
>i learn the less i know. I simply document my decisions and look for
>second opinions.
>Could you add some explanation about DTA harms? I wanna know if i must
>go through the whole index recomendation it does to analyze as I
>cannot trust it. I m sure the ones that builded it know far much more
>than i...|||I know that approach (and used it before), but with many hundred
million rows tables, I find many IOs in my querys, and that affects
performance seriously. I get Querys with similar columns and try to
get indexes to try to cover all them in order. But i find than the
optimizer don't use them as id like and sometimes i have to force with
hints.
Of course primary and alternate keys have their index. But with other
columns (FOREIGN) indexes, for example, have 6 cols, and i use to save
the clustered ones to grouping querys where possible.
The approach DTA shows are big Indexes (100GB six indexes) and very
low IOs. but i cannot use/compare now that first approach as the
database follows this way since before i arrived...
=BFIs really better the one-col-one-index way?
On 14 nov, 13:38, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
wrote:
> My approach to indexing has worked pretty well for me. First I put on
> the "obvious" indexes - the Primary Key, any alternate keys, and any
> foreign keys that are selective. Then I look for specific performance
> issues and see if indexing is the way to correct them.
> I have tried DTA a few times when a performance issue persisted, but
> for me it has never given any useful suggestions.
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 14 Nov 2007 04:12:03 -0800, GeneroDu...@.gmail.com wrote:
> >Well then,
> >But id like to have an automatic way to tune the most querys from the
> >database through diferent workloads as there are several diferent
> >databases each with many tables. I can go through the longest querys
> >and give them a separate look, but for the global map you dont
> >recomend the use of the DTA?
> >Im DBA, id like to be Skilled in SQL Server, but as always... the more
> >i learn the less i know. I simply document my decisions and look for
> >second opinions.
> >Could you add some explanation about DTA harms? I wanna know if i must
> >go through the whole index recomendation it does to analyze as I
> >cannot trust it. I m sure the ones that builded it know far much more
> >than i...- Ocultar texto de la cita -
> - Mostrar texto de la cita -
Database Tuning ADvisor and index recommendations
Hi there. I love this tool from what I've read, will it also tell me what indexes I DON'T need (I think that would be helpful - especially when I'm not the one creating indexes all the time). Thanks!Moving to tools forum.|||
More or less it does as per my experience, but in this case you might need to run thru full analysis using the profiler trace.
Refer to http://blogs.msdn.com/sqlcat/archive/2006/02/13/531339.aspx for more information.
Database Tuning Advisor - how does the max. space parameter work?
I think that the min. space required for physical design structures may be
reported incorrectly. Please see the thread at the following URL:
http://groups.google.ca/group/SQL-Se...f3426d5a?hl=en
No it is not.I have tried my best to explain in the other thread based
on my interpretation of your posting
reported incorrectly. Please see the thread at the following URL:
http://groups.google.ca/group/SQL-Se...f3426d5a?hl=en
No it is not.I have tried my best to explain in the other thread based
on my interpretation of your posting
Database Tuning Advisor
Hi All,
Can anyone tell me if it makes any difference in Database Tuning advisor when is used against stored procedures workload and simple batch statements workload.
I used it against stored procedures first and got no recommendations for it, and when I used the statements of same stored procedures outside the procedure and used the worload against the DTA, I got some recommendations.
Also, in the reports section of DTA, the Database Access Report shows no database details, whereas in the other case, I got all the details for database, tables and columns accessed.
When you use DTA with a given workload of SQL queries and updates, DTA recommends an appropriate physical design, and generates a script to implement the recommended physical design.
There is no direct connection between VSTS for DB Pro and DTA. After you use DTA you can change your DB project and DB objects as the DTA recommended.
You can read great article about DTA and get more information from: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQL2005DTA.doc
Maor
|||DTA does not get inside my database for the stored procedures where there's a 'if' condition inside the SP.
For example,
I created a stored procedure as,
createproc testSP2
@.Group_ID_id int
as
begin
select * from PATIENTS.PATIENT_FAMILY_DETAILS where Patient_id in (select Patient_id from
PATIENTS.PATIENT_INFO where Group_ID=@.GroupID)
end
GO
exec testSP2
GO
The Database, Table, column Access reports shows the details of database,tables and columns accessed in the DTA and a set of recommendations are recieved.
But when the same procedure is modifies as,
create proc testSP2
@.Group_id int
as
begin
if(1=1)
begin
select * from PATIENTS.PATIENT_FAMILY_DETAILS where Patient_id in (select Patient_id from
PATIENTS.PATIENT_INFO where Group_ID=@.GroupID)
end
end
GO
exec testSP2
GO
The Database, Table, column Access reports don't give any details and no recommendations are made.
Database Tuning Advisor
I am trying to run through a trace log to improve one of our databases here,
however, the calls originate inside a read only database, goto another
database on the same server before they finally end up in the destination
database that i want to tune.
How can i get the advisor to travel through these other two databases?
Right now, it doesn't even recognize any of the statements because none of
them affect any databases it knows about.
Bryan
Hi Bryan
I assume that you have selected all these databases/tables on the general
tab? In which case I would try look at the query plans when run manully from
management studio or possibly try and unravel what the queries and run them
directly and then look at the query plans.
John
"Bryan Aldrich" wrote:
> I am trying to run through a trace log to improve one of our databases here,
> however, the calls originate inside a read only database, goto another
> database on the same server before they finally end up in the destination
> database that i want to tune.
> How can i get the advisor to travel through these other two databases?
> Right now, it doesn't even recognize any of the statements because none of
> them affect any databases it knows about.
> Bryan
|||Yes, I even tried by selecting all of the databases involved in this trace
file. I even selected Master to see if it would execute the sp_ExecuteSQL
calls, but it didn't. It analyzes the originating database just fine. I see
the sp_ExecuteSQL call go through, but it logs it as "not referencing any
tables".
I'm hoping for some sort of automated solution because it can be difficult
to always recreate the exact parameters that make it to this database.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> I assume that you have selected all these databases/tables on the general
> tab? In which case I would try look at the query plans when run manully from
> management studio or possibly try and unravel what the queries and run them
> directly and then look at the query plans.
> John
> "Bryan Aldrich" wrote:
|||Hi Bryan
Without knowing exactly what you are doing it is difficult to really
recommend anything other than the manual approach, any automated tuning
should be taken as advice that should be reviewed rather than something which
is an absolute necessity. I would also recommended that you have a system
which can be benchmarked and any changes compared so that you can be sure
that it is a positive change before implementing it on a live environment.
John
"Bryan Aldrich" wrote:
[vbcol=seagreen]
> Yes, I even tried by selecting all of the databases involved in this trace
> file. I even selected Master to see if it would execute the sp_ExecuteSQL
> calls, but it didn't. It analyzes the originating database just fine. I see
> the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> tables".
> I'm hoping for some sort of automated solution because it can be difficult
> to always recreate the exact parameters that make it to this database.
> Bryan
> "John Bell" wrote:
|||Of course, I have a complete development environment with which I can test
with. I capture a trace from production and then analyze it on our
development environment.
It appears that I will have to manually create some queries that resemble
production that are issued directly against this database before I will be
able to use any sort of automated approach.
Thanks for your assistance.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> Without knowing exactly what you are doing it is difficult to really
> recommend anything other than the manual approach, any automated tuning
> should be taken as advice that should be reviewed rather than something which
> is an absolute necessity. I would also recommended that you have a system
> which can be benchmarked and any changes compared so that you can be sure
> that it is a positive change before implementing it on a live environment.
> John
>
> "Bryan Aldrich" wrote:
however, the calls originate inside a read only database, goto another
database on the same server before they finally end up in the destination
database that i want to tune.
How can i get the advisor to travel through these other two databases?
Right now, it doesn't even recognize any of the statements because none of
them affect any databases it knows about.
Bryan
Hi Bryan
I assume that you have selected all these databases/tables on the general
tab? In which case I would try look at the query plans when run manully from
management studio or possibly try and unravel what the queries and run them
directly and then look at the query plans.
John
"Bryan Aldrich" wrote:
> I am trying to run through a trace log to improve one of our databases here,
> however, the calls originate inside a read only database, goto another
> database on the same server before they finally end up in the destination
> database that i want to tune.
> How can i get the advisor to travel through these other two databases?
> Right now, it doesn't even recognize any of the statements because none of
> them affect any databases it knows about.
> Bryan
|||Yes, I even tried by selecting all of the databases involved in this trace
file. I even selected Master to see if it would execute the sp_ExecuteSQL
calls, but it didn't. It analyzes the originating database just fine. I see
the sp_ExecuteSQL call go through, but it logs it as "not referencing any
tables".
I'm hoping for some sort of automated solution because it can be difficult
to always recreate the exact parameters that make it to this database.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> I assume that you have selected all these databases/tables on the general
> tab? In which case I would try look at the query plans when run manully from
> management studio or possibly try and unravel what the queries and run them
> directly and then look at the query plans.
> John
> "Bryan Aldrich" wrote:
|||Hi Bryan
Without knowing exactly what you are doing it is difficult to really
recommend anything other than the manual approach, any automated tuning
should be taken as advice that should be reviewed rather than something which
is an absolute necessity. I would also recommended that you have a system
which can be benchmarked and any changes compared so that you can be sure
that it is a positive change before implementing it on a live environment.
John
"Bryan Aldrich" wrote:
[vbcol=seagreen]
> Yes, I even tried by selecting all of the databases involved in this trace
> file. I even selected Master to see if it would execute the sp_ExecuteSQL
> calls, but it didn't. It analyzes the originating database just fine. I see
> the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> tables".
> I'm hoping for some sort of automated solution because it can be difficult
> to always recreate the exact parameters that make it to this database.
> Bryan
> "John Bell" wrote:
|||Of course, I have a complete development environment with which I can test
with. I capture a trace from production and then analyze it on our
development environment.
It appears that I will have to manually create some queries that resemble
production that are issued directly against this database before I will be
able to use any sort of automated approach.
Thanks for your assistance.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> Without knowing exactly what you are doing it is difficult to really
> recommend anything other than the manual approach, any automated tuning
> should be taken as advice that should be reviewed rather than something which
> is an absolute necessity. I would also recommended that you have a system
> which can be benchmarked and any changes compared so that you can be sure
> that it is a positive change before implementing it on a live environment.
> John
>
> "Bryan Aldrich" wrote:
Database Tuning Advisor
I am trying to run through a trace log to improve one of our databases here,
however, the calls originate inside a read only database, goto another
database on the same server before they finally end up in the destination
database that i want to tune.
How can i get the advisor to travel through these other two databases?
Right now, it doesn't even recognize any of the statements because none of
them affect any databases it knows about.
BryanHi Bryan
I assume that you have selected all these databases/tables on the general
tab? In which case I would try look at the query plans when run manully from
management studio or possibly try and unravel what the queries and run them
directly and then look at the query plans.
John
"Bryan Aldrich" wrote:
> I am trying to run through a trace log to improve one of our databases her
e,
> however, the calls originate inside a read only database, goto another
> database on the same server before they finally end up in the destination
> database that i want to tune.
> How can i get the advisor to travel through these other two databases?
> Right now, it doesn't even recognize any of the statements because none of
> them affect any databases it knows about.
> Bryan|||Yes, I even tried by selecting all of the databases involved in this trace
file. I even selected Master to see if it would execute the sp_ExecuteSQL
calls, but it didn't. It analyzes the originating database just fine. I see
the sp_ExecuteSQL call go through, but it logs it as "not referencing any
tables".
I'm hoping for some sort of automated solution because it can be difficult
to always recreate the exact parameters that make it to this database.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> I assume that you have selected all these databases/tables on the general
> tab? In which case I would try look at the query plans when run manully fr
om
> management studio or possibly try and unravel what the queries and run the
m
> directly and then look at the query plans.
> John
> "Bryan Aldrich" wrote:
>|||Hi Bryan
Without knowing exactly what you are doing it is difficult to really
recommend anything other than the manual approach, any automated tuning
should be taken as advice that should be reviewed rather than something whic
h
is an absolute necessity. I would also recommended that you have a system
which can be benchmarked and any changes compared so that you can be sure
that it is a positive change before implementing it on a live environment.
John
"Bryan Aldrich" wrote:
[vbcol=seagreen]
> Yes, I even tried by selecting all of the databases involved in this trace
> file. I even selected Master to see if it would execute the sp_ExecuteSQL
> calls, but it didn't. It analyzes the originating database just fine. I se
e
> the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> tables".
> I'm hoping for some sort of automated solution because it can be difficult
> to always recreate the exact parameters that make it to this database.
> Bryan
> "John Bell" wrote:
>|||Of course, I have a complete development environment with which I can test
with. I capture a trace from production and then analyze it on our
development environment.
It appears that I will have to manually create some queries that resemble
production that are issued directly against this database before I will be
able to use any sort of automated approach.
Thanks for your assistance.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> Without knowing exactly what you are doing it is difficult to really
> recommend anything other than the manual approach, any automated tuning
> should be taken as advice that should be reviewed rather than something wh
ich
> is an absolute necessity. I would also recommended that you have a system
> which can be benchmarked and any changes compared so that you can be sure
> that it is a positive change before implementing it on a live environment.
> John
>
> "Bryan Aldrich" wrote:
>
however, the calls originate inside a read only database, goto another
database on the same server before they finally end up in the destination
database that i want to tune.
How can i get the advisor to travel through these other two databases?
Right now, it doesn't even recognize any of the statements because none of
them affect any databases it knows about.
BryanHi Bryan
I assume that you have selected all these databases/tables on the general
tab? In which case I would try look at the query plans when run manully from
management studio or possibly try and unravel what the queries and run them
directly and then look at the query plans.
John
"Bryan Aldrich" wrote:
> I am trying to run through a trace log to improve one of our databases her
e,
> however, the calls originate inside a read only database, goto another
> database on the same server before they finally end up in the destination
> database that i want to tune.
> How can i get the advisor to travel through these other two databases?
> Right now, it doesn't even recognize any of the statements because none of
> them affect any databases it knows about.
> Bryan|||Yes, I even tried by selecting all of the databases involved in this trace
file. I even selected Master to see if it would execute the sp_ExecuteSQL
calls, but it didn't. It analyzes the originating database just fine. I see
the sp_ExecuteSQL call go through, but it logs it as "not referencing any
tables".
I'm hoping for some sort of automated solution because it can be difficult
to always recreate the exact parameters that make it to this database.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> I assume that you have selected all these databases/tables on the general
> tab? In which case I would try look at the query plans when run manully fr
om
> management studio or possibly try and unravel what the queries and run the
m
> directly and then look at the query plans.
> John
> "Bryan Aldrich" wrote:
>|||Hi Bryan
Without knowing exactly what you are doing it is difficult to really
recommend anything other than the manual approach, any automated tuning
should be taken as advice that should be reviewed rather than something whic
h
is an absolute necessity. I would also recommended that you have a system
which can be benchmarked and any changes compared so that you can be sure
that it is a positive change before implementing it on a live environment.
John
"Bryan Aldrich" wrote:
[vbcol=seagreen]
> Yes, I even tried by selecting all of the databases involved in this trace
> file. I even selected Master to see if it would execute the sp_ExecuteSQL
> calls, but it didn't. It analyzes the originating database just fine. I se
e
> the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> tables".
> I'm hoping for some sort of automated solution because it can be difficult
> to always recreate the exact parameters that make it to this database.
> Bryan
> "John Bell" wrote:
>|||Of course, I have a complete development environment with which I can test
with. I capture a trace from production and then analyze it on our
development environment.
It appears that I will have to manually create some queries that resemble
production that are issued directly against this database before I will be
able to use any sort of automated approach.
Thanks for your assistance.
Bryan
"John Bell" wrote:
[vbcol=seagreen]
> Hi Bryan
> Without knowing exactly what you are doing it is difficult to really
> recommend anything other than the manual approach, any automated tuning
> should be taken as advice that should be reviewed rather than something wh
ich
> is an absolute necessity. I would also recommended that you have a system
> which can be benchmarked and any changes compared so that you can be sure
> that it is a positive change before implementing it on a live environment.
> John
>
> "Bryan Aldrich" wrote:
>
Database Tuning Advisor
I am trying to run through a trace log to improve one of our databases here,
however, the calls originate inside a read only database, goto another
database on the same server before they finally end up in the destination
database that i want to tune.
How can i get the advisor to travel through these other two databases?
Right now, it doesn't even recognize any of the statements because none of
them affect any databases it knows about.
BryanHi Bryan
I assume that you have selected all these databases/tables on the general
tab? In which case I would try look at the query plans when run manully from
management studio or possibly try and unravel what the queries and run them
directly and then look at the query plans.
John
"Bryan Aldrich" wrote:
> I am trying to run through a trace log to improve one of our databases here,
> however, the calls originate inside a read only database, goto another
> database on the same server before they finally end up in the destination
> database that i want to tune.
> How can i get the advisor to travel through these other two databases?
> Right now, it doesn't even recognize any of the statements because none of
> them affect any databases it knows about.
> Bryan|||Yes, I even tried by selecting all of the databases involved in this trace
file. I even selected Master to see if it would execute the sp_ExecuteSQL
calls, but it didn't. It analyzes the originating database just fine. I see
the sp_ExecuteSQL call go through, but it logs it as "not referencing any
tables".
I'm hoping for some sort of automated solution because it can be difficult
to always recreate the exact parameters that make it to this database.
Bryan
"John Bell" wrote:
> Hi Bryan
> I assume that you have selected all these databases/tables on the general
> tab? In which case I would try look at the query plans when run manully from
> management studio or possibly try and unravel what the queries and run them
> directly and then look at the query plans.
> John
> "Bryan Aldrich" wrote:
> > I am trying to run through a trace log to improve one of our databases here,
> > however, the calls originate inside a read only database, goto another
> > database on the same server before they finally end up in the destination
> > database that i want to tune.
> >
> > How can i get the advisor to travel through these other two databases?
> >
> > Right now, it doesn't even recognize any of the statements because none of
> > them affect any databases it knows about.
> >
> > Bryan|||Hi Bryan
Without knowing exactly what you are doing it is difficult to really
recommend anything other than the manual approach, any automated tuning
should be taken as advice that should be reviewed rather than something which
is an absolute necessity. I would also recommended that you have a system
which can be benchmarked and any changes compared so that you can be sure
that it is a positive change before implementing it on a live environment.
John
"Bryan Aldrich" wrote:
> Yes, I even tried by selecting all of the databases involved in this trace
> file. I even selected Master to see if it would execute the sp_ExecuteSQL
> calls, but it didn't. It analyzes the originating database just fine. I see
> the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> tables".
> I'm hoping for some sort of automated solution because it can be difficult
> to always recreate the exact parameters that make it to this database.
> Bryan
> "John Bell" wrote:
> > Hi Bryan
> >
> > I assume that you have selected all these databases/tables on the general
> > tab? In which case I would try look at the query plans when run manully from
> > management studio or possibly try and unravel what the queries and run them
> > directly and then look at the query plans.
> >
> > John
> >
> > "Bryan Aldrich" wrote:
> >
> > > I am trying to run through a trace log to improve one of our databases here,
> > > however, the calls originate inside a read only database, goto another
> > > database on the same server before they finally end up in the destination
> > > database that i want to tune.
> > >
> > > How can i get the advisor to travel through these other two databases?
> > >
> > > Right now, it doesn't even recognize any of the statements because none of
> > > them affect any databases it knows about.
> > >
> > > Bryan|||Of course, I have a complete development environment with which I can test
with. I capture a trace from production and then analyze it on our
development environment.
It appears that I will have to manually create some queries that resemble
production that are issued directly against this database before I will be
able to use any sort of automated approach.
Thanks for your assistance.
Bryan
"John Bell" wrote:
> Hi Bryan
> Without knowing exactly what you are doing it is difficult to really
> recommend anything other than the manual approach, any automated tuning
> should be taken as advice that should be reviewed rather than something which
> is an absolute necessity. I would also recommended that you have a system
> which can be benchmarked and any changes compared so that you can be sure
> that it is a positive change before implementing it on a live environment.
> John
>
> "Bryan Aldrich" wrote:
> > Yes, I even tried by selecting all of the databases involved in this trace
> > file. I even selected Master to see if it would execute the sp_ExecuteSQL
> > calls, but it didn't. It analyzes the originating database just fine. I see
> > the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> > tables".
> >
> > I'm hoping for some sort of automated solution because it can be difficult
> > to always recreate the exact parameters that make it to this database.
> >
> > Bryan
> >
> > "John Bell" wrote:
> >
> > > Hi Bryan
> > >
> > > I assume that you have selected all these databases/tables on the general
> > > tab? In which case I would try look at the query plans when run manully from
> > > management studio or possibly try and unravel what the queries and run them
> > > directly and then look at the query plans.
> > >
> > > John
> > >
> > > "Bryan Aldrich" wrote:
> > >
> > > > I am trying to run through a trace log to improve one of our databases here,
> > > > however, the calls originate inside a read only database, goto another
> > > > database on the same server before they finally end up in the destination
> > > > database that i want to tune.
> > > >
> > > > How can i get the advisor to travel through these other two databases?
> > > >
> > > > Right now, it doesn't even recognize any of the statements because none of
> > > > them affect any databases it knows about.
> > > >
> > > > Bryan
however, the calls originate inside a read only database, goto another
database on the same server before they finally end up in the destination
database that i want to tune.
How can i get the advisor to travel through these other two databases?
Right now, it doesn't even recognize any of the statements because none of
them affect any databases it knows about.
BryanHi Bryan
I assume that you have selected all these databases/tables on the general
tab? In which case I would try look at the query plans when run manully from
management studio or possibly try and unravel what the queries and run them
directly and then look at the query plans.
John
"Bryan Aldrich" wrote:
> I am trying to run through a trace log to improve one of our databases here,
> however, the calls originate inside a read only database, goto another
> database on the same server before they finally end up in the destination
> database that i want to tune.
> How can i get the advisor to travel through these other two databases?
> Right now, it doesn't even recognize any of the statements because none of
> them affect any databases it knows about.
> Bryan|||Yes, I even tried by selecting all of the databases involved in this trace
file. I even selected Master to see if it would execute the sp_ExecuteSQL
calls, but it didn't. It analyzes the originating database just fine. I see
the sp_ExecuteSQL call go through, but it logs it as "not referencing any
tables".
I'm hoping for some sort of automated solution because it can be difficult
to always recreate the exact parameters that make it to this database.
Bryan
"John Bell" wrote:
> Hi Bryan
> I assume that you have selected all these databases/tables on the general
> tab? In which case I would try look at the query plans when run manully from
> management studio or possibly try and unravel what the queries and run them
> directly and then look at the query plans.
> John
> "Bryan Aldrich" wrote:
> > I am trying to run through a trace log to improve one of our databases here,
> > however, the calls originate inside a read only database, goto another
> > database on the same server before they finally end up in the destination
> > database that i want to tune.
> >
> > How can i get the advisor to travel through these other two databases?
> >
> > Right now, it doesn't even recognize any of the statements because none of
> > them affect any databases it knows about.
> >
> > Bryan|||Hi Bryan
Without knowing exactly what you are doing it is difficult to really
recommend anything other than the manual approach, any automated tuning
should be taken as advice that should be reviewed rather than something which
is an absolute necessity. I would also recommended that you have a system
which can be benchmarked and any changes compared so that you can be sure
that it is a positive change before implementing it on a live environment.
John
"Bryan Aldrich" wrote:
> Yes, I even tried by selecting all of the databases involved in this trace
> file. I even selected Master to see if it would execute the sp_ExecuteSQL
> calls, but it didn't. It analyzes the originating database just fine. I see
> the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> tables".
> I'm hoping for some sort of automated solution because it can be difficult
> to always recreate the exact parameters that make it to this database.
> Bryan
> "John Bell" wrote:
> > Hi Bryan
> >
> > I assume that you have selected all these databases/tables on the general
> > tab? In which case I would try look at the query plans when run manully from
> > management studio or possibly try and unravel what the queries and run them
> > directly and then look at the query plans.
> >
> > John
> >
> > "Bryan Aldrich" wrote:
> >
> > > I am trying to run through a trace log to improve one of our databases here,
> > > however, the calls originate inside a read only database, goto another
> > > database on the same server before they finally end up in the destination
> > > database that i want to tune.
> > >
> > > How can i get the advisor to travel through these other two databases?
> > >
> > > Right now, it doesn't even recognize any of the statements because none of
> > > them affect any databases it knows about.
> > >
> > > Bryan|||Of course, I have a complete development environment with which I can test
with. I capture a trace from production and then analyze it on our
development environment.
It appears that I will have to manually create some queries that resemble
production that are issued directly against this database before I will be
able to use any sort of automated approach.
Thanks for your assistance.
Bryan
"John Bell" wrote:
> Hi Bryan
> Without knowing exactly what you are doing it is difficult to really
> recommend anything other than the manual approach, any automated tuning
> should be taken as advice that should be reviewed rather than something which
> is an absolute necessity. I would also recommended that you have a system
> which can be benchmarked and any changes compared so that you can be sure
> that it is a positive change before implementing it on a live environment.
> John
>
> "Bryan Aldrich" wrote:
> > Yes, I even tried by selecting all of the databases involved in this trace
> > file. I even selected Master to see if it would execute the sp_ExecuteSQL
> > calls, but it didn't. It analyzes the originating database just fine. I see
> > the sp_ExecuteSQL call go through, but it logs it as "not referencing any
> > tables".
> >
> > I'm hoping for some sort of automated solution because it can be difficult
> > to always recreate the exact parameters that make it to this database.
> >
> > Bryan
> >
> > "John Bell" wrote:
> >
> > > Hi Bryan
> > >
> > > I assume that you have selected all these databases/tables on the general
> > > tab? In which case I would try look at the query plans when run manully from
> > > management studio or possibly try and unravel what the queries and run them
> > > directly and then look at the query plans.
> > >
> > > John
> > >
> > > "Bryan Aldrich" wrote:
> > >
> > > > I am trying to run through a trace log to improve one of our databases here,
> > > > however, the calls originate inside a read only database, goto another
> > > > database on the same server before they finally end up in the destination
> > > > database that i want to tune.
> > > >
> > > > How can i get the advisor to travel through these other two databases?
> > > >
> > > > Right now, it doesn't even recognize any of the statements because none of
> > > > them affect any databases it knows about.
> > > >
> > > > Bryan
Subscribe to:
Posts (Atom)