473,320 Members | 1,914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
3 2885

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: frank niedermeyer | last post by:
Hello Newsgroup ! I'm working with SQL Server 7.0 and Access 2000 . I build some stored procedures and indexes to enhance the performance of my queries. Now there is another application...
0
by: NathanG | last post by:
Hi, I've run the (SQL2K) Index Tuning Wizard against our 50GB database and it has recommended changes that will produce a "-3506%" improvement iin performance. The recommendations include a...
13
by: LUIS FAJARDO | last post by:
I have the following sintax: Select * From Inventory Where PartId = Coalesce(v_PartId, PartId) this type of query is used within an store procedure that provide the v_PartId parameter, the...
24
by: Henrik Steffen | last post by:
hello all, on my master-db-server i'm running postgres 7.4.1, and I have got two slave-servers running postgres 7.4.2 running the following query on the master-server (7.4.1) delivers: ...
1
by: serge | last post by:
I am reading "SQL Server Query Performance Tuning Distilled", on page 104 it talks about one of the index design recommendations which is to choose the column that has very high selectivity of...
0
by: uzi | last post by:
Hi Does someone know how to embed the audio and video tuning wizard into an application? I have noticed that the same component is used by messenger and Microsoft one note.
2
by: Deepa Jeevagan | last post by:
Hi, The application I am currently working on is developed in Oracle 8i. I have a table which has a nullable numeric column say CustNo. This column has an index defined on it and oracle uses...
4
by: Tommy Hayes | last post by:
Hello all, I want to use the SQL Server 2005 Tuning Advisor on our database, and I'm hoping someone here can just confirm the steps for me. We have a 10GB database that has a number of...
2
by: BD | last post by:
Hi, all. My background is more Oracle than db2. My skills at SQL tuning are quite limited. I'm running 8.2 on Windows. I'm tasked with some SQL optimization, and am doing some explain plans...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.