USP DB Maintenance, Update Statistics, Missing Index

09 Aug 2024
5 Minutes to read

USP DB Maintenance, Update Statistics, Missing Index

Updated on 09 Aug 2024
5 Minutes to read

Article summary

Did you find this summary helpful?

Thank you for your feedback

User defined Stored Procedures (USP) is a precompiled collection of SQL statements that perform a specific task and can be executed by users to interact with the database efficiently.

Execute these two procedure scripts. Then Schedule a SQL job on weekly either once or twice as per the requirement.

These SPs responsible to rebuild, reorganize the indexes and update all column statistics.

Script in SQL Job like are as below:

EXEC dbo.USP_SUMMIT_DB_MAINTENANCE

EXEC dbo.USP_SUMMIT_DB_UPDATE_STATISTICS

USP_SUMMIT_DB_Maintenance

CREATE procedure [dbo].[USP_SUMMIT_DB_MAINTENANCE] as

begin

set nocount on;

if not exists (select 1 from sys.objects where type='U' and name = 'SUMMIT_DB_MAINTENANCE')

Create table for maintenance tracking purpose if it does not exist.

begin

create table dbo.SUMMIT_DB_MAINTENANCE (id int identity(1,1),table_name nvarchar(300),index_name nvarchar(300),index_type nvarchar(300),avg_fragmentation numeric(18,2)

,last_run datetime

,constraint PK_SUMMIT_DB_MAINTENANCE primary key clustered (id))

end

declare @edition nvarchar(max) = ''

Select @edition = convert(nvarchar(max),serverproperty('edition'))

Get the edition of the server using which we can do rebuilding online if enterprise edition.

declare @exec_table table (id int identity(1,1) unique clustered,qry nvarchar(max),table_name nvarchar(300),index_name nvarchar(300),index_type nvarchar(300),avg_fragmentation numeric(18,2))

insert into @exec_table (qry,table_name,index_name,index_type,avg_fragmentation)

This table dynamically collects all the executing statements for rebuilding and reorganizing.

SELECT

case when indexstats.index_type_desc = 'HEAP' then '

Alter table '+schema_name()+'."'+OBJECT_NAME(ind.OBJECT_ID)+'" REBUILD'

else

ALTER INDEX "'+ind.name+'" ON '+schema_name()+'."'+OBJECT_NAME(ind.OBJECT_ID)+'"

'+case when indexstats.avg_fragmentation_in_percent >= 30 then 'REBUILD ' else 'REORGANIZE ' end end /*+case when @edition like 'Enterprise%' then ' WITH (ONLINE = ON)' else '' end*/ + '

;

' qry

,OBJECT_NAME(ind.OBJECT_ID) AS TableName,

ind.name AS IndexName, indexstats.index_type_desc AS IndexType,

indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats

INNER JOIN sys.indexes ind

ON ind.object_id = indexstats.object_id

AND ind.index_id = indexstats.index_id

WHERE indexstats.avg_fragmentation_in_percent > 5 and indexstats.page_count > 50

ORDER BY indexstats.avg_fragmentation_in_percent DESC

declare @final_exec_qry nvarchar(max) = ''

declare @min_id int =1

declare @max_id int = (select max(id) from @exec_table)

while @min_id <= @max_id /*execute each statement with in while loop*/

begin

select @final_exec_qry = qry from @exec_table where id = @min_id

print cast(@min_id as nvarchar(20))

select @final_exec_qry

execute sp_executesql @final_exec_qry

set @final_exec_qry = ''

set @min_id = @min_id + 1

end

declare @refresh_day int = 0

select @refresh_day = datediff(day,getutcdate(),min(last_run)) from SUMMIT_DB_MAINTENANCE

print cast(@refresh_day as nvarchar(20))

if @refresh_day > 30

begin

truncate table dbo.SUMMIT_DB_MAINTENANCE

end

insert into dbo.SUMMIT_DB_MAINTENANCE (table_name,index_name,index_type,avg_fragmentation,last_run)

select table_name,index_name,index_type,avg_fragmentation,getutcdate() from @exec_table

end

USP_SUMMIT_DB_UPDATE_STATISTICS

Execute the below SQL Statements.

CREATE procedure [dbo].[USP_SUMMIT_DB_UPDATE_STATISTICS]

begin

exec [sys].[sp_updatestats]

EXEC sp_MSforeachtable @command1="EXEC sp_recompile '?'";

exec sp_MSforeachtable @command1="Update Statistics ? With FullScan"

DBCC DROPCLEANBUFFERS

DBCC FREESYSTEMCACHE ('ALL')

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

end

USP_CREATEMISSINGINDEX

Execute this procedure and schedule a SQL job once in two days to create all recommended indexes by SQL Server.

Script in SQL Job as below:

EXEC dbo.USP_CREATEMISSINGINDEX

create procedure dbo.USP_CREATEMISSINGINDEX

begin

set nocount on;

if object_id('IndexCreatedByJob') is null

begin

create table dbo.IndexCreatedByJob

(

id int not null identity(1,1)

,createscript nvarchar(max)

,dropscript nvarchar(max)

,createddt datetime

,idxrunstarttime datetime

,idxrunendtime datetime

)

end

else if (select count(1) from sys.columns with(nolock) where object_id = object_id('IndexCreatedByJob')) != 6

begin

drop table dbo.IndexCreatedByJob

create table dbo.IndexCreatedByJob

(

id int not null identity(1,1)

,createscript nvarchar(max)

,dropscript nvarchar(max)

,createddt datetime

,idxrunstarttime datetime

,idxrunendtime datetime

)

end

if object_id('tempdb..#createindex') is not null

begin

drop table #createindex

end

create table #createindex (

id int not null identity(1,1)

,sqlstmt nvarchar(max)

,dropstmt nvarchar(max)

)

insert into #createindex

(

sqlstmt

,dropstmt

)

select

sql_statement

,drop_statement

from

(

select

row_number() over(partition by Affected_table,keys order by len(include) desc) rnk

from

(

SELECT @@ServerName AS ServerName ,

DB_NAME() AS DBName ,

t.name AS 'Affected_table' ,

( LEN(ISNULL(ddmid.equality_columns, N'')

+ CASE WHEN ddmid.equality_columns IS NOT NULL

AND ddmid.inequality_columns IS NOT NULL THEN ','

ELSE ''

END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'')

+ CASE WHEN ddmid.equality_columns

IS NOT NULL

AND ddmid.inequality_columns

IS NOT NULL

THEN ','

ELSE ''

END, ',', '')) ) + 1 AS K ,

COALESCE(ddmid.equality_columns, '')

+ CASE WHEN ddmid.equality_columns IS NOT NULL

AND ddmid.inequality_columns IS NOT NULL THEN ','

ELSE ''

END + COALESCE(ddmid.inequality_columns, '') AS Keys ,

COALESCE(ddmid.included_columns, '') AS [include] ,

'Create NonClustered Index IX_' + t.name + '_'+cast(ddmig.index_group_handle as varchar(20))+'_'

+ CAST(ddmid.index_handle AS VARCHAR(20))

+ ' On ' + ddmid.[statement] COLLATE database_default

+ ' (' + ISNULL(ddmid.equality_columns, '')

+ CASE WHEN ddmid.equality_columns IS NOT NULL

AND ddmid.inequality_columns IS NOT NULL THEN ','

ELSE ''

END + ISNULL(ddmid.inequality_columns, '') + ')'

+ ISNULL(' Include (' + ddmid.included_columns + ');', ';')

AS sql_statement ,

'Drop Index IX_' + t.name + '_'+cast(ddmig.index_group_handle as varchar(20))+'_'

+ CAST(ddmid.index_handle AS VARCHAR(20))

+ ' On ' + ddmid.[statement]+';' drop_statement,

ddmigs.user_seeks ,

ddmigs.user_scans ,

CAST(( ddmigs.user_seeks + ddmigs.user_scans )

* ddmigs.avg_user_impact AS BIGINT) AS 'est_impact' ,

avg_user_impact ,

ddmigs.last_user_seek ,

( SELECT DATEDIFF(Second, create_date, GETDATE()) Seconds

FROM sys.databases with(nolock)

WHERE name = 'tempdb'

) SecondsUptime

Select *

FROM sys.dm_db_missing_index_groups ddmig with(nolock)

INNER JOIN sys.dm_db_missing_index_group_stats ddmigs with(nolock)

ON ddmigs.group_handle = ddmig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details ddmid with(nolock)

ON ddmig.index_handle = ddmid.index_handle

INNER JOIN sys.tables t with(nolock) ON ddmid.OBJECT_ID = t.OBJECT_ID

WHERE ddmid.database_id = DB_ID()

) b

) c where rnk = 1

ORDER BY

Affected_table,keys

est_impact DESC;

declare @cnt int = (select max(id) from #createindex)

set @cnt = isnull(@cnt,0)

declare @minid int = 1

declare @maxid int = @cnt

declare @sqlstmt nvarchar(max)

declare @dropstmt nvarchar(max)

declare @result as table (id int)

if @cnt > 0

begin

while @minid <= @maxid

begin

select @sqlstmt = sqlstmt,@dropstmt = dropstmt from #createindex where id = @minid

insert into dbo.IndexCreatedByJob(createscript

,dropscript

,createddt

,idxrunstarttime

)

output inserted.id into @result (id)

select @sqlstmt as createscript

,@dropstmt as dropscript

,getutcdate() as createddt

,getutcdate() as idxrunstarttime

exec sp_executesql @sqlstmt

update a

set a.idxrunendtime = getutcdate()

from dbo.IndexCreatedByJob a where exists (select top 1 1 from @result b where a.id = b.id)

delete from @result

set @dropstmt = null

set @sqlstmt = null

set @minid += 1

end

declare @purgedate datetime = dateadd(month,-3,getutcdate())

if exists (select top 1 1 from dbo.IndexCreatedByJob with(nolock) where createddt < @purgedate)

begin

delete from dbo.IndexCreatedByJob where createddt < @purgedate

end

if object_id('tempdb..#createindex') is not null

begin

drop table #createindex

end

Was this article helpful?

What's Next

Configuration Changes

Table of contents

USP_SUMMIT_DB_Maintenance
USP_SUMMIT_DB_UPDATE_STATISTICS
USP_CREATEMISSINGINDEX