Drop single-column index in favor of multi-column index

Question

Drop single-column index in favor of multi-column index

Indianrock

SSC-Insane

Points: 20333
More actions
April 6, 2017 at 11:11 am

#323607

I've been analyzing our indexes for quite a while, mainly focusing on high-value missing indexes and unused indexes. Fairly easy to do with tools such as SP_BlitzIndex. Now I'm looking at overlapping indexes and how to determine if a single-column index can be removed where that column is the first key in a multi-column index.( we have quite of few of these situations ) Using the query below which filters on Business_process_status, sql server doesn't use either of these indexes but does a key lookup on the clustered PK. This takes 9 minutes in QA. If I add a hint to use either of these indexes to the join on the Service_Request table, the run-time drops to 6 seconds. With an ORM, adding query hints is out of the question for the most part so I'm not sure where to go next?
SELECT TOP 2000 OwnershipDocProperty17.ORIGINAL_MANUFACTURER_ID AS OriginalManufacturerId,AccountOwnershipDocSummary02.OWNERSHIP_DOC_ISSUING_STATE_ABBR AS OwnershipDocIssuingState,CollateralGroupRequest15.REQUEST_TRANSACTION_TYPE AS RequestType,AccountOwnershipDocSummary02.LIENHOLDER_IDENTIFIER AS Lienholder,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_1 AS AccountNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_2 AS LoanNumber,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_3 AS LoanSuffix,AccountOwnershipDocSummary02.CUSTOM_ATTRIBUTE_4 AS Branch,AccountOwnershipDocSummary02.BORROWER_FULL_NAMES AS Borrower,AccountOwnershipDocSummary02.OWNER_FULL_NAMES AS Owner,CollateralGroupRequest15.RECORDED_REQUEST_DATE AS RequestDate,Account19.ACTUAL_PAYOFF_DATE AS PayOffDate,OwnershipDoc13.OWNERSHIP_DOC_ID AS OwnershipDocId,CollateralGroupRequest15.REQUEST_MECHANISM_TYPE AS RequestMechanism FROM ACCOUNT_OWNERSHIP_DOC_SUMMARY AS AccountOwnershipDocSummary02 INNER JOIN (OWNERSHIP_DOC AS OwnershipDoc13 INNER JOIN (SERVICED_COLLATERAL_GROUP_ITEM AS ServicedOwnershipDoc14 INNER JOIN (SERVICE_REQUEST AS CollateralGroupRequest15 INNER JOIN STATE_MESSAGE_EXCHANGE AS StateMessageExchange16 ON CollateralGroupRequest15.SERVICE_REQUEST_ID=StateMessageExchange16.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID) ON ServicedOwnershipDoc14.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID=CollateralGroupRequest15.SERVICE_REQUEST_ID) ON OwnershipDoc13.OWNERSHIP_DOC_ID=ServicedOwnershipDoc14.OWNERSHIP_DOC_ID INNER JOIN PROPERTY AS OwnershipDocProperty17 ON OwnershipDoc13.OWNERSHIP_DOC_PROPERTY_ID=OwnershipDocProperty17.PROPERTY_ID) ON AccountOwnershipDocSummary02.OWNERSHIP_DOC_ID=OwnershipDoc13.OWNERSHIP_DOC_ID LEFT OUTER JOIN ACCOUNT AS Account19 ON AccountOwnershipDocSummary02.ACCOUNT_ID=Account19.ACCOUNT_ID WHERE ((AccountOwnershipDocSummary02.STATUS <> 'CLOSED_DE' AND AccountOwnershipDocSummary02.CLIENT_ID = 20 AND CollateralGroupRequest15.REQUEST_TRANSACTION_TYPE NOT IN ( 'Unknown') AND StateMessageExchange16.MESSAGE_EXCHANGE_STATUS = 'WAITING_FOR_EXPORT' AND StateMessageExchange16.BUSINESS_PROCESS_STATUS = 'Open' AND CollateralGroupRequest15.BUSINESS_PROCESS_STATUS = 'Open' AND CollateralGroupRequest15.REQUEST_MECHANISM_TYPE = 'ELT') AND ((ServicedOwnershipDoc14.CONCRETE_TYPE IN ( 'Fdi.Po.ServicedOwnershipDoc')))) ORDER BY 11

Viewing 7 posts - 1 through 6 (of 6 total)

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

Index analysis can only take you so far. Have you updated statistics recently? Stale stats can make the optimizer make bad query plan choices.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

I updated stats with fullscan on the two indexes being ignored. Still runs for six minutes and ignores the multi-column index which, when forced with a hint, cuts the runtime to seconds.

sgmunson SSC Guru Points: 110639 More actions · Answer 3

Also, if you need to retrieve columns in the SELECT that it can get from the PK, but not from the other ignored indexes, that's probably why. You'd have to include any such fields in one of those indexes in order to "force the issue".

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

ok thanks. I'll try disabling the single-column index and adding one sql is suggesting for this query. Since this is a large table with many columns ( over 100 ) and 20 + indexes already, I don't want to slow down updates/inserts etc.

sgmunson SSC Guru Points: 110639 More actions · Answer 5

Cool beans... . Look up the term "covering index". That's what a good index does.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

Yes with the new index we get a seek and runs in 50 seconds, then once cached, a few seconds. I think part of what is happening is the clustered index scan and key lookups were loading up the plan at the far right ( beginning ) with millions of records which is going to affect some of the later operations. The idea of "get a good enough plan fast" bites us quite often. But before the index change, the query was slow even with option(recompile ). I've helped this one query, but at the cost of adding another multi-column index.