CREATE STATISTICS (Transact-SQL)

Artículo
05/23/2024

Se aplica a: SQL Server Base de datos de Azure SQL Azure SQL Managed Instance Azure Synapse Analytics Punto de conexión de análisis SQL en Microsoft Fabric Almacén en Microsoft Fabric

Crea estadísticas de optimización de consultas en una o más columnas de una tabla, en una vista indizada o en una tabla externa. Para la mayoría de las consultas, el optimizador de consultas ya genera las estadísticas necesarias para un plan de consulta de alta calidad; en algunos casos, debe crear estadísticas adicionales con CREATE STATISTICS o modificar el diseño de la consulta para mejorar el rendimiento de las consultas.

Para obtener más información, consulte Estadísticas.

Nota:

Para obtener más información sobre las estadísticas de Microsoft Fabric, consulte Estadísticas en el almacenamiento de datos de Fabric.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis para SQL Server, Azure SQL Database y Azure SQL Instancia administrada.

-- Create statistics on an external table
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WITH FULLSCAN ] ;

-- Create statistics on a regular table or indexed view
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ FULLSCAN
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS }
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | <update_stats_stream_option> [ , ...n ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
        ]
    ];

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant , ...)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_constant ]

Sintaxis de Azure Synapse Analytics and Analytics Platform System (PDW).

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name  [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant , ...)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

Sintaxis de Microsoft Fabric.

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name )
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

Argumentos

statistics_name

Nombre de las estadísticas que se van a crear.

table_or_indexed_view_name

Nombre de la tabla, vista indizada o tabla externa en la que se van a crear las estadísticas. Para crear estadísticas en otra base de datos, especifique un nombre de tabla completa.

column [ ,...n]

Una o varias columnas que se van a incluir en las estadísticas. Las columnas deben estar en orden de prioridad, de izquierda a derecha. Solo se usa la primera columna para crear el histograma. Todas las columnas se usan para las estadísticas de correlación entre columnas denominadas densidades.

Se puede especificar cualquier columna que pueda ser especificada como columna de clave de índice con las siguientes excepciones:

No se pueden especificar columnas xml, de texto completo ni de FILESTREAM.
Las columnas calculadas solo se pueden especificar si la configuración de la ARITHABORT base de datos y QUOTED_IDENTIFIER son ON.
Se pueden especificar columnas de tipo CLR definido por el usuario si el tipo admite el orden binario. Es posible especificar columnas calculadas definidas como llamadas a métodos de una columna de un tipo definido por el usuario si los métodos están marcados como deterministas.

WHERE <filter_predicate>

Especifica una expresión para seleccionar un subconjunto de filas que se va a incluir al crear el objeto de estadísticas. Las estadísticas que se crean con un predicado de filtro se llaman estadísticas filtradas. El predicado de filtro usa lógica de comparación simple y no puede hacer referencia a una columna calculada, una columna UDT, una columna de tipo de datos espacial o una columna de tipo de datos hierarchyID . No se permiten comparaciones con NULL literales con los operadores de comparación. Use en su lugar los operadores IS NULL y IS NOT NULL.

A continuación, se muestran algunos ejemplos de predicados de filtro para la tabla Production.BillOfMaterials:

WHERE StartDate > '20000101' AND EndDate <= '20000630'
WHERE ComponentID IN (533, 324, 753)
WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Para obtener más información sobre los predicados de filtro, vea Creación de índices filtrados.

FULLSCAN

Se aplica a: SQL Server 2016 (13.x) SP 1 CU 4, SQL Server 2017 (14.x) CU 1 y versiones posteriores

Calcula estadísticas examinando todas las filas. FULLSCAN y SAMPLE 100 PERCENT tienen los mismos resultados. FULLSCAN no se puede usar con la SAMPLE opción .

Cuando se omite, SQL Server utiliza el muestreo para crear las estadísticas y determina el tamaño de la muestra que se requiere para crear un plan de consulta de alta calidad.

