Help! Stored Proc: Executing Defrag with Indexes Based on Page Locking

• dspigs0705

  Grasshopper

  Points: 22

  More actions

  August 28, 2018 at 10:37 am

  #367692

  Hi all,

  I am trying to write an EXECUTE statement that runs the following criteria:
  Call SP once and EXCLUDE indexes that have PAGE_LOCKING=OFF
   REORG >=5%
   REBUILD >=30
  Call SP again, REBUILD for above

  IMPORTANT: I am using this documentation:Server Maintenance

  I'm trying to figure out how to actually exclude indexes in the statement, and have this written so far:

  
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IndexOptimize]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[IndexOptimize] AS'
END
GO
ALTER PROCEDURE [dbo].[IndexOptimize]

  @Databases nvarchar(max) = NULL,
@FragmentationLow nvarchar(max) = NULL,
@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 int = 5,
@FragmentationLevel2 int = 30,
@MinNumberOfPages int = 1000,
@MaxNumberOfPages int = NULL,
@SortInTempdb nvarchar(max) = 'N',
@MaxDOP int = NULL,
@FillFactor int = NULL,
@PadIndex nvarchar(max) = NULL,
@LOBCompaction nvarchar(max) = 'Y',
@UpdateStatistics nvarchar(max) = NULL,
@OnlyModifiedStatistics nvarchar(max) = 'N',
@StatisticsModificationLevel int = NULL,
@StatisticsSample int = NULL,
@StatisticsResample nvarchar(max) = 'N',
@PartitionLevel nvarchar(max) = 'Y',
@MSShippedObjects nvarchar(max) = 'N',
@Indexes nvarchar(max) = NULL,
@TimeLimit int = NULL,
@Delay int = NULL,
@WaitAtLowPriorityMaxDuration int = NULL,
@WaitAtLowPriorityAbortAfterWait nvarchar(max) = NULL,
@Resumable nvarchar(max) = 'N',
@AvailabilityGroups nvarchar(max) = NULL,
@LockTimeout int = NULL,
@LockMessageSeverity int = 16,
@DatabaseOrder nvarchar(max) = NULL,
@DatabasesInParallel nvarchar(max) = 'N',
@LogToTable nvarchar(max) = 'N',
@Execute nvarchar(max) = 'Y'

  --BEGIN EXECUTE BELOW

  EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

  I think I would use the WHERE clause to determine ALLOW_PAGE_LOCKS=1 or 0, but haven't been able to solve anything yet. Ideas?

  Thanks.

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  August 28, 2018 at 11:49 am

  #2003680

  The following query could form the basis of a CURSOR that could, for each row, execute the necessary SQL to check fragmentation, although I suspect you could probably just JOIN the proper DMV to this query and have it right there in your CURSOR so that the necessary maintenance SQL can be executed on a per-index basis:
  SELECT
    I.[object_id],
    T.name                AS TableName,
    I.name                AS IndexName,
    I.[type]            AS IndexTypeID,
    I.[type_desc]        AS TypeDescription,
    I.is_unique,
    I.[ignore_dup_key],
    I.is_primary_key,
    I.is_unique_constraint,
    I.fill_factor,
    I.is_padded,
    I.is_disabled,
    I.is_hypothetical,
    I.[allow_row_locks],
    I.[allow_page_locks],
    I.has_filter,
    I.filter_definition
FROM sys.indexes AS I
INNER JOIN sys.tables AS T
    ON I.[object_id] = T.[object_id]
    AND T.[type] = 'U'
WHERE    I.[type] IN (1,2)            -- Not a HEAP
    AND I.[allow_page_locks] = 0;    -- Page locking off

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)