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?
> > @.=)
>
>

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...
>
>

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...
>
>

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 -