Optimización de Índices – Desfragmentación

Los índices en una base de datos con el tiempo tienden a fragmentarse, esto contribuye a disminuir el rendimiento de la base de datos. En SQL server tiene una opción para conocer la fragmentación de los índices de la base de datos

A continuación muestro una consulta  que lista las tablas del sistema, la cantidad de registros por tabla, sus índices y porcentaje de fragmentación. La consulta se ordena por orden de registros descendente.

--Consulta para extraer tablas y registros

Select Tablas.Tabla

       ,Tablas.Registros

       ,Indices.Indice

       ,Indices.Fragmentacion

       ,Round((Registros / 1000000),2) * 100 As Orden

From

(select Distinct SCS.name + '.' + SO.Name as Tabla

      ,si.rows as 'Registros'

from sysobjects as SO

    inner join sysindexes as SI on SO.Id = SI.id

    inner join sysfilegroups as SFG on SI.GroupId = SFG.GroupId

    inner join sys.objects SOS On SO.id=SOS.object_id

    inner join sys.schemas SCS On SOS.schema_id = SCS.schema_id) As Tablas

    Inner Join (SELECT d.name + '.' + c.name as Tabla

                      ,b.name As Indice

                      ,a.avg_fragmentation_in_percent 'Fragmentacion'

                FROM sys.dm_db_index_physical_stats (DB_ID(N'Cuentame'), NULL, NULL, NULL, NULL) AS a

                     JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

                     Join sys.objects AS c on b.object_id = C.object_id

                     Join sys.schemas AS d ON c.schema_id = d.schema_id

WHERE b.name is Not NUll) As indices

      On Tablas.Tabla=indices.Tabla

Order By Registros Desc

Una vez ejecutado el listado, puedes seleccionar la tabla e índice que veas más fragmentado y reorganizar o reconstruir mediante la siguiente sentencia.

ALTER INDEX [NombreIndice] on [Esquema].[Tabla] REORGANIZE

O

ALTER INDEX [NombreIndice] on [Esquema].[Tabla] REBUILD

SQL Server es marca registrada de Microsoft.

Trucos y Consejos para optimización de consultas y mejorar los tiempos (Rendimiento)

Desde hace muchos años trabajo en desarrollo de software y en el transcurso de ese tiempo he venido adquiriendo algunas costumbres a la hora de escribir las sentencias  SQL, algunas óptimas y otras no tan ortodoxas, a continuación se las pongo a disposición. Les recuerdo que esto es bajo su propia responsabilidad.

Aquí encontrarás las diferentes formas de hacer consultas con parámetros  dinámicos y la manera de optimizar y mejorar los tiempos de consulta.

1.       La primera parte es la optimización de los campos durante el diseño de la tabla. Utiliza el tipo de campo necesario para almacenar el valor. Por ejemplo si necesito almacenar una fecha “20/11/2014”, utilizo un campo Date en vez del DateTime. Si el campo va a almacenar un texto utiliza un NVarChar(n) con un “n” que corresponda a un valor ligeramente mayor al tamaño máximo esperado, procuró nunca usar el NVarChar(Max).

2.       Trato  de tener cuidado con la creación de los índices, generalmente al inicio del sistema los coloco solo en los campos necesarios, como costumbre tengo el usar identificadores únicos de registro como llave de los datos mediante el uso de “IDENTITY”, esto hace necesario la creación de un índice adicional único por la llave de los datos. Por ejemplo, si estamos hablando de cuentas de un banco la llave natural de los datos sería el tipo y el número de la cuenta, si fuesen personas la clave natural sería el tipo y el número del documento. Para mí los índices dependen más del tipo de sistema, si es para registrar información más que para consultarla, el uso de índices debe ser mínimo.

3.       La lectura (interpretación) de un código de consulta es más sencillo si uno sigue ciertos parámetros, por ejemplo: Al momento de hacer una sentencia SQL, ubico un solo campo por línea, esto facilita la lectura de la sentencia:

Así no: Select dCliente, NombreCliente, TipoIdentificacion, Identificacion ,Direccion, Correo, EsActivo, CreadoPor, FecCreacion, ModificadoPor, FecModificacion From Cliente

Es mejor así:

Select IdCliente

      ,NombreCliente

      ,TipoIdentificacion

      ,Identificacion

      ,Direccion

      ,Correo

      ,EsActivo

      ,CreadoPor

      ,FecCreacion

      ,ModificadoPor

      ,FecModificacion

From Cliente

4.       A diferencia de los ejemplos que encuentro en internet No escribo las comas al final del campo, para mi es mejor escribirlas al comienzo, esto facilita  la actualización, cuando necesito quitar un campo, basta con agregar el carácter de comentario “--”al inicio del campo.

Select IdCliente

      ,NombreCliente

      ,TipoIdentificacion

      ,Identificacion

      ,Direccion

      ,Correo

      ,EsActivo

      --,CreadoPor

      --,FecCreacion

      --,ModificadoPor

      --,FecModificacion

From Cliente

5.       Cuando inicio la elaboración de una consulta utilizo el asterisco “*”, que me trae todos los campos, posteriormente lo sustituyo por los nombres de los campos necesarios, nunca lo dejo al final, esto impactaría demasiado en el rendimiento de la BD.

Select * From Cliente --Esto no se debe hacer

Es más optimo así:

Select IdCliente

      ,NombreCliente

      ,TipoIdentificacion

      ,Identificacion

      ,Direccion

      ,Correo

      ,EsActivo

      ,CreadoPor

      ,FecCreacion

      ,ModificadoPor

      ,FecModificacion

From Cliente

6.       Utilizo  alias para los nombres de las tablas, es más rápido para escribir y si utiliza un editor de sentencias con “IntelliSense”. Es más fácil encontrar los campos.

Select CL.IdCliente

      ,CL.NombreCliente

      ,CL.TipoIdentificacion

      ,CL.Identificacion

      ,CL.Direccion

      ,CL.Correo

      ,CL.EsActivo

      ,CL.CreadoPor

      ,CL.FecCreacion

      ,CL.ModificadoPor

      ,CL.FecModificacion

From Cliente CL

7.       Cuando comienzo a escribir las validaciones de un Where, siempre comienzo con la línea 1=1, su resultado es siembre True. Esto facilita el agregar nuevas líneas y la depuración de los comandos. Si en el momento de estar elaborando un Select complejo, necesitas quitar alguno de los parámetros es más fácil comentar la línea del parámetro a quitar.

Select CL.IdCliente

      ,CL.NombreCliente

      ,CL.TipoIdentificacion

      ,CL.Identificacion

      ,CL.Direccion

      ,CL.Correo

      ,CL.EsActivo

      ,CL.CreadoPor

      ,CL.FecCreacion

      ,CL.ModificadoPor

      ,CL.FecModificacion

From Cliente CL

Where 1=1

      --And CL.EsActivo = 1

      And CL.CreadoPor = 'Administrador'

8.       Hace algún tiempo necesitaba leer la estructura de una tabla sin la necesidad de traer datos, para esto comencé a utilizar la sentencia: Select * From DigitalTP.Cliente Where IdCliente=-1, Esto permite tener la estructura de columnas y sus tipos de datos, pero hacia un pique al índice de la llave (consulta al índice), encontré que es más eficiente usar: Select * From DigitalTP.Cliente Where 1=2

9.       Cuando empiezo la elaboración de un procedimiento almacenado, declaro los parámetros y le asigno valores predeterminados mientras elaboro la sentencia SQL. Si la consulta es a una tabla con demasiados registros, al ejecutarla las primeras veces esta puede demorarse, para evitar esto  le escribo un TOP al principio de la consulta. Una vez finalizado el procedimiento, quito los valores predeterminados y el TOP.  Para el momento de la implementación coloco al inicio del Script una validación para saber si el procedimiento ya existe, eliminarlo y volver a crearlo, esto facilita las actualizaciones a los servidores de producción.

Mientras elaboro y evaluó la consulta:

Declare @CreadoPor NVarchar(50) = 'Administrador'

Declare @EsActivo Int = 1

