Find Missing Indexes in Stored Procs with T-SQL « SQL Fool

Find Missing Indexes in Stored Procs with T-SQL « SQL Fool




use master
go

/* Create a stored procedure skeleton */
IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_missingIndexStoredProc_sp'), N'IsProcedure') IS Null
BEGIN
EXECUTE ('Create Procedure dbo.dba_missingIndexStoredProc_sp As Print ''Hello World!''')
RAISERROR('Procedure dba_missingIndexStoredProc_sp created.', 10, 1);
END;
Go

/* Drop our table if it already exists */
IF Exists(SELECT OBJECT_ID FROM sys.tables WHERE [name] = N'dba_missingIndexStoredProc')
BEGIN
DROP TABLE dbo.dba_missingIndexStoredProc
PRINT 'dba_missingIndexStoredProc table dropped!';
END

/* Create our table */
CREATE TABLE dbo.dba_missingIndexStoredProc
(
missingIndexSP_id INT IDENTITY(1,1) Not Null
, databaseName VARCHAR(128) Not Null
, databaseID INT Not Null
, objectName VARCHAR(128) Not Null
, objectID INT Not Null
, query_plan xml Not Null
, executionDate SMALLDATETIME Not Null

CONSTRAINT PK_missingIndexStoredProc
PRIMARY KEY CLUSTERED(missingIndexSP_id)
);

PRINT 'dba_missingIndexStoredProc Table Created';

/* Configure our settings */
SET ANSI_Nulls ON;
SET Quoted_Identifier ON;
Go

ALTER PROCEDURE dbo.dba_missingIndexStoredProc_sp

/* Declare Parameters */
@lastExecuted_inDays INT = 7
, @minExecutionCount INT = 7
, @logResults BIT = 1
, @displayResults BIT = 0

AS
/*********************************************************************************
Name: dba_missingIndexStoredProc_sp

Author: Michelle Ufford, http://sqlfool.com

Purpose: Retrieves stored procedures with missing indexes in their
cached query plans.

@lastExecuted_inDays = number of days old the cached query plan
can be to still appear in the results;
the HIGHER the number, the longer the
execution time.

@minExecutionCount = minimum number of executions the cached
query plan can have to still appear
in the results; the LOWER the number,
the longer the execution time.

@logResults = store results in dba_missingIndexStoredProc

@displayResults = return results to the caller

Notes: This is not 100% guaranteed to catch all missing indexes in
a stored procedure. It will only catch it if the stored proc's
query plan is still in cache. Run regularly to help minimize
the chance of missing a proc.

Called by: DBA and/or SQL Agent Job

Date User Description
----------------------------------------------------------------------------
2009-03-02 MFU Initial Release for public consumption
*********************************************************************************
Exec dbo.dba_missingIndexStoredProc_sp
@lastExecuted_inDays = 30
, @minExecutionCount = 5
, @logResults = 1
, @displayResults = 1;
*********************************************************************************/

SET NOCOUNT ON;
SET XACT_Abort ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET Numeric_RoundAbort OFF;

BEGIN

/* Declare Variables */
DECLARE @currentDateTime SMALLDATETIME;

SET @currentDateTime = GETDATE();

DECLARE @plan_handles TABLE
(
plan_handle VARBINARY(64) Not Null
);

CREATE TABLE #missingIndexes
(
databaseID INT Not Null
, objectID INT Not Null
, query_plan xml Not Null

--CONSTRAINT PK_temp_missingIndexes PRIMARY KEY CLUSTERED
--(
-- databaseID, objectID
--)
);

BEGIN Try

/* Perform some data validation */
IF @logResults = 0 And @displayResults = 0
BEGIN

/* Log the fact that there were open transactions */
EXECUTE dbo.dba_logError_sp
@errorType = 'app'
, @app_errorProcedure = 'dba_missingIndexStoredProc_sp'
, @app_errorMessage = '@logResults = 0 and @displayResults = 0; no action taken, exiting stored proc.'
, @forceExit = 1
, @returnError = 1;

END;

BEGIN TRANSACTION;

/* Retrieve distinct plan handles to minimize dm_exec_query_plan lookups */
INSERT INTO @plan_handles
SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats
WHERE last_execution_time > DATEADD(DAY, -@lastExecuted_inDays, @currentDateTime)
And execution_count > @minExecutionCount;

WITH xmlNameSpaces (
DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)

/* Retrieve our query plan's XML if there's a missing index */
INSERT INTO #missingIndexes
SELECT deqp.[dbid]
, deqp.objectid
, deqp.query_plan
FROM @plan_handles AS ph
Cross Apply sys.dm_exec_query_plan(ph.plan_handle) AS deqp
WHERE deqp.query_plan.exist('//MissingIndex') = 1
And deqp.objectid IS Not Null;

/* Do we want to store the results of our process? */
IF @logResults = 1
BEGIN
INSERT INTO dbo.dba_missingIndexStoredProc
EXECUTE sp_msForEachDB 'Use [?];
Select ''?''
, mi.databaseID
, Object_Name(o.object_id)
, o.object_id
, mi.query_plan
, GetDate()
From sys.objects As o
Join #missingIndexes As mi
On o.object_id = mi.objectID
Where databaseID = DB_ID();';

END
/* We're not logging it, so let's display it */
ELSE
BEGIN
EXECUTE sp_msForEachDB 'Use [?];
Select ''?''
, mi.databaseID
, Object_Name(o.object_id)
, o.object_id
, mi.query_plan
, GetDate()
From sys.objects As o
Join #missingIndexes As mi
On o.object_id = mi.objectID
Where databaseID = DB_ID();';
END;

/* See above; this part will only work if we've
logged our data. */
IF @displayResults = 1 And @logResults = 1
BEGIN
SELECT *
FROM dbo.dba_missingIndexStoredProc
WHERE executionDate >= @currentDateTime;
END;

