Mes: enero 2011

Cada vez que se ejecuta un procedimiento almacenado en SqlServer por primera vez, se optimiza y su plan de ejecución se compila y se almacena en el caché de SqlServer. Cada  vez que el procedimiento almacenado se ejecuta luego de ser almacenado en el caché, usará el mismo plan de ejecución, eliminando la necesidad de compilar y de almacenar el plan de ejecución de dicho procedimiento cada vez que se ejecuta. Si éste procedimiento se ejecuta 1000 o más veces por día, los recursos de hardware se optimizan al ahorrarse dichos pasos.

Si los parámetros dentro del procedimiento almacenado son idénticos en el Where, entonces reusar el plan de ejecución tiene mucho sentido. Pero qué pasa si los valores de los parámetros en cada ejecución cambian?, que pasa si el tamaño del resultado varia notablemente (Columnas y Filas), que sucede si para unos parámetros la búsqueda óptima es una búsqueda por índice, pero para otros valores el mejor camino es un table scan?

Todo eso depende de que tan genéricos sean los parámetros, de que tan bien esté definida la funcionalidad del procedimiento almacenado si están definidos de tal manera que no recarguemos su funcionalidad y que los resultados sean parecidos aun los valores de los parámetros sean diferentes el procedimiento ejecutará de manera óptima y obtendrá todos los beneficios mencionados anteriormente. Pero para el caso contrario, reutilizar el plan de ejecución puede no ser lo óptimo, como resultado la consulta se ejecutará más lentamente que si se creara dinámicamente su plan de ejecución.

En la mayoría de los casos, no es una cuestión de la cual preocuparse, pero si se hace la pregunta, por qué mi procedimiento almacenado ejecuta bien en el diseñador de consultas pero toma mucho tiempo en producción, éste puede ser el caso. Siempre teniendo en cuenta que el diseño del procedimiento almacenado, junto con granularidad, ofrecen la mejor estrategia para diseñarlos y claro para mantenerlos

Cómo solucionar el problema:

1. Aplicar recompilación a todos los procedimientos periódicamente
Ejecutar con el procedimiento almacenado del sistema sp_recompile sobre todos los procedimientos almacenados de la base de datos (Cada dos o tres meses)

2. Crear los procedimientos almacenados que consideremos que sean candidatos para sufrir este problema con la opción With Recompile, teniendo en cuenta que cada vez que ejecutemos el procedimiento almacenado el mismo se recompilará (se generará un nuevo plan de ejecución)
CREATE PROCEDURE MiProcedimiento
WITH RECOMPILE
AS
….

3. Si el caso en donde los resultados cambian demasiado dependiendo de valores específicos y plenamente identificados podemos ejecutarlo usando la opción with recopile de exec:
EXECUTE MiProcedimiento @p2 = ‘A’ WITH RECOMPILE;
GO

4. Query Hint RECOMPILE
A partir de SqlServer 2005 se introdujo RECOMPILE como query hint, lo que logra este hint es que el analizador de consulta descarte cualquier plan de ejecución previamente generado para esa consulta dentro del procedimiento almacenado y recompile sólo esta consulta

5. Creando Guías de Plan de Ejecución
Permite realizar cambios administrativos sobre procedimientos almacenados que ya han sido creados en la base de datos que no se puede o no se quiere modificar para agregar algún tipo de optimización como la mencionada. Esto se logra forzando hints sobre las consultas o fijando un plan de ejecución diferente a dicho procedimiento o consulta.

La creación de guías de plan es una opción avanzada de administración es recomendado leer detenidamente la documentación sobre el tema y luego observar los casos en donde sea prudente aplicarlo
Debemos usar éste tipo de solución cautelosamente dado que usar recompile, aunque nos aseguramos que cada vez el plan de ejecución generado es correcto, hace que perdamos los muy buenos beneficios de tener un plan de ejecución almacenado para el procedimiento y nos guía hacia un mejor diseño de los procedimientos almacenados que incluiremos en nuestros sistemas

 

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

