In our last post we discussed an approach on what we should do to find whether there is a memory issue or a way to identify if there is a memory crunch which is causing bad SQL performance.
However, not always the thing we see on the surface is the root cause. Though there may be a memory issue, there could be some un-optimized indexes behind causing that issue. Therefore, in today’s post, we will see an approach to optimize the index in our SQL instances.
While talking about indexes, there are four kind of indexes that we should invest our focus on.
Identify missing indexes at it may impact read operations.
No unused indexes at it may impact write operations.
Identify duplicate indexes at it may impact write operations.
No bad indexes (write > read ) as it may impact write operation.
One important ground rule to start, is that, our SQL instance should be running for at least few weeks so that we have good data to rely on.
Missing Indexes:
We all know that read operations are faster if appropriate indexes are there. Having said that we don’t want unnecessary indexes in our tables which will slow down write operations.
The first step is to find missing indexes from DMVs. Below is the script you can use to find the missing indexes for a database.
SELECT top 10(user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,
migs.last_user_seek, DB_NAME(mid.database_id) AS DatabaseName,
object_name(mid.object_id,mid.database_id) AS [TableName],
--mid.statement as 'Database.Schema.Table',
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.user_scans, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;The output of this query will serve as a starting point in our quest to find missing indexes. Remember, to be very cautious on what indexes should be created on tables.
Once you have a list of missing indexes, second step is to check the tables on which missing indexes have been identified. Save the output of script in excel so that we can filter them easily. Observe on which columns, the script output is asking us to create index.
Next, check the existing index on each table using sp_helpindex <tablename> . You can identify whether there is any existing index which contains those columns and whether the column order is same which script output is showing.
If an existing index can be modified to accommodate a column in existing index, its operation cost would be far less than to create a new index altogether.
The third step to verify the missing index is to check the most expensive queries and see whether the query plan is showing any of those missing indexes, or is there any table scan that could be solved using those missing indexes.
Once you have gone through all of the above three steps, you will be able to filter out only the required index . You can then send your recommendation to the development team to test those indexes in there DEV/UAT/Test environment before implementing them to production.
Its always better to go slow and create small number of indexes instead of creating indexes in a bulk as it may have a negative impact on performance.
Unused Indexes:
As the name suggests, these are the indexes which are not being used by queries and are a burden. Since, these are not used for reading, they should be removed.
Below is a nice script that you can use to identify the unused indexes. As told earlier, please ensure that the SQL instance is running for at least 3-4 weeks to have reliable data.
SELECT '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']'
AS [statement] ,
i.[name] AS [index_name] ,
ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups]
AS [user_reads] ,
ddius.[user_updates] AS [user_writes] ,
SUM(SP.rows) AS [total_rows],
'DROP INDEX [' + i.[name] + '] ON [' + su.[name] + '].[' + o.[name]
+ '] WITH ( ONLINE = OFF )' AS [drop_command]
FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]
AND i.[index_id] = ddius.[index_id]
INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id]
AND SP.[index_id] = ddius.[index_id]
INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
WHERE ddius.[database_id] = DB_ID() -- current database only
AND OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
AND ddius.[index_id] > 0
--AND i.index_id > 1 (this will exclude clustered indexes)
--AND i.is_unique = 0 (this will exclude indexes on unique key)
GROUP BY su.[name] ,
o.[name] ,
i.[name] ,
ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] ,
ddius.[user_updates]
HAVING ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
su.[name] ,
o.[name] ,
i.[name ]This script will generate a list of indexes which have not been used for any read activity (i.e., user seeks, user scans and user lookups).
Once you have the list, send them to development team to test them on UAT/test/Dev environment by removing these. Once confirmed that no query is using them or will use them in future, plan to remove them from production environment as well.
Duplicate Indexes:
If more than one index is present on similar columns then those are said to be duplicate. It is always better to remove one of the index as it is redundant and is not serving the purpose.
Below query written by Pinal Dave helps to find the duplicate indexes
WITH MyDuplicate AS (SELECT
Sch.[name] AS SchemaName,
Obj.[name] AS TableName,
Idx.[name] AS IndexName,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 1) AS Col1,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 2) AS Col2,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 3) AS Col3,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 4) AS Col4,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 5) AS Col5,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 6) AS Col6,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 7) AS Col7,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 8) AS Col8,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 9) AS Col9,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 10) AS Col10,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 11) AS Col11,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 12) AS Col12,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 13) AS Col13,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 14) AS Col14,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 15) AS Col15,
INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 16) AS Col16
FROM sys.indexes Idx
INNER JOIN sys.objects Obj ON Idx.[object_id] = Obj.[object_id]
INNER JOIN sys.schemas Sch ON Sch.[schema_id] = Obj.[schema_id]
WHERE index_id > 0)
SELECT MD1.SchemaName, MD1.TableName, MD1.IndexName,
MD2.IndexName AS OverLappingIndex,
MD1.Col1, MD1.Col2, MD1.Col3, MD1.Col4,
MD1.Col5, MD1.Col6, MD1.Col7, MD1.Col8,
MD1.Col9, MD1.Col10, MD1.Col11, MD1.Col12,
MD1.Col13, MD1.Col14, MD1.Col15, MD1.Col16
FROM MyDuplicate MD1
INNER JOIN MyDuplicate MD2 ON MD1.tablename = MD2.tablename
AND MD1.indexname <> MD2.indexname
AND MD1.Col1 = MD2.Col1
AND (MD1.Col2 IS NULL OR MD2.Col2 IS NULL OR MD1.Col2 = MD2.Col2)
AND (MD1.Col3 IS NULL OR MD2.Col3 IS NULL OR MD1.Col3 = MD2.Col3)
AND (MD1.Col4 IS NULL OR MD2.Col4 IS NULL OR MD1.Col4 = MD2.Col4)
AND (MD1.Col5 IS NULL OR MD2.Col5 IS NULL OR MD1.Col5 = MD2.Col5)
AND (MD1.Col6 IS NULL OR MD2.Col6 IS NULL OR MD1.Col6 = MD2.Col6)
AND (MD1.Col7 IS NULL OR MD2.Col7 IS NULL OR MD1.Col7 = MD2.Col7)
AND (MD1.Col8 IS NULL OR MD2.Col8 IS NULL OR MD1.Col8 = MD2.Col8)
AND (MD1.Col9 IS NULL OR MD2.Col9 IS NULL OR MD1.Col9 = MD2.Col9)
AND (MD1.Col10 IS NULL OR MD2.Col10 IS NULL OR MD1.Col10 = MD2.Col10)
AND (MD1.Col11 IS NULL OR MD2.Col11 IS NULL OR MD1.Col11 = MD2.Col11)
AND (MD1.Col12 IS NULL OR MD2.Col12 IS NULL OR MD1.Col12 = MD2.Col12)
AND (MD1.Col13 IS NULL OR MD2.Col13 IS NULL OR MD1.Col13 = MD2.Col13)
AND (MD1.Col14 IS NULL OR MD2.Col14 IS NULL OR MD1.Col14 = MD2.Col14)
AND (MD1.Col15 IS NULL OR MD2.Col15 IS NULL OR MD1.Col15 = MD2.Col15)
AND (MD1.Col16 IS NULL OR MD2.Col16 IS NULL OR MD1.Col16 = MD2.Col16)
ORDER BY
MD1.SchemaName,MD1.TableName,MD1.IndexNameFrom the output check which index are being used and which are not. Share the list with developer to test. Once approved, remove it from production.
Bad Indexes:
The phase of index optimization is removing or modifying bad indexes. Bad indexes are those on which writes are happening far more than read. Having said that, some of the indexes are necessity of application which are used once in a week or month and could not be removed. Still, try to work with application/development team to figure out a way to remove them or modify other indexes so that purpose of both are fulfilled.
You can use below script to find Bad Indexes for a database.
DECLARE @dbid int
SELECT @dbid = db_id()
SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,
'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,
'Difference' = user_updates - (user_seeks + user_scans + user_lookups)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.database_id = @dbid
AND user_updates > (user_seeks + user_scans + user_lookups)
ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC;The whole purpose of above article is to explain that as a DBA, we should maintain a sanity and hygiene of our tables and databases. Keep only relevant indexes, remove unused one. Create missing indexes only after thorough test.
Happy Learning!
P.S.: None of the scripts mentioned above are written by wordsontech team. They were gathered over a period of time from internet and have been used by us over a long period of time.
Very useful article.
Keep it up!!
-Sunder