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 gets reset at the reboot of a SQL Server instance.  With this I am able to have data of the most the current index usage stats up to the 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 the most up to the current index usage stats up to the midnight before a reboot of my SQL instances to analyze.

The post Storing sp_BlitzIndex to a Table Between Reboots first appeared on Tracy Boggiano's Blog.