How to Choose an index?

How to Choose an index?

Post by serg » Fri, 01 Nov 2002 04:36:30



Using SQL Server 7 (SP4) with Access 2002 adp project.

I am trying to improve my system's performance by adding
indexes to my table. Right now, there is only one single index set
on the Surrogate PK WODetailNo of tblWorkOrderDetails.

CREATE TABLE [dbo].[tblWorkOrderDetails] (
 [WODetailNo] [int] IDENTITY (1, 1) NOT NULL ,
 [WONo] [int] NULL ,
 [WODetailSeq] [tinyint] NULL ,
 [ProductNo] [int] NULL ,
 [WODetailQty] [smallint] NULL ,
 [WODetailCustomerPrice] [smallmoney] NULL ,
 [WODetailPrice] [smallmoney] NULL ,
 [WODetailRequiredDate] [smalldatetime] NULL ,
 [WODetailScheduledDate] [smalldatetime] NULL ,
 [ProdConsChoiceNo] [int] NULL ,
 [WODetailStatusNo] [int] NULL ,
)

The Natural keys are WONO + WODetailSeq, for example
Work Order No : 11111-1, 11111-2, 11111-3,....

If i run in the Query Analyzer the following:

select * from tblWorkOrderDetails WITH (NOLOCK) where WONO = 11111

then run the Perform Index Analysis, this is what SQL Server recommends to
me:

CREATE NONCLUSTERED INDEX [tblWorkOrderDetails0] ON
[dbo].[tblWorkOrderDetails]([WONo], [WODetailNo], [WODetailSeq],
[ProductNo], [WODetailQty], [WODetailCustomerPrice], [WODetailPrice],
[WODetailRequiredDate], [WODetailScheduledDate])

My questions are:

1- When creating indexes, do i create an index and put all columns that i
want
to index as one single index? If not, then why is SQL suggesting to create
one
single index and putting all these columns in one index?

2- How do i decide when to create an index. I am sure creating indexes on
all columns don't make sense.

I would appreciate any help.

Thank you very much

 
 
 

How to Choose an index?

Post by Erland Sommarsko » Fri, 01 Nov 2002 08:03:04



> select * from tblWorkOrderDetails WITH (NOLOCK) where WONO = 11111

> then run the Perform Index Analysis, this is what SQL Server recommends to
> me:

> CREATE NONCLUSTERED INDEX [tblWorkOrderDetails0] ON
> [dbo].[tblWorkOrderDetails]([WONo], [WODetailNo], [WODetailSeq],
> [ProductNo], [WODetailQty], [WODetailCustomerPrice], [WODetailPrice],
> [WODetailRequiredDate], [WODetailScheduledDate])

> My questions are:

> 1- When creating indexes, do i create an index and put all columns that
> i want to index as one single index? If not, then why is SQL suggesting
> to create one single index and putting all these columns in one index?

> 2- How do i decide when to create an index. I am sure creating indexes on
> all columns don't make sense.

Actually, it first looked completely crazy to me at first. But then it
occurred to me: you are in fact buying yourself a second clustered
index on the table this way.

Is it a good thing? You know the application, I don't. I believe that
the intention with the Index Tuning Wizard, which I have never came
around to play with myself, is that you should give it a day's worth
of workload. If you only feed it a single query, you can get quite
funny results.

Given your narrative, I would suggest a clustered index on WONO and
WoDetailSeq, unless you already have a clustered index, in which case
I would make the index non-clustered. But were I to actually see the
system, I might arrive at some other conclusion.

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org
The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?

 
 
 

How to Choose an index?

Post by BP Margoli » Fri, 01 Nov 2002 11:46:03


Serge,

As indicated by Erland, the more information you feed to the Index Tuning
Wizard, the more logical and the more reasonable the results coming back
from the Index Tuning Wizard will be.

If you give it a single query, the Index Tuning Wizard will definitely
recommend an indexing strategy that will improve performance for that one
query. However if you give it a workload ... all queries and actions that
constitute a representative sample of the work done by an application ...
then the Index Tuning Wizard might realize that your one query represents
.001% of your daily activity, and that the best recommendation for that one
query is a very poor recommendation when taken in context of your entire
application.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Using SQL Server 7 (SP4) with Access 2002 adp project.

> I am trying to improve my system's performance by adding
> indexes to my table. Right now, there is only one single index set
> on the Surrogate PK WODetailNo of tblWorkOrderDetails.

> CREATE TABLE [dbo].[tblWorkOrderDetails] (
>  [WODetailNo] [int] IDENTITY (1, 1) NOT NULL ,
>  [WONo] [int] NULL ,
>  [WODetailSeq] [tinyint] NULL ,
>  [ProductNo] [int] NULL ,
>  [WODetailQty] [smallint] NULL ,
>  [WODetailCustomerPrice] [smallmoney] NULL ,
>  [WODetailPrice] [smallmoney] NULL ,
>  [WODetailRequiredDate] [smalldatetime] NULL ,
>  [WODetailScheduledDate] [smalldatetime] NULL ,
>  [ProdConsChoiceNo] [int] NULL ,
>  [WODetailStatusNo] [int] NULL ,
> )

> The Natural keys are WONO + WODetailSeq, for example
> Work Order No : 11111-1, 11111-2, 11111-3,....

> If i run in the Query Analyzer the following:

> select * from tblWorkOrderDetails WITH (NOLOCK) where WONO = 11111

> then run the Perform Index Analysis, this is what SQL Server recommends to
> me:

