Nota:
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
En este artículo se proporcionan recomendaciones y ejemplos para crear y actualizar estadísticas de optimización de consultas mediante los recursos de Synapse SQL: grupo de SQL dedicado y grupo de SQL sin servidor.
Estadísticas en un almacén de datos SQL dedicado
¿Por qué usar estadísticas?
Cuanto más sepa el grupo de SQL dedicado acerca de los datos, más rápido puede ejecutar las consultas. Después de cargar datos en un grupo de SQL dedicado, la recopilación de estadísticas de los datos es una de las cosas más importantes que puede hacer para la optimización de consultas.
El optimizador de consultas del grupo de SQL dedicado se basa en el costo. Compara el costo de varios planes de consulta y elige el menor de ellos. En la mayoría de los casos, elige el plan que ejecutará más rápido.
Por ejemplo, si el optimizador considera que la fecha en que se filtra la consulta devolverá una fila, se elegirá un plan. Si estima que la fecha seleccionada devolverá 1 millón de filas, devolverá un plan diferente.
Creación automática de estadísticas
El motor del grupo de SQL dedicado analizará las consultas entrantes de los usuarios para buscar las estadísticas que faltan cuando la opción AUTO_CREATE_STATISTICS de la base de datos esté establecida en ON. Si faltan estadísticas, el optimizador de consultas crea las estadísticas en columnas individuales del predicado de consulta o en la condición de combinación.
Esta función se usa para mejorar las estimaciones de cardinalidad del plan de consulta.
Importante
Actualmente, la creación automática de estadísticas está activada de forma predeterminada.
Puede comprobar si su almacenamiento de datos tiene configurado AUTO_CREATE_STATISTICS; para ello, ejecute el siguiente comando:
SELECT name, is_auto_create_stats_on
FROM sys.databases
Si el almacenamiento de datos no tiene AUTO_CREATE_STATISTICS habilitado, se recomienda habilitar esta propiedad mediante la ejecución del comando siguiente:
ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON
Estas instrucciones desencadenarán la creación automática de estadísticas:
- Seleccionar
- INSERT-SELECT
- CTAS
- ACTUALIZACIÓN
- ELIMINAR
- EXPLAIN cuando se detecta una combinación o la presencia de un predicado
Nota:
La creación automática de estadísticas no se genera en tablas temporales o externas.
La creación automática de estadísticas se realiza de forma sincrónica. Por lo tanto, es posible que experimente un rendimiento de consultas ligeramente reducido si a las columnas les faltan estadísticas. El tiempo para crear estadísticas de una sola columna depende del tamaño de la tabla.
Para evitar la degradación del rendimiento cuantificable, debe asegurarse de que antes se han creado estadísticas. Para ello, ejecute la carga de trabajo de la prueba comparativa antes de generar los perfiles del sistema.
Nota:
La creación de estadísticas también se registra en sys.dm_pdw_exec_requests en un contexto de usuario diferente.
Cuando se creen estadísticas automáticas, tendrán el formato: WA_Sys<identificador de columna de 8 dígitos en Hex>_<identificador de tabla de 8 dígitos en Hex>. Para ver estadísticas ya creadas, ejecute el comando DBCC SHOW_STATISTICS :
DBCC SHOW_STATISTICS (<table_name>, <target>)
El table_name es el nombre de la tabla que contiene las estadísticas que se van a mostrar, que no pueden ser una tabla externa. El destino es el nombre del índice, de las estadísticas o de la columna de destino para los que se va a mostrar información estadística.
Actualizar estadísticas
Uno de los procedimientos recomendados es la actualización diaria de estadísticas en columnas de fecha al agregarse nuevas fechas. Cada vez que se cargan nuevas filas en el almacenamiento de datos, se agregan nuevas fechas de carga o fechas de transacción. Estas incorporaciones cambian la distribución de los datos y hacen que las estadísticas se queden obsoletas.
Es posible que las estadísticas de una columna de país o región de una tabla de clientes no necesiten actualizarse porque la distribución de valores no suele cambiar. Suponiendo que la distribución es constante entre los clientes, agregar nuevas filas a la variación de tabla no va a cambiar la distribución de datos.
Sin embargo, cuando el almacenamiento de datos solo contiene un país o región y trae datos de un nuevo país o región, deberá actualizar las estadísticas en la columna de país o región.
A continuación se incluyen recomendaciones para actualizar las estadísticas:
| Tipo | Recomendación |
|---|---|
| Frecuencia de actualizaciones de estadísticas | Conservador: diario Después de cargar o transformar los datos |
| Muestreo | Con menos de mil millones de filas, use el muestreo predeterminado (20 por ciento)
Con más de mil millones de filas, use el muestreo del dos por ciento. |
Determinación de la última actualización de estadísticas
Una de las primeras preguntas que debe formular al solucionar problemas de una consulta es "¿Están actualizadas las estadísticas?"
Esta pregunta no es una que se pueda responder por la antigüedad de los datos. Un objeto de estadísticas actualizadas podría ser antiguo si no ha habido ningún cambio material en los datos subyacentes. Cuando el número de filas ha cambiado sustancialmente o se produce un cambio material en la distribución de valores de una columna, es el momento de actualizar las estadísticas.
No hay una vista de administración dinámica disponible para determinar si los datos de la tabla han cambiado desde la última vez que se actualizaron las estadísticas. Conocer la edad de sus estadísticas puede proporcionarle una parte del panorama.
Puede usar la consulta siguiente para determinar la última vez que se actualizaron las estadísticas en cada tabla.
Nota:
Si hay un cambio material en la distribución de valores de una columna, debe actualizar las estadísticas independientemente de la última vez que se actualizaron.
SELECT
sm.[name] AS [schema_name],
tb.[name] AS [table_name],
co.[name] AS [stats_column_name],
st.[name] AS [stats_name],
STATS_DATE(st.[object_id],st.[stats_id]) AS [stats_last_updated_date]
FROM
sys.objects ob
JOIN sys.stats st
ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns sc
ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns co
ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types ty
ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables tb
ON co.[object_id] = tb.[object_id]
JOIN sys.schemas sm
ON tb.[schema_id] = sm.[schema_id]
WHERE
st.[user_created] = 1;
Las columnas de fecha de un almacenamiento de datos, por ejemplo, suelen necesitar actualizaciones frecuentes de estadísticas. Cada vez que se cargan nuevas filas en el almacenamiento de datos, se agregan nuevas fechas de carga o fechas de transacción. Estas incorporaciones cambian la distribución de los datos y hacen que las estadísticas se queden obsoletas.
Es posible que nunca sea necesario actualizar las estadísticas de una columna de género de una tabla de clientes. Suponiendo que la distribución es constante entre los clientes, agregar nuevas filas a la variación de tabla no va a cambiar la distribución de datos.
Sin embargo, si el almacenamiento de datos contiene solo un género y un nuevo requisito da como resultado varios géneros, debe actualizar las estadísticas en la columna de género.
Para obtener más información, consulte el artículo Estadísticas .
Implementación de la administración de estadísticas
A menudo resulta conveniente extender el proceso de carga de datos para garantizar que las estadísticas están actualizadas al final de la carga. La carga de datos se produce cuando las tablas cambian su tamaño o la distribución de los valores con mayor frecuencia. Por lo tanto, el proceso de carga es un lugar lógico para implementar algunos procesos de administración.
Los siguientes principios fundamentales se proporcionan para actualizar las estadísticas durante el proceso de carga:
- Asegúrese de que cada tabla cargada tiene al menos un objeto de estadísticas actualizado. Este proceso actualiza la información del tamaño de las tablas (recuento de filas y recuento de páginas) como parte de la actualización de las estadísticas.
- Céntrese en las columnas que participan en las cláusulas JOIN, GROUP BY, ORDER BY y DISTINCT.
- Considere la posibilidad de actualizar con más frecuencia las columnas de "clave ascendente", como las fechas de transacción, ya que estos valores no se incluirán en el histograma de estadísticas.
- Considere la posibilidad de actualizar columnas de distribución estática con menor frecuencia.
- Recuerde que cada objeto de estadística se actualiza en secuencia. Implementar solo
UPDATE STATISTICS <TABLE_NAME>no es siempre recomendable, especialmente para las tablas amplias con muchos objetos de estadísticas.
Para obtener más información, consulte Estimación de cardinalidad.
Ejemplos: Creación de estadísticas
Estos ejemplos muestran cómo utilizar diversas opciones de creación de estadísticas. Las opciones que se usan para cada columna dependen de las características de los datos y de cómo se usará la columna en las consultas.
Crear estadísticas de columna única con las opciones predeterminadas
Para crear estadísticas de una columna, especifique un nombre para el objeto de estadística y el nombre de la columna. Esta sintaxis utiliza todas las opciones predeterminadas. De forma predeterminada, el grupo de SQL dedicado muestra el 20 % de la tabla cuando crea estadísticas.
CREATE STATISTICS [statistics_name]
ON [schema_name].[table_name]([column_name]);
Por ejemplo:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1);
Crear estadísticas de columna única mediante el examen de todas las filas
La velocidad de muestreo predeterminada del 20 % es suficiente para la mayoría de las situaciones. Sin embargo, puede ajustar la velocidad de muestreo. Para probar la tabla completa, utilice esta sintaxis:
CREATE STATISTICS [statistics_name]
ON [schema_name].[table_name]([column_name])
WITH FULLSCAN;
Por ejemplo:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1)
WITH FULLSCAN;
Crear estadísticas de columna única especificando el tamaño de muestra
Otra opción que tiene es especificar el tamaño de la muestra como porcentaje:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1)
WITH SAMPLE 50 PERCENT;
Crear estadísticas de columna única solo en algunas filas
También puede crear estadísticas en una parte de las filas de la tabla, que se denomina estadística filtrada.
Por ejemplo, puede utilizar las estadísticas filtradas si piensa consultar una partición específica de una tabla grande con particiones. Al crear estadísticas solo en los valores de partición, la precisión de las estadísticas mejorará. También experimentará una mejora en el rendimiento de las consultas.
En este ejemplo se crean estadísticas sobre un intervalo de valores. Los valores pueden definirse fácilmente para que coincidan con el intervalo de valores de una partición.
CREATE STATISTICS stats_col1
ON table1(col1)
WHERE col1 > '2000101' AND col1 < '20001231';
Nota:
Para que el optimizador de consultas considere utilizar estadísticas filtradas al elegir el plan de consulta distribuida, la consulta debe adecuarse a la definición del objeto de estadísticas. Usando el ejemplo anterior, la cláusula WHERE de la consulta tiene que especificar valores de col1 entre 2 000 101 y 20 001 231.
Crear estadísticas de columna única con todas las opciones
También puede combinar las opciones. En el ejemplo siguiente se crea un objeto de estadísticas filtradas con un tamaño personalizado de ejemplo:
CREATE STATISTICS stats_col1
ON table1 (col1)
WHERE col1 > '2000101' AND col1 < '20001231'
WITH SAMPLE 50 PERCENT;
Para obtener la referencia completa, consulte CREATE STATISTICS.
Crear estadísticas de varias columnas
Para crear un objeto de estadísticas de varias columnas, use los ejemplos anteriores, pero indique más columnas.
Nota:
El histograma, que se utiliza para calcular el número de filas en el resultado de la consulta, solo está disponible para la primera columna de la definición del objeto de estadísticas.
En este ejemplo, el histograma está en product_category. Las estadísticas entre columnas se calculan en product_category y product_sub_category:
CREATE STATISTICS stats_2cols
ON table1 (product_category, product_sub_category)
WHERE product_category > '2000101' AND product_category < '20001231'
WITH SAMPLE 50 PERCENT;
Dado que existe una correlación entre product_category y product_sub_category, un objeto de estadísticas de varias columnas puede ser útil si se accede a estas columnas al mismo tiempo. Al consultar esta tabla, las estadísticas de varias columnas mejorarán las estimaciones de cardinalidad para combinaciones, agregaciones GROUP BY, recuentos distintos y filtros WHERE (siempre y cuando la columna de estadísticas principal forme parte del filtro).
Creación de estadísticas en todas las columnas de una tabla
Una manera de crear estadísticas es emitir comandos CREATE STATISTICS después de crear la tabla:
CREATE TABLE dbo.table1
(
col1 int
, col2 int
, col3 int
)
WITH
(
CLUSTERED COLUMNSTORE INDEX
)
;
CREATE STATISTICS stats_col1 on dbo.table1 (col1);
CREATE STATISTICS stats_col2 on dbo.table2 (col2);
CREATE STATISTICS stats_col3 on dbo.table3 (col3);
Usar un procedimiento almacenado para crear estadísticas en todas las columnas de una base de datos
Un grupo de SQL no tiene un procedimiento almacenado del sistema equivalente a sp_create_stats en SQL Server. Este procedimiento almacenado crea un único objeto de estadísticas de columna en cada columna de la base de datos que aún no tiene estadísticas.
El ejemplo siguiente le ayudará a empezar a trabajar con el diseño de la base de datos. No dude en adaptarlo a sus necesidades:
CREATE PROCEDURE [dbo].[prc_sqldw_create_stats]
( @create_type tinyint -- 1 default, 2 Fullscan, 3 Sample
, @sample_pct tinyint
)
AS
IF @create_type IS NULL
BEGIN
SET @create_type = 1;
END;
IF @create_type NOT IN (1,2,3)
BEGIN
THROW 151000,'Invalid value for @stats_type parameter. Valid range 1 (default), 2 (fullscan) or 3 (sample).',1;
END;
IF @sample_pct IS NULL
BEGIN;
SET @sample_pct = 20;
END;
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN;
DROP TABLE #stats_ddl;
END;
CREATE TABLE #stats_ddl
WITH ( DISTRIBUTION = HASH([seq_nmbr])
, LOCATION = USER_DB
)
AS
WITH T
AS
(
SELECT t.[name] AS [table_name]
, s.[name] AS [table_schema_name]
, c.[name] AS [column_name]
, c.[column_id] AS [column_id]
, t.[object_id] AS [object_id]
, ROW_NUMBER()
OVER(ORDER BY (SELECT NULL)) AS [seq_nmbr]
FROM sys.[tables] t
JOIN sys.[schemas] s ON t.[schema_id] = s.[schema_id]
JOIN sys.[columns] c ON t.[object_id] = c.[object_id]
LEFT JOIN sys.[stats_columns] l ON l.[object_id] = c.[object_id]
AND l.[column_id] = c.[column_id]
AND l.[stats_column_id] = 1
LEFT JOIN sys.[external_tables] e ON e.[object_id] = t.[object_id]
WHERE l.[object_id] IS NULL
AND e.[object_id] IS NULL -- not an external table
)
SELECT [table_schema_name]
, [table_name]
, [column_name]
, [column_id]
, [object_id]
, [seq_nmbr]
, CASE @create_type
WHEN 1
THEN CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+')' AS VARCHAR(8000))
WHEN 2
THEN CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH FULLSCAN' AS VARCHAR(8000))
WHEN 3
THEN CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH SAMPLE '+CONVERT(varchar(4),@sample_pct)+' PERCENT' AS VARCHAR(8000))
END AS create_stat_ddl
FROM T
;
DECLARE @i INT = 1
, @t INT = (SELECT COUNT(*) FROM #stats_ddl)
, @s NVARCHAR(4000) = N''
;
WHILE @i <= @t
BEGIN
SET @s=(SELECT create_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);
PRINT @s
EXEC sp_executesql @s
SET @i+=1;
END
DROP TABLE #stats_ddl;
Para crear estadísticas de todas las columnas de la tabla mediante los valores predeterminados, ejecute el procedimiento almacenado.
EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;
Para crear estadísticas en todas las columnas de la tabla mediante un escaneo completo, ejecute este procedimiento:
EXEC [dbo].[prc_sqldw_create_stats] 2, NULL;
Para crear estadísticas muestreadas en todas las columnas de la tabla, escriba 3 y el porcentaje de ejemplo. El procedimiento siguiente usa una frecuencia de muestreo del 20 %.
EXEC [dbo].[prc_sqldw_create_stats] 3, 20;
Ejemplos: Actualizar estadísticas
Para actualizar estadísticas, puede:
- Actualizar un objeto de estadísticas. Especificar el nombre del objeto de estadísticas que desea actualizar.
- Actualizar todos los objetos de estadísticas de una tabla. Especificar el nombre de la tabla en lugar de un objeto de estadísticas específico.
Actualizar un objeto de estadísticas específico
Para actualizar un objeto de estadísticas específico, use la siguiente sintaxis:
UPDATE STATISTICS [schema_name].[table_name]([stat_name]);
Por ejemplo:
UPDATE STATISTICS [dbo].[table1] ([stats_col1]);
Al actualizar los objetos de estadísticas específicos, puede minimizar el tiempo y los recursos necesarios para administrar las estadísticas. Esta acción requiere algo de pensamiento para seleccionar los mejores objetos de estadísticas que se van a actualizar.
Actualizar todas las estadísticas en una tabla
Un método sencillo para actualizar todos los objetos de estadísticas de una tabla es el siguiente:
UPDATE STATISTICS [schema_name].[table_name];
Por ejemplo:
UPDATE STATISTICS dbo.table1;
La instrucción UPDATE STATISTICS es fácil de usar. Recuerde que actualiza todas las estadísticas de la tabla, lo que solicita más trabajo de lo necesario.
Si el rendimiento no es un problema, este método es la manera más sencilla y completa de garantizar que las estadísticas están actualizadas.
Nota:
Al actualizar todas las estadísticas de una tabla, el grupo de SQL dedicado realiza un examen para muestrear la tabla de cada objeto de estadísticas. Si la tabla es grande y tiene muchas columnas y estadísticas, puede resultar más eficaz actualizar las estadísticas individualmente en función de las necesidades.
Para obtener una implementación de un UPDATE STATISTICS procedimiento, consulte Tablas temporales. El método de implementación difiere ligeramente del procedimiento CREATE STATISTICS, pero el resultado es el mismo.
Para obtener la sintaxis completa, vea Actualizar estadísticas.
Metadatos de las estadísticas
Hay varias funciones y vistas del sistema que puede usar para encontrar información sobre las estadísticas. Por ejemplo, puede ver si un objeto de estadísticas podría estar obsoleto mediante la función STATS_DATE(). STATS_DATE() permite ver cuándo se crearon o actualizaron las estadísticas por última vez.
Vistas de catálogo para las estadísticas
Estas vistas del sistema proporcionan información acerca de las estadísticas:
| Vista de catálogo | Descripción |
|---|---|
| sys.columns | Una fila para cada columna. |
| sys.objects | Una fila para cada objeto de la base de datos. |
| sys.schemas | Una fila para cada esquema de la base de datos. |
| sys.stats | Una fila para cada objeto de estadísticas. |
| sys.stats_columns | Una fila para cada columna del objeto de estadísticas. Vínculos a la sys.columns. |
| sys.tables | Una fila para cada tabla (incluye tablas externas). |
| sys.table_types | Una fila para cada tipo de datos. |
Funciones del sistema para las estadísticas
Estas funciones del sistema son útiles para trabajar con las estadísticas:
| Función del sistema | Descripción |
|---|---|
| STATS_DATE | Fecha en que se actualizó por última vez el objeto de estadísticas. |
| DBCC SHOW_STATISTICS | Nivel de resumen e información detallada sobre la distribución de valores según lo comprendido por el objeto de estadísticas. |
Combinar funciones y columnas de estadísticas en una vista
Esta vista reúne las columnas relacionadas con las estadísticas y los resultados de la función STATS_DATE().
CREATE VIEW dbo.vstats_columns
AS
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[filter_definition] AS [stats_filter_definition]
, st.[has_filter] AS [stats_is_filtered]
, STATS_DATE(st.[object_id],st.[stats_id])
AS [stats_last_updated_date]
, co.[name] AS [stats_column_name]
, ty.[name] AS [column_type]
, co.[max_length] AS [column_max_length]
, co.[precision] AS [column_precision]
, co.[scale] AS [column_scale]
, co.[is_nullable] AS [column_is_nullable]
, co.[collation_name] AS [column_collation_name]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS two_part_name
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS three_part_name
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types AS ty ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE 1=1
AND st.[user_created] = 1
;
Ejemplos de DBCC SHOW_STATISTICS()
DBCC SHOW_STATISTICS() muestra los datos contenidos en un objeto de estadísticas. Estos datos se presentan en tres partes:
- Cabecera
- Vector de densidad
- Histograma
El encabezado es los metadatos sobre las estadísticas. El histograma muestra la distribución de valores en la primera columna de clave del objeto de estadísticas.
El vector de densidad mide la correlación entre las columnas. El grupo de SQL dedicado calcula las estimaciones de cardinalidad con cualquiera de los datos del objeto de estadísticas.
Mostrar el encabezado, la densidad y el histograma
Este ejemplo muestra las tres partes de un objeto de estadísticas:
DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
Por ejemplo:
DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1');
Mostrar una o varias partes de DBCC SHOW_STATISTICS()
Si solo está interesado en ver partes específicas, use la cláusula WITH y especifique qué partes desea ver:
DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
WITH stat_header, histogram, density_vector
Por ejemplo:
DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1')
WITH histogram, density_vector
Diferencias de DBCC SHOW_STATISTICS()
En comparación, DBCC SHOW_STATISTICS() se implementa de forma más estricta en un grupo de SQL dedicado que en SQL Server:
- No se admiten las características no documentadas.
- No se puede usar Stats_stream.
- No se pueden combinar los resultados de subconjuntos específicos de datos de estadísticas. Por ejemplo, STAT_HEADER JOIN DENSITY_VECTOR.
- NO_INFOMSGS no se puede establecer para la supresión de mensajes.
- No se pueden usar corchetes alrededor de los nombres de las estadísticas.
- No se pueden usar nombres de columna para identificar objetos de estadísticas.
- No se admite el error personalizado 2767.
Estadísticas en el pool SQL sin servidor
Las estadísticas se crean por columna determinada para un conjunto de datos determinado (ruta de acceso de almacenamiento).
Nota:
No se pueden crear estadísticas para las columnas LOB.
¿Por qué usar estadísticas?
Cuanto más sepa el grupo de SQL sin servidor acerca de los datos, más rápido puede ejecutar consultas en ellos. La recopilación de estadísticas sobre los datos es una de las cosas más importantes que puede hacer para optimizar las consultas.
El optimizador de consultas del grupo de SQL sin servidor se basa en el costo. Compara el costo de varios planes de consulta y elige el menor de ellos. En la mayoría de los casos, elige el plan que ejecutará más rápido.
Por ejemplo, si el optimizador estima que la fecha en que se filtra la consulta devolverá una fila, se elegirá un plan. Si calcula que la fecha seleccionada devolverá 1 millón de filas, elegirá un plan diferente.
Creación automática de estadísticas
Los pools de SQL sin servidor analizan las consultas de usuario entrantes para detectar estadísticas que faltan. Si faltan las estadísticas, el optimizador de consultas crea estadísticas sobre columnas individuales en el predicado o condición de combinación de la consulta para mejorar las estimaciones de cardinalidad del plan de consulta.
La instrucción SELECT activará la creación automática de estadísticas.
Nota:
Para la creación automática de muestreo de estadísticas se usa y, en la mayoría de los casos, el porcentaje de muestreo será inferior al 100 %. Este flujo es el mismo para cada formato de archivo. Tenga en cuenta que al leer un archivo CSV con el analizador versión 1.0, no se admite muestreo y la creación automática de estadísticas no ocurrirá si el porcentaje de muestras es inferior al 100%%. En el caso de las tablas pequeñas con cardinalidad baja estimada (número de filas), la creación automática de estadísticas se desencadenará con un porcentaje de muestreo del 100 %. Esto significa básicamente que se desencadena fullscan y se crean estadísticas automáticas incluso para CSV con la versión 1.0 del analizador.
La creación automática de estadísticas se realiza de forma sincrónica, por lo que puede incurrir en un rendimiento de consultas ligeramente degradado si las columnas faltan estadísticas. El tiempo necesario para crear estadísticas de una sola columna depende del tamaño de los archivos de destino.
Creación manual de estadísticas
El grupo de SQL sin servidor le permite crear estadísticas manualmente. En caso de que use la versión 1.0 del analizador con CSV, probablemente tendrá que crear estadísticas manualmente, ya que esta versión del analizador no admite el muestreo. La creación automática de estadísticas para la versión 1.0 del analizador no ocurrirá, a menos que el porcentaje de muestreo sea del 100%.
Consulte los ejemplos siguientes para obtener instrucciones sobre cómo crear estadísticas manualmente.
Actualizar estadísticas
Los cambios en los datos de los archivos, la eliminación y la adición de archivos dan lugar a cambios de distribución de datos y hacen que las estadísticas no sean actualizadas. En ese caso, es necesario actualizar las estadísticas.
El grupo de SQL sin servidor vuelve a crear automáticamente las estadísticas de las columnas OPENROWSET si los datos cambian significativamente. Cada vez que se crean estadísticas automáticamente, también se guarda el estado actual del conjunto de datos: las rutas de acceso, los tamaños y las fechas de última modificación de los archivos.
Cuando las estadísticas están obsoletas, se crearán nuevas. El algoritmo pasa por los datos y lo compara con el estado actual del conjunto de datos. Si el tamaño de los cambios es mayor que el umbral específico, se eliminan las estadísticas antiguas y se volverán a crear a través del nuevo conjunto de datos.
Las estadísticas manuales nunca se declaran obsoletas.
Nota:
Para la recreación automática de estadísticas se utiliza el muestreo y, en la mayoría de los casos, el porcentaje de muestreo será inferior al 100%%. Este flujo es el mismo para cada formato de archivo. Tenga en cuenta que al leer el CSV con la versión 1.0 del analizador, no se admite el muestreo, y la recreación automática de estadísticas no se producirá si el porcentaje de muestreo es inferior a 100%. En ese caso, debe quitar y volver a crear las estadísticas manualmente. Consulte los ejemplos siguientes sobre cómo quitar y crear estadísticas. En el caso de tablas pequeñas con cardinalidad baja estimada (número de filas), la recreación automática de estadísticas se desencadenará con un porcentaje de muestreo de 100%. Esto significa básicamente que se desencadena fullscan y se crean estadísticas automáticas incluso para CSV con la versión 1.0 del analizador.
Una de las primeras preguntas que debe formular al solucionar problemas de una consulta es "¿Están actualizadas las estadísticas?"
Si el número de filas ha cambiado significativamente o hay un cambio material en la distribución de valores para una columna, entonces es el momento de actualizar las estadísticas.
Nota:
Si hay un cambio material en la distribución de valores de una columna, debe actualizar las estadísticas independientemente de la última vez que se actualizaron.
Implementación de la administración de estadísticas
Es posible que quiera ampliar la canalización de datos para asegurarse de que las estadísticas se actualizan cuando los datos cambian significativamente a través de la adición, eliminación o cambio de archivos.
Los siguientes principios fundamentales se proporcionan para actualizar las estadísticas:
- Asegúrese de que el conjunto de datos tiene al menos un objeto de estadísticas actualizado. Esto actualiza la información de tamaño (recuento de filas y recuento de páginas) como parte de la actualización de estadísticas.
- Céntrese en las columnas que participan en las cláusulas WHERE, JOIN, GROUP BY, ORDER BY y DISTINCT.
- Actualice columnas de "clave ascendente", como las fechas de transacción con más frecuencia, ya que estos valores no se incluirán en el histograma de estadísticas.
- Actualice las columnas de distribución estáticas con menos frecuencia.
Para obtener más información, consulte Estimación de cardinalidad.
Ejemplos: Creación de estadísticas para columnas en la ruta OPENROWSET
En los ejemplos siguientes se muestra cómo usar varias opciones para crear estadísticas en grupos de SQL sin servidor de Azure Synapse. Las opciones que se usan para cada columna dependen de las características de los datos y de cómo se usará la columna en las consultas. Para obtener más información sobre los procedimientos almacenados que se usan en estos ejemplos, revise sys.sp_create_openrowset_statistics y sys.sp_drop_openrowset_statistics, que solo se aplican a los grupos de SQL sin servidor.
Nota:
Solo puede crear estadísticas de columna única en este momento.
Los siguientes permisos son necesarios para ejecutar sp_create_openrowset_statistics y sp_drop_openrowset_statistics: ADMINISTER BULK OPERATIONS o ADMINISTER DATABASE BULK OPERATIONS.
El siguiente procedimiento almacenado se usa para crear estadísticas:
sys.sp_create_openrowset_statistics [ @stmt = ] N'statement_text'
Argumentos: [ @stmt = ] N'statement_text': especifica una instrucción Transact-SQL que devolverá los valores de columna que se usarán para las estadísticas. Puede usar TABLESAMPLE para especificar ejemplos de datos que se van a usar. Si no se especifica TABLESAMPLE, se usará FULLSCAN.
<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )
Nota:
El muestreo de CSV no funciona si usa la versión 1.0 del analizador, solo se admite FULLSCAN para CSV con la versión 1.0 del analizador.
Crear estadísticas de columna única mediante el examen de todas las filas
Para crear estadísticas en una columna, proporcione una consulta que devuelva la columna para la que necesita estadísticas.
De forma predeterminada, si no se especifica lo contrario al crear estadísticas manualmente, el grupo de SQL sin servidor usa el 100 % de los datos proporcionados en el conjunto de datos al crear las estadísticas.
Por ejemplo, para crear estadísticas con opciones predeterminadas (FULLSCAN) para una columna de población del conjunto de datos basado en el archivo us_population.csv:
EXEC sys.sp_create_openrowset_statistics N'SELECT
population
FROM OPENROWSET(
BULK ''https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/raw_us_population_county/us_population.csv'',
FORMAT = ''CSV'',
PARSER_VERSION = ''2.0'',
HEADER_ROW = TRUE)
AS [r]'
Crear estadísticas de columna única especificando el tamaño de muestra
Puede especificar el tamaño de la muestra como porcentaje:
/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO
CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = ''
GO
*/
EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
Ejemplos: Actualizar estadísticas
Para actualizar las estadísticas, debe quitarlas y crearlas. Para obtener más información, revise sys.sp_create_openrowset_statistics y sys.sp_drop_openrowset_statistics.
El procedimiento almacenado sys.sp_drop_openrowset_statistics se emplea para quitar estadísticas:
sys.sp_drop_openrowset_statistics [ @stmt = ] N'statement_text'
Nota:
Los siguientes permisos son necesarios para ejecutar sp_create_openrowset_statistics y sp_drop_openrowset_statistics: ADMINISTER BULK OPERATIONS o ADMINISTER DATABASE BULK OPERATIONS.
Argumentos: [ @stmt = ] N'statement_text': especifica la misma instrucción Transact-SQL usada cuando se crearon las estadísticas.
Para actualizar las estadísticas de la columna year del conjunto de datos, que se basa en el archivo population.csv, debe quitar y crear las estadísticas:
EXEC sys.sp_drop_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
GO
/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO
CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = ''
GO
*/
EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
Ejemplos: Creación de estadísticas para la columna de tabla externa
En los ejemplos siguientes se muestra cómo usar varias opciones para crear estadísticas. Las opciones que se usan para cada columna dependen de las características de los datos y de cómo se usará la columna en las consultas.
Nota:
Solo puede crear estadísticas de columna única en este momento.
Para crear estadísticas de una columna, especifique un nombre para el objeto de estadística y el nombre de la columna.
CREATE STATISTICS statistics_name
ON { external_table } ( column )
WITH
{ FULLSCAN
| [ SAMPLE number PERCENT ] }
, { NORECOMPUTE }
Argumentos: external_table Especifica la tabla externa en la que se deben crear las estadísticas.
FULLSCAN computa estadísticas mediante el escaneo de todas las filas. FULLSCAN y SAMPLE 100 PERCENT tienen los mismos resultados. FULLSCAN no se puede utilizar con la opción SAMPLE.
SAMPLE número PERCENT especifica el porcentaje o número de filas aproximado de la tabla o vista indizada que usa el optimizador de consultas al crear las estadísticas. El número puede ser de 0 a 100.
SAMPLE no se puede utilizar con la opción FULLSCAN.
Nota:
El muestreo de CSV no funciona si usa la versión 1.0 del analizador, solo se admite FULLSCAN para CSV con la versión 1.0 del analizador.
Crear estadísticas de columna única mediante el examen de todas las filas
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH FULLSCAN, NORECOMPUTE
Crear estadísticas de columna única especificando el tamaño de muestra
-- following sample creates statistics with sampling 5%
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH SAMPLE 5 percent, NORECOMPUTE
Ejemplos: Actualizar estadísticas
Para actualizar las estadísticas, debe quitarlas y crearlas. Omitir primero las estadísticas:
DROP STATISTICS census_external_table.sState
Y cree estadísticas:
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH FULLSCAN, NORECOMPUTE
Metadatos de las estadísticas
Hay varias funciones y vistas del sistema que puede usar para encontrar información sobre las estadísticas. Por ejemplo, puede ver si un objeto de estadísticas podría estar obsoleto mediante la función STATS_DATE(). STATS_DATE() permite ver cuándo se crearon o actualizaron las estadísticas por última vez.
Nota:
Los metadatos de estadísticas solo están disponibles para las columnas de tabla externa. Los metadatos de estadísticas no están disponibles para las columnas OPENROWSET.
Vistas de catálogo para las estadísticas
Estas vistas del sistema proporcionan información acerca de las estadísticas:
| Vista de catálogo | Descripción |
|---|---|
| sys.columns | Una fila para cada columna. |
| sys.objects | Una fila para cada objeto de la base de datos. |
| sys.schemas | Una fila para cada esquema de la base de datos. |
| sys.stats | Una fila para cada objeto de estadísticas. |
| sys.stats_columns | Una fila para cada columna del objeto de estadísticas. Vínculos a la sys.columns. |
| sys.tables | Una fila para cada tabla (incluye tablas externas). |
| sys.table_types | Una fila para cada tipo de datos. |
Funciones del sistema para las estadísticas
Estas funciones del sistema son útiles para trabajar con las estadísticas:
| Función del sistema | Descripción |
|---|---|
| STATS_DATE | Fecha en que se actualizó por última vez el objeto de estadísticas. |
Combinar funciones y columnas de estadísticas en una vista
Esta vista reúne las columnas relacionadas con las estadísticas y los resultados de la función STATS_DATE().
CREATE VIEW dbo.vstats_columns
AS
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[filter_definition] AS [stats_filter_definition]
, st.[has_filter] AS [stats_is_filtered]
, STATS_DATE(st.[object_id],st.[stats_id])
AS [stats_last_updated_date]
, co.[name] AS [stats_column_name]
, ty.[name] AS [column_type]
, co.[max_length] AS [column_max_length]
, co.[precision] AS [column_precision]
, co.[scale] AS [column_scale]
, co.[is_nullable] AS [column_is_nullable]
, co.[collation_name] AS [column_collation_name]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS two_part_name
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS three_part_name
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.types AS ty ON co.[user_type_id] = ty.[user_type_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE st.[user_created] = 1
;
Pasos siguientes
Para mejorar aún más el rendimiento de las consultas para el grupo de SQL dedicado, consulte Supervisión de la carga de trabajo y Procedimientos recomendados para el grupo de SQL dedicado.
Para mejorar aún más el rendimiento de las consultas para el grupo de SQL sin servidor, consulte Procedimientos recomendados para el grupo de SQL sin servidor.