En la siguientegráfica se presenta el esquema de ejecución de consultas en SqlServer

From y Join
1. Usar Hints
Podemos utilizar hints de consultas tales como (nolock) sobre las tablas que queremos consultar para cambiar el modo de acceso de readcommited a readuncommited –ADVERTENCIA: Utilizar este hint puede traer problemas de datos, tales como lecturas sucias o registros fantasma.

El uso de hints debe realizarse con especial cuidado y teniendo en mente las consecuencias de los mismos, asimismo, el optimizador de consultas podrá fácilmente encontrar el mejor camino para retornar la consulta dado que utiliza información estadística, caché, entre otros elementos para devolver los resultados.

 

2. Otra técnica que podemos usar antes del Where es poner las condiciones del where (cuando sean aplicables) dentro del mismo Join, es decir:
select a.Id, from tabla1 a inner join tabla2 b on a.id = b.id where b.cantidad > 50

puede ser reescrito como:

select a.Id, from tabla1 a inner join tabla2 b on a.id = b.id and b.cantidad > 50

Esto aplica especialmente para sqlServer 2005 y versiones anteriores, el analizador de SqlServer 2008 se vale de su algoritmia avanzada para determinar cuál es el mejor camino entre estas dos opciones.

3. Favorecer la lógica y operaciones por Conjuntos
Las operaciones por conjuntos implican la realización de operaciones por bloques, para lo cual Sql está optimizado:

3.1.  Evitar el uso de funciones escalares en las consultas para cada campo (en cualquier parte de la consulta), dado que la ejecución se realiza una por una, lo cual no favorece las operaciones por bloques.
3.2. Evitar al MAXIMO el uso de cursores.
3.3. Estimular el uso de funciones de usuario que devuelven tablas, pues se calculan primero y luego se aplican a la consulta favoreciendo operaciones de conjuntos.
3.4. Asimismo, estimular el uso de expresiones CTE:

Permite la reutilización de consultas, usando el join, de esta manera consultamos dos (2) veces la misma tabla
select a.Id from tabla1 ainner join tabla2 b on a.Tipo = B.IdTIpo
inner join tabla2 c on a.Tipo2 = C.IdTIpo

con CTE, solo una vez:

declare @tabla1 table (Id int, TipoA int, TipoB int) –Solo por ejemplo
declare @tabla2 table (IdTipo int) –Solo por ejemplo
;with Tabla(Id)
as(
select IdTipo from @tabla2
)
select * from @tabla1 a inner join Tabla
on a.TipoA = tabla.id
inner join @tabla1 b on b.TipoB = tabla.id

Where

1. Escribir las expresiones lógicas de menos completa a más compleja, es decir, es mejor:
select a.Id, from tabla1 a inner join tabla2 b on a.id = b.id and b.cantidad > 50 and nombre like ‘%abc%’

que usar:

select a.Id, from tabla1 a inner join tabla2 b on a.id = b.id a nombre like ‘%abc%’ and b.cantidad > 50

Esto se debe a la evaluación de la lógica tipo corto circuito

Select
1. Evitar el uso de funciones escalares por cada campo.
2. Usar Case puede ser una buena alternativa para devolver resultados directamente al usuario, usarlos con precaución, si es posible, reemplazarlos con case del lado del cliente (Reporting Services, por ejemplo).

Order By
1. Evitar al MÁXIMO los ordenamientos innecesarios dentro de las consultas, si se observan los planes de ejecución de las consultas se encuentra que es una de las cosas que más consume buena parte del tiempo de ejecución de la consulta; se recomienda ordenar, en la mayoría de los casos, del lado del cliente.

Result Set
1. Evitar al MÁXIMO el uso del select *, limitar la cardinalidad del resultado (tiene como resultado) permite, genera, mejora el uso de…? mejoras en el uso de memoria y latencia de red, dado que los resultados son sólo aquellos que necesitamos, a la vez que evitamos que el analizador de consultas deba determinar dinámicamente qué campos usar en el conjunto de resultados.

