Menu
I was recently working with one of my clients on some low hanging fruit type of query tuning. We had checked the cache for plans with missing index warnings in them and were trying to see if we could tweak and existing index or add a new index to speed things up. If you ever work with missing indexes, you surely have seen it recommend crazy things, duplicates or existing indexes or it wants something that can be added to an existing index.
The bottom line for missing index recommendations is that you should NEVER, EVER create a missing index unless you know for absolutely sure that the index does not exist and that it will really help a known performance issue. For instance, you can have missing index warnings on small tables that only return a couple of rows to the query. Those are usually not worth working on since scanning a small table can usually be done very quickly by SQL. To this day, I still find many databases that are way over indexed with indexes that were implemented simply because someone found a missing index warning and did not do their homework or they ran the dreaded Database Tuning Advisor which shoved a bunch of duplicate indexes into tables.
If you are wondering how to get a list of missing indexes, please check out Jeff Schwartz’s blog post on how to do this. (https://blog.sqlrx.com/2017/06/02/query-tuning-and-missing-index-recommendations/ ) This will give you a place to start. It is better if you know what query is throwing the missing index warning so it is a good idea to collect those either in a trace or extended events. Jeff builds on his first post and in his second post on the subject (https://blog.sqlrx.com/2017/07/20/handling-multiple-missing-index-recommendations-for-the-same-table/ ) also goes over the fun of having multiple missing index recommendations for a single table and how to deal with them.
Here’s a handy set of links for some of Jeff’s great index tuning work that you should really check out:
https://blog.sqlrx.com/2016/01/28/sql-server-20122014-extended-events-for-developers-part-1/
https://blog.sqlrx.com/2017/06/02/query-tuning-and-missing-index-recommendations/
https://blog.sqlrx.com/2017/08/10/how-indexing-affects-deletion-queries/
One of the things that I usually need when performance tuning is to know information about the existing indexes on specific tables. I always want to know what columns are in the indexes along with the included columns so that I can compare the existing indexes to the missing recommendations. This way I can better figure out if a recommendation is something that can be added to an existing index (like an included column) or if I really need to create a brand new index if it does not exist at all.
Like most DBA’s, I keep a toolkit with all kinds of handy scripts. However, I did not have one that would give me index included columns. I also wanted the query to be able to return info from one or many tables at the same time. This would be useful when dealing with things with lots of joins. I know that there are a few bloggers who have posted something similar but I wanted to have the ability to filter on one or multiple tables. So, here is what I came up with:
/********************************
Returns index columns with included columns
plus other needed index info for tables
in @tablelist variable
*********************************/
DECLARE @tablelist VARCHAR(1000)
DECLARE @sqlstr NVARCHAR(MAX)
SET @tablelist = ‘InvoiceLines,OrderLines,StockItemHoldings’ — comma delimited list of tables, can be one or multiples EX: ‘mytable’ or ‘mytable,nothertable,thirdtable’
— Query the tables
IF @tablelist <> ” OR @tablelist <> ‘?,?,?’
BEGIN
SET @tablelist = REPLACE(QUOTENAME(@tablelist,””), ‘,’, ”’,”’) — Add quotes so the IN clause will work
SET @sqlstr = ‘SELECT SCHEMA_NAME(o.schema_id) AS SchemaName
,o.name AS TableName
,i.name AS IndexName
,i.type_desc AS IndexType
,c.name AS ColumnName
,ic.is_included_column
,ic.index_column_id
,t.name
,c.max_length
,c.precision
,c.scale
FROM sys.indexes i
JOIN sys.index_columns ic ON i.index_id = ic.index_id
JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
JOIN sys.objects o ON o.object_id = i.object_id AND c.object_id = i.object_id
JOIN sys.types t on t.user_type_id = c.user_type_id
WHERE o.name IN (‘+@tablelist+‘)
ORDER BY SchemaName, TableName, IndexName, index_column_id’
EXEC sp_executesql @sqlstr
–PRINT @sqlstr
END
All you have to provide is a comma separated list of the table(s) you are interested in for the @tablelist variable and it will do the rest. The output looks like this:
I found several bloggers who had made queries that would concatenate the columns together but truthfully I found those hard to read so I settled for a list with some extra info on the data and index types. Hope this is useful to someone out there.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!