Webinar: Tips performance TSQL

La performance de nuestro código TSQL es fundamental para que nuestras aplicaciones funcionen no solo de forma eficiente sino que también podamos reducir costos de HW o bien en los servicios de la nube.

Siempre la performance fue un tema a tratar en nuestras bases de datos MSSQL pero hoy día muchas empresas que migran a la nube y no tienen su base optimizada lo terminan pagando con mayores costos mensuales en los servicios que contratan.

Les dejo este webinar que di hace un tiempo en donde muestro algunos tips de performance en T-SQL que pueden ser de utilidad.

No olvides de seguirme en mi canal de Youtube o en nuestro grupo de Facebook para DBA de habla hispana

T-SQL : Impacto en performance por no utilizar expresiones SARGS

Uno de los ANTI-PATRONES (malas prácticas) de performance más frecuente es no utilizar consultas que tengan una expresión SARGS, lo cual impactará de forma notoria en la performance de nuestros procesos.

En este artículo te voy a contar que son las expresiones SARGS y veremos varios ejemplos de estos anti-patrones con su respectivo impacto en la performance.

Que es una consulta SARGABLE

Sargable es una palabra que está compuesta por tres palabras : búsqueda, argumento y capaz.

En otras palabras, SARGable se define como “En las bases de datos relacionales, se dice que una condición (o predicado) en una consulta es sargable si el motor DBMS puede aprovechar un índice para acelerar la ejecución de la consulta.

El término se deriva de una contracción de Search ARGument ABLE

Las ventajas de usar consultas SARGS son:

  1. Usar los índices de forma eficiente
  2. Consumir la menor cantidad de recursos de nuestro servidor (CPU, IOPS, RAM)
  3. Mejorar los tiempos de respuesta de nuestros procesos / consultas.

Ejemplos

Para mis ejemplos utilizaré la base de datos AdventureWorks la cual podras descargar del siguiente link

https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

Funciones en los WHERE

Usar funciones en los campos de los WHERE hará que nuestra consulta no sea SARG compatible y por ende no se usen los índices de forma eficiente.

Veamos los siguientes ejemplos

SELECT * FROM Person.Person
WHERE LastName = 'Miller'
SELECT * FROM Person.Person
WHERE LEFT(LastName,4) = 'Mill'
SELECT * FROM Person.Person
WHERE upper(LastName) = 'MILLER'

Ahora vamos a analizar los planes de ejecución de cada una de nuestras consultas

Consulta 1
Consulta 2
Consulta 3

Como se puede observar la consulta 1 que si es SARGS usa de forma eficiente el indice (Index Seek), pero en cambio la consulta 2 y 3 como no son SARGS no lo están usando de forma eficiente (Index Scan)

Veamos que significa esto en numeros de costos de recursos.

ConsultaCosto Total Query PlanCosto CPUCosto I/O
10.280,01350.263
20.400.03630.365
30.390.03630.361

Hagamos ahora una prueba de stress de performance con nuestras 3 consultas para ver que sucede, para eso voy a utilizar la herramienta gratuita SQLQueryStress con cada una de las consultas usando 50 hilos y 50 iteraciones

Como se puede observar en las pruebas de stress también vemos impacto en los tiempos del proceso y consumos de recursos.

Funciones de fecha

Ahora vamos a ver otro ejemplo más donde usamos funciones en los WHERE pero con fechas.

Para eso veremos estas dos consultas las cuales retornan los mismos resultados.

SELECT SalesOrderID,OrderDate   
 FROM Sales.SalesOrderHeader H
 WHERE OrderDate > = '20110101'
 AND OrderDate < '20120101'
SELECT SalesOrderID,OrderDate   
 FROM Sales.SalesOrderHeader H
 WHERE YEAR(OrderDate)  = '2011'

Ahora vamos a observar los planes de ejecución donde veremos que la consulta 1 utiliza el índice de forma eficiente mientras la consulta 2 no lo hace

La siguiente tabla muestra los costos y consumos de la consulta 1 y 2 en donde se puede observar un consumo mucho mayor en la consulta 2 que en la 1, esta tabla es más grande que la del ejemplo anterior.

ConsultaCosto Total query PlanCosto CPUCosto I/O
10.00720.00190.0053
20.0980.0340.060

Uso de ISNULL en el WHERE

En este otro ejemplo veremos el uso de la función ISNULL en el WHERE y ver que sucede con SARG

Para este ejemplo primero vamos a crear un índice nuevo en nuestra tabla Person.Person

DROP INDEX IF EXISTS IX1 ON Person.Person
CREATE INDEX IX1 ON  Person.Person(MIDDLENAME)

Ahora veremos estas dos consultas que retornan los mismos resultados, pero en la primera usamos el ISNULL en el WHERE y en la segunda no.

SELECT BusinessEntityID,MiddleName 
FROM Person.Person
WHERE ISNULL(MiddleName,'A')='A' 
SELECT BusinessEntityID,MiddleName  
FROM Person.Person
WHERE (MiddleName IS NULL OR MiddleName ='A')  