/* If you have an open transaction, commit it */
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;

END Try
BEGIN Catch

/* Whoops, there was an error... rollback! */
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;

/* Return an error message and log it */
EXECUTE dbo.dba_logError_sp;

END Catch;

/* Clean-Up! */
DROP TABLE #missingIndexes;

SET NOCOUNT OFF;
RETURN 0;
END
Go


IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_logError_sp'), N'IsProcedure') = 1
BEGIN
DROP PROCEDURE dbo.dba_logError_sp;
PRINT 'Procedure dba_logError_sp dropped';
END;
Go

IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_errorLog'), N'IsTable') IS Null
BEGIN

CREATE TABLE dbo.dba_errorLog
( errorLog_id INT IDENTITY(1,1)
, errorType CHAR(3)
CONSTRAINT [DF_errorLog_errorType] DEFAULT 'sys'
, errorDate DATETIME
CONSTRAINT [DF_errorLog_errorDate] DEFAULT(GETDATE())
, errorLine INT
, errorMessage NVARCHAR(4000)
, errorNumber INT
, errorProcedure NVARCHAR(126)
, procParameters NVARCHAR(4000)
, errorSeverity INT
, errorState INT
, databaseName NVARCHAR(255)
CONSTRAINT PK_errorLog_errorLogID PRIMARY KEY CLUSTERED
(
errorLog_id
)
);

PRINT 'Table dba_errorLog created';

END;
Go


SET ANSI_Nulls ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET NOCOUNT ON;
SET Numeric_RoundAbort OFF;
SET Quoted_Identifier ON;
Go

CREATE PROCEDURE dbo.dba_logError_sp
(
/* Declare Parameters */
@errorType CHAR(3) = 'sys'
, @app_errorProcedure VARCHAR(50) = ''
, @app_errorMessage NVARCHAR(4000) = ''
, @procParameters NVARCHAR(4000) = ''
, @userFriendly BIT = 0
, @forceExit BIT = 1
, @returnError BIT = 1
)
AS
/***************************************************************
Name: dba_logError_sp

Author: Michelle F. Ufford, http://sqlfool.com

Purpose: Retrieves error information and logs in the
dba_errorLog table.

@errorType = options are "app" or "sys"; "app" are custom
application errors, i.e. business logic errors;
"sys" are system errors, i.e. PK errors

@app_errorProcedure = stored procedure name,
needed for app errors

@app_errorMessage = custom app error message

@procParameters = optional; log the parameters that were passed
to the proc that resulted in an error

@userFriendly = displays a generic error message if = 1

@forceExit = forces the proc to rollback and exit;
mostly useful for application errors.

@returnError = returns the error to the calling app if = 1

Called by: Another stored procedure

Date Initials Description
----------------------------------------------------------------------------
2008-12-16 MFU Initial Release
****************************************************************
Exec dbo.dba_logError_sp
@errorType = 'app'
, @app_errorProcedure = 'someTableInsertProcName'
, @app_errorMessage = 'Some app-specific error message'
, @userFriendly = 1
, @forceExit = 1
, @returnError = 1;
****************************************************************/

SET NOCOUNT ON;
SET XACT_Abort ON;

BEGIN

/* Declare Variables */
DECLARE @errorNumber INT
, @errorProcedure VARCHAR(50)
, @dbName sysname
, @errorLine INT
, @errorMessage NVARCHAR(4000)
, @errorSeverity INT
, @errorState INT
, @errorReturnMessage NVARCHAR(4000)
, @errorReturnSeverity INT
, @currentDateTime SMALLDATETIME;

DECLARE @errorReturnID TABLE (errorID VARCHAR(10));

/* Initialize Variables */
SELECT @currentDateTime = GETDATE();

/* Capture our error details */
IF @errorType = 'sys'
BEGIN

/* Get our system error details and hold it */
SELECT
@errorNumber = Error_Number()
, @errorProcedure = Error_Procedure()
, @dbName = DB_NAME()
, @errorLine = Error_Line()
, @errorMessage = Error_Message()
, @errorSeverity = Error_Severity()
, @errorState = Error_State() ;

END
ELSE
BEGIN

/* Get our custom app error details and hold it */
SELECT
@errorNumber = 0
, @errorProcedure = @app_errorProcedure
, @dbName = DB_NAME()
, @errorLine = 0
, @errorMessage = @app_errorMessage
, @errorSeverity = 0
, @errorState = 0 ;

END;

/* And keep a copy for our logs */
INSERT INTO dbo.dba_errorLog
(
errorType
, errorDate
, errorLine
, errorMessage
, errorNumber
, errorProcedure
, procParameters
, errorSeverity
, errorState
, databaseName
)
OUTPUT Inserted.errorLog_id INTO @errorReturnID
VALUES
(
@errorType
, @currentDateTime
, @errorLine
, @errorMessage
, @errorNumber
, @errorProcedure
, @procParameters
, @errorSeverity
, @errorState
, @dbName
);

/* Should we display a user friendly message to the application? */
IF @userFriendly = 1
SELECT @errorReturnMessage = 'An error has occurred in the database (' + errorID + ')'
FROM @errorReturnID;
ELSE
SELECT @errorReturnMessage = @errorMessage;

/* Do we want to force the application to exit? */
IF @forceExit = 1
SELECT @errorReturnSeverity = 15
ELSE
SELECT @errorReturnSeverity = @errorSeverity;

/* Should we return an error message to the calling proc? */
IF @returnError = 1
RAISERROR
(
@errorReturnMessage
, @errorReturnSeverity
, 1
) WITH NoWait;

SET NOCOUNT OFF;
RETURN 0;

END
Go