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.

DataSummit 2019

El pasado lunes 27 de mayo se realizo en Buenos Aires el evento DataSummit 2019 organizado por nuestra comunidad de SQL Pass Argentina

En este evento que duro todo el día en las oficinas de Microsoft Argentina y que han participado mas de 60 asistentes, se han dado varias charlas de Data Platform y Analytics donde los oradores mostramos muchas de las nuevas funcionalidades que nos ofrece la plataforma de Microsoft ya sea para ambientes on-prem como azure.

Por mi lado he dado estas dos conferencias de 90 minutos cada una:

Modernizando BI en azure

En esta charla mostré con ejemplos reales como se pueden usar los servicios de azure para poder implementar una solución completa de BI y analytics. Observamos el uso de Datalake Storage, Blob Storage, SQL datawarehouse, Analysis Services, Powerbi, Big Data (HdInisight y Databricks) y Datafactory para los ETL.

En el siguiente link comparto el material que use en mi charla como así también varios enlaces de interés

SQL Machine Learning y Big Data cluster SQL 2019

En esta charla he mostrado como usar las características de Machine Learning en SQL usando lenguajes como R y Python como así también las nuevas características de Big data cluster de SQL 2019

En el siguiente link comparto el material de dicha charla

Algunas imágenes del evento

¿Como concatenar valores en SQL sin preocuparnos por los tipos de datos y valores nulos?

Seguramente en más de una oportunidad hemos tenido que concatenar variable o campos en SQL Server. Ahora bien, lo que nos sucede es que puede suceder que esos campos no sean del tipo varchar o bien tengan valores nulos lo cual nos puede complicar un poco el código TSQL a utilizar.

Supongamos que tenemos las siguientes variables que necesitamos concatenar

DECLARE @NRO INT = 10
DECLARE @STRING VARCHAR(255) = 'TRIGGERDB CONSULTING'
DECLARE @STRING2 VARCHAR(255) = NULL
DECLARE @FECHA DATE = GETDATE()

Si para concatenar estas variables intentamos ejecutar el siguiente código TSQL nos encontraremos con un error en la conversión de tipo de datos.

SELECT @NRO +'-' + @STRING + '-' + @STRING2 + '-' + @FECHA

Msg 206, Level 16, State 2, Line 8
Operand type clash: date is incompatible with int

Con lo cual si lo queremos hacer de forma correcta deberíamos ejecutar el siguiente código

SELECT CONVERT(VARCHAR(10),@NRO)
		+ '-' +  ISNULL(@STRING,'') 
		+ '-' + ISNULL(@STRING2,'') 
		+ '-' + CONVERT(VARCHAR(12),@FECHA)

Ahora bien, esto nos obliga a hacer conversiones y además tratar los posibles nulos ya que si no lo hacemos todo el resultado nos dará null

Usando CONCAT

La instrucción CONCAT() (la cual esta disponible desde SQL Server 2012 en adelante) nos permite concatenar distintos tipos de valores y llevarlos a un string de forma automática donde ademas si hay nulos no impactaran en el resultado.

Para escribir entonces de forma correcta nuestro código según el caso anterior y usando la instruccion CONCAT debemos hacer algo como esto:

SELECT CONCAT(@NRO,'-',@STRING,'-',@STRING2,'-',@FECHA)

10-TRIGGERDB CONSULTING–2019-03-11
(1 row affected)

Como se puede observar es mucho más limpia y simple de usar