1

I have a question on indexing strategy as i am trying to do indexing for a database.

I understand the basic indexing strategy which are:

  • Clustered index on PrimaryKey
  • Add Non Clustered index for other columns as per requirement.
  • Should have indexes for WHERE/JOIN/GROUP BY/ORDER BY columns
  • Covering indexes for columns to avoid keylookup.
  • Check the workload and avoid adding too many indexes
  • Index Foreign Keys
  • Index any other unique keys etc.. I still have some questions and i couldn't find an exact answer to my questions.

Here is an example of a table that i have.

USE [DEMODB]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employee_Header](
    [Id] [uniqueidentifier] NOT NULL,
    [PageId] [uniqueidentifier] NOT NULL,
    [Dept] [varchar](256) NULL,
    [DeptName] [varchar](256) NULL,
    [SubUnit] [varchar](256) NULL,
    [ShiftType] [varchar](256) NULL,
    [ReportDate] [varchar](256) NULL,
    [ReportTime] [varchar](256) NULL,
    [AccessNumber] [varchar](256) NULL,
    [AccessOnAfterDate] [varchar](256) NULL,
    [AccessOnAfterTime] [varchar](256) NULL,
    [EmpBand] [varchar](256) NULL,
    [WorkSite] [varchar](256) NULL,
    [Location] [varchar](256) NULL,
    [Location_FieldValue] [varchar](256) NULL,
    [Location_Description] [varchar](256) NULL,
 CONSTRAINT [PK_Employee_Header] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

As you can see [Id] is the primary key here.[PageID] is the foreign key and it is not unique. There are few queries which uses whereclause on `[Dept],[SubUnit], [ShiftType] and [ReportDate]'.

One query displays columns [Dept] and [ShiftType] and other query displays [Dept],[ReportDate],[ReportTime],[EmpBand]

  1. I am confused as to how to create NonClustered indexes with Include columns so that same indexes can be used to both queries Or
  2. should i create separate clustered indexes for where each columns
  3. then create a separate single NonClustered index with PrimaryKey column as unique column and all the display columns in the include section.
  4. Should i just keep the display columns in the keycolumn section?

Most of the examples available shows querying to a single table with an Id and one or more select column. Can someone help me to understand how you approach similar situations?

I created two indexes as shown below.

First one is for the where clauses.I have all the where clause fields added as key columns in this non clustered index

CREATE NONCLUSTERED INDEX [ix_Employee_Filters] ON [dbo].[Employee_Header] ( [Dept] ASC, [SubUnit] ASC, [ShiftType] ASC, [ReportDate] ASC, )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

The second one is for the select columns.Some of the select columns are already part of the first index so i have only two columns that needs to be covered. So i added them as Include fields with Id(Primary key field) as the key column.

CREATE NONCLUSTERED INDEX [ix_Employee_Include_Fields] ON [dbo].[Employee_Header] ( [Id] ASC ) INCLUDE ( [ReportTime], [EmpBand]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

Still when i run and see the execution plan, i get the message that there is a missing index.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[Employee_Header] ([ReportDate]) INCLUDE ([Id],[Dept],[ShiftType],[ReportTime],[EmpBand])

I don't understand why query optimizer is suggesting to a date filed as key field and other fields in the include section. I already have a Nonclustered index with ReportDate among other where fields.

Appreciate for sharing your approach.But can anyone answer/share your perspective on the specific example mentioned?

  • 1
    You should include the exact queries you're looking to add supporting indexes for. Once you add a given index, you can test your queries to see if the execution plan utilizes them or not to sanity check whether they're usable by each query. – LowlyDBA Oct 16 at 19:02
  • @LowlyDBA.I have the select fields and where field which is being used in two queries.How do you approach/or what combination of non clustered indexes u will create?I will check the execution plan to see if the indexes i created is used or not.But before that i need to know for where clause i should have this index and for select fields i should have this covering index?I am trying to understand how seasoned DBA's like you approach this ? – user9516827 Oct 16 at 19:09
  • 1
    For all we know one of your queries is using a function, which could materially affect indexing. I would not approach this unless I knew the queries I was creating indexes for (a word-problem summary of the queries doesn't count). – LowlyDBA Oct 16 at 19:29
2

For a general-purpose or OLTP design, the initial index design should be more conservative:

  • Clustered index on Primary Key.

  • Unique non-clustered index any other unique keys.

  • Index supporting each Foreign Key (where not already covered above).

Then, for very large tables, consider changing to a Nonclustered Primary Key and a Clustered Columnstore.

Then additional indexes as needed for optimization of the actual workload. These can be indexes with included columns, filtered indexes, non-clustered columnstore indexes, or indexed views on a case-by-case basis.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.