miércoles, 21 de diciembre de 2011

Mantenimiento de Indices 2 - Rutina de Optimización

Amigos,

En la primer parte de nuestra nota sobre Mantenimiento de Indices, hemos repasado conceptos y analizado distintas alternativas para abordar el problema de la fragmentación de índices. Si no han leído aún el artículo lo pueden hacer previamente, se los recomiendo.

He recibido numerosos correos a partir de esa nota, solicitando una rutina que permita tener los índices saludables, con el menor costo posible.

Pues bien amigos, sus solicitudes son siempre tenidas en cuenta... Vamos entonces con la rutina.

La Rutina de Optimización de índices, (a partir de ahora "ROI"), realiza las siguientes acciones:

1) Estudia la fragmentación de los índices en la base de datos indicada
2) A partir de los niveles de fragmentación encontrados en cada índice, se toman 3 caminos distintos a saber:
2.1) Si la Fragmentación es Baja (<= al %5) no se toma acción alguna sobre el índice
2.2) Si la Fragmentación es Media (> 5% y < 30%), se hace un Index Reorganize
2.3) Si la Fragmentación es Alta (> 30%), se implementa un Index Rebuild On Line
3) Loguea en una tabla llamada Log_Index, las acciones que ha implementado con cada índice en cada caso.

Elementos que Componen Nuestra “ROI”

  1)      Store Procedure “IndexOptimize” – es el sp que tiene toda la lógica. En el se evalua el % de fragmentación de cada idx de la base de datos y se toma la decisión del camino a seguir. El resultado es el armado de una query dinámica que es ejecutada mediante el store procedure que vamos a ver en el punto 2), y luego logueada en la tabla del punto 4)

22)      Store Procedure “Command Execute” – es el sp que ejecuta la query dinámica que es armada en base a la lógica aplicada en el store procedure madre del punto 

33)    Tabla de Logueo “Log_Index” – es la tabla en la cual podremos ver, una vez  finalizada la ejecución del sp “Index Optimize”, las acciones llevadas a cabo por el mismo

44)    Function “DataBaseSelect” – es una “tabled-valued” function utilizada por el sp madre.

         (**) Es muy importante destacar que sin estos cuatro objetos  nuestra rutina “ROI” NO FUNCIONARA     



GENERANDO LOS 4 OBJETOS (SCRIPTS):

11) Store Procedure “IndexOptimize”


