Making smarter indexes

Bryan Rebok and Brent Ozar recently opened my eyes to something I didn’t know.  When SQL Server recommends missing indexes to you it puts the columns in order in which they are found in the table.  That’s it.  I always thought there was more logic into it.  But there isn’t.  Upon reading this I had a terrible realization that I’ve made a lot of awful indexes in my time.  I owe the world an apology.  I hope this post can serve as that apology.

I’ve written a function that accepts the equality column list from dm_db_missing_index_details as a parameter and spits those columns back out in order by their cardinality.  This won’t necessarily be the proper order for the columns in every index, but it is far more likely to be correct than the initial result from the DMV.

One very important note:  It depends upon dm_db_stats_histogram, a new DMF that is available starting with SQL Server 2016 (13.x) SP1 CU2.  Without it the only way to get this information is with DBCC SHOW_STATISTICS which I can’t use in T-SQL to complete this task.

To see it in action consider this table from WideWorldImporters.

SELECT 
COUNT(DISTINCT taxrate) TaxRate
, COUNT(DISTINCT stockitemid) StockItemID
, COUNT(DISTINCT taxamount) TaxAmount
FROM Sales.InvoiceLines

Clearly any indexes on this table would be more valuable with TaxRate at the end…if at all.  But what does SQL Server ask for?

Ewwww.  That’s terrible.  We would surely want TaxAmount to be first if all 3 were being used as equality columns!!

Enter dbo.OrganizeColumns.  It will find the statistics for these columns, use the histogram information to determine the distinct list of items in each column, then reorder the output putting the largest numbers first.

Check out how it has pushed TaxRate to the end and moved TaxAmount ahead of StockItemID!  Absent any other information about the query workload or tables, this is a far better estimate of column order.

You can download your free copy of OrganizeColumns here.  Just add this one new line to a missing indexes query like the one found here to use it.

, dbo.OrganizeColumns(mid.object_id, mid.equality_columns) equality_columns_fixed

Again, I’m sorry, world, for all the bad indexes I’ve made.  Can we still be friends?

2 Comments

  1. Hahaha, glad you found that as interesting as we did. Interesting idea on the function!


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.