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 -

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.