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(PayDate)
GO
CREATE INDEX idxStartDate ON dbo.Scratch(StartDate)
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 )