🥪 📯 👈🏻 varchar (max) -varchar (max) y en producción 👩🏾‍⚕️ 🚬 👩🏻

Recientemente participé en una discusión sobre el impacto en el rendimiento de especificar longitudes en columnas nvarchar. Los argumentos fueron razonables en ambos lados, y como tenía tiempo libre, decidí probar un poco. El resultado fue esta publicación.

Spoiler: no es tan simple.

Todas las pruebas se realizaron en SQL Server 2014 Developer Edition, se obtuvieron aproximadamente los mismos resultados en SQL Server 2016 (con ligeras diferencias). Lo siguiente debería ser relevante para SQL Server 2005-2016 (y las pruebas se requieren en 2017/2019, ya que han aparecido las adaptaciones de memoria adaptativa, lo que puede corregir algo la situación).

Necesitamos un procedimiento almacenado de Erik Darling sp_pressure_detector , que le permite obtener mucha información sobre el estado actual del sistema ySQL Query Stress es una utilidad de código abierto muy buena Adam Machanic / Erik Ejlskov Jensen para la prueba de carga de MS SQL Server.

De qué estamos hablando

La pregunta que estoy tratando de responder es si la elección de la longitud del campo (n) varchar afecta el rendimiento (en adelante, simplemente varchar está en todas partes, aunque todo también es relevante para nvarchar), o puede usar varchar (max) y no steam, porque si la longitud de la cadena es <8000 (4000 para nvarchar) caracteres, luego varchar (max) y varchar (N) se almacenan EN FILA.

Puesto de cocina

create table ##v10  (i int, d datetime, v varchar(10));
create table ##v100 (i int, d datetime, v varchar(100));
create table ##vmax (i int, d datetime, v varchar(max));

Creamos 3 tablas de tres campos, la diferencia está solo en la longitud de varchar: 10/100 / max. Y llénalos con los mismos datos:

;with x as (select 1 x union all select 1)
, xx as (select 1 x from x x1, x x2)
, xxx as (select 1 x from xx x1, xx x2, xx x3)
, xxxx as (
	select row_number() over(order by (select null)) i
		, dateadd(second, row_number() over(order by (select null)), '20200101') d
		, cast (row_number() over(order by (select null)) as varchar(10))  v 		
	from xxx x1, xxx x2, xxx x3
) --262144 
insert into ##v10			--varchar(10)
select i, d, v from xxxx;	

insert into ##v100			--varchar(100)
select i, d, v from ##v10;

insert into ##vmax			--varchar(max)
select i, d, v from ##v10;

Como resultado, cada tabla contendrá 262144 filas. La columna I (entero) contiene números no repetidos del 1 al 262145; d (datetime) fechas únicas y v (varchar) - cast (I como varchar (10)). Para hacerlo un poco más parecido a la vida real, cree un índice de clúster único en i:

create unique clustered index #cidx10 on ##v10(i);
create unique clustered index #cidx100 on ##v100(i);
create unique clustered index #cidxmax on ##vmax(i);

Vamos

Primero, veamos los planes para la ejecución de diferentes solicitudes.

Primero, verifique que la selección por campo varchar no dependa de su longitud (si <8000 caracteres están almacenados allí). Incluimos un plan de ejecución válido y miramos:

select * from ##v10 where v = '123';
select * from ##v100 where v = '123';
select * from ##vmax where v = '123';

Curiosamente, la diferencia, aunque pequeña, está ahí. El plan de consulta con varchar (max) primero selecciona todas las filas y luego las filtra, y varchar (10) y varchar (100) verifican las coincidencias al escanear el índice agrupado. Debido a esto, el escaneo tarda casi 3 veces más: 0.068 segundos versus 0.022 para varchar (10).

Ahora veamos qué sucede si simplemente mostramos la columna varchar y seleccionamos los datos por la clave de índice del clúster:

select * from ##v10  where i between 200000 and 201000;
select * from ##v100 where i between 200000 and 201000;
select * from ##vmax where i between 200000 and 201000;

Aquí todo está claro: no hay diferencia para tales solicitudes.

Ahora para la parte interesante. En la solicitud anterior, obtuvimos solo 1001 filas, y ahora queremos ordenarlas por columna no indexada. Intentamos:

select * from ##v10  where i between 200000 and 201000 order by d;
select * from ##v100 where i between 200000 and 201000 order by d;
select * from ##vmax where i between 200000 and 201000 order by d;

¿Y qué es tan amarillo?

Divertido, es decir la solicitud solicitó y recibió 6.5 megabytes de RAM para la clasificación, y solo usó 96 kilobytes. Y cuánto peor será si hay más líneas. Bueno, que no sea 1000, sino 100000:

Pero aquí es más serio. Además, la primera solicitud que funciona con el varchar más pequeño (10) tampoco está satisfecha con algo:

A la izquierda hay una advertencia de la última solicitud: se solicitaron 500 megabytes y solo se usaron 9,5 megabytes. Y a la derecha hay una advertencia de clasificación: se solicitaron 8840 kilobytes, pero no había suficientes y se escribieron y leyeron otras 360 páginas (8 kb cada una) de tempdb.

Y aquí surge la pregunta: ¿WTF?

La respuesta es cómo funciona el Optimizador de consultas de SQL Server. Para ordenar algo, primero debe poner algo en la memoria. ¿Cómo entender cuánta memoria se necesita? En general, sabemos cuánto tipo de datos ocupa espacio. Pero, ¿qué pasa con las cadenas de longitud variable? Pero es más interesante con ellos. Al asignar memoria para los tipos de unión / hash, SQL Server considera que están medio llenos en promedio. Y asigna memoria para ellos como (tamaño / 2) * el número esperado de líneas. Pero varchar (max) puede almacenar hasta 2 GB: ¿cuánto asignar? SQL Server cree que habrá la mitad de varchar (8000), es decir aproximadamente 4 kb por línea.

Lo interesante es que esta asignación de memoria genera problemas no solo con varchar (max), si el tamaño de sus varchars se elige con amor para que la mayoría de ellos estén medio llenos y más grandes, esto también genera problemas. Problemas de un plan diferente, pero no menos grave. En la figura anterior hay una descripción: SQL Server no pudo asignar correctamente la memoria para ordenar un pequeño varchar y usó tempdb para almacenar resultados intermedios. Si tempdb se encuentra en discos lentos, o es utilizado activamente por otras solicitudes, esto puede convertirse en un cuello de botella.

Estrés de consulta SQL

Ahora veamos qué sucede cuando se ejecutan consultas masivas. Ejecute el Estrés de consultas SQL, conéctelo a nuestro servidor y diga ejecutar todas estas consultas 10 veces en 50 hilos.

Los resultados de la primera consulta:

Es interesante, pero sin índices, cuando se busca, varchar (max) se muestra peor que nadie, y es mucho peor en términos de tiempo de procesador para la iteración y el tiempo de ejecución general.

sp_pressure_detector no muestra nada interesante aquí, así que no cito su salida.
Resultados de la segunda consulta:

Aquí se espera todo, igual de bueno.

Ahora para la parte interesante. Una consulta con la clasificación de las mil filas resultantes:

Todo resultó ser exactamente igual que con la solicitud anterior: no hay muchas líneas, la clasificación no causa problemas.

Ahora, la última consulta que clasifica de manera irrazonable muchas filas (agregué las 1000 principales para no extraer toda la lista ordenada):

Y aquí está la salida de sp_pressure_detector:

