By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,086 Members | 1,452 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,086 IT Pros & Developers. It's quick & easy.

Index Tuning Wizard - headache

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a

"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

P: n/a
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

P: n/a
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.