Transcript Cardinality Estimates in SQL Server 2014

sqlrockstar@
thomaslarock.com
linkedin.com/in/sqlrockstar
@sqlrockstar
QUERY
QUERY
DATA
Connection
Request
Optimization
• Connection made to SQL Server
• Session ID (SPID) assigned
• Requests sent to engine
• Commonly called queries
• Query Optimization
Connection
Request
Optimization
• Connection made to SQL Server
• Session ID (SPID) assigned
• Requests sent to engine
• Commonly called queries
• Query Optimization
Connection
Request
Optimization
• Connection made to SQL Server
• Session ID (SPID) assigned
• Requests sent to engine
• Commonly called queries
• Query Optimization
Connection
Request
Optimization
• Connection made to SQL Server
• Session ID (SPID) assigned
• Requests sent to engine
• Commonly called queries
• Query Optimization
1.
1.
2.
1.
2.
3.
1.
2.
3.
4.
1.
2.
3.
4.
5.
1.
2.
3.
4.
5.
ASC/DESC Key Estimates
Multiple Column Dependency
Join Estimates
If there are no changes to
estimates or the query
execution plan…
If there are no changes to
estimates or the query
execution plan…
…but you are seeing degraded
performance…
If there are no changes to
estimates or the query
execution plan…
…but you are seeing degraded
performance…
…it’s not related to the new CE,
but general performance
tuning may be required
If there are no changes to
estimates but the query
execution plan changed…
If there are no changes to
estimates but the query
execution plan changed…
…and you are seeing degraded
performance…
If there are no changes to
estimates but the query
execution plan changed…
…and you are seeing degraded
performance…
…unlikely to be related to the
new CE, but general
performance tuning may be
required
If there are changes to the
estimates regardless of the
query execution plan shape…
If there are changes to the
estimates regardless of the
query execution plan shape…
…and you are seeing degraded
performance…
If there are changes to the
estimates regardless of the
query execution plan shape…
…and you are seeing degraded
performance…
…action may be necessary if
the degradation exceeds
workload performance service
level agreements
Auto Create/Update stats
Auto Create/Update stats
Database compatibility mode
Auto Create/Update stats
Database compatibility mode
Query trace flags
Auto Create/Update stats
Database compatibility mode
Query trace flags
XML showplan
http://www.trySQLSever.com
http://www.powerbi.com
http://microsoft.com/bigdata
http://channel9.msdn.com/Events/TechEd
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn