Finding Queries From SQL Server Missing Index Requests

When I do performance tuning I look at it from both ways, the most expensive queries and the indexes which SQL Server is already recommending. Now just because an expensive query doesn’t have an index recommendation against it doesn’t mean that its performance cannot be improved by adding or changing an index for it…. but that’s another subject. I’m saying that because you shouldn’t just look at what SQL Server recommends and blindly implement them or accept them as the only things which can be improved.

Index Recommendations.

Now I normally look at what SQL Server is recommending from the sys.dm_db_missing_index_details DMV and compare them against the expensive queries to see how they can be tuned, see what can be ignored, and what would be beneficial. however, I’ve recently come across another script, which will display all the plans that SQL Server used to come up with the index recommendation;

WITH XMLNAMESPACES (DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’)
, PlanMissingIndexes
AS (SELECT query_plan, usecounts
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE qp.query_plan.exist(‘//MissingIndexes’) = 1)
, MissingIndexes
AS (SELECT stmt_xml.value(‘(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]’, ‘sysname’) AS DatabaseName,
stmt_xml.value(‘(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Schema)[1]’, ‘sysname’) AS SchemaName,
stmt_xml.value(‘(QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]’, ‘sysname’) AS TableName,
stmt_xml.value(‘(QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]’, ‘float’) AS Impact,
ISNULL(CAST(stmt_xml.value(‘(@StatementSubTreeCost)[1]’, ‘VARCHAR(128)’) AS FLOAT), 0) AS Cost,
pmi.usecounts UseCounts,
STUFF((SELECT DISTINCT ‘, ‘ + c.value(‘(@Name)[1]’, ‘sysname’)
FROM stmt_xml.nodes(‘//ColumnGroup’) AS t(cg)
CROSS APPLY cg.nodes(‘Column’) AS r(c)
WHERE cg.value(‘(@Usage)[1]’, ‘sysname’) = ‘EQUALITY’
FOR XML PATH(”)),1,2,”
) AS equality_columns,
STUFF(( SELECT DISTINCT ‘, ‘ + c.value(‘(@Name)[1]’, ‘sysname’)
FROM stmt_xml.nodes(‘//ColumnGroup’) AS t(cg)
CROSS APPLY cg.nodes(‘Column’) AS r(c)
WHERE cg.value(‘(@Usage)[1]’, ‘sysname’) = ‘INEQUALITY’
FOR XML PATH(”)),1,2,”
) AS inequality_columns,
STUFF((SELECT DISTINCT ‘, ‘ + c.value(‘(@Name)[1]’, ‘sysname’)
FROM stmt_xml.nodes(‘//ColumnGroup’) AS t(cg)
CROSS APPLY cg.nodes(‘Column’) AS r(c)
WHERE cg.value(‘(@Usage)[1]’, ‘sysname’) = ‘INCLUDE’
FOR XML PATH(”)),1,2,”
) AS include_columns,
query_plan,
stmt_xml.value(‘(@StatementText)[1]’, ‘varchar(4000)’) AS sql_text
FROM PlanMissingIndexes pmi
CROSS APPLY query_plan.nodes(‘//StmtSimple’) AS stmt(stmt_xml)
WHERE stmt_xml.exist(‘QueryPlan/MissingIndexes’) = 1)

SELECT TOP 200
DatabaseName,
SchemaName,
TableName,
equality_columns,
inequality_columns,
include_columns,
UseCounts,
Cost,
Cost * UseCounts [AggregateCost],
Impact,
query_plan
FROM MissingIndexes
WHERE DatabaseName = ‘[YourDatabaseName]’ AND TableName = ‘[YourTableName]’
ORDER BY Cost * UseCounts DESC;

This way, you can even see all the smaller, low-cost queries which SQL Server has built it’s recommendation from. I think this is amazing. I hope you have fun with it!

Transaction log growing when performing index maintenance

With Index maintenance; when operating on large indexes, the index operation can generate large data loads, causing the transaction log to fill quickly. This process ensures the index operation can be rolled back should there be a problem. Unfortunately this means the transaction log cannot be truncated until process has been completed(The log can still be backed up). Obviously the transaction log must have sufficient room to store both the index operation transaction and any user transactions for the duration of the index operation.

So what can we do? 

Well your first option is to throw additional disk space at the database log drive. which isn’t ideal as disk space doesn’t grow on trees…

Alternatively we can set the SORT_IN_TEMPDB option to ON.

What does that do?

This separates the index transactions from the user transactions.

The index transactions will be stored in the tempdb transaction log, and the concurrent user transactions will be stored in the transaction log of the user database. This allows for the transaction log of the user database to be truncated during the index operation if it is required.

This also means that if the tempdb log is not on the same disk as the user database log, the two logs are not competing for the same disk space.

Does that mean TempDB will incur more usage?

yes and no.

If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs. however it’s only active during the index maintenance, normal data usage for sorts will still occur in memory(or spill to tempdb depending on memory grants)

So how do we set it?

You can either do this at per index, by creating the index and setting the SORT_IN_TEMPDB=ON Option.

or

If you’re using Ola Hallengren Index Maintenance Scripts set the; @SortInTempdb=’Y’ Parameter.

SQL Server; Finding Unused Indexes

You should have an understanding of how SQL Server indexing works, how they improve selectivity and impact on DML Operations.

This script finds all your Indexes, and displays their Scan/Seek/Lookup count as per Index Usage Stats. Index Usage stats are reset as you restart SQL Server, so make sure that the SQL Server has been online and used for at least  a few months before running.

I Would not out right drop any indexes that are unused; contact the Application Vendor/Development Team. Do some testing on a test environment… Have a rollback plan… MONITOR.

Issues being; an index might have been implemented for a report or process that is executed infrequently and the report or process might me reliant on that index (Be that for performance issues or it’s been hard coded with a query hint)

 

— DMV Unused Indexes
DECLARE @dbid INT
, @dbName VARCHAR(100);
SELECT @dbid = DB_ID()
, @dbName = DB_NAME();
WITH partitionCTE (OBJECT_ID, index_id, row_count, partition_count)
AS
(
SELECT [OBJECT_ID]
, index_id
, SUM([ROWS]) AS ‘row_count’
, COUNT(partition_id) AS ‘partition_count’
FROM sys.partitions
GROUP BY [OBJECT_ID]
, index_id
)
SELECT OBJECT_NAME(i.[OBJECT_ID]) AS objectName
, i.name
, CASE
WHEN i.is_unique = 1
THEN ‘UNIQUE ‘
ELSE ”
END + i.type_desc AS ‘indexType’
, ddius.user_seeks
, ddius.user_scans
, ddius.user_lookups
, ddius.user_updates
, cte.row_count
, CASE WHEN partition_count > 1 THEN ‘yes’
ELSE ‘no’ END AS ‘partitioned?’
, CASE
WHEN i.type = 2 And i.is_unique = 0
THEN ‘Drop Index ‘ + i.name
+ ‘ On ‘ + @dbName
+ ‘.dbo.’ + OBJECT_NAME(ddius.[OBJECT_ID]) + ‘;’
WHEN i.type = 2 And i.is_unique = 1
THEN ‘Alter Table ‘ + @dbName
+ ‘.dbo.’ + OBJECT_NAME(ddius.[OBJECT_ID])
+ ‘ Drop Constraint ‘ + i.name + ‘;’
ELSE ”
END AS ‘SQL_DropStatement’
FROM sys.indexes AS i
INNER Join sys.dm_db_index_usage_stats ddius
ON i.OBJECT_ID = ddius.OBJECT_ID
And i.index_id = ddius.index_id
INNER Join partitionCTE AS cte
ON i.OBJECT_ID = cte.OBJECT_ID
And i.index_id = cte.index_id
WHERE ddius.database_id = @dbid
ORDER BY
(ddius.user_seeks + ddius.user_scans + ddius.user_lookups)
, user_updates DESC;
GO

Script Credit: Pinal Dave

*DISCLAIMER: I WILL NOT ACCEPT ANY LIABILITY FOR SCRIPTS YOU EXECUTE