que - El nuevo estimador de cardinalidad(SQL Server 2014) está muy lejos
no se puede conectar a local sql server 2014 (3)
Tengo una base de datos del almacén de datos y me enfrento a problemas con el nuevo estimador de cardinalidad de SQL Server 2014.
Después de actualizar el servidor de base de datos a SQL Server 2014, he observado una gran diferencia en el rendimiento de las consultas. Algunas consultas se ejecutan mucho más lentamente (30 segundos en SQL 2012 frente a 5 minutos en SQL 2014). Después de investigar los planes de ejecución, he visto que las estimaciones de cardinalidad en el SQL Server 2014 están muy lejos y no puedo encontrar una razón para ello.
Este es un ejemplo de un plan de ejecución de consultas (operador de la parte superior izquierda) en SQL 2012 vs. SQL 2014:
Algunos detalles:
Mis consultas son típicas consultas de carga de tablas de datos del almacén de datos. Consultar una tabla transaccional y unir muchas tablas de dimensiones (15-20) (siempre hay 0 o 1 registros que se unen desde la tabla dimensional).
He actualizado las estadísticas de todas las tablas (con FULLSCAN) para asegurarme de que las estadísticas estén actualizadas.
Las claves de negocio de las tablas de dimensiones están indexadas (índice no clust único). Me parece que debido a la singularidad de este índice, el antiguo estimador de cardinalidad (SQL 2012) asume correctamente que hay un máximo. 1 registro que se une (el número estimado de registros no cambia en el plan de ejecución).
Intenté limitar el problema al ejemplo más simple: SELECT con 2 combinaciones:
Aquí está la estimación de la cardinalidad en los operadores 1 y 2 en SQL 2012 vs. SQL 2014:
| Est.rows - SQL2012 | Est.rows - SQL2014
Operator 1 | 7653 | 7653
Operator 2 | 7653 | 10000
Como puede ver, SQL Server 2014 pierde la estimación en más del 30% (10000 vs. 7653). Porque tengo cca. 15-20 se une en una consulta típica, la estimación final va muy lejos.
Puedo poner la base de datos en el modo de compatibilidad inferior (110) y funciona bien entonces (igual que en SQL Server 2012), pero realmente me gustaría saber cuál es la razón de este comportamiento. ¿Por qué el resultado del estimador de cardinalidad de SQL Server 2014 es incorrecto?
Creo que hoy no hay una respuesta simple a esta pregunta interesante. La mejor respuesta que conozco es el siguiente video: http://channel9.msdn.com/events/TechEd/NorthAmerica/2014/DBI-B331#fbid= . Cuenta con numerosos ejemplos de estimadores nuevos y antiguos. El video dura aproximadamente 50 minutos, pero vale la pena.
Un resumen del video que se relaciona con esta pregunta:
Viejos supuestos de estimaciones de cardinalidad:
- Uniformidad - los datos se distribuyen uniformemente.
- Independencia - la columna 1 no tiene relación con la columna 2.
- Contención: cuando dos atributos pueden ser iguales, se asume que son iguales.
- Inclusión - debe haber un partido.
Para usar el estimador de cardinalidad de SQL Server 2014 en SQL Server 2014 use la siguiente opción:
- Opción (querytraceon 9481) - revertir a 2012
Qué está haciendo el nuevo estimador (basado en el video):
- SQL Server utiliza una selectividad promedio en el índice y estima el número de filas al multiplicar la densidad de la clave por el número total de filas en el índice.
- El nuevo estimador no funciona muy bien con distribuciones dentadas.
- La mayoría de las diferencias entre los estimadores se basan en la cláusula WHERE.
- El nuevo estimador de cardinalidad cree que existe una correlación entre las tablas.
- Puede crear estadísticas filtradas para mejorar las consultas. ( http://msdn.microsoft.com/en-us/library/ms188038.aspx )
Para hacer / checklist:
1. Auto Create / Update Stats
2. Check database compatibility mode (120/110)
3. Test using query trace flags
4. XML showplan
Actualizar Novedades en el estimador de cardinalidad (SQL Server 2016)
- El más preciso.
- La CE predice cuántas filas probablemente devolverá tu consulta
- SQL Server 2016 el almacén de consultas
- Otra opción para rastrear las predicciones de cardinalidad de la CE es usar el evento extendido llamado query_optimizer_estimate_cardinality
- CE entiende que el valor máximo puede ser mayor que cuando se recopilaron las estadísticas por última vez
- CE entiende que los predicados filtrados en la misma tabla a menudo están correlacionados
- CE ya no asume ninguna correlación entre predicados filtrados de diferentes tablas
Más detalles:
https://www.sqlshack.com/query-optimizer-changes-in-sql-server-2016-explained/
Esta no es una respuesta directa a esta pregunta, pero podría ayudar a aquellos que enfrentan un problema de rendimiento similar relacionado con la base de datos SCCM (también conocida como ConfigMgr) relacionada con los cambios del Estimador de cardinalidad (CE). Las consultas SQL pueden agotarse o su consola ConfigMgr puede ejecutarse lentamente debido a los nuevos cambios del Estimador de cardinalidad (CE) en SQL Server 2014 y SQL Server 2016. Microsoft ha dado una solución a este problema here que sugiere aplicar un Estimador de cardinalidad SQL apropiado (CE). ) nivel de compatibilidad como se muestra en la siguiente tabla:
SQL Server version Supported compatibility Recommended compatibility
level values level for ConfigMgr
SQL Server 2016 130, 120, 110, 100 130
SQL Server 2014 120, 110, 100 110
¡Espero que esto ayude!
Me pregunto si se está encontrando con este problema en torno a las estimaciones de selectividad de varias columnas:
http://www.sqlskills.com/blogs/kimberly/multi-column-statistics-exponential-backoff/
Parece que todavía hay algunos caprichos con el nuevo CE. Intenta usar el TF 4137 como se describe y ve si eso ayuda.
por último, asegúrese de estar en la última CU y de que se está ejecutando con TF 4199 para habilitar de forma general todas las correcciones del optimizador de consultas, ya que siempre es posible probar esto en un entorno que no sea de producción y tener en cuenta las regresiones en otras consultas al habilitar la configuración globalmente