table needs indexes to improve performance

Question

I was having timeout issue when giving long period of DateTime in below query (query runs from c# application). Table had 30 million rows with a non-clustered index on ID(not a primary key).

Found that there was no primary key so I recently updated ID as Primary Key, it’s not giving me timeout now. Can anyone help me for the below query to create index on more than one key for future and also if I remove non clustered index from this table and create on more than one column? Data is increasing rapidly and need improvement on performace

select 
ID, ReferenceNo, MinNo, DateTime, DataNo from tbl1
where 
DateTime BETWEEN '04/09/2013' AND '20/11/2013'
and  ReferenceNo = 4 and MinNo = 3 and DataNo = 14 Order by ID

this is the create script

CREATE TABLE [dbo].[tbl1](  [ID] [int] IDENTITY(1,1) not null,  [ReferenceNo] [int] not null,   [MinNo] [int] not null,     [DateTime] [datetime] not null,     [DataNo] [int] not null,  CONSTRAINT [tbl1_pk] PRIMARY KEY CLUSTERED  ([ID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS 
= ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]

Could you provide a complete table definition for tbl1? Right click the table in SSMSE & choose "Script as Create..." — laylarenee, Nov 21 '13 at 11:00
I provided an answer. Note that adding an index slows down insert/update times because the table & index need updated together. Indexes also become fragmented over time and need rebuilt to perform at max efficiency. — laylarenee, Nov 22 '13 at 14:25

Olle Johansson · Answer 1 · 2013-11-21T11:57:27.337

Its hard to tell which index you should use without knowing more about your database and how its used.

You may want to change the ID column to a clustered index. If ID is an identity column you will get very few page splits while inserting new data. It will however require you to rebuild the table and that may be a problem depending on your usage of the database. You will be looking at some downtime.

If you want a covering index it should look something like this:

CREATE NONCLUSTERED INDEX [MyCoveringIndex] ON tbl1
(
    [ReferenceNo] ASC,
    [MinNo] ASC,
    [DataNo] ASC,
    [DateTime ] ASC
)

Its no need to include ID as a column as its already in the clusted index (clusted index columns will be included in all other indexes). This will however use up a whole lot of space (somewhere in the range of 1GB if the columns above are of the types int and datetime). It will also affect your insert, update and delete performance on the table in (most cases) a negative way.

You can create the index in online mode if you are using Enterprice Edition of SQL server. In all other cases there will be a lock on the table while creating the index.

Its also hard to know what other queries that are made against the table. You may want to tweek the order of the columns in the index to better match other queries.

score 0 · Answer 2 · answered Nov 22 '13 at 14:13

Indexing all fields would be fastest, but would likely waste a ton of space. I would guess that a date index would provide the most benefit with the least storage capacity cost because the data is probably evenly spread out over a large period of time. If the MIN() MAX() dates are close together, then this will not be as effective:

CREATE NONCLUSTERED INDEX [IDX_1] ON [dbo].[tbl1] (
    [DateTime] ASC
)
GO

As a side note, you can use SSMSE's "Display Estimated Execution Plan" which will show you what the DB needs to do to get your data. It will suggest missing indexes and also provide CREATE INDEX statements. These suggestions can be quite wasteful, but they will give you an idea of what is taking so long. This option is in the Standard Toolbar, four icons to the right from "Execute".

table needs indexes to improve performance

2 Answers2