Grouping and Aggregation
Una vez finalizada la etapa de la consulta en donde se obtienen los resultados iniciales, el motor procede a realizar el agrupamiento y agregación de los resultados (agregación: aplicar una función a un grupo)
Debemos evitar al máximo realizar Agrupaciones y Agregaciones del lado de la base de datos, dado que son operaciones costosas, esto es especialmente cierto para operaciones de reportes.

Having
Sólo debe ser aplicado para filtrar sobre las funciones de agregación, NO debe ser utilizado para filtrar las filas de la consulta, lo cual se debe incluir en el Where de la consulta.

OTRAS RECOMENDACIONES
Usar CLR
Programación .Net para para crear procedimientos almacenados, funciones de usuario y agregaciones, etc. Usarlo cuando se requiera realizar cálculos avanzados del lado de la base de datos, en ningún momento debe usarse como reemplazo para las operaciones a acceso a datos.

Particionamiento de Datos
Crear grupos de archivos en diferentes discos y ubicar en ellos las tablas de mayor uso, lo cual favorece las operaciones INNER JOIN  (Versión inferior a la enterprise)

Particionamiento de Vertical de Filas
Crear particionamiento de filas usando funciones de particionamiento para ubicar asimismo las filas en diferentes grupos de archivos y optimizar el acceso a los datos en las operaciones JOIN (versión Enterprise)

Dividir las consultas en consultas más pequeñas
Usando variables tipo tabla podemos realizar consultas preliminares sobre algunas de las tablas que participan en las operaciones JOIN o LEFT JOIN, de esta manera cargaremos a memoria (luego de cierto límite de filas las variables tipo tabla también pasan a disco, por lo que debemos ubicar en ellas solo las tablas que relativamente tengan pocos registros: menos de 100,000)  de manera anticipada, permitiéndonos la reutilización de las mismas, con la ventaja que cuando salen de ámbito se destruyen automáticamente (no hay que realizar un drop como a las tablas temporales).

En próximos artículos extenderé estos y otros temas

 

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

Como todos los temas de optimización las soluciones presentadas en éste artículo deben probarse,
intentar varias alternativas y luego decidir dependiendo de los
resultados y especialmente de la evolución de los datos, es decir, dependiendo del
momento del ciclo de vida de la base de datos tendremos cierto uso de
la información (por ejemplo durante procesos de migración) y luego
tendremos otro tipo (en Producción, cuando cierto tipo de producto salga
de circulación, etc.)

El proceso de optimización y mantenimiento es algo continuo y
debe estar acorde a las necesidades y cambios del negocio

Pimero, qué es un SEMI JOIN

SELECT     HumanResources.Employee.BusinessEntityID, MyEmployees.EmployeeID
FROM         HumanResources.Employee LEFT OUTER JOIN
                      MyEmployees ON HumanResources.Employee.BusinessEntityID = MyEmployees.EmployeeID

 

Es cuando queremos hacer un left (o Right join) con otra(s) tabla(s) y queremos ver qué registros figuran en la primera tabla (HumanResources.Employee) y cuales en la segunda(MyEmployees)

 

Si en la segunda tabla no existe dicho registro se presentará NULL como se vé en la gráfica

 

Ahora, Definamos un ANTI SEMI JOIN

SELECT     HumanResources.Employee.BusinessEntityID, MyEmployees.EmployeeID
FROM         HumanResources.Employee LEFT OUTER JOIN
                      MyEmployees ON HumanResources.Employee.BusinessEntityID = MyEmployees.EmployeeID
WHERE     (MyEmployees.EmployeeID IS NULL)

Queremos encontrar los registros que estén en la primera tabla (HumanResources.Employee) que NO  estén en la segunda tabla (MyEmployee)

Ésta consulta cumple su cometido pero analicemos a fondo su funcionamiento, realiza el Left Join que mas pesado que realizar un inner join normal y filtra los resultados para la columna null

 

Ahora, qué ALTERNATIVA tenemos para éste caso:

Usaremos la función Exists

