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:
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.
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)