¿Qué nos dice él? Todas las sesiones solicitan 489 MB cada una (para ordenar), pero solo 22 de ellas tenían suficiente memoria para SQL Server, ¡incluso teniendo en cuenta que todas estas 22 sesiones usan solo 9 MB cada una!
Se dispone de un total de 11 GB de memoria, se asignaron 229 sesiones a 489.625 cada una y SQL Server solo tenía 258 megabytes disponibles, y también quieren obtener nuevas sesiones para 489. ¿Qué debo hacer? Espere hasta que la memoria se libere; esperan, sin siquiera comenzar a ejecutarse. ¿Qué harán los usuarios si tales solicitudes se realizan en sus sesiones? Demasiado para esperar.

Por cierto, preste atención a la figura con varchar (10), las solicitudes con varchar (10) tomaron más tiempo que las solicitudes con varchar (100), y esto a pesar del hecho de que tengo tempdb en un disco muy rápido. Cuanto peor sea la unidad en tempdb, más lenta se ejecutará la consulta.

Nota separada para SQL Server 2012/2014

SQL Server 2012/2014 sort spills. char/nchar – spill’ tempdb. MS , , .

:

create table ##c6  (i int, d datetime, v char(6));
insert into ##c6 (i, d, v)
select i, d, v
from ##v10
select * from ##c6 where i between 100000 and 200000 order by d;

( ):

DBCC TRACEON (7470, -1);

, spill’ .

recomendaciones

Tenga cuidado al ordenar sus consultas donde tenga (n) columnas varchar. Si aún se necesita la clasificación, es altamente deseable que la columna de clasificación tenga un índice.

Tenga en cuenta que para obtener una ordenación no es necesario utilizar explícitamente el orden por: su apariencia también es posible con combinaciones de fusión, por ejemplo. El mismo problema con la asignación de memoria también es posible con las combinaciones hash, por ejemplo, con varchar (max):

select top 100 * 
from ##vmax v1
inner hash join ##v10 v2 on v1.i = v2.i

¡2.5 GB de memoria asignada, 25 megabytes usados!

La principal conclusión para mí : el tamaño de la columna (n) varchar - ¡IMPORTANTE! Si el tamaño es demasiado pequeño, son posibles los derrames en tempdb; si es demasiado grande, las solicitudes de memoria son demasiado grandes. Si hay algún tipo, sería conveniente declarar la longitud de varchar como la longitud promedio de registro * 2, y en el caso de SQL Server 2012/2014, incluso más.

Conclusión inesperada para mí : varchar (max), que contiene menos de 8000 caracteres, en realidad funciona más lentamente, con filtros. Todavía no sé cómo explicarlo. Cavaré más.

Retiro de bonificación para mí: ya casi haciendo clic en "publicar", pensé que incluso con varchar (max) puede experimentar el problema de "small varchar'a". De hecho, cuando se almacena en varchar (max) más de 4000 caracteres (2000 para nvarchar), la clasificación puede ser un problema.

insert into ##vmax(i, d, v)
select i, d, replicate('a', 4000) v
from ##v10;

select * from ##vmax where i between 200000 and 201000 order by d;

truncate table ##vmax;

insert into ##vmax(i, d, v)
select i, d, replicate('a', 4100) v
from ##v10;

select * from ##vmax where i between 200000 and 201000 order by d;

¿Por qué al principio escribí que no todo es tan simple? Porque, por ejemplo, en la computadora portátil de mi casa con un disco medio muerto, los derrames en tempdb al ordenar varchar "pequeño" llevaron al hecho de que tales solicitudes se ejecutaron ORDEN más lento que solicitudes similares con varchar (max). Si tiene un buen hardware, puede que no sea un problema, pero no debe olvidarse de ellos.

Lo que sería aún más interesante es ver si hay algún problema debido a los tamaños demasiado grandes / pequeños de los varchars en otros DBMS. Si tiene la oportunidad de comprobarlo, me alegrará compartirlo.

Pequeño bono

Desafortunadamente, no es posible detectar tales problemas usando el caché del plan de consulta. Aquí hay ejemplos de planes del caché: por desgracia, no hay advertencias en ellos.