Observemos ahora cada plan de ejecución y sus costos

Consulta 1
Consulta2

ConsultaCosto Total Query PlanCosto CPUCosto I/O
10.0520.0220.030
20.0270.0200.016

Aquí también podemos observar que la consulta 1 no es SARG y por ende hace un scan del indice siendo mas costoso que la consulta 2

Operadores en los campos del WHERE

Otra de las situaciones que hacen que una consulta no sea SARG es que se utilicen operadores en los campos del WHERE.

Vamos a analizarlo con ejemplos como los casos anteriores.

Para este ejemplo usaremos la tabla Sales.SalesOrderDetail de nuestra base de datos de ejemplo.

Primero creamos un índice como el siguiente:

DROP INDEX IF EXISTS IX_2 ON
 [Sales].[SalesOrderDetail] 
 CREATE NONCLUSTERED INDEX IX_2 ON
 [Sales].[SalesOrderDetail] (
 [UnitPriceDiscount]
 )
 INCLUDE (
  [ProductID],
  [UnitPrice],
  [OrderQty]
 );

Luego vamos a probar las siguientes consultas y observar sus planes de ejecución / consumos

SELECT  
  [ProductID],
  [UnitPrice],
  [OrderQty]
 FROM  Sales.SalesOrderDetail
 WHERE UnitPriceDiscount + 0.10 >= 0.30 -- no es SARG compatible
 GROUP BY 
 [ProductID],
 [UnitPrice],
 [OrderQty]
SELECT  
  [ProductID],
  [UnitPrice],
  [OrderQty]
 FROM  Sales.SalesOrderDetail
 WHERE UnitPriceDiscount >= 0.20
 GROUP BY 
 [ProductID],
 [UnitPrice],
 [OrderQty]
ConsultaCosto Total Query PlanCosto CPUCosto I/O
10.660.1380.52
20.0250.00960.0156

Como se puede observar la primer consulta al no se SARG compatible tiene un mayor costo por usar el índice en modo Scan,

JOINS y SARG

Los JOINS no se quedan atras tambien de estas prácticas, si aplicamos comandos no SARG en los ON vamos a ver un impacto en la performance.

Veamos las dos siguientes consultas donde ambas dan los mismos resultados pero en una no usamos SARG compatible y por ende cambiará nuestro plan de ejecución.

SELECT p.ProductID,
  p.Name AS ProductName,
  c.Name AS Category,
  s.Name AS SubCategory
 FROM Production.Product AS p
 inner JOIN Production.ProductSubcategory AS s
  ON p.ProductSubcategoryID = s.ProductSubcategoryID
 INNER JOIN Production.ProductCategory AS c
  ON s.ProductCategoryID + 10 = c.ProductCategoryID + 10
SELECT p.ProductID,
  p.Name AS ProductName,
  c.Name AS Category,
  s.Name AS SubCategory
 FROM Production.Product AS p
 inner JOIN Production.ProductSubcategory AS s
  ON p.ProductSubcategoryID = s.ProductSubcategoryID
 INNER JOIN Production.ProductCategory AS c
  ON s.ProductCategoryID  = c.ProductCategoryID 
ConsultaCosto Total Query PlanCosto CPUCosto I/O
10.0650.04200.0212
20.0530.02980.0212

Conclusiones finales

La utilización de los anti-patrones que vimos los cuales hacen que una consulta TSQL puede ser SARGS compatible tiene impactos muy negativos en nuestros consumos de recursos y performance en general.

En mis años de experiencia veo de forma recurrente estas malas prácticas en el código TSQL las cuales sugiero siempre corregir en mis clientes.

Recuerden que más allá de los tiempos y consumos, si estamos en Azure por ejemplo vamos a estar gastando más dinero por no tener optimizados nuestro código.

Rating: 1 out of 5.

SQL Server performance: ORDER BY y consumo de CPU

Cuando escribimos nuestro código T-SQL en algunas ocasiones se observa la instrucción ORDER BY la cual técnicamente lo que hace es ordenar el set de datos resultantes y a menos que exista un TOP lo único que se logra es solamente una presentación de los datos.

Ahora bien, ¿ sabemos el impacto que esto tiene en la performance y el consumo de recursos ? , en este post te voy a mostrar que es lo que sucede cuando aplicas un ordenamiento y cuales serian las posibles alternativas de solución.

Ejemplo 1

Vamos a suponer que tenemos las siguientes consultas T-SQL

SELECT * 
FROM PURCHASING.PURCHASEORDERHEADER 

SELECT * 
FROM PURCHASING.PURCHASEORDERHEADER 
ORDER BY ORDERDATE

Si observamos sus planes de ejecución nos encontraremos el siguiente resultado:

Nota: Como se puede observar la segunda consulta tiene una operación adicional y además han aumentado sus costos en consumo de recursos (sobre todo CPU) con relación a la primer consulta.

Ejemplo 2

Supongamos los dos siguientes ejemplos en donde armamos una consulta pero ahora con un WHERE sobre un campo que tiene índices en la tabla.

SELECT * 
FROM PURCHASING.PURCHASEORDERHEADER 
WHERE EMPLOYEEID = 25

SELECT * 
FROM PURCHASING.PURCHASEORDERHEADER 
WHERE EMPLOYEEID = 25
ORDER BY ORDERDATE

Si observamos los planes de ejecución nos encontraremos con algo similar al ejercicio anterior

Nota: Podemos observar que por mas que se use el índice nos aparece en la segunda consulta el operador de SORT

Como evitar el operador SORT

La única forma de evitar este operador es creando un índice que contenga las columnas (claro la pregunta es si vale la pena hacer esto cuando en muchos casos es solo por presentación el uso de ORDER BY)

CREATE INDEX IX1 ON 
PURCHASING.PURCHASEORDERHEADER (EMPLOYEEID,ORDERDATE) 

Si ahora volvemos a correr la consulta anterior veremos el cambio en nuestro plan de ejecución donde no solo no esta el operador SORT sino que su costo a bajado

Conclusiones finales

Usar un ORDER BY cuando no tenemos un TOP no tiene mucho sentido y genera un consumo mas alto de recursos en nuestro servidor de base de datos, sobre todo CPU (recordemos que por este recurso es como se nos cobra tanto en licencias o azure)

Crear índices para solo ordenar suele ser una muy mala idea, básicamente porque vamos a penalizar las operaciones de insert, update , delete o merge.

Esta práctica del ORDER BY la he visto en mucho código TSQL a lo largo de mis años haciendo optimizaciones, yo recomiendo evitarla por completo y si hay un TOP en ese caso si crear el índice correspondiente

Video explicativo

Links adicionales

SQL Sentry Explore Plan

SQLQueryStress

Video y material Webinar TSQL Tips

Mejorar los errores Data Truncation en SQL Server

Los errores de truncamiento de datos es algo que alguna vez como programadores de SQL Server nos hemos topado.
cuando hablamos de estos errores nos referimos a por ejemplo querer insertar en un VARCHAR(10) la palabra ‘TRIGGERDB CONSULTING SRL‘ lo cual como es lógico nos dará un error.

Ahora bien , este error no es muy detallado que digamos, por ende vamos a ver como podemos trabajar con lo que se denomina “Silent Truncation”

Veamos el siguiente ejemplo:

USE MASTER
GO
DROP DATABASE IF EXISTS TRIGGERDB2019
CREATE DATABASE TRIGGERDB2019
GO

USE TRIGGERDB2019
GO
DROP TABLE IF EXISTS DBO.DEMO_TRUNCATE;

CREATE TABLE DBO.DEMO_TRUNCATE
(ID INT IDENTITY PRIMARY KEY,
 C1 VARCHAR (10));

INSERT INTO DBO.DEMO_TRUNCATE (C1)
VALUES ('TRIGGERDB CONSULTING SRL')
Al ejecutar nuestra operación INSERT observamos el siguiente error

Msg 8152, Level 16, State 30, Line 16
Los datos de cadena o binarios se truncarían.
Se terminó la instrucción.

Msg 8152, Level 16, State 30, Line 1
String or binary data would be truncated.
The statement has been terminated.

SQL Server 2019 

En SQL Server 2019 usando solamente el modo de compatibilidad 150 podremos tener un mejor mensaje de error.

ALTER DATABASE TRIGGERDB2019 
SET COMPATIBILITY_LEVEL = 150

INSERT INTO DBO.DEMO_TRUNCATE (C1)
VALUES ('TRIGGERDB CONSULTING SRL')

Msg 2628, Level 16, State 1, Line 16
Los datos binarios o de la cadena se truncan en la columna “C1” de la tabla “TRIGGERDB2019.dbo.DEMO_TRUNCATE”. Valor truncado: “TRIGGERDB “.
Se terminó la instrucción.

Msg 2628, Level 16, State 1, Line 7
String or binary data would be truncated in table ‘TRIGGERDB2019.dbo.DEMO_TRUNCATE’, column ‘C1’. Truncated value: ‘TRIGGERDB ‘.
The statement has been terminated.

SQL Server 2016 y 2017 

Si se quiere usar esta funcionalidad en SQL Server 2016 o 2017 se requiere activar el Trace Flag 460 el cual esta disponible en el Sp2 de SQL Server 2016 y CU6 de 2017  

TF 460 en el scope
DBCC TRACEON (460)

INSERT INTO DBO.DEMO_TRUNCATE (C1)
VALUES ('TRIGGERDB CONSULTING SRL')

Msg 2628, Level 16, State 1, Line 16
Los datos binarios o de la cadena se truncan en la columna “C1” de la tabla “TRIGGERDB2019.dbo.DEMO_TRUNCATE”. Valor truncado: “TRIGGERDB “.
Se terminó la instrucción.

Msg 2628, Level 16, State 1, Line 7
String or binary data would be truncated in table ‘TRIGGERDB2019.dbo.DEMO_TRUNCATE’, column ‘C1’. Truncated value: ‘TRIGGERDB ‘.
The statement has been terminated.