Cardinality Estimator en SQL Server 2014. ¿Qué es? Y

cómo nos beneficia?

11 de Marzo 2015 (12 pm GMT -5)

Percy Reyes

Resumen:

En esta sesión revisaremos de que se trata el

CE en SQL Server 2014, cómo nos beneficia,

y consideraciones a tomar en cuanto en una

migración a esta versión.

Está por comenzar:Próximos Eventos

Configuración y casos de uso para

AlwaysON availability groups

readable secondaries

1 de Abril

Kenneth Ureña

Creando una solución Always On

SQL Server 2014 Híbrida

18 de Marzo

Miguel Escobar

Power Query y el Lenguaje M

25 de Marzo

German Cayo

Moderador: José L. Rivera

Manténgase conectado a nosotros!

Visítenos en http://globalspanish.sqlpass.org

/SpanishPASSVC

lnkd.in/dtYBzev

/user/SpanishPASSVC

/SpanishPASSVC

4

5

Oportunidades de Voluntariado

PASS no pudiera existir sin personas apasionadas y

dedicadas de todas partes del mundo que dan de su

tiempo como voluntarios.

Se un voluntario ahora!!

Para identificar oportunidades locales visita

volunteer.sqlpass.org

Recuerda actualizar tu perfil en las secciones de

“MyVolunteering” y MyPASS para mas detalles.

Sigan Participando!

• Obtén tu membresía gratuita en sqlpass.org

• Linked In: http://www.sqlpass.org/linkedin

• Facebook: http://www.sqlpass.org/facebook

• Twitter: @SQLPASS

• PASS: http://www.sqlpass.org

Cardinality Estimator en SQL Server 2014.

¿Qué es? Y cómo nos beneficia?

11 de Marzo de 2015

Percy Reyes

Microsoft SQL Server MVP

Sr. SQL DBA with over 10 years

Author at MSSQLTips.com | SQL PASS Peru Chapter Leader

MCITP DBA, MCITP Dev, MCTS, MCP

www.percyreyes.comModerador: José L. Rivera

Agenda

What inputs is used by the Query Optimizer

What is CE?

The importance of CE

New deployments vs upgrade

Validating a Query’s Cardinality Estimator Version

Why can query plans change with the new CE?

How to control new cardinality estimator

Recommendations

Guidelines on query performance troubleshooting with new CE

What inputs is used by the Query Optimizer

- The SQL Query and/or Stored Procedure

- If the query or stored procedure is parameterized, then the value of the parameters

- Knowledge about the index structures of table(s) to be accessed

- Statistics of index(es) and columns of the table(s) to be accessed

- Hints assigned to the query

- Global max degree of parallelism setting

- Hardware Settings (How many CPUs, How much RAM, Disk Subsystem configuration)

What is CE?

- Cardinality estimations are predictions of final row count and row counts of intermediate results (such as joins, filtering and aggregation. The

component which does cardinality estimation is called Cardinality Estimator (CE) .

- Prior to SQL Server 2014, cardinality estimator was largely based on SQL Server 7.0 code base.

- The cardinality Estimator has been redesigned.

The importance of CE

- Under estimating rows can lead to:

- The selection of serial plan when parallelism would have been more optimal.

- Inappropriate join strategies.

- Inefficient index selection and navigation strategies.

- Memory spills to disk, for example, where not enough memory was requested for sort or

hash operations

- Inversely, over estimating rows can lead to:

- Selection of a parallel plan when a serial plan might be more optimal.

- Inappropriate join strategy selection.

- Inefficient index navigation strategies (scan versus seek).

- Inflated memory grants.

- Wasted memory and unnecessarily throttled concurrency.

So, improving the accuracy of row estimates can improve the quality of the query execution

plan and, as a result, improve the performance of the query.

New deployments vs upgrade

- SQL Server 2014 uses database compatibility level to determine if new cardinality estimator

will be used

- You will continue to use old cardinality estimator in upgrade and restore situations by

default unless you have manually changed the compatibility level to be 120.

- You will use cardinality estimator based on current database under the query is compiled

(even though the query references temp table)

- Regarding upgrade of system databases:

- model, msdb and tempdb will be changed to 120

- master system database retains the compatibility level it had before upgrade

Validating a Query’s Cardinality Estimator Version

In the SQL 2014 XML plan, check the new attribute in StmtSimple called CardinalityEstimationModelVersion

- Capturing a new SQL Server 2014 XEvent called query_optimizer_estimate_cardinality

Why can query plans change with the new CE?

1. The new CE is calculating combined filter density/selectivity differently

The way how the CE calculates the selectivity of a combination of filters submitted with a query

- With Old CE:

- Treating the selectivity/density of each filter/column independently

- Old calculation formula: d1 * d2* d3* d4* … dn

- Row Estimated=# Total of rows / (1/combined density)

- With NEW CE:

- Combinations of values of different columns are not as independent.

- New CE works based on a new calculation formula to calculate combined density of filters.

- The NEW calculation formula now would d1 * d2^(1/2) * d3^(1/4) * d4^(1/8)

- Row Estimated=# Total of rows / (1/combined density)

Why can query plans change with the new CE?

2. The new CE is treating ascending/descending key scenarios differently

What if we would add a new value out of the range values of an statistics?

- An update statistics with FULLSCAN was performed and after a new value is added, which would be

out of the range of the column statistics, then the new CE would estimate 1 row. The same behavior is

for old CE.

- An update statistics performed in the default sampling and after a new value is added, which would

be out of the range of the column statistics, then the new CE also will now report an estimate of

around 1000 rows. The old CE would estimate 1 row.

- An update statistics performed in the default sampling or with FULLSCAN and after several NEW

values are added, which would be out of the range of the column statistics, then the new CE also will

now report an estimate of around 1000 rows.

IMPORTANT: The # of rows estimated which are found outside the boundaries of the statistics will

be majorly impacted by the # of values and their occurrence in the column according to the

statistics.

- What if we would add a new value within the range values of an statistics?

- The behavior here is not too much different between the old and the new CE

- The new as well as the old CE would return an estimate of 1 row only

How to control new cardinality estimatorTrace flag at query level

- You use QUERYTRACEON hint

- Example: SELECT* FROM Product where ProductId= 4672 OPTION(QUERYTRACEON 2312)

Trace flags at server level

- Flag 2312 is used to force new cardinality estimator

- Flag 9481 is used to force old cardinality estimator

- If you enable both trace flags, neither will be used. Instead, database compatibility level will determine

which version of cardinality estimator to be used

Trace flag at database level

- Via database compatibility level

Order of Precedente

Recommendations

- Before changing to the new CE in production, test all critical workloads using representative data in a

production-like environment.

- If you cannot change or fully test the existing application, you can still migrate to SQL Server 2014.

However, the database compatibility level should remain below 120 until you can perform thorough

testing.

- To leverage new SQL Server 2014 features without activating the new CE, change to the latest database

compatibility level and enable trace flag 9481 at the server level. Use DBCC TRACEON with the -1

argument to enable the trace flag globally. As an alternative, you can use the –T startup option to enable

the trace flag during SQL Server startup.

- If creating a new database for a new application, use database compatibility level 120 by default.

Guidelines on query performance troubleshooting with new CE- Statistics

- Make sure you enable auto update and auto create statistics for the database

- If you have large tables, you may need to schedule jobs to manually update statistics.

- Tune your indexes

- XML Plan will display missing index warning for a query

- Missing index DMVs

- Database Tuning Advisor (DTA) can be used to help you tune a specific query

- Analyze the execution plans to detect and fix any index performance issue

- Constructs not significantly addressed by the new cardinality estimator

- Table variables. You will continue to get low estimate (1) for table variables.

- Multi-statement table valued function (TVF): Multi-statement TVF will continue to get estimate of

100 instead of 1 in earlier version. But this can still cause issues if your TVF returns many rows.

- Behaviors of Table valued parameter (TVP) and local variables are unchanged. The number of rows

of TVP at compile time will be used for cardinality estimate regardless if the rows will change for

future executions. Local variables will continued to be optimized for unknown.

Thank You: Q&A

PERCY REYES• Twitter: @percyreyes

• Blog: mssqlinternals.com

• percy.reyes@live.com

• Website: www.percyreyes.com

sqlpassperu.com

Power Query y el Lenguaje M

18 de Marzo (12 pm GMT -5)

Miguel Escobar

Resúmen:Power Query es una nueva herramienta de la familia Power BI que se

encarga del proceso de extracción y transformación de datos. Ha sido

catalogada como una de las mejores herramientas para Excel en salir al

mercado por parte de Microsoft en los últimos años y en esta sesión

conocerás muchas razones por las cuales te encantará.

Próximo Evento