0

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)

2

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.

3
  • Thanks a lot Brent for taking out time and answering my question, it means a lot to me. I am reading answer again and again to make out what exactly needs to be done.I could see that APT_ID is not very selective. I am not sure if filtered index would be of any use here or should I just drop this index completely and let the query go for clustered index seek/scan. Dec 1 '20 at 11:49
  • Yes, unfortunately personalized index design is beyond the scope of what I can do quickly here - it involves reviewing the queries and the tables. That's the kind of thing I cover in my training classes, though. Hope that helps!
    – Brent Ozar
    Dec 1 '20 at 12:03
  • Thanks a lot, have subscribed to mastering class yesterday(taking benefit of Black Friday Sale), will go through it and I am sure that's going to add lot of values :). Thank you again. Dec 1 '20 at 12:12
1

Impossible to say anything since missing index is dead-stupid.

For one thing, it doesn't account for selectivity. Having an index that doesn't cover the query is just fine if you only return few rows. But MI doesn't care about selectivity so it want to cover every query. And it can suggest one index on (a) including (b,c), and another index on (a) including (b,c,d). I.e., no smartness (which comes from the fact that it doesn't want to spend too much time when generating these recommendations).

You create indexes to support your queries.

Turn on query store and work from there.

Or create indexes and after a while see whether they were used.

Or, if you know your query load, then work with the queries.

Perhaps I missed some key point in your post, then pls let me know. :-)

2
  • Thank you for taking time and answering the question, I fail to understand when index is already there and not used efficiently then why it is again asking to create index on the same column. I will use Query store soon after upgrade(in the pipeline). Dec 1 '20 at 8:35
  • See my first sentence: because it is dead stupid. :-) Are you saying that it suggests to create the exact same index? If so, consider it a bug and report it to MS. Or functionally the same (as in my example above)? Then chalk it down to not wanting to spend many CPU cycles when generating the suggestions. Don't count on these recommendations is bottom line. Dec 1 '20 at 9:55

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.