sp_BlitzIndex Custom Collection

[DavidWiseman]

Intro

sp_BlitzIndex is a community script that provides a health check for your indexes - from missing indexes to duplicate and unused indexes. DBA Dash doesn't currently provide any form of index advice so sp_Blitz can be useful to fill this gap.

Prerequisites

We need sp_BlitzIndex deployed on our monitored instances. Also, we probably need to pass some parameters to sp_BlitzIndex but custom collections don't support this in the current version. This is easy to work around with a wrapper stored procedure.

To collect from a specific DB:

CREATE OR ALTER PROC [dbo].[DBADash_sp_BlitzIndex]
AS
EXEC [dbo].[sp_BlitzIndex] @Mode=0,@BringThePain=0,@DatabaseName='YourDB'

To collect from all DBs:

CREATE OR ALTER PROC [dbo].[DBADash_sp_BlitzIndex]
AS
EXEC [dbo].[sp_BlitzIndex] @Mode=0,@BringThePain=0,@GetAllDatabases=1

The DBA Dash service account will need appropriate permissions to run sp_BlitzIndex.

Follow the instructions here for creating the custom collection.

⚠️The default collection name will be DBADash_sp_BlitzIndex or whatever name you used for the wrapper stored procedure. Change the collection name to sp_BlitzIndex. The custom reports available here assume the collection name is sp_BlitzIndex.

Reports

Run the script below to replace the default reports.

IF EXISTS (SELECT * FROM sys.procedures WHERE Name = N'sp_BlitzIndex Example' AND schema_id = SCHEMA_ID('UserReport'))
BEGIN
    DROP PROC [UserReport].[sp_BlitzIndex Example]
END

/*
	sp_BlitzIndex Detail
	

	Custom report for DBA Dash.
	http://dbadash.com
	Generated: 2023-12-08 13:50:00 
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER     PROC [UserReport].[sp_BlitzIndex Detail]
(
	/* Table-valued parameter passing a list of InstanceIDs (dbo.Instance table) associated with the current context in DBA Dash */
	@InstanceIDs IDs READONLY,
	/* Optional @SnapshotDate parameter to show the data associated with a specific snapshot.  Otherwise the last snapshot is shown. UTC */
	@SnapshotDate DATETIME2 = NULL,
	@Finding VARCHAR(4000) = NULL,
	@DatabaseID INT =NULL
)
AS
DECLARE @DatabaseName NVARCHAR(128)
SELECT @DatabaseName = name 
FROM dbo.Databases
WHERE DatabaseID = @DatabaseID;

WITH T AS (
	SELECT	I.InstanceDisplayName AS Instance,
			UD.SnapshotDate,
			UD.[Priority],
			UD.[Finding],
			UD.[Database Name],
			UD.[Details: schema.table.index(indexid)],
			UD.[Definition: [Property]] ColumnName {datatype maxbytes}],
			UD.[Secret Columns],
			UD.[Usage],
			UD.[Size],
			UD.[More Info],
			UD.[URL],
			UD.[Create TSQL],
			UD.[Sample Query Plan],
			/* Get the last snapshot for each instance (RNK=1) */
			RANK() OVER(PARTITION BY UD.InstanceID ORDER BY UD.SnapshotDate DESC) RNK
	FROM UserData.sp_BlitzIndex UD
	JOIN dbo.Instances I ON I.InstanceID = UD.InstanceID
	WHERE EXISTS(SELECT 1
				FROM @InstanceIDs T
				WHERE T.ID = UD.InstanceID
				)
	AND (@SnapshotDate IS NULL OR UD.SnapshotDate = @SnapshotDate)
	AND (UD.Finding = @Finding OR @Finding IS NULL)
	AND (UD.[Database Name] = @DatabaseName OR @DatabaseName IS NULL)
)
SELECT T.Instance,
	T.SnapshotDate,
	T.[Priority],
	T.[Finding],
	T.[Database Name],
	T.[Details: schema.table.index(indexid)],
	T.[Definition: [Property]] ColumnName {datatype maxbytes}],
	T.[Secret Columns],
	T.[Usage],
	T.[Size],
	T.[More Info],
	T.[URL],
	T.[Create TSQL],
	T.[Sample Query Plan]
