Ola Hallengren – DatabaseBackup – Missing Indexes

Background

I am really high on Ola Hallengren’s Database Utility Scripts and I have wanted to get this down for a while now.

 

Performance

 

Missing Indexes

Missing Indexes can be a drag on system performance.

Unfortunately, there are a couple of missing indexes on the msdb.dbo.backupset table.

 

Code

Programmable

dbo.DatabaseBackup

Code Snippet

 


	DECLARE @CurrentDifferentialBaseLSN numeric(25,0)
	DECLARE @CurrentDatabaseName		sysname
	DECLARE @CurrentDatabaseID int

	DECLARE @Version numeric(18,10)
	DECLARE @AmazonRDS bit

	DECLARE @CurrentDifferentialBaseIsSnapshot bit

	SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.'
                          + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))
                          - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))

	SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END

	SET @CurrentDatabaseName = db_name()
	SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)



	SELECT @CurrentDifferentialBaseLSN = differential_base_lsn
    FROM sys.master_files
    WHERE database_id = @CurrentDatabaseID
    AND [type] = 0
    AND [file_id] = 1

    -- Workaround for a bug in SQL Server 2005
    IF @Version >= 9 AND @Version < 10
    AND EXISTS
    (
           SELECT * FROM sys.master_files 
           WHERE database_id = @CurrentDatabaseID 
           AND [type] = 0 AND [file_id] = 1 
           AND differential_base_lsn IS NOT NULL 
           AND differential_base_guid IS NOT NULL 
           AND differential_base_time IS NULL
    )
    BEGIN
    
          SET @CurrentDifferentialBaseLSN = NULL


    END

    SELECT @CurrentDifferentialBaseIsSnapshot = is_snapshot
    FROM   msdb.dbo.backupset
    WHERE  database_name = @CurrentDatabaseName
    AND    [type] = 'D'
    AND    checkpoint_lsn = @CurrentDifferentialBaseLSN

 

Missing Index

checkpoint_lsn_20161203_0857pm

 

Index Creation Statement

USE [msdb]
GO

CREATE NONCLUSTERED INDEX [INDX_CheckpointLSN_Type_DatabaseName]
ON [dbo].[backupset] 
(
	  [checkpoint_lsn]
	, [type]
	, [database_name]
)

GO


 

 

Code Snippet – INDX_DBA_DatabaseName_IsDamaged
Query Plan

queryplan_20170216_1226pm

Script
/*
Missing Index Details from sql01.master )
The Query Processor estimates that implementing the following index could improve the query cost by 92.1703%.
*/

/*
USE [msdb]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[backupset] ([database_name],[is_damaged])
INCLUDE ([database_backup_lsn],[backup_finish_date],[type])
GO
*/

use [msdb]
go

if object_id('[dbo].[backupset]') is not null
begin

	print 'Reviewing [dbo].[backupset] .... '

	/*
		Query:

			declare @CurrentDatabaseName    sysname
			declare @CurrentLatestBackup	datetime
			declare @CurrentDifferentialBaseLSN numeric(20, 1)

			set @CurrentDatabaseName = 'msdb'
			set @CurrentDifferentialBaseLSN = 1

			BEGIN        

				SELECT @CurrentLatestBackup = MAX(backup_finish_date)        
				FROM   msdb.dbo.backupset        
				WHERE (
							   [type] IN('D','I')        
							OR database_backup_lsn < @CurrentDifferentialBaseLSN
					  )        
				AND is_damaged = 0        
				AND database_name = @CurrentDatabaseName

			END

	*/

	/*

		CREATE NONCLUSTERED INDEX [INDX_DBA_DatabaseName_IsDamaged]
		ON [dbo].[backupset] ([database_name],[is_damaged])
		INCLUDE ([database_backup_lsn],[backup_finish_date],[type])

	*/

	if not exists
	(
		select *
		from   sys.indexes tblSI
		where  tblSI.object_id = object_id('[dbo].[backupset]')
		and    tblSI.[name] = 'INDX_DBA_DatabaseName_IsDamaged'
	)
	begin

		print 'Creating Index INDX_DBA_DatabaseName_IsDamaged ...'

		CREATE NONCLUSTERED INDEX [INDX_DBA_DatabaseName_IsDamaged]
		ON [dbo].[backupset] 
		(
			  [database_name]
			, [is_damaged]
		)
		INCLUDE 
		(
			   [database_backup_lsn]
			 , [backup_finish_date]
			 , [type]
		)
		with
		(
			FILLFACTOR=80
		)
		print 'Created Index INDX_DBA_DatabaseName_IsDamaged'

	end

	print 'Reviewed [dbo].[backupset]'

end

go


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s