Blog Post

SQL Saturday 496 Exeter – PreCon Agenda



Unfortunately due to matters outside of my control, SQL Saturday 502 in Edinburgh has been cancelled, I would like to thank the team though for selecting my pre-con.  Let me know if you would have liked to attend and if a viable option, I would be more than happy to still give the session at some other time.

Also ,after a few requests I thought it best to publish a fuller agenda for the precon at Exeter.

Agenda AM

Plan and optimizer basics.   We start the day with an overview of the optimizer and touch base on many different aspects of it , the functionality involved, why it can sometimes do ‘crazy’ things and remediation of such things.  Overall it will look like this: 

  • Physical and Logical Operators
  • Things the optimizer can do to help you
  • Things you can do to help the optimizer
  • Why schema definition is be important
  • Implied knowledge vs explicit knowledge

Estimation and Statistics  We will start building on the knowledge previously gained to understand how sqlserver ‘costs’ a plan.  The basis of this is Statistics and we will drill down into the algorithms on Estimation and the ‘guesswork’ and assumptions that come with that.

  • The metrics used to cost plans
  • What does estimation mean ?
  • What are statistics and how are they used ?
  • What effect do constants and variables have?
  • Stored procedure effect
  • Remediation for when the assumptions made are wrong.

All being well, lunch Smile

Agenda PM

SQL2014/16 – The new cardinality estimator The cardinality estimator had a significant rebuild in SQL Server 2014,  this section will look at those changes and their effects.  These are sometimes subtle and sometimes not,  significantly though if you are running SQL server 2014 you will need to understand  why your resource usage has changed compared to previous versions.

  • Cardinality Estimator was rewritten in SQL2014
  • Many changes that have subtle and not-so subtle effects
  • Incrementing statistics
    • Statistics at the partition level
  • SQL2016 – Query Store
    • Alerting you to plan changes

Query Optimizer Internals Finally we will get further into the inner workings of the optimizer than you ever thought possible.  If you wish to know why one candidate plan has been preferred over another than this is where to look.  This is a deep dive session exposing inner workings and how to interpret some extremely verbose logging.  

  • Deep dive using documented and undocumented trace flags
  • Gaining further insight into the optimizer operations
  • Examining Stages, The Memo, Trees and Rules

If this sounds like a fun way to spend the day, I would love your company Smile contains all the signup details.