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)