467,908 Members | 1,844 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,908 developers. It's quick & easy.

Index Tuning Wizard - headache

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(uniqueid 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=@counter+1
end
Jul 20 '05 #1
  • viewed: 2696
Share:
3 Replies

"Maryam" <ma*********@hotmail.com> wrote in message
news:41**************************@posting.google.c om...
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(uniqueid 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=@counter+1
end


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

Simon
Jul 20 '05 #2
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:

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(uniqueid 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=@counter+1
end


--
(Please reply only to the newsgroup)
Jul 20 '05 #3
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
SQLProfilerStandard 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,longcol02 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 <so***@toomuchspamalready.nl> wrote in message news:<40***************@toomuchspamalready.nl>...
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:

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(uniqueid 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=@counter+1
end

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by frank niedermeyer | last post: by
13 posts views Thread by LUIS FAJARDO | last post: by
24 posts views Thread by Henrik Steffen | last post: by
reply views Thread by uzi | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.