Select Top 1000 CL.IdCliente

      ,CL.NombreCliente

      ,CL.TipoIdentificacion

      ,CL.Identificacion

      ,CL.Direccion

      ,CL.Correo

      ,CL.EsActivo

      ,CL.CreadoPor

      ,CL.FecCreacion

      ,CL.ModificadoPor

      ,CL.FecModificacion

From Cliente CL

Where 1=1

      And CL.EsActivo = @EsActivo

   And CL.CreadoPor = @CreadoPor

Cuando ya tengo listo el procedimiento para enviar a producción.

If (select Count(*) from sysobjects where type='P' and name='[uspCliente_Consultar')>0

BEGIN

      DROP PROCEDURE dbo.[uspCliente_Consultar];

END

GO

CREATE PROCEDURE [dbo].[uspCliente_Consultar] 

                         @CreadoPor NVarchar(50)

                        ,@EsActivo Int

AS 

BEGIN 

Select CL.IdCliente

      ,CL.NombreCliente

      ,CL.TipoIdentificacion

      ,CL.Identificacion

      ,CL.Direccion

      ,CL.Correo

      ,CL.EsActivo

      ,CL.CreadoPor

      ,CL.FecCreacion

      ,CL.ModificadoPor

      ,CL.FecModificacion

From Cliente CL

Where 1=1

      And CL.EsActivo = @EsActivo

      And CL.CreadoPor = @CreadoPor

End

10.   Cuando la base de datos es muy grande, en ocasiones  elaboro procedimientos almacenados genéricos para algunas consultas, los cuales pueden responder a múltiples necesidades, estas reciben múltiples parámetros y filtran los datos por los parámetros que traen valores, lo anterior hace que el uso de los parámetros del Where  sea más delicado. Para esto hay varias maneras de configurar las consultas, aquí describiré a continuación las que conozco:

Una manera rápida, es adicionar una validación al parámetro de tal manera que si viene con un valor nulo no se tenga en cuenta para la búsqueda de registros, Esto es rápido de hacer aunque degrada el desempeño de la base de datos, su uso debiese restringir a tablas de poco tamaño Ejemplo:

Where 1=1

      And (@EsActivo Is Null Or CL.EsActivo = @EsActivo)

   And (@CreadoPor Is Null Or CL.CreadoPor = @CreadoPor)

Otra manera es mediante el uso de la instrucción  COALESCE

Where 1=1

      And CL.EsActivo=COALESCE(@EsActivo, EsActivo)

      And CL.CreadoPor=COALESCE(@CreadoPor, CL.CreadoPor)

11.   Existe otra manera que consiste en la elaboración de una consulta con las sentencias del Where  dinámicas, que filtren la información de los parámetros que traen valores y ejecutar la consulta mediante el comando Exec. Hacer la consulta así, es rápido, pero evita que el motor de la BD pueda armar planes de ejecución y abre una puerta a inyecciones de SQL.

Declare @IdCliente int=Null

Declare @FK_TipoIdentificacion int=Null

Declare @Identificacion nvarchar(128)=Null

Declare @NombreCliente nvarchar(256)='Gustavo Molina'

Declare @Contacto nvarchar(128)=Null

Declare @CargoContacto nvarchar(128)=Null

Declare @Ciudad nvarchar(50)=Null

Declare @Direccion nvarchar(256)=Null

Declare @Correo nvarchar(256)=Null

Declare @Movil nvarchar(128)=Null

Declare @Telefono nvarchar(128)=Null

Declare @EsActivo int=Null

Declare @CreadoPor nvarchar(50)=Null

Declare @FecCreacion date=Null

Declare @ModificadoPor nvarchar(50)=Null

Declare @FecModificacion date=Null

Declare @Comilla NVarchar(1) = Char(39)

