Blog Archive

SQL-Server

 
Eladio Rincón

Sort Warning in SQL Server: Complicaciones derivadas del Sort Operator

2011-08-07 17:51:49 por Eladio Rincón

Introducción

Cada día hay más literatura sobre los problemas derivados del uso del Sort Operator en SQL Server ineficientemente, y a pesar de ello, sigue siendo habitual encontrar el problema en clientes.

En esta publicación, podrá reproducción uno de los problemas derivados del Sort Operator, y tendrá claro cómo diagnosticar el problema asociado.

http://dilbert.com/mashups/comic/130971/

 

El problema

SQL Server tiene decenas de operadores; uno de ellos es el operador Sort, que ordena un conjunto de filas. La ordenación de las filas puede suceder por varias razones:

  • El usuario especificó la claúsula ORDER BY en su consulta
  • SQL Server necesita ordenar las filas para usar el conjunto en el siguiente operador (Stream Aggregate, o Merge Join)
  • SQL Server necesita ordenar varias filas para insertar en una tabla – ordenado en función de la(s) columna(s) en el índice clustered.
  • otros casos adicionales

Para ordenar ese conjunto de filas, SQL Server utiliza memoria para ello, y esa memoria, la reserva antes de comenzar a procesar la consulta. Imaginese que SQL Server estima que en la ordenación estarán involucradas 300 filas; el Engine reservará memoria para ordenar esas filas, y comenzará a procesar la consulta; cuando llegue a la parte de la consulta en la que necesita ordenar las filas, si el número de filas es diferente al número de filas estimado puede resultar problemático:

  • Si estimó filas de más de las que están entrando, SQL Server habrá reservado memoria de más par ese operador, lo cual no es negativo para esa consulta, pero es potencialmente peligroso porque se estará usando memoria ineficientemente.
  • Si estimó filas de menos de las que están entrando, SQL Server no tendrá espacio suficiente en memoria para ordenar todas las filas, por lo que deberá apoyarse en otra estructura alternativa para hacer la ordenación: esa estructura alternativa es Tempdb.

Preparación del problema

Para reproducir el problema vamos a proceder a un ejemplo sencillo; crearemos una tabla en la que insertaremos muchas filas y ejecutaremos una consulta con un ORDER BY.

Creación de tabla y relleno de algo de más de 100.000 filas:

use Adventureworks 
go

drop table predicados;
create table predicados
(id int identity, d date, s char(10)
constraint predicados_pk primary key (id))
go
insert predicados 
values
  ('20081010', 'aaa'), ('20081011', 'abb')
, ('20081012', 'acc') ,('20081013', 'add')
, ('20081012', 'baa') ,('20081013', 'bdd')
, ('20081012', 'bcc') ,('20081013', 'bdd')
go

insert predicados
select top 100000 p.d, p.s
from predicados p
cross join master.dbo.spt_values c
cross join master.dbo.spt_values c2
go

select COUNT(*) filas from predicados

 

Ahora, usando un truquillo rápido y “sucio”, haremos creer a SQL Server que tiene 5.000 filas en la tabla, en lugar de las 100.000 reales; para ello usaremos el siguiente comando:

update statistics predicados
with ROWCOUNT  = 5000;

No es habitual usar este comando en escenarios reales, pero es muy útil para escenarios de laboratorio, demostraciones, etc. Si lo usa en sus sistemas, sea consciente de lo que está haciendo.

Ya está el entorno, preparado, ahora nos tocará reproducir el problema, ejecutando la siguiente consulta:

select *
from predicados
order by d desc
option (recompile);

 

Monitorización e identificación del problema

Para identificar y monitorizar el problema lo haremos con tres herramientas diferentes:

  • SQL Server Profiler
  • Planes de ejecución de SQL Server
  • Dynamic Management Views de SQL Server

Para Identificarlo con SQL Server, abra una traz de SQL Server Profiler contra la instancia de SQL Server, e incluya el evento “Sort Warnings”, como se ve en la siguiente figura(recuerde de utilizar los “filtros de columnas” para acotar las mediciones a su propia consulta):

Ponga en marcha la traza y ejecute la consulta anterior, habilitando la opción de mostrar el plan de ejecución (Crtl+M).

Una vez finalizada la consulta, verá en SQL Server Profiler algo como lo siguiente:

Donde se puede comprobar que se ha activado el evento Sort warning durante el procesamiento de la consulta; fíjese que el Sort warning, ha sucedido antes de finalizar la consulta. Por suerte, el equipo de desarrollo de SQL Server son buena gente, y en la traza por defecto de SQL Server – la que siempre (a menos que digas lo contrario) está en marcha en la instancia de SQL Server – registra el evento Sort Warning, por lo que te ayudará a identificar si tu SQL Server tiene o no el problema.

Y el Plan de ejecución obtenido será el siguiente:

Donde puede comprobarse:

  • Que SQL Server estima que va a procesar 5.000 filas, y el realidad procesa 100.000 filas.
  • y que SQL Server estima que el tamaño de datos a procesar es del 117Kb – lo necesario para las 5.000 filas estimadas.

