Freddy Angarita

A continuación presento alguno de los contadores de rendimiento, pertenecientes a la instrumentación de SqlServer que ayudan al proceso de diagnóstico y optimización de SqlServer

 

En el gráfico se presenta el grupo de contadores de rendimiento, luego al abrirlo se presenta cada contador independiente y luego en la parte de abajo (si aplica) que es lo q se quiere medir

Procesador

Processor		Esperado
%User Time	SqlServer se ejecuta en modo de Usuario. El modo privilegiado es usado por el Sistema operativo para acceder a los componentes de hardware. Este componente debería presentar un valor mayor de 70%, si el valor es menor que esto se deben revisar los indicadores %privileged time y %processor time para verificar si existe algún tipo de problema con el procesador	>70%
%Privileged Time	El sistema operativo mueve los hilos al modo de acceso privilegiado para acceder servicios de Hardware. Este contador debe tener un valor menor a 20%.	<20%
%Processor Time	Presenta el porcentaje de tiempo usado por la CPU en un periodo de tiempo. Idealmente debe ser menor que 70%.	<70%
Interrupts/Sec	Es el promedio de incidentes por segundo, al cual el procesador recibió y procesó interrupciones de Hardware

 

System		Esperado
Processor Queue Length	Es el número de hilos que espearan a ser procesados. Este contador por valor general divido por el número de núcleos disponibles. Debería ser menor que 2	<2

Disco

Physical Disk		Esperado
Current Queue Length	Un tamaño sostenido de una cola de disco presenta un posible problema con el subsistema de I/O.	0
Average Queue Length	Si el promedio de longitud de la cola es mayor que 2 indica que existe un problema potencial con el subsistea de IO, esto tambien puede deberse a un indicador disk sec/read y disk sec/write alto.	<=2
Disk Sec/Read Disk Sec/Writes	Ninguno de estos contadores debería tener un valor superior a 15ms en condiciones normales. Valores continuos sobre 20ms puede indicar un problema en el disco o sobrecarga del sistema o alto fraccionamiento del disco. Bajo estas condiciones se pueden considerar las siguientes alternativas: Mover los archivos de base de datos a discos adicionales Crear grupos de archivos en diferentes discos y pasar algunas tablas a cada uno de esos discos  (pueden tener difernte RAID) lo cual puede influir notablemente en el desempeño Revisar los índices de las tablas Revisar los contadores de CPU y memoria con el fin de identificar otros cuellos de Botella	<=15

 Memory/Cache

Memory
Page Faults/Sec:	Los fallos de página ocurren cuando se busca una página en memoria y no se encuentra. Existen 2 tipos de fallos de página: Fuertes (Hard) : Requiere acceso a disco Suaves (Soft).  La página buscada está en alguna otra parte de la memoria Un alto número de fallos de página por segundo indica que existe un problema con la memoria usada por Sql Server. Usando este contador junto con SqlServer:MemoryManager se puede determinar si existe algun tipo de presión sobre la memoria Bajo estas condiciones puede considerarse: Revisar los procedimientos y las prácticas usadas para el desarrollo de los mismos Aumentar la memoria del servidor Revisar el espacio en disco disponible en donde se ubiquen los temporales del sistema operativo
Pages/Sec	Este contador es valor real de fallos Fuertes de páginas y debe ser usado en correlación con Page Faults/Sec y SQL Server memory Manager para revisar la presión sobre la memoria

SQL Server

Access Methods
Forwarded Records/Sec	Numero de registros traídos usando punteros de registros, es decir, cuando los registros tienen espacios vacíos entre ellos o cuando el tamaño del registro no queda ajustado al tamaño de una página, esta situación puede presentarse cuando se inicia con un registro corto y se actualiza el dato quedando parte de éste en otra página lo que requiere la generación de un puntero para saber en qué lugar está la otra parte del registro. Esto se puede evitar siguiendo los siguientes pasos: Ser cuidadoso al determinar cuales de las columnas permiten o no nulos Usar valores por defecto (para evitar el uso de nulos) Usar char en lugar de Varchar cuando sea posible Manejar un esquema de normalización en donde el número de campos sea menos (Soluciones OLTP)
Full Scans/Sec	Se realiza una lectura completa de una Tabla o de un índice. Esto puede ser causado por el uso indebido de índices.
Page Splits/Sec	Fraccionamiento sobre las páginas de los índices. Este indicador está asociado a las páginas hoja del Arbol de índices que no se almacenan conitnuamente lo que lleva a su fraccionamiento en disco. Esto se puede evitar configurando apropiadamente Fill Factor

  

Memory Manager

Memory Grants Pending

Memoria que se necesita para procesar cada una de las peticiones de los usuarios. Si no se dispone de memoria suficiente entonces el proceso debe esperar a que se le sea asignada para su ejecución, lo que por supuesto impacta el desempeño de la consulta o procedimiento en ejecución Para evitarlo se pueden seguir los siguientes pasos: Agregar mas memoria al servidor Asignando mas memoria a SqlServer Creando Índices apropiados

 

Buffer Manager		Esperado
Buffer Cache Hit Ratio	Porcentaje del tiempo en que las páginas solicitadas están ya en memoria. Idealmente debe mantenerse sobre 99%, si es menor que 95% indica que SqlServer no tiene suficiente memoria y que agregar más memoria al servidor, asignada a SqlServer, sería muy beficioso	>99%
Checkpoints/Sec	Paginas escritas a disco durante el proceso de CheckPoint. Se identifica presión sobre la memoria si el contador tiene un valor superior a 300 Segundos	<300s
Lazy Writes/Sec	Paginas escritas a disco durante el proceso LazyWriter (Escribe las páginas en segundo plano al disco). Se identifica presión sobre la memoria si el contador tiene un valor superior a 300 Segundos	<300s
Page Life Expectancy	Este es uno de los principales contadores para identificar presión sobre la memoria. Determina el tiempo en segundos en el que la página reside en el Caché de SqlServer. Si el valor es bajo indica los siguientes problemas: El Caché es Frio (Revisar información sobre Fallos de Página Page Faults) Problemas de Memoria Falta de creación de ïndices Si Checkpoints/Sec, Lazy Writes/Sec y Page life expectancy juntos es menor a 300 Segundos entonces indica que la causa es la falta de memoria y que se debe agregar más memoria al servidor

 

Databases
Transactions/Sec	Indica el número de transacciones que ocurren por sengundo en el servidor

 

General StatisticsCounters
User Connections	Número de conexiones hechas a SqlServer

 

Latches
Average Latch wait Time	Son objetos livianos de sincronización. No se mantienen durante la duración de la transacción. Típicamente se usan cuando se transfieren filas a memoria, mientras se contolan modificaciones a las filas, etc. Un valor alto puede indicar algún problema con el subsistema de Memoria

 

Locks

Average Wait Time(ms) Lock Wait time(ms) Lock waits/Sec

Los contadores relacionados a los bloqueos que mantiene SqlServer en un momento determinado de las transacciones. Las transacciones deben ser tan cortas como sea posible y por lo tanto debería mantener bloqueos la menor cantidad de tiempo posible para evitar para bloqueos a otros usaurios o procesos. Un Valor alto para cualquiera de estos contadores indica: Presión sobre la memoria Problemas con el Disco Índices inadecuados Diseño inadecuado de tablas u objetos de SqlServer Inadecuada ubicación de los archivos de base de datos Uso indebido de los niveles de aislamiento de SqlServer

Los mencionados, son algunos de los contadores preferidos, aparte de esos pueden revisarse estos contadores:

Object Name Counter Name
SQL Statistics Compilations/sec
SQL Statistics Recompilations/sec
SQL Statistics Batch Requests/sec
SQL Server: Buffer Manager Readahead pages/sec
 

 

Algunas Definiciones

---

Páginas Sucias: Páginas que han entrado al caché y han sido modificadas, pero aun no se han escrito a disco.

CheckPoints: Escribe todas las páginas sucias a disco. Crea un punto al cual se garantiza que todas las páginas han sido escritas a disco

Fill Factor: Factor por el cual crecen los archivos de base datos, cuando se espera una gran cantidad de nuevos registros en la base de datos se debe establecer un FillFactor alto con el fin que los datos no queden fragmentados y un FillFactor un poco menor en caso de ser utilizada en procesos de actualización y solo consulta

Lazy Writer:  es un proceso que elimina conjuntos de buffers viejos y no usaados de memoria (Los buffers que tienen cambios deben ser escritos de nuevo al disco antes que el buffer pueda ser reusado para una página diferente).

 

Espero sea de ayuda

 

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

En el artículo en el artículo de optimización de consultas se menciona como uno de los procesos de optimización, la asignación de grupos de archivos a diferentes tables para utilizar mejor el hardware del servidor y hacer de las consultas un proceso mas veloz, para determinar cual son las tablas que son candidatas para pasarlas a otros grupos de archivos podemos usar:

select si.rows as ‘ConteoFilas’, SO.Name as ‘Tabla’, SI.name as ‘Nombre Indice’, SFG.groupname as ‘Grupo de Archivos’
from sysobjects as SO join sysindexes as SI on SO.Id = SI.id join sysfilegroups as SFG on SI.GroupId = SFG.GroupId
order by si.rows desc, SO.Name , SI.name, SFG.GroupName

Las tablas que contengan un mayor número de registros son candidatas para hacer el paso a otro(s) grupos de archivo

select

Esto nos devuelte el numero de filas y nombre de la tabla y el grupo de archivos al cual pertenece, en versiones como Standard éste puede ser uno de los pasos para lograr más velocidad en tablas de hechos, tales como movimientos y registros paso a paso

si.rows as ‘Rows’, SO.Name as ‘Table’, SI.name as ‘Index’, SFG.groupname as ‘Filegroup’ from sysobjects as SO join sysindexes as SI on SO.Id = SI.id join sysfilegroups as SFG on SI.GroupId = SFG.GroupId
order by si.rows desc, SO.Name , SI.name, SFG.GroupName

Espero sea de ayuda

 

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

 Detectar si un determinado usuario tiene o no autorización para realizar alguna tarea, detectar cuales de los ítems no ha sido asociados a él es tan solo algunas de las aplicaciones para éste artículo.

Como primera instancia tenemos una tabla de ejemplo, que se genera usando:

declare @t1 table (id int, VALOR CHAR(1))

 

insert into

@t1 values (1, ‘A’)
insert into @t1 values (2, ‘B’)
insert into @t1 values (3, ‘C’)
insert into @t1 values (4, ‘D’)
insert into @t1 values (5, ‘E’)

select

* from @t1

id	VALOR
1	A
2	B
3	C
4	D
5	E

Queremos saber por cada uno de los valores en la columna Id, cuales de los valores en la columna VALOR no están asociados con un Id

Es decir que para el Id 1, los valores deben ser B,C,D,E

La primera aproximación para saber cual de los VALORES está aplicado a cada uno de los Id es realizar un Cross Join

SELECT

* from @t1 t1 cross join @t1 t2

id	VALOR	id	VALOR
1	A	1	A
2	B	1	A
3	C	1	A
4	D	1	A
5	E	1	A
1	A	2	B
2	B	2	B
3	C	2	B
4	D	2	B
5	E	2	B
1	A	3	C
2	B	3	C
3	C	3	C
4	D	3	C
5	E	3	C
1	A	4	D
2	B	4	D
3	C	4	D
4	D	4	D
5	E	4	D
1	A	5	E
2	B	5	E
3	C	5	E
4	D	5	E
5	E	5	E

Se nota en ésta combinación que 1 ya está asociado con A en la fila         y notamos que en las columnas         vemos que los valos con 1 no están asociados

Por lo que si creamos un condición podemos eliminar los que ya están asociados dejando solo los que NO están asociados

SELECT t2.id, t1.VALOR from @t1 t1 cross join @t1 t2 where not (t1.id = t2.id and t2.VALOR = t2.VALOR )

id	VALOR
1	B
1	C
1	D
1	E
2	A
2	C
2	D
2	E
3	A
3	B
3	D
3	E
4	A
4	B
4	C
4	E
5	A
5	B
5	C
5	D

 

 

Espero sea de ayuda

 

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

Importar grandes volúmenes de datos puede tomar mucho tiempo en SSIS si no se hace de la manera apropiada, y una de las principales técnicas para lograr este propósito es el uso de archivos Raw (Crudos), los cuales son archivos binarios de altísimo rendimiento que por sus características permiten leer y escribir datos a alta velocidad haciéndolos ideales para ser el paso intermedio entre el origen y el destino de los datos (Stage).

Para usarlos, sin embargo, debe crearse primero un archivo vacío que tenga el esquema de datos que vamos a utilizar posteriormente en nuestra solución, así que presento los pasos para crear este esquema usando un paquete que apoya dicha tarea:

1. Crear un nuevo paquete
2. Agregar un Data Flow
3. Agrear un Origen de Datos Ole Db
4. Crear una consulta para el origen Ole Db que devuelva cero registros (top 0) y que tenga los registros que deseamos incluir en el esquema del archivo raw
   select top 0
   cast(null as int) as Id,
   cast(null as varchar(50)) as Value
5. Si es necesario, Agregar una transformación DataConversion para establecer el tipo de datos desedo para cada columna
6. Agregar un Destino de Archivo Raw y seleccionar las columnas que se quieren en el archivo Raw
7. Ejecutar el Paquete
8. Usar el Archivo Raw

Adjunto se encuentra un paquete de ejemplo para realizar ésta tarea

Espero sea de ayuda

 

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

Siempre buscando la optimización de consultas debemos intentar siempre el uso de operaciones de conjunto sobre las operaciones fila a fila, éste es uno de los ejemplos en donde usando T-Sql podemos obtener los últimos IDs generados a partir de la inserción en una las tablas que tienen Identity,

 DECLARE @output TABLE (id int)  –Tabla para capturar los IDs generados

Insert into A (fname, lname) — Tabla en la que insertaremos los datos
OUTPUT inserted.ID INTO @output –Captura de los IDs generados con el uso de la palabra clave OUTPUT
SELECT fname, lname FROM B — Consulta que trae los datos a insertar

select * from @output — Presentando resultados (opcional)

 

Mediante el uso de ésta técnica podemos aumentar notablemente la velocidad de nuestra consultas y procedimientos almacenados

 

Espero sea de ayuda

 

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

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

 

Como siempre, una de las cosas que mas se necesita al crear un procedimiento es generar un número de determinado de filas para poderlas combinar con alguna otra tabla, ésta técnica presenta cómo crear una tabla que cuente de un número a otro, en incrementos de mánera eficiente

LA PRIMERA APROXIMACIÓN

declare @StartNumber int, @Nrows int, @increment int


set @StartNumber = 1
set @Nrows  = 1000000
set @increment = 1


----
declare @q table  (i int)
declare @index int

set @index = @StartNumber 

while @index < @Nrows 
Begin
	insert into @q values(@index)
	set @index = @index + @increment 
End

select i from @q 

En éste punto, se genera el resultado y tiene los parámetros que se podrían esperar: el Inicio, El número de filas y el Incremento, pero al ejecutarlo con un millón de filas, toma (en mi computador) 25 segundos, lo cual es muy lento, sin emabrgo ésta aproximación tendríaa el límite de registros del tipo de datos que usemos como parámetro, es decir, se puede llegar hasta el máximo que permite un entero, o el máximo que soporte un bigint, lo cual es bastante grande, pero mucho mas lento al hacerlo uno por uno. Ahora vamos a investigar otra alternativa que nos permitirá generar los registros mucho más rápido usando otras técnicas

SEGUNDA APROXIMACIÓN

declare @StartNumber int, @NumberOfRows int, @Increment int

set @StartNumber = 1
set @NumberOfRows = 1000000
set @Increment = 1

declare @Rows table (i int)

Declare @NumGen Table (Num int)	
Declare @cnt int	
Set @cnt = @StartNumber
While @cnt <= 100 
Begin		
	Insert Into @NumGen values (@cnt)
	Set @cnt = @cnt + @Increment
End	
set @StartNumber = @StartNumber - @Increment 

insert into @Rows
Select @StartNumber + RowNum	From 	
(
Select Row_Number() Over (Order By N1.Num) As RowNum
From @NumGen N1, @NumGen N2, @NumGen N3, @NumGen N4, @NumGen N5, @NumGen N6, @NumGen N7, @NumGen N8, @NumGen N9, @NumGen N10) RowNums	
Where RowNum <= @NumberOfRows

select * from @Rows

 

Lo que sucede es que estamos haciendo una generación inicial de una tabla hasta 100 normalmente (también porque para casos en donde la tabla a generar es mas corta) y luego multiplicando por conjuntos éste esfuerzo con el fin de no generar una a una las filas sino por bloques de a 100 filas y en éste caso 10 bloques de a 100 filas, al hacer ésta combinación aplicamos una combinación lo cual nos da un máximo de filas a generar de 100^10, lo cual suficiente en la mayoría de los casos.

 

Usando nuestra solución podemos generar la función con la firma:

 

alter function GenTable(@StartNumber int, @NumberOfRows int, @Increment int = 1)

 

Y podemos utilizarlo para crear un ejemplo: Imaginemos que queremos agregar hora a hora las siguientes 12 horas a las fechas de actividad de un cliente, cómo se podría hacer?

EJEMPLO DE USO

--Tabla que proviene de la Base de Datos simulada--
declare @fechas table (IdCliente int, Fecha datetime)

insert into @fechas values 
(1, '2001-01-20 08:23:20'), 
(2, '2001-02-24 18:23:20'), 
(3, '2001-03-23 12:23:20')
--Fin Tabla que proviene de la Base de Datos simulada--

select * from @fechas 

insert into @fechas 
select IdCliente, DATEADD(hh,ids.i,fecha) as FechasHoras  from @fechas 
cross join (select i from dbo.GenTable(1,12,default)) Ids
order by FechasHoras

select * from @fechas 
order by Fecha 

 

Aplicamos cada registro generado a cada una de las filas (CrossJoin) que provienen de la tabla y luego usamos la función DateAdd para hacer el trabajo fila a fila, sin usar cursores, sin necesidad de recorrer la variable tipo tabla y como siempre favoreciendo las operacione de conjunto sobre las operaciones realizadas fila a fila

 Por supuesto exiiten otras alternattivas se puede generar usando CTE, la cuestión es la bandera MAXRECURSION, la cual limita el rango de acción de la solución planteada a cierto número de registos (depende de la implementación) Y claro la MAS recomendada y eficiente de todas que es usar CLR, especial para este tipo de casos Los invito a probar las diferentes alternativas y enviar sus comentarios sobre el tema

FREDDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP