CODEWITHSUNDEEP
sqlsql-serverindexing
Darren Thompson
Darren Thompson

Reputation: 1

How to combine potential indexes

Some "missing index" code (see below) I got from internet searches is listing a lot of potential missing indexes for a particular table. Literally it's saying that I need 30 indexes. I already had 8 before running the code. Most experts state that a table should average 5. Can I combine a majority of these missing indexes so that it covers most of the tables indexing needs?

For example: These two indexes are similar enough that it seems like they could be combined. But can they?

CREATE INDEX [NCI_12345] ON [DB].[dbo].[someTable] 
  ([PatSample], [StatusID], [Sub1Sample]) 
  INCLUDE ([PatID], [ProgID], [CQINumber])

CREATE INDEX [NCI_2535_2534] ON [DB].[dbo].[someTable] 
  ([PatSample], [SecRestOnly]) 
  INCLUDE ([CQINumber])

If I combine them it'd look like this:

CREATE INDEX [NCI_12345] ON [DB].[dbo].[someTable] 
  ([PatSample], [StatusID], [Sub1Sample], [SecRestOnly]) 
  INCLUDE ([PatID], [ProgID], [CQINumber])

NOTE: I just took the first statement and added [SecRestOnly] to it.

QUESTION: Would combining these satisfy both index needs? And if not, how would a highly used table with lots of fields ever just have 5 indexes?

Here's the code used to get "missing indexes":

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
  (migs.user_seeks + migs.user_scans) AS improvement_measure, 
  LEFT (PARSENAME(mid.STATEMENT, 1), 32) as TableName,
  'CREATE INDEX [NCI_' + CONVERT (VARCHAR, mig.index_group_handle) + '_' 
  + CONVERT (VARCHAR, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.STATEMENT, 1), 32) + ']'
  + ' ON ' + mid.STATEMENT
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM [sys].dm_db_missing_index_groups mig
INNER JOIN [sys].dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN [sys].dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;```

Upvotes: 0

Views: 224

Answers (1)

Alex W
Alex W

Reputation: 3373

The sample you gave will not give you the desired result. The index on ([PatSample], [SecRestOnly]) will optimize search condition such as "PatSample = val1 and SecRestOnly = val2". The combined index will not because there are other segments between the two columns in the search condition. The key to remembers is that multi-segmented index can only be used to optimize multiple "equality" search when the columns in the search are the initial consecutive segments of the index.

Given that, it can be reasoned that suppose you have one index on (col1, col2) and another on (col1, col2, col3), then the former is not needed.

How many index to have is a trade off between update performance and search performance. More index will slow down insert/update/delete but will give query optimizer more options to optimize searches. Given your example, does your application require searching on the "SecRestOnly" by itself frequently, if that is the case, it would be better to have an index with "secRestOnly" by itself or as the first segment of a multi-segment index. If the search is rarely done on the column, then it may be reasonable to not have such index.

Upvotes: 1

Related Questions