Para ver que realmente este problema está incidiendo el tempdb, vamos a ejecutar la consulta anterior, pero vamos a monitorizar los accesos a disco que se hacen antes y después de la consulta con la Dynamic Management View sys.dm_io_virtual_file_stats; para ello capturaremos la actividad en tempdb antes y después de la operación; el código utilizado es el siguiente:

create table t (id int identity, 
    fileid int, 
    num_of_reads int, num_of_writes int, 
    num_of_bytes_read int, num_of_bytes_written int)
go
truncate table t;
insert into t 
select 
    file_id,  
    num_of_reads, num_of_writes, 
    num_of_bytes_read, num_of_bytes_written
from sys.dm_io_virtual_file_stats (2,-1);
go
select *
from predicados
order by d desc
option (recompile);
go
insert into t 
select 
    file_id,  
    num_of_reads, num_of_writes, 
    num_of_bytes_read, num_of_bytes_written
from sys.dm_io_virtual_file_stats (2,-1)
go
select
    t1.fileid  
    , abs(t1.num_of_reads-t2.num_of_reads) num_of_reads
    , abs(t1.num_of_writes-t2.num_of_writes) num_of_writes
    , abs(t1.num_of_bytes_read-t2.num_of_bytes_read) num_of_bytes_read
    , abs(t1.num_of_bytes_written-t2.num_of_bytes_written) num_of_bytes_written
from t t1, t t2
where t1.id = 1
and t2.id = 3

 

Notas del código:

  • No se utilizan tablas temporales para no inferir en la actividad de tempdb.
  • Sólo se filtra la actividad de tempdb (id de base de datos número 2).
  • En las consulta final, codificamos “a fuego” las filas 1 y 3 porque sabemos que son las de los archivos de datos de tempdb Smile
  • Adapte el código para sus necesidades

Tras la ejecución obtenemos los siguientes resultados:

Donde podemos ver:

  • se han realizado 39 escrituras y 38 lecturas a tempdb.
  • se han escrito y leido casi 2.5Mb a tempdb.

Hecho que acaba demostrando que el Operador Sort, ha utilizado tempdb para realizar la ordenación de filas.

 

Solución

Para este caso concreto la solución es muy sencilla; ejecute la siguiente instrucción, y vuelva a ejecutar la consulta del ORDER BY:

alter index predicados_pk
on predicados rebuild;

Hemos reconstruido el índice; si hubiera ejecutado la instrucción:

update statistics predicados

no se habrían re-actualizado las estadisticas, porque no ha habido modificaciones en la tabla, por lo que las estadísticas permanecerían igual (el engaño de las 5.000 filas). SEA CONSCIENTE del uso de ROWCOUNT en UPDATE STATISTICS.

Si vuelve a ejecutar la consulta, podrá ver ahora que en SQL Server Profiler no aparece más el evento Sort Warning, y el plan de ejecución se parecerá a lo siguiente:

Fíjese en lo siguiente:

  • La estimación de filas es igual que las filas reales.
  • La estimación de los datos a procesar coinciden casi con los 2.5Mb que antes vimos como actividad de Tempdb Smile

Conclusiones

Con un ejemplo rápido y sucio, hemos visto qué efectos puede tener en su SQL Server el operador Sort con estimaciones de filas no apropiadas; considere como desarrollador de bases de datos (DBD) que no sólo el ORDER BY utiliza el operador Sort (si el índice es adecuado, incluso puede suceder que ni siquiera necesite hacer un Sort porque los datos ya vengan ordenados).

Parte de su trabajo consistirá en estudiar sus planes de ejecución, para conocer cómo actúan en SQL Server, qué recursos necesitan, y qué puede hacer para que su sistema sea eficiente.

Finalmente, considere que con estimaciones – estadísticas – adecuadas ayudará a que su SQL Server sea más eficiente; transmita a sus compañeros de BBDD la importancia de tener las estadisticas actualizadas. Estudie los conceptos de actualización de estadisticas automática por base de datos, su actualización al reconstruir índices, o su funcionamiento al ejecutar el comando sp_updatestats.

Comments

#re: Sort Warning in SQL Server: Complicaciones derivadas del Sort Operator
Wednesday, August 10, 2011 - 06:21 PM by Ninfulo
Te felicito por el artículo. Mi pregunta siguiente es, cómo podemos capturar que consultas están haciendo sort warnings y quizás también hash warnings?
#re: Sort Warning in SQL Server: Complicaciones derivadas del Sort Operator
Wednesday, August 10, 2011 - 06:25 PM by Eladio
hola Ninfulo, con las trazas de SQL Profiler debes incluir los eventos Sort Warning y Hash Warning; cuando analices la traza, fíjate en el SPID que ha generado en warning y mira en la traza hacia abajo hasta encontrar el SPID con el RPC.Completed o SQL.Completed. Si no tienes herramientas para ello, tendrás que hacerlo manualmente.
Leave a Comment
(*) Title:
(*) Name:
Your URL:
(*) Comments:
Follow us on: