Column not covered by index: new index or add to existing

Question

Column not covered by index: new index or add to existing

Indianrock

SSC-Insane

Points: 20333
More actions
January 15, 2015 at 9:24 am

#314561

We have a large table with many columns and many indexes. One poorly performing query is having to do a key lookup when the where clause includes a particular column with no covering index.
Are you generally better off adding a new index or adding the column to an existing index ( included columns )
Column: LAST_STATE_RESPONSE_CODE
The Query Processor estimates that implementing the following index could improve the query cost by 88.9332%.
*/
/*
USE [ database name]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SERVICE_REQUEST] ([BUSINESS_PROCESS_STATUS],[color=#F00]LAST_STATE_RESPONSE_CODE[b][size="3"][/size][/b][/color],[CONCRETE_TYPE])
INCLUDE ([LIENHOLDER_PERFORMING_LIEN_FILING_ID],[MAKE],[YEAR],[MANUFACTURER_ID],[CLIENT_ID])
GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

My experience is that it is almost always better to just add this to an existing index (assuming said index isn't already a hugely-bloated DTA-created mess).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Indianrock SSC-Insane Points: 20333 More actions · Answer 2

Thanks. There are several indexes only covering one column that I could add to.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

In this case, you want to find an existing index that has BUSINESS_PROCESS_STATUS as the leading key column and then add the new column as a key column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Indianrock SSC-Insane Points: 20333 More actions · Answer 4

Thanks, now to test in QA

CREATE NONCLUSTERED INDEX [index_ServiceRequest_BusinessProcessStatus] ON [dbo].[SERVICE_REQUEST]

(

[BUSINESS_PROCESS_STATUS] ASC,

[LAST_STATE_RESPONSE_CODE] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [CM_Log_Index]

GO

--- sample query

SELECT TOP 2000 NewYorkLienFilingRequest02.SERVICE_REQUEST_ID AS NewYorkLienFilingRequest02_SERVICE_REQUEST_ID14,RecordedLegalEntity17.FULL_NAME AS RecordedLegalEntity17_FULL_NAME16,AccountOwnershipDocSummary18.BORROWER_FULL_NAMES AS AccountOwnershipDocSummary18_BORROWER_FULL_NAMES18,NewYorkLienFilingRequest02.MANUFACTURER_ID AS NewYorkLienFilingRequest02_MANUFACTURER_ID20,TitlingAgencyLienholder19.LIENHOLDER_IDENTIFIER AS TitlingAgencyLienholder19_LIENHOLDER_IDENTIFIER22,NewYorkLienFilingRequest02.NEW_YORK_LIEN_FILING_TRANSACTION_TYPE AS NewYorkLienFilingRequest02_NEW_YORK_LIEN_FILING_TRANSACTION_TYPE24,Account15.CUSTOM_ATTRIBUTE_1 AS Account15_CUSTOM_ATTRIBUTE_126,Account15.CUSTOM_ATTRIBUTE_2 AS Account15_CUSTOM_ATTRIBUTE_228,Account15.CUSTOM_ATTRIBUTE_3 AS Account15_CUSTOM_ATTRIBUTE_330,Account15.CUSTOM_ATTRIBUTE_4 AS Account15_CUSTOM_ATTRIBUTE_432,BusinessUnit110.LONG_NAME AS BusinessUnit110_LONG_NAME34,NewYorkLienFilingRequest02.DOCUMENT_NUMBER AS NewYorkLienFilingRequest02_DOCUMENT_NUMBER36,NewYorkLienFilingRequest02.FINANCED_DATE AS NewYorkLienFilingRequest02_FINANCED_DATE38,Account15.BOOKED_DATE AS Account15_BOOKED_DATE40,NewYorkLienFilingRequest02.LAST_EXPORT_TO_STATE_DATE AS NewYorkLienFilingRequest02_LAST_EXPORT_TO_STATE_DATE6,FollowupEntity111.ENTITY_CODE AS FollowupEntity111_ENTITY_CODE44,NewYorkLienFilingRequest02.RECORDED_REQUEST_DATE AS NewYorkLienFilingRequest02_RECORDED_REQUEST_DATE46 FROM SERVICE_REQUEST AS NewYorkLienFilingRequest02 INNER JOIN (SERVICED_COLLATERAL_GROUP_ITEM AS ServicedCollateralGroupItem13 INNER JOIN (SERVICED_COLLATERAL_GROUP_ITEM AS ServicedAccount14 INNER JOIN (ACCOUNT AS Account15 INNER JOIN ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary18 ON Account15.ACCOUNT_ID=AccountOwnershipDocSummary18.ACCOUNT_ID LEFT OUTER JOIN BUSINESS_UNIT AS BusinessUnit110 ON Account15.BUSINESS_UNIT_ID=BusinessUnit110.BUSINESS_UNIT_ID LEFT OUTER JOIN LEGAL_ENTITY AS FollowupEntity111 ON Account15.FOLLOWUP_ENTITYLEGAL_ENTITY_ID=FollowupEntity111.LEGAL_ENTITY_ID) ON ServicedAccount14.ACCOUNT_ID=Account15.ACCOUNT_ID) ON ServicedCollateralGroupItem13.SERVICED_COLLATERAL_GROUP_ITEM_ID=ServicedAccount14.SERVICED_COLLATERAL_GROUP_ITEM_ID) ON NewYorkLienFilingRequest02.SERVICE_REQUEST_ID=ServicedCollateralGroupItem13.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID LEFT OUTER JOIN LEGAL_ENTITY AS RecordedLegalEntity17 ON NewYorkLienFilingRequest02.SERVICE_REQUEST_ID=RecordedLegalEntity17.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID INNER JOIN TITLING_AGENCY_LIENHOLDER AS TitlingAgencyLienholder19 ON NewYorkLienFilingRequest02.LIENHOLDER_PERFORMING_LIEN_FILING_ID=TitlingAgencyLienholder19.TITLING_AGENCY_LIENHOLDER_ID WHERE ((Account15.CLIENT_ID = 11985 AND NewYorkLienFilingRequest02.BUSINESS_PROCESS_STATUS = 'Open' AND NewYorkLienFilingRequest02.LAST_EXPORT_TO_STATE_DATE IS NOT NULL AND NewYorkLienFilingRequest02.LAST_STATE_RESPONSE_CODE = '00014' AND NewYorkLienFilingRequest02.REQUEST_TRANSACTION_TYPE = 'LienFiling' AND NewYorkLienFilingRequest02.LAST_EXPORT_TO_STATE_DATE IS NOT NULL ) AND ((NewYorkLienFilingRequest02.CONCRETE_TYPE IN ( 'Fdi.Po.NewYorkLienFilingRequest', 'Fdi.Po.NewYorkCollateralLienFilingRequest'))))