USE [arcalltv]
GO
/****** Object:  StoredProcedure [dbo].[IndexOptimize]    Script Date: 01/24/2014 15:26:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[IndexOptimize]
-------------------------------------------------
-- Se Asignan Valores x Parámetro, Harcodeados --
--------------------*----------------------------
@Databases nvarchar(max) = 'arcalltv',
@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE',
@FragmentationLow nvarchar(max) = 'NOTHING',
--
@FragmentationLevel1 int = 5,
@FragmentationLevel2 int = 40,
--
@PageCountLevel int = 1000,
@SortInTempdb nvarchar(max) = 'Y',
@MaxDOP int = NULL,
@FillFactor int = 90,
@LOBCompaction nvarchar(max) = 'Y',
@StatisticsSample int = NULL,
@PartitionLevel nvarchar(max) = 'Y',
@TimeLimit int = NULL,
@Execute nvarchar(max) = 'Y'

AS

BEGIN

set nocount on
set lock_timeout 3600000
-------------------------------------
-- Se Declaran Variables Generales --
-------------------------------------
DECLARE
@StartMessage nvarchar(max),
@EndMessage nvarchar(max),
@DatabaseMessage nvarchar(max),
@ErrorMessage nvarchar(max),
@StartTime datetime,
@CurrentID int,
@CurrentDatabase nvarchar(max),
@CurrentIsDatabaseAccessible bit,
@CurrentMirroringRole nvarchar(max),
@CurrentCommandSelect01 nvarchar(max),
@CurrentCommandSelect02 nvarchar(max),
@CurrentCommandSelect03 nvarchar(max),
@CurrentCommandSelect04 nvarchar(max),
@CurrentCommandSelect05 nvarchar(max),
@CurrentCommand01 nvarchar(max),
@CurrentCommand02 nvarchar(max),
@CurrentCommandOutput01 int,
@CurrentCommandOutput02 int,
@CurrentIxID int,
@CurrentSchemaID int,
@CurrentSchemaName nvarchar(max),
@CurrentObjectID int,
@CurrentObjectName nvarchar(max),
@CurrentObjectType nvarchar(max),
@CurrentIndexID int,
@CurrentIndexName nvarchar(max),
@CurrentIndexType int,
@CurrentPartitionID bigint,
@CurrentPartitionNumber int,
@CurrentPartitionCount int,
@CurrentIsPartition bit,
@CurrentIndexExists bit,
@CurrentIsLOB bit,
@CurrentAllowPageLocks bit,
@CurrentOnReadOnlyFileGroup bit,
@CurrentFragmentationLevel float,
@CurrentPageCount bigint,
@CurrentAction nvarchar(max),
@CurrentComment nvarchar(max),
@fecha varchar(8),
@Error int,
@db_id int

--------------------------------------------------------------------
-- Se Declara la Tabla para cargar las Base de Datos  a optimizar --
-------------------------------------------------------------------
DECLARE @tmpDatabases TABLE (ID int IDENTITY PRIMARY KEY,
DatabaseName nvarchar(max),
Completed bit)

-----------------------------------------------------------------------------
-- Se Declara la Tabla sobre la cual se van a cargar los índices de cada BD -
-----------------------------------------------------------------------------
DECLARE @tmpIndexes TABLE (IxID int IDENTITY PRIMARY KEY,
SchemaID int,
SchemaName nvarchar(max),
ObjectID int,
ObjectName nvarchar(max),
ObjectType nvarchar(max),
IndexID int,
IndexName nvarchar(max),
IndexType int,
PartitionID bigint,
PartitionNumber int,
PartitionCount int,
Selected bit,
Completed bit)

DECLARE @tmpIndexExists TABLE ([Count] int)
DECLARE @tmpIsLOB TABLE ([Count] int)
DECLARE @tmpAllowPageLocks TABLE ([Count] int)
DECLARE @tmpOnReadOnlyFileGroup TABLE ([Count] int)

----------------------------------------------
-- Se Declara la Tabla de Acciones a Seguir --
----------------------------------------------
DECLARE @Actions TABLE ([Action] nvarchar(max))

--------------------------------------------
-- Se Carga la Tabla de Acciones a Seguir --
--------------------------------------------
INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_ONLINE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_OFFLINE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE')
INSERT INTO @Actions([Action]) VALUES('STATISTICS_UPDATE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE_STATISTICS_UPDATE')
INSERT INTO @Actions([Action]) VALUES('NOTHING')

SET @Error = 0

select @db_id = db_id('arcalltv')


-----------------------------------------------------------------------------
-- Se guarda en :                                                                        
-- @StatTime --> La hora de comienzo de la operacion de reindexado                      
-- @StartMessage --> El status inicial del SqlServer y las acciones a Tomar --- en cada caso
-----------------------------------------------------------------------------

Select @StartTime = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),120),120)
Select @fecha = convert(varchar, convert(datetime,getdate()),02)


SET @StartMessage = 'DateTime: ' + CONVERT(nvarchar,@StartTime,120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Server: ' + convert(nvarchar, SERVERPROPERTY('ServerName')) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Version: ' + convert(nvarchar, SERVERPROPERTY('ProductVersion')) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Edition: ' + convert(nvarchar, SERVERPROPERTY('Edition')) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(sys.schemas.name) FROM sys.schemas INNER JOIN sys.objects ON sys.schemas.[schema_id] = sys.objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationHigh = ' + ISNULL('''' + REPLACE(@FragmentationHigh,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationMedium = ' + ISNULL('''' + REPLACE(@FragmentationMedium,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLow = ' + ISNULL('''' + REPLACE(@FragmentationLow,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLevel1 = ' + ISNULL(CAST(@FragmentationLevel1 AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLevel2 = ' + ISNULL(CAST(@FragmentationLevel2 AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @PageCountLevel = ' + ISNULL(CAST(@PageCountLevel AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @SortInTempdb = ' + ISNULL('''' + REPLACE(@SortInTempdb,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @MaxDOP = ' + ISNULL(CAST(@MaxDOP AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @FillFactor = ' + ISNULL(CAST(@FillFactor AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @LOBCompaction = ' + ISNULL('''' + REPLACE(@LOBCompaction,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @StatisticsSample = ' + ISNULL(CAST(@StatisticsSample AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @PartitionLevel = ' + ISNULL('''' + REPLACE(@PartitionLevel,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @TimeLimit = ' + ISNULL(CAST(@TimeLimit AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)
SET @StartMessage = REPLACE(@StartMessage,'%','%%')

---------------------------------------------------------------------------
-- Se Carga en Tabla Log_Index el Comienzo de la operación de reindexado --
---------------------------------------------------------------------------
insert into log_index
values (@databases, @Fecha, @StartTime, @startmessage,  null,null, null,null)

--------------------------------------------------------------------------------------------------------------
-- Se Carga en la Tabla @tmpDatabases las Bases de Datos a optimizar (utilizando la función DatabaseSelect) --
---------------------------------------------------- ---------------------------------------------------------
INSERT INTO @tmpDatabases (DatabaseName, Completed)
SELECT DatabaseName AS DatabaseName,
0            AS Completed
FROM dbo.DatabaseSelect (@Databases)
ORDER BY DatabaseName ASC

-------------------------------------------
-- Se Chequean Los Parámetros de entrada --
-------------------------------------------
-- Se Chequea si la Versión del Sql Server es apta para el desfragmentado on line --
IF 'INDEX_REBUILD_ONLINE' IN(@FragmentationHigh, @FragmentationMedium, @FragmentationLow) AND SERVERPROPERTY('EngineEdition') <> 3
BEGIN
SET @ErrorMessage = 'Online rebuild is only supported in Enterprise and Developer Edition.' + CHAR(13) + CHAR(10)
Select @StartTime = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),120),120)
insert into log_index
values (@databases, @fecha, @starttime, null,  NULL, NULL, NULL, @ERRORMESSAGE)
END

-- Se Chequea si la Versión del Sql Server es apta para la desfragmentación en pararelo on line (en este caso opción no utilizada)--
IF @MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') <> 3
BEGIN
SET @ErrorMessage = 'Parallel index operations are only supported in Enterprise and Developer Edition.'  + CHAR(13) + CHAR(10)
Select @StartTime = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),120),120)
insert into log_index
values (@databases, @fecha, @starttime, null,  NULL, NULL, NULL, @ERRORMESSAGE)
END


-----------------------------------------------------------------------------------------
--  Se Selecciona una base de datos (en este caso se selecciona el valor hardcodeado)  --
-----------------------------------------------------------------------------------------
WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)
BEGIN

SELECT TOP 1 @CurrentID = ID,
@CurrentDatabase = DatabaseName
FROM @tmpDatabases
WHERE Completed = 0
ORDER BY ID ASC

-- Se Evalúa Estado de Recovery de la BD y si es Accesible o No a partir de ese estado --
IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = DB_ID(@CurrentDatabase) AND database_guid IS NOT NULL)
BEGIN
SET @CurrentIsDatabaseAccessible = 1
END
ELSE
BEGIN
SET @CurrentIsDatabaseAccessible = 0
END

-- Se Evalúa si la BD está Espejada --
SELECT @CurrentMirroringRole = mirroring_role_desc
FROM sys.database_mirroring
WHERE database_id = DB_ID(@CurrentDatabase)

-- Set database message
SET @DatabaseMessage = 'DateTime: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabase) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'Status') AS nvarchar) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Mirroring role: ' + ISNULL(@CurrentMirroringRole,'None') + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabase,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Updateability: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'Updateability') AS nvarchar) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'UserAccess') AS nvarchar) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'Recovery') AS nvarchar) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = REPLACE(@DatabaseMessage,'%','%%')

------------------------------------------------------------------------------- Se Carga en Tabla Log_Index el datetime del Comienzo de la operación sobre -- la BD y el Estado de la misma --
-----------------------------------------------------------------------------Select @StartTime = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),120),120)
insert into log_index
values (@databases, @Fecha, @StartTime, null, @DATABASEMESSAGE, null, null, null)

-----------------------------------------------------------------------------
-- Se Chequea el estado de la BD y si todo está ok se comienza con la
-- Optimización --
-----------------------------------------------------------------------------
IF DATABASEPROPERTYEX(@CurrentDatabase,'Status') = 'ONLINE' and not
(DATABASEPROPERTYEX(@CurrentDatabase,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0) and
DATABASEPROPERTYEX(@CurrentDatabase,'Updateability') = 'READ_WRITE'
BEGIN
------------------------------------------------
-- Se Seleccionan los Indices de la actual BD --
------------------------------------------------
IF @PartitionLevel = 'N'
SET @CurrentCommandSelect01 = 'SELECT ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id], ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[name], ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id], ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[name], RTRIM(' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type]), ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id, ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[name], ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type], NULL AS partition_id, NULL AS partition_number, NULL AS partition_count, 0 AS selected, 0 AS completed FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[schema_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] IN(''U'',''V'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.is_ms_shipped = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type] IN(1,2,3,4) AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_disabled = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_hypothetical = 0 ORDER BY ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] ASC, ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] ASC, ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id ASC'
IF @PartitionLevel = 'Y'
SET @CurrentCommandSelect01 = 'SELECT ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id], ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[name], ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id], ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[name], RTRIM(' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type]), ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id, ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[name], ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type], ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.partition_id, ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.partition_number, IndexPartitions.partition_count, 0 AS selected, 0 AS completed FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[schema_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.[object_id] AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.index_id LEFT OUTER JOIN (SELECT [object_id], index_id, COUNT(*) AS partition_count FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions GROUP BY [object_id], index_id) IndexPartitions ON ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.[object_id] = IndexPartitions.[object_id] AND ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.[index_id] = IndexPartitions.[index_id] WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] IN(''U'',''V'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.is_ms_shipped = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type] IN(1,2,3,4) AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_disabled = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_hypothetical = 0 ORDER BY ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] ASC, ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] ASC, ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id ASC, ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.partition_number ASC'

--------------------------------------------------
-- Se cargan en la tabla los índices a optmizar --
--------------------------------------------------
INSERT INTO
@tmpIndexes
(SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IndexID, IndexName, IndexType, PartitionID, PartitionNumber, PartitionCount, Selected, Completed)
EXECUTE(@CurrentCommandSelect01)

UPDATE @tmpIndexes
SET Selected = 1
FROM @tmpIndexes

-----------------------------------------------------------------------------
-- LOOP -- Carga uno a uno los índices de la Base de Datos y se da Optimizan -- los mismos
----------------------------------------------------------------------------

WHILE EXISTS (SELECT * FROM @tmpIndexes WHERE Selected = 1 AND Completed = 0)
BEGIN
-- Se carga el primero de los Indices del vector
SELECT TOP 1 @CurrentIxID = IxID,
@CurrentSchemaID = SchemaID,
@CurrentSchemaName = SchemaName,
@CurrentObjectID = ObjectID,
@CurrentObjectName = ObjectName,
@CurrentObjectType = ObjectType,
@CurrentIndexID = IndexID,
@CurrentIndexName = IndexName,
@CurrentIndexType = IndexType,
@CurrentPartitionID = PartitionID,
@CurrentPartitionNumber = PartitionNumber,
@CurrentPartitionCount = PartitionCount
FROM @tmpIndexes
WHERE Selected = 1
AND Completed = 0
ORDER BY IxID ASC

-- Es Un Indice Particionado? --
IF @CurrentPartitionNumber IS NULL OR @CurrentPartitionCount = 1
BEGIN
SET @CurrentIsPartition = 0
END
ELSE
BEGIN
SET @CurrentIsPartition = 1
END

-- Existe el índice? --
IF @CurrentIsPartition = 0
SET @CurrentCommandSelect02 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[schema_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] IN(''U'',''V'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.is_ms_shipped = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type] IN(1,2,3,4) AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_disabled = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_hypothetical = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] = ' + CAST(@CurrentSchemaID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[name] = N' + QUOTENAME(@CurrentSchemaName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[name] = N' + QUOTENAME(@CurrentObjectName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] = N' + QUOTENAME(@CurrentObjectType,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id = ' + CAST(@CurrentIndexID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[name] = N' + QUOTENAME(@CurrentIndexName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type] = ' + CAST(@CurrentIndexType AS nvarchar)
IF @CurrentIsPartition = 1
SET @CurrentCommandSelect02 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[schema_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.[object_id] AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.index_id WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] IN(''U'',''V'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.is_ms_shipped = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type] IN(1,2,3,4) AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_disabled = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_hypothetical = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] = ' + CAST(@CurrentSchemaID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[name] = N' + QUOTENAME(@CurrentSchemaName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[name] = N' + QUOTENAME(@CurrentObjectName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] = N' + QUOTENAME(@CurrentObjectType,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id = ' + CAST(@CurrentIndexID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[name] = N' + QUOTENAME(@CurrentIndexName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type] = ' + CAST(@CurrentIndexType AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.partition_id = ' + CAST(@CurrentPartitionID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.partition_number = ' + CAST(@CurrentPartitionNumber AS nvarchar)

-- Se Hace un Count del Indice y se guarda en tabla --
INSERT INTO @tmpIndexExists ([Count])
EXECUTE(@CurrentCommandSelect02)

IF (SELECT [Count] FROM @tmpIndexExists) > 0
BEGIN
SET @CurrentIndexExists = 1
END
ELSE
BEGIN
SET @CurrentIndexExists = 0
END

IF @CurrentIndexExists = 0
GOTO NoAction

-- Contiene el Indice Algún Campo LOB? --
IF @CurrentIndexType = 1
SET @CurrentCommandSelect03 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.columns INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.types ON ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.system_type_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.types.user_type_id OR (' + QUOTENAME(@CurrentDatabase) + '.sys.columns.user_type_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.types.user_type_id AND '+ QUOTENAME(@CurrentDatabase) + '.sys.types.is_assembly_type = 1) WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND (' + QUOTENAME(@CurrentDatabase) + '.sys.types.name IN(''xml'',''image'',''text'',''ntext'') OR (' + QUOTENAME(@CurrentDatabase) + '.sys.types.name IN(''varchar'',''nvarchar'',''varbinary'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.max_length = -1) OR (' + QUOTENAME(@CurrentDatabase) + '.sys.types.is_assembly_type = 1 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.max_length = -1))'
IF @CurrentIndexType = 2
SET @CurrentCommandSelect03 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.columns ON ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.[object_id] AND ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns.column_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.column_id INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.types ON ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.system_type_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.types.user_type_id OR (' + QUOTENAME(@CurrentDatabase) + '.sys.columns.user_type_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.types.user_type_id AND ' + QUOTENAME(@CurrentDatabase) + '.sys.types.is_assembly_type = 1) WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns.index_id = ' + CAST(@CurrentIndexID AS nvarchar) + ' AND (' + QUOTENAME(@CurrentDatabase) + '.sys.types.[name] IN(''xml'',''image'',''text'',''ntext'') OR (' + QUOTENAME(@CurrentDatabase) + '.sys.types.[name] IN(''varchar'',''nvarchar'',''varbinary'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.max_length = -1) OR (' + QUOTENAME(@CurrentDatabase) + '.sys.types.is_assembly_type = 1 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.max_length = -1))'
IF @CurrentIndexType = 3
SET @CurrentCommandSelect03 = 'SELECT 1'
IF @CurrentIndexType = 4
SET @CurrentCommandSelect03 = 'SELECT 1'


INSERT INTO @tmpIsLOB ([Count])
EXECUTE(@CurrentCommandSelect03)

IF (SELECT [Count] FROM @tmpIsLOB) > 0
BEGIN
SET @CurrentIsLOB = 1
END
ELSE
BEGIN
SET @CurrentIsLOB = 0
END

-- Está la Opción "Allow_Page_Locks" seteada en On? --
SET @CurrentCommandSelect04 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[index_id] = ' + CAST(@CurrentIndexID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[allow_page_locks] = 1'

INSERT INTO @tmpAllowPageLocks ([Count])
EXECUTE(@CurrentCommandSelect04)

IF (SELECT [Count] FROM @tmpAllowPageLocks) > 0
BEGIN
SET @CurrentAllowPageLocks = 1
END
ELSE
BEGIN
SET @CurrentAllowPageLocks = 0
END

-- El Indice refiere a una tabla que corresponde a un Filegroup Read-Only? --
SET @CurrentCommandSelect05 = 'SELECT COUNT(*) FROM (SELECT ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.destination_data_spaces ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.data_space_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.destination_data_spaces.partition_scheme_id INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups ON ' + QUOTENAME(@CurrentDatabase) + '.sys.destination_data_spaces.data_space_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.data_space_id WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.is_read_only = 1 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[index_id] = ' + CAST(@CurrentIndexID AS nvarchar)
IF @CurrentIsPartition = 1
SET @CurrentCommandSelect05 = @CurrentCommandSelect05 + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.destination_data_spaces.destination_id = ' + CAST(@CurrentPartitionNumber AS nvarchar)
SET @CurrentCommandSelect05 = @CurrentCommandSelect05 + ' UNION SELECT ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.data_space_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.data_space_id WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.is_read_only = 1 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[index_id] = ' + CAST(@CurrentIndexID AS nvarchar)
IF @CurrentIndexType = 1
SET @CurrentCommandSelect05 = @CurrentCommandSelect05 + ' UNION SELECT ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.tables INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups ON ' + QUOTENAME(@CurrentDatabase) + '.sys.tables.lob_data_space_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.data_space_id WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.is_read_only = 1 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.tables.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar)
SET @CurrentCommandSelect05 = @CurrentCommandSelect05 + ') ReadOnlyFileGroups'

INSERT INTO @tmpOnReadOnlyFileGroup ([Count])
EXECUTE(@CurrentCommandSelect05)

IF (SELECT [Count] FROM @tmpOnReadOnlyFileGroup) > 0
BEGIN
SET @CurrentOnReadOnlyFileGroup = 1
END
ELSE
BEGIN
SET @CurrentOnReadOnlyFileGroup = 0
END

--*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
-- Está fragmentado el índice ?  --
---*-*-**-*-*-*-*-*-*-*-*-*-*-*-*--
SELECT
@CurrentFragmentationLevel = MAX(avg_fragmentation_in_percent),
@CurrentPageCount = SUM(page_count)
FROM sys.dm_db_index_physical_stats(@db_id, @CurrentObjectID, @CurrentIndexID, @CurrentPartitionNumber, 'LIMITED')
WHERE
alloc_unit_type_desc = 'IN_ROW_DATA' AND
index_level = 0

-----------------------------------------------------------------------
-- Si se produce error tratando de consultar esta vista,lo loguea en
-- Log_Index y no ejecuta acción alguna –
-----------------------------------------------------------------------
SET @Error = @@ERROR
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The dynamic management view sys.dm_db_index_physical_stats is locked on the index ' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + '.' + QUOTENAME(@CurrentIndexName) + '.' + CHAR(13) + CHAR(10)
Select @StartTime = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),120),120)
insert into log_index
values (@databases, @fecha, @starttime, null,  NULL, NULL, NULL, @ERRORMESSAGE)
GOTO NoAction
END

-------------------------------------------------------------------------
-- Se decide el Tipo de Optimización Acorde al Nivel de Fragmentación  --
-------------------------------------------------------------------------
SELECT @CurrentAction = CASE
WHEN (@CurrentFragmentationLevel >= @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel) THEN @FragmentationHigh
WHEN (@CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel) THEN @FragmentationMedium
WHEN (@CurrentFragmentationLevel < @FragmentationLevel1 OR @CurrentPageCount < @PageCountLevel) THEN @FragmentationLow
else 'NOTHING'
END

--------------------------------------------------------------------------
-- Se guarda en la tabla Log_Index el Detalle de la Acción a Ejecutar   --
---------------------------------------------------------------------------
SET @CurrentComment = 'ObjectType: ' + CASE WHEN @CurrentObjectType = 'U' THEN 'Table' WHEN @CurrentObjectType = 'V' THEN 'View' ELSE 'N/A' END + ', '
SET @CurrentComment = @CurrentComment + 'IndexType: ' + CASE WHEN @CurrentIndexType = 1 THEN 'Clustered' WHEN @CurrentIndexType = 2 THEN 'NonClustered' WHEN @CurrentIndexType = 3 THEN 'XML' WHEN @CurrentIndexType = 4 THEN 'Spatial' ELSE 'N/A' END + ', '
SET @CurrentComment = @CurrentComment + 'LOB: ' + CASE WHEN @CurrentIsLOB = 1 THEN 'Yes' WHEN @CurrentIsLOB = 0 THEN 'No' ELSE 'N/A' END + ', '
SET @CurrentComment = @CurrentComment + 'AllowPageLocks: ' + CASE WHEN @CurrentAllowPageLocks = 1 THEN 'Yes' WHEN @CurrentAllowPageLocks = 0 THEN 'No' ELSE 'N/A' END + ', '
SET @CurrentComment = @CurrentComment + 'PageCount: ' + CAST(@CurrentPageCount AS nvarchar) + ', '
SET @CurrentComment = @CurrentComment + 'Fragmentation: ' + CAST(@CurrentFragmentationLevel AS nvarchar)

----------------------
-- Chequea Time Out --
----------------------
IF GETDATE() >= DATEADD(ss,@TimeLimit,@StartTime)
BEGIN
SET @Execute = 'N'
END

IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE','INDEX_REORGANIZE','INDEX_REORGANIZE_STATISTICS_UPDATE') AND @CurrentOnReadOnlyFileGroup = 0
BEGIN

---------------------------------------------------------------
-- Se Comienza el Armado del Script de Optimización Dinánico --
---------------------------------------------------------------
SET @CurrentCommand01 = 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName)

------------------------------------
-- Arma el comando para un rebuild –
------------------------------------
IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE')
BEGIN
SET @CurrentCommand01 = @CurrentCommand01 + ' REBUILD'
IF @CurrentIsPartition = 1
SET @CurrentCommand01 = @CurrentCommand01 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)
SET @CurrentCommand01 = @CurrentCommand01 + ' WITH ('
IF @SortInTempdb = 'Y'
SET @CurrentCommand01 = @CurrentCommand01 + 'SORT_IN_TEMPDB = ON'
IF @SortInTempdb = 'N'
SET @CurrentCommand01 = @CurrentCommand01 + 'SORT_IN_TEMPDB = OFF'
IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentIsPartition = 0
SET @CurrentCommand01 = @CurrentCommand01 + ', ONLINE = ON'
IF @CurrentAction = 'INDEX_REBUILD_OFFLINE' AND @CurrentIsPartition = 0
SET @CurrentCommand01 = @CurrentCommand01 + ', ONLINE = OFF'
IF @MaxDOP IS NOT NULL
SET @CurrentCommand01 = @CurrentCommand01 + ', MAXDOP = ' + CAST(@MaxDOP AS nvarchar)
IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0
SET @CurrentCommand01 = @CurrentCommand01 + ', FILLFACTOR = ' + CAST(@FillFactor AS nvarchar)
SET @CurrentCommand01 = @CurrentCommand01 + ')'
END

---------------------------------------
-- Arma el comando para un reorganize –
----------------------------------------
IF @CurrentAction IN('INDEX_REORGANIZE')
BEGIN
SET @CurrentCommand01 = @CurrentCommand01 + ' REORGANIZE'
IF @CurrentIsPartition = 1
SET @CurrentCommand01 = @CurrentCommand01 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)
SET @CurrentCommand01 = @CurrentCommand01 + ' WITH ('
IF @LOBCompaction = 'Y'
SET @CurrentCommand01 = @CurrentCommand01 + 'LOB_COMPACTION = ON'
IF @LOBCompaction = 'N'
SET @CurrentCommand01 = @CurrentCommand01 + 'LOB_COMPACTION = OFF'
SET @CurrentCommand01 = @CurrentCommand01 + ')'
END

------------------------------------------------------------------
-- ejecuta el sp [dbo].[CommandExecute]y le pasa los parámetros --
------------------------------------------------------------------
EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @CurrentCommand01, @CurrentComment, 2, @Execute

------------------------------
-- Carga en Tabla Log_Index –
------------------------------
Select @StartTime = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),120),120)
insert into log_index
values (@databases, @fecha, @starttime, null,  NULL,@CURRENTCOMMENT, @CurrentCommand01, null)
SET @Error = @@ERROR
IF @Error <> 0
SET @CurrentCommandOutput01 = @Error
END


--------------------------
-- Rutina para No Action –
--------------------------
NoAction:

-------------------------------------
-- Update that the index is completed
-------------------------------------
UPDATE @tmpIndexes
SET Completed = 1
WHERE IxID = @CurrentIxID

---------------------
-- Clear variables
---------------------
SET @CurrentCommandSelect02 = NULL
SET @CurrentCommandSelect03 = NULL
SET @CurrentCommandSelect04 = NULL
SET @CurrentCommandSelect05 = NULL

SET @CurrentCommand01 = NULL
SET @CurrentCommand02 = NULL

SET @CurrentCommandOutput01 = NULL
SET @CurrentCommandOutput02 = NULL

SET @CurrentIxID = NULL
SET @CurrentSchemaID = NULL
SET @CurrentSchemaName = NULL
SET @CurrentObjectID = NULL
SET @CurrentObjectName = NULL
SET @CurrentObjectType = NULL
SET @CurrentIndexID = NULL
SET @CurrentIndexName = NULL
SET @CurrentIndexType = NULL
SET @CurrentPartitionID = NULL
SET @CurrentPartitionNumber = NULL
SET @CurrentPartitionCount = NULL
SET @CurrentIsPartition = NULL
SET @CurrentIndexExists = NULL
SET @CurrentIsLOB = NULL
SET @CurrentAllowPageLocks = NULL
SET @CurrentOnReadOnlyFileGroup = NULL
SET @CurrentFragmentationLevel = NULL
SET @CurrentPageCount = NULL
SET @CurrentAction = NULL
SET @CurrentComment = NULL

DELETE FROM @tmpIndexExists
DELETE FROM @tmpIsLOB
DELETE FROM @tmpAllowPageLocks
DELETE FROM @tmpOnReadOnlyFileGroup
-- fin rutina no action
END
-- fin loop reindexado todos los indices de una base de datos
END

-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE ID = @CurrentID

--------------------
-- Clear variables –
---------------------
SET @CurrentID = NULL
SET @CurrentDatabase = NULL
SET @CurrentIsDatabaseAccessible = NULL
SET @CurrentMirroringRole = NULL

SET @CurrentCommandSelect01 = NULL

DELETE FROM @tmpIndexes

-- fin loop reindexado todos lAS BASES DE DATOS

END

----------------------------------------------------
--// Log completing information                                                                 
----------------------------------------------------
Logging:
Select @StartTime = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),120),120)
insert into log_index
values (@databases, @fecha, @starttime, null,  NULL,'final', 'final', null)

END


--- FIN DEL SP –



  2)                      Store Procedure “Command Execute”


USE [arcalltv]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CommandExecute]

@Command nvarchar(max),
@Comment nvarchar(max),
@Mode int,
@Execute nvarchar(max)

AS

BEGIN

SET NOCOUNT ON
SET LOCK_TIMEOUT 3600000

-------------------------------------------------------------------------- ---- Declare variables                                                                         
-------------------------------------------------------------------------

DECLARE @StartMessage nvarchar(max)
DECLARE @EndMessage nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)
DECLARE @ErrorMessageOriginal nvarchar(max)

DECLARE @StartTime datetime
DECLARE @EndTime datetime

DECLARE @StartTimeSec datetime
DECLARE @EndTimeSec datetime

DECLARE @Error int

SET @Error = 0

-----------------------------------------------------------------------------
--// Check input parameters                                                                    
-----------------------------------------------------------------------------

IF @Command IS NULL OR @Command = ''
BEGIN
SET @ErrorMessage = 'The value for parameter @Command is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @Comment IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @Comment is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @Mode NOT IN(1,2) OR @Mode IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @Mode is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @Execute NOT IN('Y','N') OR @Execute IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @Execute is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

-----------------------------------------------------------------------------
--// Check error variable                                                                       
----------------------------------------------------------------------------
IF @Error <> 0 GOTO ReturnCode

----------------------------------------------------------------------------
--// Log initial information                                                                    -----------------------------------------------------------------------------

SET @StartTime = GETDATE()
SET @StartTimeSec = CONVERT(datetime,CONVERT(nvarchar,@StartTime,120),120)

SET @StartMessage = 'DateTime: ' + CONVERT(nvarchar,@StartTimeSec,120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Command: ' + @Command
IF @Comment <> '' SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10) + 'Comment: ' + @Comment
SET @StartMessage = REPLACE(@StartMessage,'%','%%')
RAISERROR(@StartMessage,10,1) WITH NOWAIT

-------------------------------------------------------------------------------// Execute command                                                                           
-----------------------------------------------------------------------------

IF @Mode = 1 AND @Execute = 'Y'
BEGIN
EXECUTE(@Command)
SET @Error = @@ERROR
END

IF @Mode = 2 AND @Execute = 'Y'
BEGIN
BEGIN TRY
EXECUTE(@Command)
END TRY
BEGIN CATCH
SET @Error = ERROR_NUMBER()
SET @ErrorMessageOriginal = ERROR_MESSAGE()
SET @ErrorMessage = 'Msg ' + CAST(@Error AS nvarchar) + ', ' + ISNULL(@ErrorMessageOriginal,'')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END CATCH
END

-----------------------------------------------------------------------------
--// Log completing information                                                                 
----------------------------------------------------------------------------

SET @EndTime = GETDATE()
SET @EndTimeSec = CONVERT(datetime,CONVERT(varchar,@EndTime,120),120)

SET @EndMessage = 'Outcome: ' + CASE WHEN @Execute = 'N' THEN 'Not Executed' WHEN @Error = 0 THEN 'Succeeded' ELSE 'Failed' END + CHAR(13) + CHAR(10)
SET @EndMessage = @EndMessage + 'Duration: ' + CASE WHEN DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) > 0 THEN CAST(DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) AS nvarchar) + '.' ELSE '' END + CONVERT(nvarchar,@EndTimeSec - @StartTimeSec,108) + CHAR(13) + CHAR(10)
SET @EndMessage = @EndMessage + 'DateTime: ' + CONVERT(nvarchar,@EndTimeSec,120) + CHAR(13) + CHAR(10)
SET @EndMessage = REPLACE(@EndMessage,'%','%%')
RAISERROR(@EndMessage,10,1) WITH NOWAIT

-----------------------------------------------------------------------------
--// Return code                                                                                
----------------------------------------------------------------------------

ReturnCode:

RETURN @Error

-----------------------------------------------------------------------------

END





 3)                      Tabla de Logueo “Log_Index”

USE [arcalltv]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[log_index](
      [databases] [varchar](100) NULL,
      [fecha] [varchar](8) NULL,
      [fecha_hora] [datetime] NULL,
      [mensaje_inicial] [varchar](2000) NULL,
      [mensaje_database] [varchar](2000) NULL,
      [mensaje_indexado] [varchar](2000) NULL,
      [ejecucion] [varchar](2000) NULL,
      [error] [varchar](500) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO

 4)                      Function “DatabaseSelect”

USE [arcalltv]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[DatabaseSelect] (@DatabaseList nvarchar(max))

RETURNS @Database TABLE (DatabaseName nvarchar(max) NOT NULL)

AS

BEGIN

---------------------------------------------------------------------------
--// Declare variables
---------------------------------------------------------------------------

DECLARE @DatabaseItem nvarchar(max)
DECLARE @Position int

DECLARE @CurrentID int
DECLARE @CurrentDatabaseName nvarchar(max)
DECLARE @CurrentDatabaseStatus bit

DECLARE @Database01 TABLE (DatabaseName nvarchar(max))

DECLARE @Database02 TABLE (ID int IDENTITY PRIMARY KEY,
DatabaseName nvarchar(max),
DatabaseStatus bit,
Completed bit)

DECLARE @Database03 TABLE (DatabaseName nvarchar(max),
DatabaseStatus bit)

DECLARE @Sysdatabases TABLE (DatabaseName nvarchar(max))

---------------------------------------------------------------------------
--// Split input string into elements
---------------------------------------------------------------------------

SET @DatabaseList = REPLACE(REPLACE(REPLACE(REPLACE(@DatabaseList,'[',''),']',''),'''',''),'"','')

WHILE CHARINDEX(', ',@DatabaseList) > 0 SET @DatabaseList = REPLACE(@DatabaseList,', ',',')
WHILE CHARINDEX(' ,',@DatabaseList) > 0 SET @DatabaseList = REPLACE(@DatabaseList,' ,',',')

WHILE CHARINDEX(',,',@DatabaseList) > 0 SET @DatabaseList = REPLACE(@DatabaseList,',,',',')

IF RIGHT(@DatabaseList,1) = ',' SET @DatabaseList = LEFT(@DatabaseList,LEN(@DatabaseList) - 1)
IF LEFT(@DatabaseList,1) = ','  SET @DatabaseList = RIGHT(@DatabaseList,LEN(@DatabaseList) - 1)

SET @DatabaseList = LTRIM(RTRIM(@DatabaseList))

WHILE LEN(@DatabaseList) > 0
BEGIN
SET @Position = CHARINDEX(',', @DatabaseList)
IF @Position = 0
BEGIN
SET @DatabaseItem = @DatabaseList
SET @DatabaseList = ''
END
ELSE
BEGIN
SET @DatabaseItem = LEFT(@DatabaseList, @Position - 1)
SET @DatabaseList = RIGHT(@DatabaseList, LEN(@DatabaseList) - @Position)
END
IF @DatabaseItem <> '-' INSERT INTO @Database01 (DatabaseName) VALUES(@DatabaseItem)
END

---------------------------------------------------------------------------
--// Handle database exclusions
---------------------------------------------------------------------------

INSERT INTO @Database02 (DatabaseName, DatabaseStatus, Completed)
SELECT DISTINCT DatabaseName = CASE WHEN DatabaseName LIKE '-%' THEN RIGHT(DatabaseName,LEN(DatabaseName) - 1) ELSE DatabaseName END,
DatabaseStatus = CASE WHEN DatabaseName LIKE '-%' THEN 0 ELSE 1 END,
0 AS Completed
FROM @Database01

---------------------------------------------------------------------------
--// Resolve elements
---------------------------------------------------------------------------

WHILE EXISTS (SELECT * FROM @Database02 WHERE Completed = 0)
BEGIN

SELECT TOP 1 @CurrentID = ID,
@CurrentDatabaseName = DatabaseName,
@CurrentDatabaseStatus = DatabaseStatus
FROM @Database02
WHERE Completed = 0
ORDER BY ID ASC

IF @CurrentDatabaseName = 'SYSTEM_DATABASES'
BEGIN
INSERT INTO @Database03 (DatabaseName, DatabaseStatus)
SELECT [name], @CurrentDatabaseStatus
FROM sys.databases
WHERE database_id <= 4
END
ELSE IF @CurrentDatabaseName = 'USER_DATABASES'
BEGIN
INSERT INTO @Database03 (DatabaseName, DatabaseStatus)
SELECT [name], @CurrentDatabaseStatus
FROM sys.databases
WHERE database_id > 4
END
ELSE IF @CurrentDatabaseName = 'ALL_DATABASES'
BEGIN
INSERT INTO @Database03 (DatabaseName, DatabaseStatus)
SELECT [name], @CurrentDatabaseStatus
FROM sys.databases
END
ELSE IF CHARINDEX('%',@CurrentDatabaseName) > 0
BEGIN
INSERT INTO @Database03 (DatabaseName, DatabaseStatus)
SELECT [name], @CurrentDatabaseStatus
FROM sys.databases
WHERE [name] LIKE REPLACE(@CurrentDatabaseName,'_','[_]')
END
ELSE
BEGIN
INSERT INTO @Database03 (DatabaseName, DatabaseStatus)
SELECT [name], @CurrentDatabaseStatus
FROM sys.databases
WHERE [name] = @CurrentDatabaseName
END

UPDATE @Database02
SET Completed = 1
WHERE ID = @CurrentID

SET @CurrentID = NULL
SET @CurrentDatabaseName = NULL
SET @CurrentDatabaseStatus = NULL

END

---------------------------------------------------------------------------
--// Handle tempdb and database snapshots
---------------------------------------------------------------------------
INSERT INTO @Sysdatabases (DatabaseName)
SELECT [name]
FROM sys.databases
WHERE [name] <> 'tempdb'
AND source_database_id IS NULL

---------------------------------------------------------------------------
--// Return results
--------------------------------------------------------------------------

INSERT INTO @Database (DatabaseName)
SELECT DatabaseName
  FROM @Sysdatabases
  INTERSECT
  SELECT DatabaseName
  FROM @Database03
  WHERE DatabaseStatus = 1
  EXCEPT
  SELECT DatabaseName
  FROM @Database03
  WHERE DatabaseStatus = 0

  RETURN

  ---------------------------------------------------------------------------

END

GO

IMPLEMENTACION DE LA “ROI”

1)            Se crean los 4 objetos precedentes
2)          Se cambia el nombre de la base de datos almacenada en @databases por el nombre de la base de datos que queremos optimizar en sus idx

3)          Se crea un JOB que ejecute el sp “IndexOptimize” (recomiendo un Schedule de 1 vez por semana)

1 comentario: