Sql-server – Need advise on Indexaphobia: High value missing index with High Impact. When index is already there

indexsp-blitzindexsql server

I am trying to understand the true meaning of Missing index for this particular table when I already have index on it and how to address it.

I have a heavily used table and is of approximately 2.5GB. Since its heavily used, a bit hesitant to create index which are not very much required(debatable). This table was heap earlier, recently it is changed to table after changing primary key to clustered from non-clustered.

When I run sp_blitzindex with database name or with this table, it gives result as below:

Result of Blitzindex

Mostly it suggests to create index on APT_ID column and include suggests LOGID, RECEIVE_TIME and few other columns. If we look at table definition primary key is defined at LOGID and RECEIVE_TIME. And also we have a NC index on APT_ID column.

Table DDL is as below:

CREATE TABLE [dbo].[TXN_LOG](
    [LOGID] [int] IDENTITY(1,1) NOT NULL,
    [RECEIVE_TIME] [varchar](15) NOT NULL,
    [APT_ID] [int] NOT NULL,
    [VAR32_01] [varchar](32) NULL,
    [VAR32_02] [varchar](32) NULL,
    .
    .
    .
    [ERROR_CODE] [varchar](20) NULL,
    [MESSAGE_ID] [varchar](40) NULL,
    [END_POINT_ID] [varchar](50) NULL,
    [NODE_ID] [varchar](40) NULL,
    [TIMEOUT_NETWORK_ID] [int] NULL,
    [TXN_SUMMARY] [numeric](1, 0) NULL,
    [D_FLAG] [numeric](1, 0) NULL,
     CONSTRAINT [PKTXN_LOG] PRIMARY KEY CLUSTERED 
    (
        [LOGID] ASC,
        [RECEIVE_TIME] ASC
    ))
GO

NC Index on this table is :

CREATE NONCLUSTERED INDEX [IDX_TXN_LOG_1] ON [dbo].[TXN_LOG]
(
    [APT_ID] ASC
)
INCLUDE([RECEIVE_TIME]) 
GO

Current usage of index doesn't seem to support NC index as write is higher than read count.

Usage of Index

Sincere apologies for attaching images in the question. Appreciate if I can get some expert advice on this. Thanks in advance.

Version: Microsoft SQL Server 2014 (SP3-GDR) (KB4532095) – 12.0.6118.4
(X64) Dec 12 2019 21:46:15 Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3
(Build 9600: ) (Hypervisor)

Best Answer

To clean up and simplify your question a little:

  • The index you already have is on [LOGID], [RECEIVE_TIME]
  • SQL Server is asking for an index on APT_ID, and it wants you to include LOGID, RECEIVE_TIME

That is indeed a different index than you already have.

Let's take the old example of the phone book. The white pages of the phone book were on LAST_NAME, FIRST_NAME, MIDDLE_NAME. That's great if you run a query like this:

SELECT *
FROM PHONE_BOOK
WHERE LAST_NAME = 'OZAR'

But if you don't know someone's last name, and you ask for this:

SELECT *
FROM PHONE_BOOK
WHERE FIRST_NAME = 'BRENT'

Then you're going to be scanning the entire phone book looking for me. The first column in the index is incredibly important. That's why SQL Server is asking for an index on APT_ID, plus other columns included.

So you might ask, "Why isn't an existing index on APT_ID alone enough?" The problem is that in your case, APT_ID isn't selective enough, or people are asking for ranges of APT_IDs. SQL Server's having to do tons of key lookups, and the missing index recommendations are trying to remove those.

Related Question