Index Performance

[Mansoor Azam]

I have the following table with indexes

CREATE TABLE dbo.Scratch (

ItemID int IDENTITY (1, 1) NOT NULL ,
Login varchar (12) NOT NULL ,
StartDate datetime NULL ,
PayDate datetime NULL ,
LastDisconnect datetime NULL

)

GO

CREATE INDEX idxPayDate ON dbo.Scratch(Pay Date)

GO

CREATE INDEX idxStartDate ON dbo.Scratch(Sta rtDate)

GO

The index distribution stats show a very poor average row hits (whatever
that means) for these two indexes

e.g for idxPaydate its 61160 (11.89% very poor )

and there are many nulls in the distribution steps.

Also I have many queries with conditions like (paydate is null and startdate
is not null etc) which means the index will not be used anyway (is this
correct?). My application is giving timeouts for such queries.

So my question is how can I make better indexes and make sure they are used?

thx

(i'm using SQL 6.5 )

[Hari]

Re: Index Performance

Hi,

As you said most of your searches are based on both PayDate and StartDate,
Why dont you create an index based on both fields
and see how effective it is. I have seen so many instances where indivual
indexes are not beoing used in SQL 6.5.
Try creating the below index and see whether it is useful. If it increases
the performance you can drop the old 2 indexes.


CREATE INDEX idx_new ON dbo.Scratch(Pay Date,StartDate)

Thanks
Hari
MCDBA





"Mansoor Azam" <mansoorb@shoa. net> wrote in message
news:c6l5ds$d8a vq$1@ID-31123.news.uni-berlin.de...[color=blue]
> I have the following table with indexes
>
> CREATE TABLE dbo.Scratch (
>
> ItemID int IDENTITY (1, 1) NOT NULL ,
> Login varchar (12) NOT NULL ,
> StartDate datetime NULL ,
> PayDate datetime NULL ,
> LastDisconnect datetime NULL
>
> )
>
> GO
>
> CREATE INDEX idxPayDate ON dbo.Scratch(Pay Date)
>
> GO
>
> CREATE INDEX idxStartDate ON dbo.Scratch(Sta rtDate)
>
> GO
>
> The index distribution stats show a very poor average row hits (whatever
> that means) for these two indexes
>
> e.g for idxPaydate its 61160 (11.89% very poor )
>
> and there are many nulls in the distribution steps.
>
> Also I have many queries with conditions like (paydate is null and[/color]
startdate[color=blue]
> is not null etc) which means the index will not be used anyway (is this
> correct?). My application is giving timeouts for such queries.
>
> So my question is how can I make better indexes and make sure they are[/color]
used?[color=blue]
>
> thx
>
> (i'm using SQL 6.5 )
>
>
>
>[/color]

[Gert-Jan Strik]

Re: Index Performance

What is true for SQL-Server 7.0 and above is especially true for 6.5:
make sure the table has a clustered index! Currently, your table doesn't
seem to have one.

Apart from advantages in general, the clustered index is very useful for
range queries that return a fair percentage of all rows (such as the
queries you describe). In that case, you would to create the clustered
index on the columns mentioned in the WHERE clause.

FYI: SQL-Server 6.5 does not support index intersection, which means
that only one (or zero) indexes will be used for each table in the
query. Therefore, a compound index (as suggested by Hari) might also
help.

Hope this helps,
Gert-Jan


Mansoor Azam wrote:[color=blue]
>
> I have the following table with indexes
>
> CREATE TABLE dbo.Scratch (
>
> ItemID int IDENTITY (1, 1) NOT NULL ,
> Login varchar (12) NOT NULL ,
> StartDate datetime NULL ,
> PayDate datetime NULL ,
> LastDisconnect datetime NULL
>
> )
>
> GO
>
> CREATE INDEX idxPayDate ON dbo.Scratch(Pay Date)
>
> GO
>
> CREATE INDEX idxStartDate ON dbo.Scratch(Sta rtDate)
>
> GO
>
> The index distribution stats show a very poor average row hits (whatever
> that means) for these two indexes
>
> e.g for idxPaydate its 61160 (11.89% very poor )
>
> and there are many nulls in the distribution steps.
>
> Also I have many queries with conditions like (paydate is null and startdate
> is not null etc) which means the index will not be used anyway (is this
> correct?). My application is giving timeouts for such queries.
>
> So my question is how can I make better indexes and make sure they are used?
>
> thx
>
> (i'm using SQL 6.5 )[/color]

--
(Please reply only to the newsgroup)