0

I would like to create a weekly to monthly job to execute sp_BlitzIndex during the night and insert the results into a table so that I can consume the results.

Here's what I've tried:

INSERT INTO dbo.[IndexBlitzResults]
           ([PK_IndexBlitzID]
           ,[Priority]
           ,[Finding]
           ,[DatabaseName]
           ,[Details]
           ,[Definition]
           ,[SecretColumns]
           ,[Usage]
           ,[Size]
           ,[MoreInfo]
           ,[URL]
           ,[CreateTSQL])
exec    [ozar].[sp_BlitzIndex]
        @Mode = 0,
        @GetAllDatabases = 1,
        @BringThePain = 1

And here's the error that it throws:

Starting run. sp_BlitzIndex(TM) v5.4 - June 03, 2017 Create temp tables. Adding UQ index on #IndexSanity (database_id, object_id, index_id)

redacted_db Inserting data into #IndexColumns for clustered indexes and heaps Failure populating temp tables. Last @dsql: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT 29, s.name,
si.object_id, si.index_id, sc.key_ordinal, sc.is_included_column, sc.is_descending_key, sc.partition_ordinal, c.name as column_name, st.name as system_type_name, c.max_length, c.[precision], c.[scale], c.collation_name, c.is_nullable, c.is_identity, c.is_computed, c.is_replicated, c.is_sparse, c.is_filestream, CAST(ic.seed_value AS BIGINT), CAST(ic.increment_value AS INT), CAST(ic.last_value AS BIGINT), ic.is_not_for_replication FROM [redacted_db].sys.indexes si JOIN [redacted_db].sys.columns c ON si.object_id=c.object_id LEFT JOIN [redacted_db].sys.index_columns sc ON sc.object_id = si.object_id and sc.index_id=si.index_id AND sc.column_id=c.column_id LEFT JOIN [redacted_db].sys.identity_columns ic ON c.object_id=ic.object_id and c.column_id=ic.column_id JOIN [redacted_db].sys.types st ON c.system_type_id=st.system_type_id AND c.user_type_id=st.user_type_id JOIN [redacted_db].sys.objects AS so ON si.object_id = so.object_id AND so.is_ms_shipped = 0 JOIN [redacted_db].sys.schemas AS s ON s.schema_id = so.schema_id WHERE si.index_id in (0,1) OPTION (RECOMPILE);

Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1427 [Batch Start Line 2] redacted_db database failed to process. An INSERT EXEC statement cannot be nested. Msg 3915, Level 16, State 0, Procedure sp_BlitzIndex, Line 1431 [Batch Start Line 2] Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I looked into using openrowset, but from what I could tell you can't pass parameters to the stored procedure.

So, how can I insert the results of this stored procedure into the table?

1

I think there is a open request to have this fixed.

It does appear that they have made some changes in March 2017 as referenced here:

  • Added Output parameters to sp_BlitzIndex to send Mode 2 results to permanent repository

I also found a workaround that uses Powershell.

Using PowerShell To Store sp_BlitzIndex Results In A Table.

I'm copying the important information from that post.

First we need to create a table to store our results in:

USE Master --change this to wherever you want this table to reside
GO
CREATE TABLE [dbo].[BlitzIndexStats](
    [Priority] [tinyint] NULL,
    [Finding] [varchar](255) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Details] [varchar](2000) NULL,
    [Definition] [varchar](4000) NULL,
    [SecretColumns] [varchar](512) NULL,
    [Usage] [varchar](255) NULL,
    [Size] [varchar](255) NULL,
    [CaptureDate] [datetime] NOT NULL DEFAULT (sysdatetime())
) ON [PRIMARY]

I decided to leave off things like the URL and CREATE T-SQL statements that are returned, and only store the data I was after.

Next we use the following PowerShell script to run sp_BlitzIndex and store the results in our newly created BlitzIndexStats table.

#call the sp_BlitzIndex procedure and specify the names and data types of the results; 
#save the result set in the $BlitzIndex variable
$BlitzIndex = Invoke-Sqlcmd -ServerInstance $SQLInstance -Database master -Query "EXEC ('exec dbo.sp_BlitzIndex @DatabaseName=''--YourDBNameHere--'',@Mode=4')
WITH RESULT SETS
(
    (
    [Priority] smallint
    ,[Finding] varchar(255)
    ,[DatabaseName] nvarchar(128)
    ,[Details] varchar(2000)
    ,[Definition] varchar(4000)
    ,[SecretColumns] varchar(512)
    ,[Usage] varchar(255)
    ,[Size] varchar(255)
    ,[Info] varchar(255)
    ,[URL] varchar(255)
    ,[CreateStatement] varchar(4000)
    )
)"

#Query the data that we want out of our $BlitzIndex result set, and set the $BlitzIndex result set to just contain that data
$BlitzIndex = $BlitzIndex | SELECT Priority,Finding,DatabaseName,Details,Definition,SecretColumns,Usage,Size | Where-Object {$_.Priority -gt 0} | Sort-Object Priority,Finding

#Loop through our desired results
foreach ($i in $BlitzIndex) {
    #we have to set these variables to keep the PowerShell from erroring when encountering sub-expression statements
    $Priority = $i.Priority
    $Finding = $i.Finding
    $DatabaseName = $i.DatabaseName
    $Details = $i.Details
    $Definition = $i.Definition
    $SecretColumns = $i.SecretColumns
    $Usage = $i.Usage
    $Size = $i.Size
    #Insert our results into our table
    Invoke-Sqlcmd -ServerInstance $SQLInstance -Database master -Query "INSERT INTO dbo.BlitzIndexStats (Priority,Finding,DatabaseName,Details,Definition,SecretColumns,Usage,Size) VALUES ($Priority,'$Finding','$DatabaseName','$Details','$Definition','$SecretColumns','$Usage','$Size')"
}

There are two things I want to point out before running that script:

  1. Be sure to replace “–YourDBNameHere–“ with your database name
  2. If you created the BlitzIndexStats table in a different database, modify “-Database master” in the last line of the script
  • Note that according to Issue 221, this is implemented but only for Mode 2. – Gabe Feb 28 '18 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.