Storing sp_BlitzIndex to a Table Between Reboots

I mentioned in my New Database Job – The 90 Day Plan blog how I have a trick for storing index usage stats up until close to the next reboot of the SQL Server.  You really can do this for any DMV-related query that you get reset at the reboot of a SQL Server instance.  With this, I am able to have data of most of the current index usage stats up to midnight before a reboot of my SQL instances to analyze.

CREATE TABLE [dbo].[ServerStartupTimes](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[sqlserver_start_time] [DATETIME] NOT NULL,
CONSTRAINT [PK_ServerStartupTimes] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO

Then at the in a SQL Agent job, I start it with the following code to check a table the table for the last startup time.  If the current startup time does not match the last recorded time the time it will insert the new time in the ServerStartupTimes table. If the time matches it will delete the last batch of data recorded from sp_BlitzIndex.

DECLARE @recorded_start_time DATETIME,
    @current_start_time DATETIME,
    @last_run_datetime DATETIME

SELECT TOP (1) @recorded_start_time = sqlserver_start_time 
FROM dbo.ServerStartupTimes 
ORDER BY ID DESC

SELECT @current_start_time = sqlserver_start_time 
FROM sys.dm_os_sys_info

IF @current_start_time <> @recorded_start_time OR @recorded_start_time IS NULL
BEGIN
    INSERT INTO dbo.ServerStartupTimes (sqlserver_start_time)
    VALUES (@current_start_time)
END
ELSE
BEGIN
    SELECT @last_run_datetime = MAX(run_datetime) 
    FROM dbo.BlitzIndex
    WHERE run_datetime > GETDATE() -2

    DELETE FROM dbo.BlitzIndex 
    WHERE run_datetime = @last_run_datetime
END

Next, we insert the data into the table we want to keep the index usage data in:

EXEC dbo.sp_BlitzIndex @Mode = 2, 
    @GetAllDatabases = 1, 
    @BringThePain = 1,
    @OutputDatabaseName = 'DBATools',
    @OutputSchemaName  = 'dbo',
    @OutputTableName = 'BlitzIndex'

Then I create an index on the run_datetime column if it doesn’t exist so I can cleanup data faster.

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_BlitzIndex_run_datetime')
BEGIN
    CREATE NONCLUSTERED INDEX [IX_BlitzIndex_run_datetime] ON [dbo].[BlitzIndex]
    (
        [run_datetime] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END

Finally, we can cleanup any data older than x number of days, I do 90 days:

DELETE FROM dbo.BlitzIndex
WHERE run_datetime <= GETDATE() - 90

Final script for the whole job is:

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ServerStartupTimes')
BEGIN
    CREATE TABLE [dbo].[ServerStartupTimes](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [sqlserver_start_time] [datetime] NOT NULL,
     CONSTRAINT [PK_ServerStartupTimes] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
END
GO

DECLARE @recorded_start_time DATETIME,
    @current_start_time DATETIME,
    @last_run_datetime DATETIME

SELECT TOP (1) @recorded_start_time = sqlserver_start_time 
FROM dbo.ServerStartupTimes 
ORDER BY ID DESC

SELECT @current_start_time = sqlserver_start_time 
FROM sys.dm_os_sys_info

IF @current_start_time <> @recorded_start_time OR @recorded_start_time IS NULL
BEGIN
    INSERT INTO dbo.ServerStartupTimes (sqlserver_start_time)
    VALUES (@current_start_time)
END
ELSE
BEGIN
    SELECT @last_run_datetime = MAX(run_datetime) 
    FROM dbo.BlitzIndex
    WHERE run_datetime > GETDATE() -2

    DELETE FROM dbo.BlitzIndex 
    WHERE run_datetime = @last_run_datetime
END

EXEC dbo.sp_BlitzIndex @Mode = 2, 
    @GetAllDatabases = 1, 
    @BringThePain = 1,
    @OutputDatabaseName = 'DBATools',
    @OutputSchemaName  = 'dbo',
    @OutputTableName = 'BlitzIndex'

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_BlitzIndex_run_datetime')
BEGIN
    CREATE NONCLUSTERED INDEX [IX_BlitzIndex_run_datetime] ON [dbo].[BlitzIndex]
    (
        [run_datetime] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END

DELETE FROM dbo.BlitzIndex
WHERE run_datetime <= GETDATE() - 90

And with data I have current index usage stats up to the midnight before a reboot of my SQL instances to analyze.

Related Posts

2 thoughts on “Storing sp_BlitzIndex to a Table Between Reboots

  1. Probably because the structure of the table is different when you pass different parameters into the sp_blitzIndex procedure. You will have to export it differently to get the right results if you want to you can DM on Twitter with your email address and I’ll take a look at it when I’m at a SQL Server tomorrow.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.