Script Para Determinar Nivel de Fragmentación de Indices

Que tal amigos, una vez más con uds. esta vez para acercarles un script útil, para tener a mano a la hora de echar un vistazo a los niveles de fragmentación de nuestros idx.
Les recomiendo antes que nada leer en mi blog el white paper relacionado con el tema.

Hoy nada de teoría, simplemente dejarles el script aclarandoles lo siguiente:

- El script puede ser utilizado para medir la fragmetnación a nivel de Servidor/Base de Datos/Tablas (por default se los dejo "comentado (--)" para que lo puedan utilizar a nivel de Base de Datos

- En el where pueden filtrar el nivel de fragmentación que desean listar.

Quedo a disposición de uds. Saludos!

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

-- Detalle: Se listan lo objetos que se encuentran fragmentados

-- acorde a lo seteado en el wheare    

-- Detalle 2 (podemos hacer a nivel de Servidor/ Base de Datos / Tabla)                          

-- Autor: GH - Octubre 2013                                                                      

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

SELECT

db.name as 'Database',

o.name as 'Table',

case when I.name is null then 'Heap' else I.name end as 'Indice',

left(avg_fragmentation_in_percent,4) as '% frag'

--FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) AS a   

FROM sys.dm_db_index_physical_stats (DB_ID('NOMBRE BASE DE DATOS'), NULL, NULL, NULL, NULL) AS a  

--FROM sys.dm_db_index_physical_stats (DB_ID('NOMBRE BASE DE DATOS'), OBJECT_ID('NOMBRE DE TABLA'), NULL, NULL, NULL) AS a  

-- left para obtener nombre db --

LEFT JOIN Sys.Databases as DB    

on A.Database_Id = DB.Database_Id

-- left para obtener nombre del idx --

LEFT JOIN sys.indexes AS I

ON

a.object_id = I.object_id AND

a.index_id = I.index_id

-- left para obtener nombre tabla --

LEFT JOIN sys.objects as o

on I.[object_id] = o.[object_id]

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

WHERE avg_fragmentation_in_percent > 20 -- % de fragmentación en este caso > 20

and index_level = 0 --> analizando la rama principal de los índices

and page_count > 1000 --> mas de 1000 hojas

order by

o.name 

Compute Scalar - Qué es y Cómo Evitarlo

Amigos,

    Hoy les quiero hablar acerca de un operador llamado "Computer Scalar", presente en nuestros planes de ejecución frecuentemente.

   Como su nombre lo sugiere el operador indica la presencia de un cómputo escalar que devuelve un valor. También podemos hallar este  operador  dentro de nuestro plan de ejecución cuando realizamos conversiones explícitas o implícitas dentro de nuestas queries.
  
   Normalmente ignoramos al "Compute Scalar" ya que no representa un costo grande dentro del contexto de nuestro plan de ejecución  mas debemos tratar de tomar las medidas necesarias para evitarlo cuando realizamos operaciones de conversión o cálculos dentro de cursores grandes o de  loops de dimensiones considerables  Por qué? Porque pueden generar grandes cargas en nuestro server a nivel cpu, derivando esto en problemas de performance.   
   
   Les dejo pues un ejemplo de una operación que genera un "Computer Scalar" y abajo el cómo evitar el operador obteniendo grandes ganacias de performance con el mismo resultado final.

  1)  Operación que genera Computer Scalar:
 
  DECLARE @Count Int

 SET @Count = 0

 WHILE @Count < 100000000

 BEGIN

  IF EXISTS(SELECT Idcdr FROM Tabla WHERE Idcdr = @I)

     Begin

      --Accion

     End

  SET @Count = @Count + 1;

 END

  

  2)  Evitando el  Computer Scalar con el uso del @@ROWCOUNT en lugar del Exists

 DECLARE @Count Int

 SET @Count = 0

 WHILE @Count < 1000000

 BEGIN

   SELECT Idcdr FROM Tabla WHERE Id = @I

   IF @@ROWCOUNT > 0

   BEGIN

     -- Accion

   END

   SET @Count = @Count + 1;

 END

  Amigos "de yapa" y para que entiendan lo importante que es definir una variable corréctamente les dejo un ejemplo más que genera un Compute Scalar por "conversión implícita" y la solución como paso 2 del ejemplo:

 1)  Buscando el Máximo Idcdr (campo integer) dentro de una tabla produciendo un  Compute Scalar por mala definición de la variable @idini defnida como numeric (18,0) (cuando el campo idcdr de la tabla cdr_subscriptions es integer)

 -- Declaro variables --

Declare @idini numeric

-- Determino el Idcr maximo de la Tabla -

Select @idini =  coalesce (max(idcdr),0)

from cdr_subscriptions with (nolock)

 
** La mala definición de la variable genera una conversión implícita de numeric a integer lo que a su vez produce un "Compute Scalar"

  2)  Buscando el Máximo Idcdr (campo integer) dentro de una tabla eliminando el  Compute Scalar definiendo corréctamente la variable @idini  -esta vez como integer-

 -- Declaro variables --

Declare @idini integer

-- Determino el Idcr maximo de la Tabla -

Select @idini =  coalesce (max(idcdr),0)

from cdr_subscriptions with (nolock)

 

 Amigos, espero que lo expuesto haya sido de vuestra utilidad, les dejo mi saludo y como siempre estoy a vuestra disposición.

 Gustavo Herrera.

Espacio Ocupado Por Cada Una de Las Tablas En La Base de Datos

Amigos,

     Hoy quiero compartir un script muy útil que he preparado para uds.

     Cuántas veces notamos que nuestras bases de datos crecen a un rítmo importante sin saber exáctamente cuál o cuáles son las tablas "culpables" de ese crecimiento?

     Cuánta veces hemos pensado al ver nuestras tablas por consola "mmmm me parece que los índices ya representan mucho del espacio ocupado por la totalidad de la tabla..Debería analizar esto..."

      Pues bien, les traigo la solución...;  Un simple script que, haciendo uso de un sp del sistema llamado sp_spaceused,  nos permite listar para cada tabla de usuario de la base de datos en la que lo ejecutemos lo siguiente: (en orden descendente por la cantidad de registros de cada tabla):

    A - Epacio total ocupado por la tabla (MB)                                            

    B - Espacio en la tabla utilizado por datos (MB)                                       

    C - % del espacio utilizado por datos                                                  

    D - Espacio en la tabla utilizado por índices (MB)                                     

    E - % del espacio utilizado por indices                                                -- 

    D - Cantidad total de registros de las tabla

     Solo les queda probarlo... si tienen dudas... como siempre estoy a vuestra disposición...

BEGIN

-- Declaro Variable

Declare @object_name as varchar(50)

-- Declaro Tabla Temporal

CREATE TABLE #results

  (name varchar(50),

   filas integer,

   reserved varchar(50),

   data varchar(50),

   index_size varchar(50),

   Unused varchar(50))

/* Cursor_Tablas -Guarda en la tabla #result

 el resultado de la ejecución del sp_spaceused

sobre c/u de las tablas de usuario de la BD */

DECLARE Cursor_Tablas

Cursor For

Select distinct(s.name + '.' + o.name)

      from sys.schemas s

INNER JOIN sys.objects o  

ON o.schema_id = s.schema_id

Where

      type = 'U'  --Tablas de usuario

OPEN Cursor_Tablas

Fetch Next From  Cursor_Tablas

Into @object_name

WHILE @@FETCH_STATUS = 0

 BEGIN

   Insert Into #results

   EXEC sp_spaceused @object_name

   Fetch Next From cursor_tablas

   Into @object_name

 END;

Close Cursor_Tablas;

Deallocate Cursor_Tablas;

-- Se quita el "KB" de la tabla #result --

UPDATE

#results

SET

reserved = LEFT(reserved,LEN(reserved)-3),

data = LEFT(data,LEN(data)-3),

index_size = LEFT(index_size,LEN(index_size)-3),

unused = LEFT(unused,LEN(unused)-3)

-- Se listan las tablas ordenadas descendentemente por la cantidad de registros --

SELECT

distinct(t.Name) AS 'Table',

reserved/1024 as '[Disco (MB)]',

data/1024 as '[Datos (MB)]',

case when (data/1024) = 0 then 0.00 else (((data/1024)*100)/(reserved/1024)) end as '[% Datos (MB)]',

index_size/1024 as '[Idx (MB)]',

case when (data/1024) = 0 then 0.00 else 100-(((data/1024)*100)/(reserved/1024)) end as '[% Index (MB)]',

filas AS  'Records'

FROM #results as t

Inner Join sys.objects o

ON o.name =  t.name

Where

reserved is not null and

o.Type <> 'S' AND 

O.Type <> 'IT'     

order by

filas desc

--Eliminar la tabla temporal

Drop Table #results

Analizar Utilización de Indices - Depurar Indices no Utilizados

Amigos, 

    En más e una oportunidad hemos caído en la cuenta de que nuestros índices representan un valor elevado del espacio en mb que está utilizando una tabla. Este es un signo de que "algo está mal" y que, por consiguiente, es necesario revalidar  los índices creados historicamente sobre la tabla para poder establecer su utilización verdadera y de ese modo llevar adelante una eventual depuración.

  Recordemos como concepto general que los índices bien utlizados aceleran los tiempos de respuesta de una consulta  reduciendo los tiempos de  bloqueo sobre las tablas en las cuales se establecen la peticiónes. Por contrapartida una tabla excesivamente indexada genera una notable pérdida de performance en los procesos de Insert / Update / Delete ya que el motor de nuestra base de datos, dependiendo de la magnitud de estas operaciones, se verá forzado a rehacer los idx para mantenerlos actualizados, generando  una sobrecarga innecesaria en nuestro servidor y tiempos de actualización de data mucho más perezosos.

  Una vez más la gente de Microsoft pensó en nosotros, los sacrificados DBA, y puso a nuestra diposición una vista dinámica que, joineada con otras vistas del sistema, nos proporciona la data necesaria como para realizar esta tarea de análisis.

  Estoy hablando de la vista sys.dm_db_index_usage_stats..
   
  Esta vista acumula, desde el último reinicio del motor de nuestra base de datos, un contador para cada uno de los índices, y para los siguientes eventos, (relacionados diréctamente con la utilización de los indices como producto de la ejecucion de las queries por nosotros definidas ) a saber:

       user_seeks --  cantidad de veces que se utilizó el índice en búsquedas directas
     user_scans -- cantidad de veces que  scanearon en base al idx
       user_lookups -- cantidad de lookups 

   Estos tres eventos nos hablan de la utilización efectiva que han tenidos nuestros índices. 

   Pero para que nuestro estudio sea realmente efectivo, Microsoft nos propociona un cuarto campo vital para determinar el "costo/beneficio" de tener un idx activo en nuestra base de datos hablo del contador:

     user_updates -- este contador nos habla de la cantidad de veces que el sql server tuvo que hacer una tarea de mantenimiento sobre el índice (que puede ser la recontrucción total o parcial del mismo), como consecuencia de operaciones de Insert - Update - Delete.


    Ahora bien, tenemos estos datos valiosísimos... Cómo establecer si un índice justifica su existencia?

     A - Si el idx tiene valores elevados en cualquier de los 3 primeros campos detallados no debería ser deprecado

     B -  Si el idx no presenta valores significativos de utilización y a la vez  requiere de frecuentes tareas de mantenimiento por parte del sistema (contador user_updates con cifras elevadas), debería de eliminado.
  
   C - Si el idx presenta valores intermedios de utilización y mantenimiento, pues nada mejor que apelar al conocimiento que uno tiene sobre el sistema y la base de datos para de ese modo determinar la acción a seguir. Es decir, si vale la pena o no mentener el idx.


 Ahora les dejo la query que armé en base a la vista, linkeando la misma con otras vistas del sistema, para que puedan ya mismo empezar a revalidar sus indices.

  
------------------------------------------------------------------------------------------
-- Detalle:  Estudiar la utilización de los idx de una base de datos           
-- Vista base: sys.dm_db_index_usage_stats                                        - Autor: Gustavo Herrera para "SQL Server Para Todos"                   --------------------------------------------------------------------------------------------

Use NombreBaseDatos
SELECT 
DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName,
si.name AS IndexName,
sc.Name AS ColumnName,
sis.user_seeks,  
sis.user_scans,  
sis.user_lookups, 
sis.user_updates 
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si  on sis.object_id = si.object_id and sis.index_id = si.Index_id
INNER JOIN sys.index_columns sic on sis.object_id = sic.object_id and sic.Index_id = si.Index_id
INNER JOIN sys.columns sc on sis.object_id = sc.object_id and sic.Column_id = sc.Column_id
INNER JOIN  sys.objects o on si.object_id = o.object_id
WHERE sis.database_id = DB_ID('NombreBaseDatos') and o.type = 'U' ;
go

       Quedo a disposición de uds, hasta la próxima !

Borrar Distribution Database

Amigos,

   Esta vez les traigo un script muy chiquito pero eficiente.

   Si están trabajando en ambiente de test con Replication seguramente se han encontrado con el problema de no poder borrar manualmente la base de datos del sistema llamada Distribution, una vez que finalizaron las pruebas.

   Pues bien, a no enloquecer, aquí les traigo la solución:

    use master
    go
    alter database distribution set offline;
    drop database distribution;

 
   Sencillo verdad?

   Pues eso es todo por hoy, saludos a su disposición.

   Gustavo Herrera para Sql Server Para Todos.

Mantener Particiones de Tablas (Split y Merge de Particiones)

Amigos,

Hemos aprendido la importancia de particionar tablas, y también bajo que condiciones es conveniente optar por hacerlo...
.
Hemos dado 5 pasos para la partición de una tabla http://gherrerasqlserver.blogspot.com.ar/2013/05/particionar-tablas-en-5-pasos.html

Pues bien, siguiendo con el mismo ejemplo hemos de entender cómo mantener la partición creada.

Recordemos que en nuestro ejemplo habíamos particionado la tabla llamada "VENTAS".


 [dbo].[ventas]([id] [int] NULL,
[fecha] [datetime] NULL, ---> idx_fecha (clustered)
[idproducto] [int] NULL,
[cantidad] [int] NULL)


Esta tabla contenía 20 millones de registros, lo cuales fueron reasignados a 4 data files distintos acorde al siguiente Partition Function:


 [PF_Ventas](datetime)
As Range Left For Values
('2011-06-30 23:59:59', '2011-12-31 23:59:59','2012-06-30 23:59:59' )


Y que habíamos mapeado los registros a los filegroups creados acorde al siguiente Partition Scheme:


[PS_Ventas] As Partition [PF_Ventas]
To ([Fg_Ventas_20011A], [Fg_Ventas_20011B] , [Fg_Ventas_20012A], [Fg_Ventas_20012B] )



Pues bien amigos, la pregunta es...

Qué pasa cuando comienzo el primer semestre del año 2013?, Dónde serán sincronizados los registros?