SELECT     a.BusinessEntityID
FROM         HumanResources.Employee a
where not exists(select EmployeeID from MyEmployees b where a.BusinessEntityID = b.EmployeeID)

Observemos la comparación entre el primer método y la alternativa:

El costo relativo para el batch es el mismo, y aparentemente tienen  el mismo costo, pero observemos 2 cosas interesantes sobre éste plan de ejecución, primero es que de la primera manera tenemos un filtro, es de recordar que los ordenamientos, las agrupaciones y los filtros son las operaciones más costosas dentro de una consulta; en la segunda parte ejecutando con Not Exists, vemos que el analizador identifica que es un Left Anti Semi Join, por lo cual automáticamente aplica las optimizaciones para este caso y luego realiza las operaciones adicionales de la consulta

Usando repetidamente ésta técnica, especialmente para consultas que impliquen muchos registros puede aumentar el rendimiento de la misma a la vez que permite una facil escritura y mantenimiento e incluso control sobre la consulta a realizar

 

 

FREDDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

 

Ahora con la aparición de los parámetros tipo tabla en SqlServer, se elimina la necesidad de crear parametros complejos o de trucos para enviar información hacia los procedimientos almacenados, pero para quienes no quieren enviar esta información, crear los tipos de datos que se requieren para recibir los parámetros tipo tabla, presento una alternativa para ello en varias aproximaciones

Función de usuario para la generación de Tablas

--USO: select * from fnCSV2Table('1,2,3,4,5,6,7,8',default)
--Convierte una CSV a una tabla, lo que facilita las consultas 
--Reduce la necesidad de consultas dinámicas dentro de los procedimientos almacenados
create function fnCSV2Table(@CSV varchar(max), @Separator char(1) = ',')
returns @list table (data int)
as
Begin
	declare @SecondIndex int
	declare @FirtsIndex int
	
	set @SecondIndex = charindex(@Separator,@CSV)
	set @FirtsIndex = 0
	
	if @SecondIndex > 0 set @CSV = @CSV + @Separator
	
	while (@SecondIndex > 0)
	Begin
		insert into @list values(substring(@CSV,@FirtsIndex,@SecondIndex - @FirtsIndex))
		set @FirtsIndex = @SecondIndex + 1
		set @SecondIndex = charindex(@Separator,@CSV,@FirtsIndex)
	End
	if @FirtsIndex =0 and len(@CSV)>0 
		insert into @list values(@CSV)
	return
End
go

--USO: select * from fnConvertCSV2TextTable('1,2,3,4,5,6,7,8',default)
--Convierte una CSV a una tabla (tipo texto), lo que facilita las consultas 
--Reduce la necesidad de consultas dinámicas dentro de los procedimientos almacenados
create function fnConvertCSV2TextTable(@CSV varchar(max), @Separator char(1) = ',')
returns @list table (data varchar(8000))
with encryption
as
Begin
	declare @SecondIndex int
	declare @FirtsIndex int
	
	set @SecondIndex = charindex(@Separator,@CSV)
	set @FirtsIndex = 0
	
	if @SecondIndex > 0 set @CSV = @CSV + @Separator
	
	while (@SecondIndex > 0)
	Begin
		insert into @list values(substring(@CSV,@FirtsIndex,@SecondIndex - @FirtsIndex))
		set @FirtsIndex = @SecondIndex + 1
		set @SecondIndex = charindex(@Separator,@CSV,@FirtsIndex)
	End	
	if @FirtsIndex =0 and len(@CSV)>0 
		insert into @list values(@CSV)
	return
End
go

Es posible que no sea la aproximación mas óptimizada desde el punto de vista programación T-Sql, pero si es seguro, soluciona el problema de una manera simple y facil de administrar

Cómo Usuarla

Una vez convertida a tabla podemos usarla en un join para filtrar los datos que queremos consultar, podemos usar Cross Apply para aplicar a cada registro la función, etc., la idea es generar una tabla y poderla utilizar facilmente dentro de nuestro código

FREDDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP