DBCC SHOWCONTIG (Transact-SQL)

DBCC SHOWCONTIG (Transact-SQL)

The Rambling DBA: Jonathan Kehayias : Digging into the SQL Plan Cache: Finding Missing Indexes

The Rambling DBA: Jonathan Kehayias : Digging into the SQL Plan Cache: Finding Missing Indexes



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

SELECT query_plan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,
DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS database_id,
OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID,
n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')
AS statement,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY'
FOR XML PATH('')
) AS equality_columns,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY'
FOR XML PATH('')
) AS inequality_columns,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'
FOR XML PATH('')
) AS include_columns
INTO #MissingIndexInfo
FROM
(
SELECT query_plan
FROM (
SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats WITH(NOLOCK)
) AS qs
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
WHERE tp.query_plan.exist('//MissingIndex')=1
) AS tab (query_plan)
CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)
WHERE n.exist('QueryPlan/MissingIndexes') = 1

-- Trim trailing comma from lists
UPDATE #MissingIndexInfo
SET equality_columns = LEFT(equality_columns,LEN(equality_columns)-1),
inequality_columns = LEFT(inequality_columns,LEN(inequality_columns)-1),
include_columns = LEFT(include_columns,LEN(include_columns)-1)

SELECT *
FROM #MissingIndexInfo

DROP TABLE #MissingIndexInfo

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

How to Identify I/O Bottlenecks in MS SQL Server

How to Identify I/O Bottlenecks in MS SQL Server

Understanding and Using Parallelism in SQL Server

Understanding and Using Parallelism in SQL Server

How to Track Down Deadlocks Using SQL Server 2005 Profiler

How to Track Down Deadlocks Using SQL Server 2005 Profiler

Identify Missing Indexes Using SQL Server DMVs

Identify Missing Indexes Using SQL Server DMVs

Add Command Prompt Here Shortcut to Windows Explorer

Add Command Prompt Here Shortcut to Windows Explorer

cmd line here

Using the SSMS Query Editor in SQLCMD Mode

Using the SSMS Query Editor in SQLCMD Mode

Step By Step Log-Shipping Configuration for SQL Server

Step By Step Log-Shipping Configuration for SQL Server