- 09 Aug 2024
- 5 Minutes to read
- Print
- PDF
USP DB Maintenance, Update Statistics, Missing Index
- Updated on 09 Aug 2024
- 5 Minutes to read
- Print
- PDF
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 GO EXEC dbo.USP_SUMMIT_DB_UPDATE_STATISTICS GO |
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] as 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
GO
create procedure dbo.USP_CREATEMISSINGINDEX as 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 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 end go |