Index Performance

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mansoor Azam (mansoorb_at_shoa.net)
Date: 04/27/04 

Date: Tue, 27 Apr 2004 13:25:38 +0500

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 )

Relevant Pages

  • Re: Help with query on Audit Trail
    ... queried all records with NewValue 2nd Line renaming the DateTime ... created a new query based on the queries of step 1 and 2. ... Line in the column OldValue or NewValue, ...
    (comp.databases.ms-access)
  • Re: Suitable Table Design ?
    ... in Queries - Note that AmendId is a sequence number for each of the ... > CURRENT_TIMESTAMP, concrtdate DATETIME NOT NULL, brokerid INTEGER NOT NULL ... PRIMARY KEY ...
    (microsoft.public.sqlserver.programming)
  • Help with query on Audit Trail
    ... queried all records with NewValue 2nd Line renaming the DateTime ... created a new query based on the queries of step 1 and 2. ... Line in the column OldValue or NewValue, ...
    (comp.databases.ms-access)
  • Re: Using Function Table ? using Sql Server 2008
    ... As I have no sound knowledge about performance and tuning of queries, however I have heard that performance can be evaluated using Execution Plan, is it true? ... @StartDate datetime, ... SELECT DATEADD(day, N, @StartDate) ... @Start BIGINT, ...
    (microsoft.public.sqlserver.programming)
  • Re: User-defined functions in where clause
    ... function expression directly into your queries. ... datetime), and you should see an improvement. ... >UDF is used to row by row process similar how cursors work. ... >>Run this query in query analyzer with the server trace option on and you ...
    (microsoft.public.sqlserver.programming)