CREATE STATISTICS (Transact-SQL)
Se aplica a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsPunto de conexión de análisis SQL en Microsoft FabricAlmacenamiento 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 genera ya las estadísticas necesarias para un plan de consulta de alta calidad; en algunos casos, para mejorar el rendimiento de la consulta necesita crear estadísticas adicionales con CREATE STATISTICS modificar el diseño de la consulta.
Para obtener más información, consulte Estadísticas.
Nota:
Para más información sobre las estadísticas de Microsoft Fabric, consulte Estadísticas en Microsoft Fabric.
Convenciones de sintaxis de Transact-SQL
Sintaxis
-- Syntax for SQL Server and Azure SQL Database
-- 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_contant ]
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse
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 | = | <> | != | > | >= | !> | < | <= | !<
-- Syntax for 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
}
]
[;]
Nota:
Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.
Argumentos
statistics_name
Es el nombre de las estadísticas que se van a crear.
table_or_indexed_view_name
Es el nombre de la tabla, de la vista indizada o de la tabla externa en 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:
Las columnas FILESTREAM, de texto completo y XML no se pueden especificar.
Solamente se pueden especificar columnas calculadas si las opciones de base de datos ARITHABORT y QUOTED_IDENTIFIER están establecidas en 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 utiliza la lógica de comparación simple y no puede hacer referencia a una columna calculada, a una columna UDT, a una columna de tipo de datos espacial o a una columna de tipo de datos hierarchyID. Las comparaciones que utilizan literales NULL no se admiten con los operadores de comparación. En su lugar, use los operadores IS NULL e 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 índices filtrados, vea Crear índices filtrados.
FULLSCAN
Se aplica a: SQL Server 2016 (13.x) (a partir de SQL Server 2016 [13.x] SP1 CU4) y versiones posteriores (a partir de SQL Server 2017 [14.x] CU1).
Calcula estadísticas examinando todas las filas. FULLSCAN y SAMPLE 100 PERCENT tienen los mismos resultados. FULLSCAN no se puede utilizar con la opción SAMPLE.
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 el almacenamiento de Microsoft Fabric, solo se admiten estadísticas basadas en EJEMPLO de columna única y FULLSCAN de una sola columna. Cuando no se incluye ninguna opción, se crean estadísticas FULLSCAN.
SAMPLE number { PERCENT | ROWS }
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. En PERCENT, number puede tener un valor comprendido entre 0 y 100, mientras que en ROWS, number puede tener un valor comprendido entre 0 y el 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 los 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 utiliza el muestreo y determina el tamaño de muestra estadísticamente significativo de forma predeterminada, tal y como se exige para crear planes de consulta de alta calidad.
SAMPLE no se puede utilizar con la opción FULLSCAN. Cuando no se especifica SAMPLE ni FULLSCAN, el optimizador de consultas utiliza los datos muestreados y calcula el tamaño de la muestra de forma predeterminada.
Recomendamos no especificar 0 PERCENT ni 0 ROWS. Cuando se especifican 0 PERCENT o 0 ROWS, el objeto de estadísticas se crea pero no contiene datos de estadísticas.
En el almacenamiento de Microsoft Fabric, solo se admiten estadísticas basadas en EJEMPLO de columna única y FULLSCAN de una sola columna. Cuando no se incluye ninguna opción, se crean estadísticas FULLSCAN.
PERSIST_SAMPLE_PERCENT = { ON | OFF }
Cuando está ON, las estadísticas conservan el porcentaje de muestreo de la creación para las actualizaciones posteriores que no especifican explícitamente un porcentaje de muestreo. Cuando está OFF, el porcentaje de muestreo de estadísticas se restablece al muestreo predeterminado en actualizaciones posteriores que no especifiquen 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 automática de actualización de las estadísticas, AUTO_UPDATE_STATISTICS, 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 las estadísticas, quite las estadísticas con DROP STATISTICS y, a continuación, ejecute CREATE STATISTICS sin la opción NORECOMPUTE.
Advertencia
Utilizar esta opción puede producir planes de consulta poco óptimos. Se recomienda usar esta opción con moderación y que lo haga únicamente un administrador de sistemas cualificado.
Para obtener más información sobre la opción AUTO_STATISTICS_UPDATE, vea Opciones de ALTER DATABASE SET (Transact-SQL). 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 }
Válido para : SQL Server 2014 (12.x) y versiones posteriores.
Cuando se establece en ON, se crean estadísticas por cada partición. Cuando se establece en OFF, se combinan las estadísticas de todas las particiones. El valor predeterminado es OFF.
Si no se admiten las 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 (a partir de SQL Server 2016 (13.x) SP2 y SQL Server 2017 (14.x) CU3).
Reemplaza la opción de configuración max degree of parallelism durante la operación estadística. Para obtener más información, vea Establecer la opción de configuración del servidor Grado máximo de paralelismo. Utilice MAXDOP para establecer un límite para el número de procesadores utilizados en la ejecución de un 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 estadística paralela al número especificado o a un número inferior, en función de la carga de trabajo actual del sistema.
0 (predeterminado)
Usa el número real de procesadores o menos, según la carga de trabajo actual del sistema.
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: Azure SQL Database, Azure SQL Managed Instance y a partir de SQL Server 2022 (16.x)
Antes de SQL Server 2022 (16.x), si un usuario crea estadísticas manualmente o si se crean con una herramienta de terceros en una base de datos de usuario, esos objetos de estadísticas pueden bloquear o interferir con los cambios de esquema que el cliente puede desear.
A partir de SQL Server 2022 (16.x), la opción AUTO_DROP está habilitada de forma predeterminada en todas las bases de datos nuevas y migradas. La propiedad AUTO_DROP permite la creación de objetos de estadísticas en un modo tal que el objeto estadístico no bloquee un cambio de esquema posterior, sino que las estadísticas se quitarán según sea necesario. De esta manera, las estadísticas creadas manualmente con AUTO_DROP habilitado se comportan como las estadísticas creadas automáticamente.
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, pueden 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 en SQL Server 2022 (16.x) desde una versión anterior, se recomienda ejecutar sp_updatestats en la base de datos y establecer los metadatos adecuados para la característica AUTO_DROP de las estadísticas.
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 la tabla externa es grande, es mucho más rápido utilizar el muestreo predeterminado en vez 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 realiza en las columnas de la tabla antes de crear estadísticas filtradas, porque no puede quitar, modificar, cambiar el nombre de la definición de una columna de tabla definida en un predicado de estadísticas filtradas.
Limitaciones y restricciones
- 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 opción MAXDOP no es compatible con opciones STATS_STREAM, ROWCOUNT y 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.
- En Azure SQL Database no se admiten CREATE y DROP STATISTICS en las tablas externas.
Ejemplos
Los ejemplos usan la base de datos AdventureWorks.
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. En Azure SQL Database no se admiten CREATE y DROP STATISTICS en las tablas externas.
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 estadísticas NamePurchase para todas las filas de las columnas BusinessEntityID y EmailPromotion de la tabla Person, y se establece un porcentaje de muestreo del 100 % para todas las actualizaciones siguientes en las que no se especifique un porcentaje de muestreo de forma explícita.
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;
Pasos siguientes
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de