SQL Server – missing indexes in current requests

Do you experience performance problems in your SQL Server? It could be because of queries that are missing indexes. There are some techniques you could use to identify any missing indexes, but many of them are looking at historical executions. If you want to know if there are any long running queries right now that are missing any index you could use my script:

CREATE PROCEDURE [dbo].[spExecRequestsWithMissingIndexes]
	@dbName sysname = NULL
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

IF DB_ID(@dbName) IS NULL AND @dbName IS NOT NULL
	RAISERROR('Databasename not found',16,1) WITH NOWAiT;

SELECT 
	er.session_id,
	er.blocking_session_id,
	er.start_time,
	er.status,
	dbName = db_name(er.database_id),
	er.wait_type,
	er.wait_time,
	er.last_wait_type,
	er.granted_query_memory,
	er.reads,
	er.logical_reads,
	er.writes,
	er.row_count,
	er.total_elapsed_time,
	er.cpu_time,
	er.open_transaction_count,
	er.open_transaction_count,
	s.text,
	qp.query_plan,
	logDate = CONVERT(DATE,GETDATE()),
	logTime = CONVERT(TIME,GETDATE())
FROM sys.dm_exec_requests er 
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) s
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE 
	(qp.dbid=db_id(@dbName) or @dbname is null)
	AND CONVERT(varchar(max), qp.query_plan) LIKE '%<Missing%'

Execute the script in a “SQL Toolbox” to create the procedure. You can now execute the procedure to see if there are any current requests that are missing any index. Click on the Query_plan to view the execution plan and see the missing index suggestion. Make sure that there are no index that can be modified to fit the suggested missing index before you create a new one.