FROM T
WHERE T.RNK = 1
OPTION(RECOMPILE)



GO

/* Report customizations in GUI */
DELETE dbo.CustomReport
WHERE SchemaName = 'UserReport'
AND ProcedureName = 'sp_BlitzIndex Detail'

INSERT INTO dbo.CustomReport(SchemaName,ProcedureName,MetaData)
VALUES('UserReport','sp_BlitzIndex Detail','{
  "ReportName": "sp_BlitzIndex Detail",
  "CustomReportResults": {
    "0": {
      "ColumnAlias": {},
      "CellFormatString": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [],
      "ResultName": "Result0",
      "LinkColumns": {
        "Details: schema.table.index(indexid)": {
          "$type": "TextLinkColumnInfo",
          "TargetColumn": "Details: schema.table.index(indexid)",
          "TextHandling": "None"
        },
        "URL": {
          "$type": "UrlLinkColumnInfo",
          "TargetColumn": "URL"
        },
        "Create TSQL": {
          "$type": "TextLinkColumnInfo",
          "TargetColumn": "Create TSQL",
          "TextHandling": "SQL"
        },
        "Sample Query Plan": {
          "$type": "QueryPlanLinkColumnInfo",
          "TargetColumn": "Sample Query Plan"
        }
      },
      "CellHighlightingRules": {}
    }
  }
}')

/*
	sp_BlitzIndex
	

	Custom report for DBA Dash.
	http://dbadash.com
	Generated: 2023-12-08 13:50:24 
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER     PROC [UserReport].[sp_BlitzIndex]
(
	/* Table-valued parameter passing a list of InstanceIDs (dbo.Instance table) associated with the current context in DBA Dash */
	@InstanceIDs IDs READONLY
)
AS
WITH LatestSnapshot AS (
	SELECT	UD.Priority,
			UD.Finding,
			UD.URL,
			UD.InstanceID,
			UD.[Database Name],
			/* Get the last snapshot for each instance (RNK=1) */
			RANK() OVER(PARTITION BY UD.InstanceID ORDER BY UD.SnapshotDate DESC) RNK
	FROM UserData.[sp_BlitzIndex]  UD
	WHERE EXISTS(SELECT 1
				FROM @InstanceIDs T
				WHERE T.ID = UD.InstanceID
				)
	AND UD.Priority >=0
)
SELECT	UD.Priority,
		UD.Finding,
		UD.URL,
		COUNT(*) AS [Total],
		COUNT(DISTINCT CONCAT(InstanceID,'~`',UD.[Database Name])) AS [Affected Databases],
		COUNT(DISTINCT InstanceID) AS [Affected Instances]
FROM LatestSnapshot UD
WHERE UD.RNK=1
GROUP BY UD.Priority,
	UD.Finding,
	UD.URL
ORDER BY UD.Priority 



GO

/* Report customizations in GUI */
DELETE dbo.CustomReport
WHERE SchemaName = 'UserReport'
AND ProcedureName = 'sp_BlitzIndex'

INSERT INTO dbo.CustomReport(SchemaName,ProcedureName,MetaData)
VALUES('UserReport','sp_BlitzIndex','{
  "ReportName": "sp_BlitzIndex",
  "CustomReportResults": {
    "0": {
      "ColumnAlias": {},
      "CellFormatString": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [],
      "ResultName": "Result0",
      "LinkColumns": {
        "URL": {
          "$type": "UrlLinkColumnInfo",
          "TargetColumn": "URL"
        },
        "Total": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "sp_BlitzIndex Detail",
          "ColumnToParameterMap": {
            "@Finding": "Finding"
          }
        },
        "Affected Databases": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "sp_BlitzIndex Detail",
          "ColumnToParameterMap": {
            "@Finding": "Finding"
          }
        },
        "Affected Instances": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "sp_BlitzIndex Detail",
          "ColumnToParameterMap": {
            "@Finding": "Finding"
          }
        }
      },
      "CellHighlightingRules": {}
    }
  }
}')