> CREATE NONCLUSTERED INDEX [tblWorkOrderDetails0] ON
> [dbo].[tblWorkOrderDetails]([WONo], [WODetailNo], [WODetailSeq],
> [ProductNo], [WODetailQty], [WODetailCustomerPrice], [WODetailPrice],
> [WODetailRequiredDate], [WODetailScheduledDate])

> My questions are:

> 1- When creating indexes, do i create an index and put all columns that i
> want
> to index as one single index? If not, then why is SQL suggesting to create
> one
> single index and putting all these columns in one index?

> 2- How do i decide when to create an index. I am sure creating indexes on
> all columns don't make sense.

> I would appreciate any help.

> Thank you very much

 
 
 

How to Choose an index?

Post by serg » Fri, 01 Nov 2002 23:41:01


Quote:> around to play with myself, is that you should give it a day's worth
> of workload.

I tried this many times, i ran the Profiler wizard run for hours, then
tried to run the Index Tuning Wizard on the file, but kept getting errors
and never got any feedback. So i said to myself let me add indexes
the current way i was doing until i figure out how to troubleshoot the
error.

Quote:>If you only feed it a single query, you can get quite
> funny results.

> Given your narrative, I would suggest a clustered index on WONO and
> WoDetailSeq, unless you already have a clustered index, in which case
> I would make the index non-clustered. But were I to actually see the
> system, I might arrive at some other conclusion.

I was planning on putting an index on WONO and WODetailSeq until
i ran that wizard and the wizard got me completely confused.

Thank you

> --

> I support PASS - the definitive global community for SQL Server
> professionals - http://www.sqlpass.org
> The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?

 
 
 

How to Choose an index?

Post by serg » Fri, 01 Nov 2002 23:42:49


Quote:> As indicated by Erland, the more information you feed to the Index Tuning
> Wizard, the more logical and the more reasonable the results coming back
> from the Index Tuning Wizard will be.

> If you give it a single query, the Index Tuning Wizard will definitely
> recommend an indexing strategy that will improve performance for that one
> query. However if you give it a workload ... all queries and actions that
> constitute a representative sample of the work done by an application ...
> then the Index Tuning Wizard might realize that your one query represents
> .001% of your daily activity, and that the best recommendation for that
one
> query is a very poor recommendation when taken in context of your entire
> application.

Alright, i understand your explanation and it makes a lot of sense to me
now.
I'm going to try to further use the Profiler, learn it a bit more and try to
run
the Index Tuning Wizard on some trace files.

Thank you

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Using SQL Server 7 (SP4) with Access 2002 adp project.

> > I am trying to improve my system's performance by adding
> > indexes to my table. Right now, there is only one single index set
> > on the Surrogate PK WODetailNo of tblWorkOrderDetails.

> > CREATE TABLE [dbo].[tblWorkOrderDetails] (
> >  [WODetailNo] [int] IDENTITY (1, 1) NOT NULL ,
> >  [WONo] [int] NULL ,
> >  [WODetailSeq] [tinyint] NULL ,
> >  [ProductNo] [int] NULL ,
> >  [WODetailQty] [smallint] NULL ,
> >  [WODetailCustomerPrice] [smallmoney] NULL ,
> >  [WODetailPrice] [smallmoney] NULL ,
> >  [WODetailRequiredDate] [smalldatetime] NULL ,
> >  [WODetailScheduledDate] [smalldatetime] NULL ,
> >  [ProdConsChoiceNo] [int] NULL ,
> >  [WODetailStatusNo] [int] NULL ,
> > )

> > The Natural keys are WONO + WODetailSeq, for example
> > Work Order No : 11111-1, 11111-2, 11111-3,....

> > If i run in the Query Analyzer the following:

> > select * from tblWorkOrderDetails WITH (NOLOCK) where WONO = 11111

> > then run the Perform Index Analysis, this is what SQL Server recommends
to
> > me:

> > CREATE NONCLUSTERED INDEX [tblWorkOrderDetails0] ON
> > [dbo].[tblWorkOrderDetails]([WONo], [WODetailNo], [WODetailSeq],
> > [ProductNo], [WODetailQty], [WODetailCustomerPrice], [WODetailPrice],
> > [WODetailRequiredDate], [WODetailScheduledDate])

> > My questions are:

> > 1- When creating indexes, do i create an index and put all columns that
i
> > want
> > to index as one single index? If not, then why is SQL suggesting to
create
> > one
> > single index and putting all these columns in one index?

> > 2- How do i decide when to create an index. I am sure creating indexes
on
> > all columns don't make sense.

> > I would appreciate any help.

> > Thank you very much

 
 
 

1. allowing user to choose active index

hi,

i am trying to allow the user to select the active index of a database, and am
having some problems.

i am using a combobox to hold the list of indexes.  the list of available
indexes is obtained by the following:

  MDTable.IndexDefs.Update;
  MDTable.GetIndexNames(IndexList.Items);

The indexlist is the tcombobox; mdtable is the table being reindexed.  so the
above works okay - the list is obtained.

question: once the user selects one, how do i activate it?
i thought of doing onchange:

  MDTable.IndexName := IndexList.Items[i];

but the question is: how do i know what that "i" is?

any help would be appreciated. if you follow up to this message, please send
a copy of it to me via email.

thanks,
yash

2. Patch version of 2.5

3. Why is optimizer choosing this index?

4. Custom Aggregations

5. Optimizer choosing wrong index in 12.0

6. Lock mode in stored procedures?

7. Optimixer chose Non-Index sacn

8. Unique value over different servers

9. Optimixer chose Non-Index scan

10. How does IDS 7.3 choose between indexes?

11. OnLine choosing wrong index

12. Choosing wrong Index

13. Why is SQL Server choosing the wrong index?