DEFINITIONS

Definitions More Info.
Definition ID984
TitleSQL
CategoryNOTES
DefinitionDynamic Index maintenance hallengren
Definition DescriptionUSE AtlantisDB
GO
DECLARE @TableName VARCHAR(MAX)= NULL
-- Tablo adı parametresini SET ediyorsun
SELECT @TableName=COALESCE(@TableName+',','')+'['+t.TABLE_CATALOG+'].['+ t.TABLE_SCHEMA+'].['+o.Name+']'
FROM
sysindexes AS i
INNER JOIN sysobjects AS o ON i.id = o.id
INNER JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME = o.name
WHERE
i.indid < 2
AND OBJECTPROPERTY(o.id,'IsMSShipped') = 0
AND i.rowcnt >1000000
AND i.rowcnt <= 100000000
AND o.name NOT LIKE '%Temp_MesajHareketDB%'
AND o.name NOT LIKE '%Temporary%'
AND o.xtype != 'TF'
ORDER BY
o.name ASC
--PRINT (@TableName)

-- Hallengren script calistirilir
DECLARE @sql varchar(max)
DECLARE @databaseName nvarchar(100)

SET @databaseName='AtlantisDB'

SET @sql='EXECUTE DPAudit.dbo.IndexOptimize
@Databases = '''+@databaseName+''',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 15,
@FragmentationLevel2 = 16,
@MinNumberOfPages = 1000,
@MaxNumberOfPages = NULL,
@SortInTempdb = ''N'',
@MaxDOP = 18,
@FillFactor = 90,
@PadIndex = NULL,
@LOBCompaction = ''Y'',
@UpdateStatistics = NULL,
@OnlyModifiedStatistics = ''N'',
@StatisticsModificationLevel = NULL,
@StatisticsSample = NULL,
@StatisticsResample = ''N'',
@PartitionLevel = ''Y'',
@MSShippedObjects = ''N'',
@Indexes='''+@TableName+''',
@TimeLimit = 7200,
--@Delay = 20,
@WaitAtLowPriorityMaxDuration = NULL,
@WaitAtLowPriorityAbortAfterWait = NULL,
@Resumable = ''N'',
@AvailabilityGroups = NULL,
@LockTimeout = NULL,
@LockMessageSeverity = 16,
@StringDelimiter = '','',
@DatabaseOrder = NULL,
@DatabasesInParallel = ''N'',
@LogToTable = ''Y'',
@Execute = ''Y'' '+char(10)+char(13)
--EXECUTE(@sql)
PRINT(@sql)
RecordBycunay
Record Date13-10-2019 21:17:52
Düzenle
Kopyala
Sil