Sample Header Ad - 728x90

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

0 votes
2 answers
810 views
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)
Asked by Learning_DBAdmin (3924 rep)
Dec 1, 2020, 08:04 AM
Last activity: Dec 1, 2020, 10:45 AM