Declare @SqlExec as NVarchar(Max)

       Set @SqlExec = '

       Select   CL.IdCliente

                    ,CL.FK_TipoIdentificacion

                    ,CL.Identificacion

                    ,CL.NombreCliente

                    ,CL.Contacto

                    ,CL.CargoContacto

                    ,CL.Ciudad

                    ,CL.Direccion

                    ,CL.Correo

                    ,CL.Movil

                    ,CL.Telefono

                    ,CL.EsActivo

                    ,CL.CreadoPor

                    ,CL.FecCreacion

                    ,CL.ModificadoPor

                    ,CL.FecModificacion

       From DigitalTP.Cliente CL

       Where 1=1 '

       If(@IdCliente Is Not Null)

             Set @SqlExec = @SqlExec + ' And IdCliente = @IdCliente'

       If(@FK_TipoIdentificacion Is Not Null)

             Set @SqlExec = @SqlExec + ' And FK_TipoIdentificacion = @FK_TipoIdentificacion'

       If(@Identificacion Is Not Null)

             Set @SqlExec = @SqlExec + ' And Identificacion = ' + @Comilla + @Identificacion + @Comilla

       If(@NombreCliente Is Not Null)

             Set @SqlExec = @SqlExec + ' And NombreCliente = ' + @Comilla + @NombreCliente + @Comilla

       If(@Contacto Is Not Null)

             Set @SqlExec = @SqlExec + ' And Contacto = ' + @Comilla + @Contacto + @Comilla

       If(@CargoContacto Is Not Null)

             Set @SqlExec = @SqlExec + ' And CargoContacto = ' + @Comilla + @CargoContacto + @Comilla

       If(@Ciudad Is Not Null)

             Set @SqlExec = @SqlExec + ' And Ciudad = ' + @Comilla + @Ciudad + @Comilla

       If(@Direccion Is Not Null)

             Set @SqlExec = @SqlExec + ' And Direccion = ' + @Comilla + @Direccion + @Comilla

       If(@Correo Is Not Null)

             Set @SqlExec = @SqlExec + ' And Correo = ' + @Comilla + @Correo + @Comilla

       If(@Movil Is Not Null)

             Set @SqlExec = @SqlExec + ' And Movil =' + @Comilla + @Movil + @Comilla

       If(@Telefono Is Not Null)

             Set @SqlExec = @SqlExec + ' And Telefono = ' + @Comilla + @Telefono + @Comilla

       If(@EsActivo Is Not Null)

             Set @SqlExec = @SqlExec + ' And EsActivo = ' + @Comilla + @EsActivo + @Comilla

       If(@CreadoPor Is Not Null)

             Set @SqlExec = @SqlExec + ' And CreadoPor = ' + @Comilla + @CreadoPor + @Comilla

       If(@FecCreacion Is Not Null)

             Set @SqlExec = @SqlExec + ' And FecCreacion = ' + @Comilla + Cast(@FecCreacion as nVarchar) + @Comilla

       If(@ModificadoPor Is Not Null)

             Set @SqlExec = @SqlExec + ' And ModificadoPor ' + @Comilla + @ModificadoPor + @Comilla

       If(@FecModificacion Is Not Null)

             Set @SqlExec = @SqlExec + ' And FecModificacion ='+ @Comilla + Cast(@FecModificacion as NVarchar) + @Comilla

       Exec (@SqlExec)

12.   La última que conozco y tal vez la más versátil es la de utilizar el comando “Exec sp_executesql”, este comando permite la ejecución de un SELECT dinámico que acepta parámetros y adicionalmente arma planes de ejecución.   A diferencia del Exec, evita las inyecciones de SQL y es más óptimo para el motor de la BD. A continuación les dejo un ejemplo con uso de parámetros múltiples.

CREATE PROCEDURE [dbo].[uspCliente_Consultar] 

                     @IdCliente int=Null

                    ,@FK_TipoIdentificacion int=Null

                    ,@Identificacion nvarchar(128)=Null

                    ,@NombreCliente nvarchar(256)=Null

                    ,@Contacto nvarchar(128)=Null

                    ,@CargoContacto nvarchar(128)=Null

                    ,@Ciudad nvarchar(50)=Null

                    ,@Direccion nvarchar(256)=Null

                    ,@Correo nvarchar(256)=Null

                    ,@Movil nvarchar(128)=Null

                    ,@Telefono nvarchar(128)=Null

                    ,@EsActivo int=Null

                    ,@CreadoPor nvarchar(50)=Null

                    ,@FecCreacion date=Null

                    ,@ModificadoPor nvarchar(50)=Null

                    ,@FecModificacion date=Null

AS 

BEGIN 

       Declare @SqlExec as NVarchar(Max)

       Declare @SqlParametros as NVarchar(3000)=','

Set @SqlParametros = '@IdCliente int,@FK_TipoIdentificacion int,@Identificacion nvarchar(128),@NombreCliente nvarchar(256),@Contacto nvarchar(128),@CargoContacto nvarchar(128),@Ciudad nvarchar(50),@Direccion nvarchar(256),@Correo nvarchar(256),@Movil nvarchar(128),@Telefono nvarchar(128),@EsActivo int,@CreadoPor nvarchar(50),@FecCreacion date,@ModificadoPor nvarchar(50),@FecModificacion date'

       Set @SqlExec = '

       Select   CL.IdCliente

                    ,CL.FK_TipoIdentificacion

                    ,CL.Identificacion

                    ,CL.NombreCliente

                    ,CL.Contacto

                    ,CL.CargoContacto

                    ,CL.Ciudad

                    ,CL.Direccion

                    ,CL.Correo

                    ,CL.Movil

                    ,CL.Telefono

                    ,CL.EsActivo

                    ,CL.CreadoPor

                    ,CL.FecCreacion

                    ,CL.ModificadoPor

                    ,CL.FecModificacion

       From DigitalTP.Cliente CL

       Where 1=1 '

If(@IdCliente Is Not Null)

   Set @SqlExec = @SqlExec + ' And IdCliente = @IdCliente'

If(@FK_TipoIdentificacion Is Not Null)

   Set @SqlExec=@SqlExec + ' And FK_TipoIdentificacion=FK_TipoIdentificacion'

If(@Identificacion Is Not Null)

   Set @SqlExec = @SqlExec + ' And Identificacion = @Identificacion'

If(@NombreCliente Is Not Null)

   Set @SqlExec = @SqlExec + ' And NombreCliente = @NombreCliente'

If(@Contacto Is Not Null)

   Set @SqlExec = @SqlExec + ' And Contacto = @Contacto'

If(@CargoContacto Is Not Null)

   Set @SqlExec = @SqlExec + ' And CargoContacto = @CargoContacto'

If(@Ciudad Is Not Null)

   Set @SqlExec = @SqlExec + ' And Ciudad = @Ciudad'

If(@Direccion Is Not Null)

   Set @SqlExec = @SqlExec + ' And Direccion = @Direccion'

If(@Correo Is Not Null)

   Set @SqlExec = @SqlExec + ' And Correo = @Correo'

If(@Movil Is Not Null)

   Set @SqlExec = @SqlExec + ' And Movil = @Movil'

If(@Telefono Is Not Null)

   Set @SqlExec = @SqlExec + ' And Telefono = @Telefono'

If(@EsActivo Is Not Null)

   Set @SqlExec = @SqlExec + ' And EsActivo = @EsActivo'

If(@CreadoPor Is Not Null)

   Set @SqlExec = @SqlExec + ' And CreadoPor = @CreadoPor'

If(@FecCreacion Is Not Null)

   Set @SqlExec = @SqlExec + ' And FecCreacion = @FecCreacion'

If(@ModificadoPor Is Not Null)

   Set @SqlExec = @SqlExec + ' And ModificadoPor = @ModificadoPor'

If(@FecModificacion Is Not Null)

   Set @SqlExec = @SqlExec + ' And FecModificacion = @FecModificacion'

Exec sp_executesql  @SqlExec

                    ,@SqlParametros

,@IdCliente

,@FK_TipoIdentificacion

,@Identificacion

,@NombreCliente

,@Contacto

,@CargoContacto

,@Ciudad

,@Direccion

,@Correo

,@Movil

,@Telefono

,@EsActivo

,@CreadoPor

,@FecCreacion

,@ModificadoPor

,@FecModificacion

End

Si las opiniones aquí expresadas de fueron de tu interés o no te parecen adecuadas, por favor deja un comentario o dale un ( G+1 ) si no tienes mucho tiempo.