Index Tuning Wizard - headache

[Maryam]

Hi,

I am having problems getting anything useful out of the index tuning
wizard.

I have created a table and inserted data into it. When i run the index
tuning wizard i expect 2 indexes to be recommended so the book says
(Index011 with a key on the uniqueid column and a non clustered index
named table02 with a key on the col03 and LongCol02)
Instead i get nothing being recommended.

What am i doing wrong????

Please help
Maryam


if exists (select name from dbo.sysobjects where name ='table01' and
type ='u')
drop table table01
create table table01(uniquei d int identity, longcol02 char(300)
DEFAULT 'THIS IS THE DEfault column',
col03 char(1))
go

declare @counter int
set @counter =1
while @counter<=1000
begin
insert table01 (col03) values('a')
insert table01 (col03) values('b')
insert table01 (col03) values('c')
insert table01 (col03) values('d')
insert table01 (col03) values('e')
insert table01 (col03) values('f')
set @counter=@count er+1
end

[Simon Hayes]

Re: Index Tuning Wizard - headache


"Maryam" <maryamafzal@ho tmail.com> wrote in message
news:41ec4650.0 405110835.2e666 d2c@posting.goo gle.com...[color=blue]
> Hi,
>
> I am having problems getting anything useful out of the index tuning
> wizard.
>
> I have created a table and inserted data into it. When i run the index
> tuning wizard i expect 2 indexes to be recommended so the book says
> (Index011 with a key on the uniqueid column and a non clustered index
> named table02 with a key on the col03 and LongCol02)
> Instead i get nothing being recommended.
>
> What am i doing wrong????
>
> Please help
> Maryam
>
>
> if exists (select name from dbo.sysobjects where name ='table01' and
> type ='u')
> drop table table01
> create table table01(uniquei d int identity, longcol02 char(300)
> DEFAULT 'THIS IS THE DEfault column',
> col03 char(1))
> go
>
> declare @counter int
> set @counter =1
> while @counter<=1000
> begin
> insert table01 (col03) values('a')
> insert table01 (col03) values('b')
> insert table01 (col03) values('c')
> insert table01 (col03) values('d')
> insert table01 (col03) values('e')
> insert table01 (col03) values('f')
> set @counter=@count er+1
> end[/color]

What sort of queries (workload) did you trace to provide to the wizard?

Simon

[Gert-Jan Strik]

Re: Index Tuning Wizard - headache

1. What batch of statements are you feeding the the Index Tuning Wizard
on which to base its optimizations?

2. In your example, each page of the table (8060 bytes) would be able to
hold 25 or 26 rows. You only insert 6 rows, which means the table has
only one page. In that case there is not much to optimize...

Gert-Jan


Maryam wrote:[color=blue]
>
> Hi,
>
> I am having problems getting anything useful out of the index tuning
> wizard.
>
> I have created a table and inserted data into it. When i run the index
> tuning wizard i expect 2 indexes to be recommended so the book says
> (Index011 with a key on the uniqueid column and a non clustered index
> named table02 with a key on the col03 and LongCol02)
> Instead i get nothing being recommended.
>
> What am i doing wrong????
>
> Please help
> Maryam
>
> if exists (select name from dbo.sysobjects where name ='table01' and
> type ='u')
> drop table table01
> create table table01(uniquei d int identity, longcol02 char(300)
> DEFAULT 'THIS IS THE DEfault column',
> col03 char(1))
> go
>
> declare @counter int
> set @counter =1
> while @counter<=1000
> begin
> insert table01 (col03) values('a')
> insert table01 (col03) values('b')
> insert table01 (col03) values('c')
> insert table01 (col03) values('d')
> insert table01 (col03) values('e')
> insert table01 (col03) values('f')
> set @counter=@count er+1
> end[/color]

--
(Please reply only to the newsgroup)

[Maryam]

Re: Index Tuning Wizard - headache

Hi

Very strange, it worked today.

I tried again today and it worked, im not sure why. (my table only
has 3000 records)

Initially i created a trace file "trace01" based on the
SQLProfilerStan dard template and ran the trace whilst executing the
code to

create the table, insert the data and run the select statements.

select col03, longcol02 from table01 where col03='a'
select uniqueid,longco l02 from table01 where unqueid=10000
select * from table01 where uniqueid between 5000 and 10000
go

I then opened the index tuning wizard, kept the standard settings
(tried thorough) and selected the "trace01" template into the workload
file, then selected table01 and continued.

Maryam


Gert-Jan Strik <sorry@toomuchs pamalready.nl> wrote in message news:<40A12A05. 982F4054@toomuc hspamalready.nl >...[color=blue]
> 1. What batch of statements are you feeding the the Index Tuning Wizard
> on which to base its optimizations?
>
> 2. In your example, each page of the table (8060 bytes) would be able to
> hold 25 or 26 rows. You only insert 6 rows, which means the table has
> only one page. In that case there is not much to optimize...
>
> Gert-Jan
>
>
> Maryam wrote:[color=green]
> >
> > Hi,
> >
> > I am having problems getting anything useful out of the index tuning
> > wizard.
> >
> > I have created a table and inserted data into it. When i run the index
> > tuning wizard i expect 2 indexes to be recommended so the book says
> > (Index011 with a key on the uniqueid column and a non clustered index
> > named table02 with a key on the col03 and LongCol02)
> > Instead i get nothing being recommended.
> >
> > What am i doing wrong????
> >
> > Please help
> > Maryam
> >
> > if exists (select name from dbo.sysobjects where name ='table01' and
> > type ='u')
> > drop table table01
> > create table table01(uniquei d int identity, longcol02 char(300)
> > DEFAULT 'THIS IS THE DEfault column',
> > col03 char(1))
> > go
> >
> > declare @counter int
> > set @counter =1
> > while @counter<=1000
> > begin
> > insert table01 (col03) values('a')
> > insert table01 (col03) values('b')
> > insert table01 (col03) values('c')
> > insert table01 (col03) values('d')
> > insert table01 (col03) values('e')
> > insert table01 (col03) values('f')
> > set @counter=@count er+1
> > end[/color][/color]