Si no hacemos un trabajo de mantenimiento de la partición, todos los registros del primer semestre del 2013 serán asignados al Filegroup [Fg_Ventas_20012B] , por exceder el último límite de nuestro Partition Function (2012-06-30 23:59:59' ).

Esto es muy malo, pues el datafile contenido en el filegroup [Fg_Ventas_2012B] comenzará a crecer desbalanceando nuestra tabla particionada y, por consiguiente, provocando lentos pero progresivos problemas de performance a medida que vaya creciendo el archivo mencionado.

Vamos entonces a tomar cartas en el asunto. (recomiendo, como siempre, hacer este tipo de trabajos con la Base de Datos en modo "Single User"):


Primer Paso : Agregar Un Nuevo Filegroup Para Contener los Datos el File Con los Datos del Primer Semestre del 2013
Alter Database Prueba
Add FileGroup [Fg_Ventas_20013A]



Segundo Paso : Agregar Files Para  los Filegroups Creados
Alter Database Prueba
Add File
(Name = 'ventas_2013A',
 Filename = 'H:\Data\ventas2013A.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup [Fg_Ventas_20013A]


Tercer Paso : Moficar El Partition Scheme Para Mapear al Mismo el Nuevo FileGroup Creado en el primer paso
Alter Partition Scheme [PS_Ventas]

Next Used [Fg_Ventas_20013A]


Cuarto Paso : Moficar El Partition Function Agregando un Nuevo Limite 

Alter Partition Function   [PF_Ventas]
Split Range ('2012-12-31 23:59:59')

(**)  En este cuarto paso los datos del 2013 son reasignados a la partición nueva. Esto hará crecer nuestro archivo de log y demorará unos minutos (proporcionales a la cantidad de registros del año 2013 que ya hubiese ingresado a nuestra tabla)

LISTO! Nuestra Tabla Particionada ya Está Preparada Para Recibir los Datos del 2013 !!!





Nota:  es recomendable que no nos dejemos "llegar el agua al cuello" y , siguiendo con este ejemplo, hagamos este trabajo de mantenimiento de nuestra tabla particionada, antes de la finalización del año 2012.


Esto sería todo amigos... pero... Qué pasa si borramos todos los datos correspondientes al primer semestre del año 2011 (ya que de contaduría nos indican que ya no es necesario tenerlos en línea)?

La respuesta sería..." Mmm pues bien, he borrado los datos, y ahora me sobra una partición (la del primer semestre del 2011)."

Por suerte Microsoft pensó en nosotros y nos dió la oportunidad de hacer algo llamado "Merge" para tal fin, Se implementa el Merge siguiendo estos pasos: (suponiendo que ya hemos borrado de la tabla Ventas todos los registros del primer semestre del 2011)

Primer Paso : Se hace un Merge de la Partición más Vieja con La Partición Inmediatamente Superior  (deja de existir la partición 2011A, la cual es absorbida por la 2011B)

Alter Partition Function  PF_Ventas()
Merge Range ('2011-06-30 23:59:59')


Segundo  Paso : Ya podemos borrar el File y el FileGroup en Desuso...


Alter Database [Prueba]  Remove File [Ventas_2011A]
Alter Database [Prueba]  Remove FileGroup  [Fg_Ventas_20011A]





Y bien amigos, ahora si hemos llegado al fin de este capítulo.

Espero haber sido claro y quedo a vuestra entera disposición.

Un placer tenerlos del otro lado :)

ss Gustavo Herrera.

Otro Artículo Recomendado por el Autor

"Mantenimiento de Estadíasticas Para Una Performance Optima de Nuestra BD"

https://www.blogger.com/blogger.g?blogID=4841087034568585749#editor/target=post;postID=5281491474180357583;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=postname

PARTICIONAR TABLAS EN 5 PASOS

Amigos,

Hoy quiero hablarles de un tema vigente desde el SQL Server 2005, especialmente útil cuando necesitamos mejorar los tiempos de respuesta en tablas de varios millones de registros en ambientes de reporting productivos.

Hasta el SQL Server 2000 no existía la posibilidad de particionar tablas. Toda la info de una tabla debía recaer en un mismo datafile provocando esto enormes problemas a la hora de consultar o insertar información en la tabla cuya manipulación, (por su tamaño), se hacía casi imposible.

A partir del SQL Server 2005 Microsoft introdujo el concepto de "Partición de Tablas", lo cual nos permite "dividir" nuestras tablas en porciones, las cuales serán ubicadas en varios archivos (tantos como determinemos) y alocados en más de una unidad de disco.

De este modo, podremos sacarle el máximo provecho a una de las grandes ventajas que tiene el motor de base de datos de Microsoft, la posibilidad de realizar lecto escrituras paralelas (con toda la ganancia de tiempo y performance que ello signifca).

El particionar una tabla no es difícil y es especialmente útil cuando ud dispone de una tabla de > de 15 millones de registros y al menos más de una unidad de disco disponible para alocar las futuras particiones.

Para ello debemos seguir los siguientes 5  pasos (vamos a la práctica).

Supongamos que tenemos una tabla llamada ventas la cual tiene 20 millones de registros correspondientes a los años 2011 y 2012, con la siguiente estructura:


CREATE TABLE [dbo].[ventas](
[id] [int] NULL,
[fecha] [datetime] NULL, ---> idx_fecha (clustered)
[idproducto] [int] NULL,
[cantidad] [int] NULL)


Pues bien, llegó el momento de determinar un criterio de partición. Yo creo que en este caso, por la característica de la data y la cantidad de registros, sería conveniente generar 4 datafiles de 5 millones de registros aprox. cada uno. Es decir,  generar cuatro archivos con un semestre cada uno (de los dos años que hoy contiene la tabla)

Primer Paso : Agregar Filegroups Para Contener los Files Semestrales


Alter Database Prueba
Add FileGroup [Fg_Ventas_20011A]
Alter Database Prueba
Add FileGroup [Fg_Ventas_20011B]
Alter Database Prueba
Add FileGroup [Fg_Ventas_20012A]
Alter Database Prueba
Add FileGroup [Fg_Ventas_20012B]

Segundo Paso : Agregar Files Para  los Filegroups Creados


Alter Database Prueba
Add File
(Name = 'ventas_2011A',
 Filename = 'D:\Data\ventas2011A.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup Fg_Ventas_20011A]


Alter Database Prueba
Add File
(Name = 'ventas_2011B,
 Filename = 'E:\Data\ventas2011B.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup Fg_Ventas_20011B]


Alter Database Prueba
Add File
(Name = 'ventas_2012A',
 Filename = 'F:\Data\ventas2012A.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup Fg_Ventas_20012A]




Alter Database Prueba
Add File
(Name = 'ventas_2012B',
 Filename = 'G:\Data\ventas2012B.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup Fg_Ventas_20012B]


Tercer Paso :  Generar una Partition Function (la cual determinará los rangos a partir de los cuales particionaremos la tabla, - en nuestro caso por fecha -)


Create Partition Function [PF_Ventas](datetime)
As Range Right For Values
 ('2011-06-30 23:59:59', '2011-12-31 23:59:59','2012-06-30 23:59:59' )


Cuarto Paso :  Generar una Partition Scheme (el cual mapeará las particiones a los filegroups creados en el primer paso)


Create Partition Scheme [PS_Ventas] As Partition [PF_Ventas]
To ([Fg_Ventas_20011A], [Fg_Ventas_20011B] , [Fg_Ventas_20012A], [Fg_Ventas_20012B] )


Quinto Paso:  Distribuir Físicamente los Registros de la Tabla Ventas en los Files Creados Mediante el Dropeado y la Creación de Indice Cluster Sobre el Partiton Scheme

5.1    Drop Index [ix_fecha] On [dbo].[ventas]
         With( Online = Off )


5.2    Create Clustered Index [ix_fecha] On [dbo].[Ventas] (fecha)
          on [PS_Ventas] (fecha)

Listo! Ya tenemos nuestra tabla particionada y con una mejora en la performance que los invito a probarla

(*) IMPORTANTE: Si nuestra tabla tenía índices nonclustered adicionales, los mismos deben ser recreados sobre la partición, tal cual lo hecho en el paso 5, para lograr un óptima performance.

Les dejo por último un script hecho en base a metadata que nos provee el SQL Server que tiene como objetivo el verificar que la particiones creadas hayan sido efectivamente bien "pobladas" de datos, acorde a los trabajos realizados.

SELECT 

t.name AS TableName, 

i.name AS field, 

p.partition_number,

r.value AS BoundaryValue ,

rows

From

Sys.Tables AS t 

Join Sys.Indexes AS i

On t.object_id = i.object_id

Join sys.partitions AS p

On i.object_id = p.object_id And

       i.index_id = p.index_id 

Join  sys.partition_schemes AS s 

On i.data_space_id = s.data_space_id

 Join sys.partition_functions AS f 

ON s.function_id = f.function_id

Left Joing sys.partition_range_values AS r 

On  f.function_id = r.function_id and 

      r.boundary_id = p.partition_number

Where

t.name = 'Ventas' and

i.type <= 1

Order By p.partition_number;

(**) En un próximo post abordaré la manutención de las particiones, algo que no es problemático, pero que si requiere de un trabajo que debe ser tenido en cuenta a la hora de determinar si vale la pena o no particionar una tabla (costo/beneficio). 

Eso es todo amigos, como siempre estoy para ayudarles. No dejen de escribirme que siempre estoy dispuesto a darles una mano.

GUSTAVO HERRERA para "SQL SERVER PARA TODOS"

Otro Post recomendado por el Autor

Mantenimiento de Estadísticas Para una Performance Optima de Nuestra BD:

https://www.blogger.com/blogger.g?blogID=4841087034568585749#editor/target=post;postID=5281491474180357583;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=postname

Mover Archivo TempDb

Amigos,

Muchas veces nos encontramos ante la necesidad de mover, de relocalizar, el archivo MDF correspondiente a la TempDb, o su archivo de Log.

Como todos sabemos el tempdb crece en tamaño ante operaciones que requieren mucho uso de memoria en general.

Pues bien, dispuestos entonces a "mover" los archivos de la TempDb hacia un disco con mayor capacidad, seguramente intentaremos dettachar la base para luego mover el archivo y volver a attachar. Wrong. No es posible dettachar una base del sistema.

Cuál es entonces la forma correcta de hacerlo?, aquí les dejo los pasos a saber:

1) Utilizar una nueva query en el Management Estudio y valiéndose de un Alter Database reubicar el archivo.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '{nueva ruta}\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '{nueva ruta}\templog.ldf');
GO

(*) Donde dice "nueva ruta", escribir la nueva ruta de los archivos.


2) Una vez que han ejecutado lo escrito en el punto 1), simplemente reinicien el servicio del sql server y listo, la TempDb se habrá "relocalizado" y sus problemas de espacio en disco habrán encontrado una solución. (no se olviden de borrar el viejo archivo tempdb)

3) Vamos a comprobar que nuestra nueva localización sea la deseada escribiendo y ejecutando lo sigte:

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');


Amigos, sencillo pero útil. Como siempre quedo a disposición de uds, saludos.


Otro post recomendado por el autor

"Mantenimiento de Estadísticas Para Una Performance Optima de Nuestra BD"
https://www.blogger.com/blogger.g?blogID=4841087034568585749#editor/target=post;postID=5281491474180357583;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=postname

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)