En Warehouse en Microsoft Fabric, solo se admiten estadísticas basadas en columna FULLSCAN única y basada en columna SAMPLEúnica. Cuando no se incluye ninguna opción, SAMPLE se crean estadísticas.

SAMPLE number { PERCENT | ROWS }

Especifica el porcentaje aproximado, o el número de filas, en la tabla o vista indizada que el optimizador de consultas va a usar al crear estadísticas. Para PERCENT, el número puede ser de 0 a 100 y para ROWS, el número puede ser de 0 al número total de filas. El porcentaje o número de filas real de los ejemplos del optimizador de consultas podría no coincidir con el porcentaje o el número especificado. Por ejemplo, el optimizador de consultas examina todas las filas en una página de datos.

SAMPLE es útil para casos especiales en los que el plan de consulta, basado en el muestreo predeterminado, no es óptimo. En la mayoría de las situaciones, no es necesario especificar SAMPLE porque el optimizador de consultas ya usa el muestreo y determina el tamaño de muestra estadísticamente significativo de forma predeterminada, según sea necesario para crear planes de consulta de alta calidad.

SAMPLE no se puede usar con la opción FULLSCAN. FULLSCAN Cuando SAMPLE no se especifican, el optimizador de consultas usa datos muestreados y calcula el tamaño de la muestra de forma predeterminada.

Recomendamos no especificar 0 PERCENT ni 0 ROWS. Cuando 0 PERCENT se especifica o 0 ROWS , se crea el objeto statistics, pero no contiene datos de estadísticas.

En Warehouse en Microsoft Fabric, solo se admiten estadísticas basadas en columna FULLSCAN única y basada en columna SAMPLEúnica. Cuando no se incluye ninguna opción, FULLSCAN se crean estadísticas.

PERSIST_SAMPLE_PERCENT = { ON | OFF }

Cuando ON, las estadísticas conservan el porcentaje de muestreo de creación para las actualizaciones posteriores que no especifican explícitamente un porcentaje de muestreo. Cuando OFF, el porcentaje de muestreo de estadísticas se restablece al muestreo predeterminado en las actualizaciones posteriores que no especifican explícitamente un porcentaje de muestreo. El valor predeterminado es OFF.

Nota:

Si se trunca la tabla, todas las estadísticas creadas en el HoBT truncado volverán a usar el porcentaje de muestreo predeterminado.

STATS_STREAM = stats_stream

Solamente se identifica con fines informativos. No compatible. La compatibilidad con versiones posteriores no está garantizada.

NORECOMPUTE

Deshabilite la opción de actualización automática de estadísticas, AUTO_STATISTICS_UPDATE, para statistics_name. Si se especifica esta opción, el optimizador de consultas finalizará cualquier actualización de las estadísticas que se esté realizando para statistics_name y deshabilitará las actualizaciones futuras.

Para volver a habilitar las actualizaciones de estadísticas, quite las estadísticas con DROP STATISTICS y, a continuación, ejecute CREATE STATISTICS sin la NORECOMPUTE opción .

Advertencia

Si deshabilita la actualización automática de estadísticas, podría impedir que el optimizador de consultas seleccione planes de ejecución óptimos para las consultas que implican la tabla. Debe usar esta opción con moderación y solo por un administrador de bases de datos calificado.

Para obtener más información sobre la AUTO_STATISTICS_UPDATE opción, vea OPCIONES ALTER DATABASE SET. Para obtener más información sobre cómo deshabilitar y volver a habilitar las actualizaciones de estadísticas, vea Estadísticas.

INCREMENTAL = { ON | OFF }

Se aplica a: SQL Server 2014 (12.x) y versiones posteriores.

Cuando ON, las estadísticas creadas son por estadísticas de partición. Cuando OFF, las estadísticas se combinan para todas las particiones. El valor predeterminado es OFF.

Si no se admiten estadísticas por partición, se genera un error. Las estadísticas incrementales no se admiten para los siguientes tipos de estadísticas:

Estadísticas creadas con índices que no están alineados por partición con la tabla base.
Estadísticas creadas sobre bases de datos secundarias legibles AlwaysOn.
Estadísticas creadas sobre bases de datos de solo lectura.
Estadísticas creadas sobre índices filtrados.
Estadísticas creadas sobre vistas.
Estadísticas creadas sobre tablas internas.
Estadísticas creadas con índices espaciales o índices XML.

MAXDOP = max_degree_of_parallelism

Se aplica a: SQL Server 2016 (13.x) SP 2, SQL Server 2017 (14.x) CU 3 y versiones posteriores

Invalida la opción de configuración grado máximo de paralelismo durante la operación estadística. Para obtener más información, consulte Configuración del grado máximo de paralelismo (opción de configuración del servidor). Use MAXDOP para limitar el número de procesadores usados en una ejecución de plan paralelo. El máximo es 64 procesadores.

max_degree_of_parallelism puede tener estos valores:

1: suprime la generación de planes paralelos.
>1: restringe el número máximo de procesadores usados en una operación de índice paralela al número especificado.
0 (valor predeterminado): usa el número real de procesadores o menos en función de la carga de trabajo del sistema actual.

update_stats_stream_option

Solamente se identifica con fines informativos. No compatible. La compatibilidad con versiones posteriores no está garantizada.

AUTO_DROP = { ON | OFF }

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores, y Azure SQL Database, Azure SQL Instancia administrada

Antes de SQL Server 2022 (16.x), si un usuario o una herramienta de terceros crean manualmente estadísticas en una base de datos de usuario, esos objetos de estadísticas pueden bloquear o interferir con los cambios de esquema que el cliente podría querer.

A partir de SQL Server 2022 (16.x), la AUTO_DROP opción está habilitada de forma predeterminada en todas las bases de datos nuevas y migradas. La AUTO_DROP propiedad permite la creación de objetos de estadísticas en un modo de modo que el objeto estadístico no bloquee un cambio de esquema posterior, sino que las estadísticas se quitan según sea necesario. De esta manera, las estadísticas creadas manualmente con AUTO_DROP habilitado se comportan como estadísticas autocreadas.

Nota:

Si se intenta establecer o anular la propiedad Auto_Drop en las estadísticas creadas automáticamente, se pueden producir errores. Las estadísticas creadas automáticamente siempre usan la eliminación automática. Algunas copias de seguridad, cuando se restauran, podrían tener esta propiedad establecida incorrectamente hasta la próxima vez que se actualice el objeto de estadísticas (manual o automáticamente). Sin embargo, las estadísticas creadas automáticamente siempre se comportan como estadísticas de eliminación automática. Al restaurar una base de datos a SQL Server 2022 (16.x) desde una versión anterior, se recomienda ejecutar sp_updatestats en la base de datos, estableciendo los metadatos adecuados para la característica de estadísticas AUTO_DROP .

Para obtener más información, consulte Opción AUTO_DROP.

Permisos

Se requiere uno de estos permisos:

ALTER TABLE
El usuario es el propietario de la tabla
Pertenencia al rol fijo de base de db_ddladmin.

Comentarios

SQL Server puede usar tempdb para ordenar las filas buscadas antes de crear las estadísticas.

Estadísticas para tablas externas

Al crear las estadísticas de tabla externa, SQL Server importa la tabla externa en una tabla de SQL Server temporal y crea las estadísticas. En las estadísticas de muestra, solo se importan las filas muestreadas. Si tiene una tabla externa grande, es más rápido usar el muestreo predeterminado en lugar de la opción de examen completo.

Cuando la tabla externa usa DELIMITEDTEXT, CSV, PARQUET o DELTA como tipos de datos, las tablas externas solo admiten estadísticas para una columna por cada comando CREATE STATISTICS.

Estadísticas con una condición de filtrado

Las estadísticas filtradas pueden mejorar el rendimiento de las consultas que se seleccionan desde subconjuntos de datos bien definidos. Las estadísticas filtradas utilizan un predicado de filtro de la cláusula WHERE para seleccionar el subconjunto de datos que se incluye en las estadísticas.

Cuándo utilizar CREATE STATISTICS

Para obtener más información sobre cuándo usar CREATE STATISTICS, vea Estadísticas.

Hacer referencia a las dependencias para las estadísticas filtradas

La vista de catálogo sys.sql_expression_dependencies realiza el seguimiento de cada columna en la expresión del predicado de estadísticas filtradas como una dependencia de referencia. Tenga en cuenta las operaciones que se realizan en las columnas de tabla antes de crear estadísticas filtradas. No se puede quitar, cambiar el nombre ni modificar la definición de una columna de tabla definida en un predicado de estadísticas filtrada.

Limitaciones

No se admite la actualización de estadísticas en tablas externas. Para actualizar las estadísticas en una tabla externa, quite las estadísticas y vuelva a crearlas.
Puede mostrar hasta 64 columnas por objeto de estadísticas.
La MAXDOP opción no es compatible con STATS_STREAMlas opciones , ROWCOUNTy PAGECOUNT .
La opción MAXDOP está limitada por la configuración MAX_DOP del grupo de cargas de trabajo de Resource Governor, si se usa.
CREATE y DROP STATISTICS en tablas externas no se admiten en Azure SQL Database.

Ejemplos

Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se puede descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.

A Uso de CREATE STATISTICS con SAMPLE number PERCENT

En el ejemplo siguiente, se crean las estadísticas ContactMail1 a partir de una muestra aleatoria del cinco por ciento de las columnas BusinessEntityID y EmailPromotion de la tabla Person de la base de datos AdventureWorks2022.

CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

B. Uso de CREATE STATISTICS con FULLSCAN y NORECOMPUTE

En el ejemplo siguiente se crean las estadísticas NamePurchase para todas las filas de las columnas BusinessEntityID y EmailPromotion de la tabla Person y se deshabilita la posibilidad de volver a calcular las estadísticas automáticamente.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

C. Uso de CREATE STATISTICS para crear estadísticas filtradas

En el ejemplo siguiente se crean las estadísticas filtradas ContactPromotion1. El Motor de base de datos muestra el 50 por ciento de los datos y, a continuación, selecciona las filas cuyo valor EmailPromotion es igual a 2.

CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

D. Crear estadísticas en una tabla externa

La única decisión que debe tomar al crear las estadísticas en una tabla externa, además de proporcionar la lista de columnas, es si desea crear las estadísticas mediante el muestreo de las filas o examinando todas las filas. CREATE y DROP STATISTICS en tablas externas no se admiten en Azure SQL Database.

Puesto que SQL Server importa datos de la tabla externa en una tabla temporal para crear estadísticas, la opción de examen completo tardará mucho más tiempo. En una tabla grande, el método de muestreo predeterminado generalmente es suficiente.

--Create statistics on an external table and use default sampling.
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

--Create statistics on an external table and scan all the rows
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

E. Uso de CREATE STATISTICS con FULLSCAN y PERSIST_SAMPLE_PERCENT

En el ejemplo siguiente se crean las NamePurchase estadísticas de todas las filas de la BusinessEntityID tabla y EmailPromotion y se establece un porcentaje de Person muestreo del 100 % para todas las actualizaciones posteriores que no especifican explícitamente un porcentaje de muestreo.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

Ejemplos en los que se usa la base de datos AdventureWorksDW.

F. Crear estadísticas en dos columnas

En el ejemplo siguiente se crean estadísticas de CustomerStats1 basadas en las columnas CustomerKey y EmailAddress de la tabla DimCustomer. Las estadísticas se crean en función de un muestreo estadísticamente significativo de las filas de la tabla Customer.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

G. Crear estadísticas mediante un examen completo

En este ejemplo se crean las estadísticas de CustomerStatsFullScan, en función del examen de todas las filas de la tabla DimCustomer.

CREATE STATISTICS CustomerStatsFullScan
ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

H. Crear las estadísticas especificando el porcentaje de muestreo

En este ejemplo se crean las estadísticas de CustomerStatsSampleScan, en función del examen del 50 % de las filas de la tabla DimCustomer.

CREATE STATISTICS CustomerStatsSampleScan
ON DimCustomer (CustomerKey, EmailAddress) WITH SAMPLE 50 PERCENT;

I. Uso de CREATE STATISTICS con AUTO_DROP

Para usar las estadísticas de eliminación automática, basta con agregar lo siguiente a la cláusula "WITH" de creación o actualización de estadísticas.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH AUTO_DROP = ON

Para evaluar la configuración de eliminación automática en las estadísticas existentes, use la columna auto_drop en sys.stats:

SELECT object_id, [name], auto_drop
FROM sys.stats;

Compartir a través de

CREATE STATISTICS (Transact-SQL)

Sintaxis

Argumentos

statistics_name

table_or_indexed_view_name

column [ ,...n]

WHERE <filter_predicate>

FULLSCAN

SAMPLE number { PERCENT | ROWS }

PERSIST_SAMPLE_PERCENT = { ON | OFF }

STATS_STREAM = stats_stream

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

Permisos

Comentarios

Estadísticas para tablas externas

Estadísticas con una condición de filtrado

Cuándo utilizar CREATE STATISTICS

Hacer referencia a las dependencias para las estadísticas filtradas

Limitaciones

Ejemplos

A Uso de CREATE STATISTICS con SAMPLE number PERCENT

B. Uso de CREATE STATISTICS con FULLSCAN y NORECOMPUTE

C. Uso de CREATE STATISTICS para crear estadísticas filtradas

D. Crear estadísticas en una tabla externa

E. Uso de CREATE STATISTICS con FULLSCAN y PERSIST_SAMPLE_PERCENT

Ejemplos en los que se usa la base de datos AdventureWorksDW.

F. Crear estadísticas en dos columnas

G. Crear estadísticas mediante un examen completo

H. Crear las estadísticas especificando el porcentaje de muestreo

I. Uso de CREATE STATISTICS con AUTO_DROP

Comentarios

Recursos adicionales

Compartir a través de

CREATE STATISTICS (Transact-SQL)

Sintaxis

Argumentos

statistics_name

table_or_indexed_view_name

column [ ,...n]

WHERE <filter_predicate>

FULLSCAN

SAMPLE number { PERCENT | ROWS }

PERSIST_SAMPLE_PERCENT = { ON | OFF }

STATS_STREAM = stats_stream

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

Permisos

Comentarios

Estadísticas para tablas externas

Estadísticas con una condición de filtrado

Cuándo utilizar CREATE STATISTICS

Hacer referencia a las dependencias para las estadísticas filtradas

Limitaciones

Ejemplos

A Uso de CREATE STATISTICS con SAMPLE number PERCENT

B. Uso de CREATE STATISTICS con FULLSCAN y NORECOMPUTE

C. Uso de CREATE STATISTICS para crear estadísticas filtradas

D. Crear estadísticas en una tabla externa

E. Uso de CREATE STATISTICS con FULLSCAN y PERSIST_SAMPLE_PERCENT

Ejemplos en los que se usa la base de datos AdventureWorksDW.

F. Crear estadísticas en dos columnas

G. Crear estadísticas mediante un examen completo

H. Crear las estadísticas especificando el porcentaje de muestreo

I. Uso de CREATE STATISTICS con AUTO_DROP

Contenido relacionado

Comentarios

Recursos adicionales