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)
