Estadísticas y Tipos de Datos, Qué Podría Salir Mal?

Posted on 09 de febrero de 2017

One of the most interesting things in SQL Server are Statistics. Today I want to write about the creation and update. Una de las cosas más interesantes en SQL Server desde mi punto de vista son las estadísticas y cómo el optimizador las interpreta para producir planes de consulta óptimos. Pero hoy quiero escribir sobre algo que me acaba de ocurrir y hacer pensar. Esto se refiere a la primera etapa del proceso, la creación.

Si no hemos cambiado nada en nuestras bases de datos (o model), auto create statistics debería estar activado por defecto, lo que significa que SQL Server creará estadísticas automáticamente cuando crea que esa información sería beneficiosa para generar un plan de ejecución.

Esto suena como un regalo, ¿verdad? Claro, es bueno no tener que pensar qué estadísticas tenemos que crear y demás, pero como todo, puede tener un lado oscuro.

 
Antecedentes

Tal vez voy a echar a perder toda la historia, pero así es como sucedieron las cosas.

Estaba revisando los registros del último mantenimiento de la base de datos que incluye, por supuesto, actualizar las estadísticas. Luego vi que había una estadística específica que tardó más de 10 horas en actualizarse, lo que me hizo sospechar. Así que traté de averiguar por qué.

Básicamente todo estaba correcto, y las razones por las que tardó tanto eran

  • La tabla tiene 41 millones de filas
  • Los actualizó usando FULLSCAN

 
No especifiqué FULLSCAN, pero al usar RESAMPLE se usa la última muestra, si esta es FULLSCAN … ahí lo tienes.

OK, entonces tenemos esta estadística llamada _WA_xxxxxxx, por lo tanto, creada automáticamente, en una columna de mi tabla. Y esta columna era (redobles) NVARCHAR (MAX) (badum ts)

Mi tabla contiene documentos xml almacenados en el tipo de datos NVARCHAR (MAX), por lo que mis estadísticas se veían así

01_dbcc_show_statistics

Vea cómo [Rows Sampled] es lo mismo que [Rows], por lo que FULLSCAN se realizó. Y el número no es pequeño en absoluto.

Pero la peor parte es la que se supone que es más útil y donde el optimizador de consultas recopila información de los datos para producir buenos planes de ejecución, el Histograma. En este caso, no parece muy útil, ¿verdad?

Además, debe saber que las estadísticas de las columnas [N]VARCHAR() tienen un máximo de 200 caracteres en RANGE_HI_KEY, por lo que para un tipo de datos MAX como este no tiene sentido

 
Estadísticas automáticas y tipos de datos

Después de ver esto, me preocupé un poco y me pregunté si todos los otros tipos de datos crearán estadísticas automáticamente. ¡Así que decidí crear una tabla con todos los tipos de datos para comprobarlo!

USE master
GO
CREATE DATABASE stats_sample
GO

USE stats_sample
GO
CREATE TABLE stats_sample (c_bigint bigint NULL  DEFAULT CONVERT(bigint, '1')
, c_binary binary NULL  DEFAULT CONVERT(binary, '1')
, c_bit bit NULL  DEFAULT CONVERT(bit, '1')
, c_char char NULL  DEFAULT CONVERT(char, '0')
, c_date date NULL  DEFAULT GETDATE()
, c_datetime datetime NULL  DEFAULT GETDATE()
, c_datetime2 datetime2 NULL  DEFAULT GETDATE()
, c_datetimeoffset datetimeoffset NULL  DEFAULT GETDATE()
, c_decimal decimal NULL  DEFAULT CONVERT(decimal, '1')
, c_float float NULL  DEFAULT CONVERT(float, '1')
, c_geography geography NULL  DEFAULT 'CURVEPOLYGON(CIRCULARSTRING(-1 1, -1 1, -1 1, -1 1, -1 1))'
, c_geometry geometry NULL  DEFAULT geometry::Parse('POINT(1 1 NULL NULL)')
, c_hierarchyid hierarchyid NULL  DEFAULT '/'
, c_image image NULL  DEFAULT CONVERT(image, '1')
, c_int int NULL  DEFAULT CONVERT(int, '1')
, c_money money NULL  DEFAULT CONVERT(money, '1')
, c_nchar nchar NULL  DEFAULT CONVERT(nchar, '0')
, c_ntext ntext NULL  DEFAULT CONVERT(ntext, '0')
, c_numeric numeric NULL  DEFAULT CONVERT(numeric, '1')
, c_nvarchar nvarchar NULL  DEFAULT CONVERT(nvarchar, '0')
, c_real real NULL  DEFAULT CONVERT(real, '1')
, c_smalldatetime smalldatetime NULL  DEFAULT GETDATE()
, c_smallint smallint NULL  DEFAULT CONVERT(smallint, '1')
, c_smallmoney smallmoney NULL  DEFAULT CONVERT(smallmoney, '1')
, c_sql_variant sql_variant NULL  DEFAULT CONVERT(sql_variant, '1')
, c_sysname sysname NULL  DEFAULT CONVERT(sysname, '0')
, c_text text NULL  DEFAULT CONVERT(text, '0')
, c_time time NULL  DEFAULT GETDATE()
, c_timestamp timestamp NULL 
, c_tinyint tinyint NULL  DEFAULT CONVERT(tinyint, '1')
, c_uniqueidentifier uniqueidentifier NULL  DEFAULT NEWID()
, c_varbinary varbinary NULL  DEFAULT CONVERT(varbinary, '1')
, c_varchar varchar NULL  DEFAULT CONVERT(varchar, '0')
, c_xml xml NULL  DEFAULT CONVERT(xml, '1'))
GO

INSERT INTO dbo.stats_sample DEFAULT VALUES
GO 1000

Tened en cuenta que he tenido que crear un valor predeterminado ficticio para poder generar algunas estadísticas.

Una vez que mi tabla esté en su sitio y cargada, las estadísticas automáticas se activarán si el optimizador de consultas cree que sería bueno tenerlas para obtener estimaciones y generar un mejor plan de ejecución, así que ahí vamos

SELECT * FROM stats_sample WHERE c_bigint IS NOT NULL
SELECT * FROM stats_sample WHERE c_binary IS NOT NULL
SELECT * FROM stats_sample WHERE c_bit IS NOT NULL
SELECT * FROM stats_sample WHERE c_char IS NOT NULL
SELECT * FROM stats_sample WHERE c_date IS NOT NULL
SELECT * FROM stats_sample WHERE c_datetime IS NOT NULL
SELECT * FROM stats_sample WHERE c_datetime2 IS NOT NULL
SELECT * FROM stats_sample WHERE c_datetimeoffset IS NOT NULL
SELECT * FROM stats_sample WHERE c_decimal IS NOT NULL
SELECT * FROM stats_sample WHERE c_float IS NOT NULL
SELECT * FROM stats_sample WHERE c_geography IS NOT NULL
SELECT * FROM stats_sample WHERE c_geometry IS NOT NULL
SELECT * FROM stats_sample WHERE c_hierarchyid IS NOT NULL
SELECT * FROM stats_sample WHERE c_image IS NOT NULL
SELECT * FROM stats_sample WHERE c_int IS NOT NULL
SELECT * FROM stats_sample WHERE c_money IS NOT NULL
SELECT * FROM stats_sample WHERE c_nchar IS NOT NULL
SELECT * FROM stats_sample WHERE c_ntext IS NOT NULL
SELECT * FROM stats_sample WHERE c_numeric IS NOT NULL
SELECT * FROM stats_sample WHERE c_nvarchar IS NOT NULL
SELECT * FROM stats_sample WHERE c_real IS NOT NULL
SELECT * FROM stats_sample WHERE c_smalldatetime IS NOT NULL
SELECT * FROM stats_sample WHERE c_smallint IS NOT NULL
SELECT * FROM stats_sample WHERE c_smallmoney IS NOT NULL
SELECT * FROM stats_sample WHERE c_sql_variant IS NOT NULL
SELECT * FROM stats_sample WHERE c_sysname IS NOT NULL
SELECT * FROM stats_sample WHERE c_text IS NOT NULL
SELECT * FROM stats_sample WHERE c_time IS NOT NULL
SELECT * FROM stats_sample WHERE c_timestamp IS NOT NULL
SELECT * FROM stats_sample WHERE c_tinyint IS NOT NULL
SELECT * FROM stats_sample WHERE c_uniqueidentifier IS NOT NULL
SELECT * FROM stats_sample WHERE c_varbinary IS NOT NULL
SELECT * FROM stats_sample WHERE c_varchar IS NOT NULL
SELECT * FROM stats_sample WHERE c_xml IS NOT NULL
GO

Genial, eso debería ser suficiente. Veamos si tenemos estadísticas en nuestra tabla o no.

SELECT OBJECT_NAME(stc.object_id) AS object_name, st.auto_created, st.name AS stats_name, c.name, t.name
    FROM sys.stats_columns AS stc
        INNER JOIN sys.stats AS st
            ON st.object_id = stc.object_id
                AND st.stats_id = stc.stats_id
        INNER JOIN sys.columns AS c
            ON c.object_id = stc.object_id
                AND c.column_id = stc.column_id 
        INNER JOIN sys.types AS t
            ON t.user_type_id = c.user_type_id  
    WHERE stc.object_id = OBJECT_ID('dbo.stats_sample')

02_auto_created_stats

Sí, ahí están, todas estas _WA_Sys_ estadísticas me dicen que se han creado automáticamente (hay una bandera en sys.stats si no me crees) pero puedo ver que solo hay 31, donde creé 34 columnas.

Es gracioso, veamos qué tipos de datos obtuvieron estadísticas.

SELECT * 
	FROM sys.types AS t
		LEFT JOIN (SELECT c.user_type_id
						FROM sys.stats_columns AS stc
							INNER JOIN sys.columns AS c
								ON c.object_id = stc.object_id
									AND c.column_id = stc.column_id 
						WHERE stc.object_id = OBJECT_ID('dbo.stats_sample')) AS st
			ON st.user_type_id = t.user_type_id
	WHERE st.user_type_id IS NULL

03_data_types_without_statistics

 
Allí están. Los tipos de datos XML y CLR (geometry y geography son CLR) no tienen estadísticas, lo que significa que tampoco puede crearlas manualmente.

Solo por el gusto de comprobarlo.

CREATE STATISTICS st_stats_sample_geometry		ON dbo.stats_sample (c_geometry)
GO
CREATE STATISTICS st_stats_sample_geography		ON dbo.stats_sample (c_geography)
GO
CREATE STATISTICS st_stats_sample_xml			ON dbo.stats_sample (c_xml)
GO

04_create_stats_error

 
Así que aparte de estos tipos de datos especiales, el resto acaba de crear las estadísticas sin ninguna pregunta, para bien o para mal.

 
La parte buena

Bastante obvio, las estadísticas son muy importantes para el optimizador de consultas, por lo que el hecho de sacarlas de la estantería (es el comportamiento predeterminado) es una gran ganancia.

 
La parte mala

A veces, las estadísticas no son tan útiles como nos gustaría, y si lo mezclamos con algunas otras malas prácticas, como no usar índices de texto completo para [N]VARCHAR(MAX) y consultas como cualquier otra columna, lo que activará automáticamente -crear estadísticas, el resultado puede ser un desastre.

 
¿Y qué hacemos?

¿Es hora de entrar en pánico y correr en círculos? Tampoco es para tanto.

Compruebad vuestro mantenimiento y sus estadísticas en general, si estáis en SQL 2008R2 SP2, SQL 2012 SP1 o superior, puede usar DMV dm_db_stats_properties, lo cual es genial, pero para versiones anteriores puede que tengáis que escribir algo vosotros mismos usando DBCC SHOW_STATISTICS

Alguna consulta para comenzar podría ser algo así

SELECT OBJECT_NAME(st.object_id) AS [object_name]
		, st.name AS stats_name
		, st.object_id
		, st.stats_id
		, stp.last_updated
		, stp.rows
		, stp.rows_sampled
		, stp.modification_counter
		-- , *
	FROM sys.stats AS st
		CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) AS stp
	WHERE OBJECTPROPERTYEX(st.object_id, 'IsMSShipped') = 0

Si detectáis algo sospechoso, todavía hay algunas cosas que podemos hacer al respecto. Este post se está alargando bastante, pero ya total… vamos a sumergirnos en una ligera charla teórica.

 
Auto Update Stats

Probablemente uséis vuestros propios scripts o los de Ola Hallengren para mantener índices y estadísticas, pero si no lo hacéis, SQL Server aún quiere que tengáis las estadísticas actualizadas, por lo que activará automáticamente las estadísticas de actualización cuando estén desactualizadas.

Entonces, ¿cuando caducan las estadísticas? En pocas palabras, más o menos cuando hay una relación del 20% entre filas y el contador de modificaciones en dm_db_stats_properties, y esto es importante porque BOL indica

  • SQL Server (2014 y anterior) usa un umbral basado en el porcentaje de filas modificadas. Esto es independientemente del número de filas en la tabla.
  • SQL Server (comenzando en 2016 y bajo el nivel de compatibilidad 130) usa un umbral que se ajusta según el número de filas en la tabla. Con este cambio, las estadísticas en tablas grandes se actualizarán más a menudo.

 
Ved cómo se habla sobre el porcentaje de filas cambiadas. Y eso es engañoso, al menos para mí, ya que cada modificación en esa columna cuenta, por lo que si modificas la misma fila una y otra vez, el contador seguirá aumentando, y mientras el resto de la tabla no se modifique, actualiza automáticamente las estadísticas ( o incluso vuestros scripts) creerán que es hora de actualizar.

Y de nuevo en BOL, eso sucederá antes de que se ejecute la consulta que queremos ejecutar, a menos que AUTO_UPDATE_STATISTICS_ASYNC esté activado, y la actualización se retrase a otro momento.

Ahora imagínaos si la actualización de mis estadísticas de 10 horas se
se activan porque alguien ejecuta una consulta en vez de durante una ventana de mantenimiento … cosa fea.

Todavía podemos hacer algunos trucos, como actualizar las estadísticas manualmente sin filas y NORECOMPUTE, y eso matará sus estadísticas hasta que se revierta, pero tened cuidado y no salteís corriendo a hacerlo antes de evaluar todas las consecuencias.

 
Conclusión

Como la mayoría de las veces, no hay una solución única que se adapte a todas las situaciones, por lo tanto, a medida que tengamos una mejor comprensión de los aspectos internos, podremos realizar una mejor elección y predecir las consecuencias de nuestra elección.

Algunos enlaces útiled como referencia

 
Gracias por leer y si tenéis alguna pregunta, por favor usad los comentarios a continuación.

 

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.