T-SQL for Performance and Accuracy – Vern Rabe: A preconference essential for anyone using SQL Server

SQL Saturday Preconference: February 9th, 2018 in Redmond, WA

Do you think you write T-SQL queries that perform well?  Think again.  I learn something important – and I mean career and life-altering important – when I attend one of Vern’s sessions on T-SQL optimization.  He taught me SQL Server a long time ago and I recommend this all-day session to anyone who needs these skills.

It seems like there’s always a query or a stored procedure that takes way too long to execute. Don’t be quick to blame it on a missing index, or out of date statistics, or insufficient memory. Often a slow performing query can be drastically improved with a rewrite. This preconference will teach you how to write T-SQL with two goals in mind: Getting the results you expect (accuracy), and quickly (performance).

In this preconference we will start with some SQL Server internals, where you will learn how the optimizer determines the execution plan, which join algorithms should be used in which situations, and some of the right and wrong choices that the optimizer can make. We’ll discuss how statistics are key to the optimizer’s choices, how the plan cache is used (for good or bad), how to minimize blocking, and how the ubiquitous transaction log is a performance factor in just about everything.

After we discuss the internals of query execution, you will learn how to use various tools to identify poorly performing queries and the reasons for their poor performance. We’ll dig deep into the execution plan, identifying some details of how the optimizer chose to execute a query. We’ll learn about tell-tale warnings, estimated vs. actual plans, and parallel execution threads. We’ll also learn where the graphical execution plan can mislead you.

Next, we’ll get into specific of T-SQL queries, where we’ll discuss many of the query writing mistakes that can lead the optimizer down a bad path, and most importantly, how to rewrite those queries to give the optimizer the appropriate information to make good decisions. All while making sure the results are exactly what they should be.

Some of the topics covered:

• Graphical Execution Plans
• Statistics
• Cardinality estimator
• Plan cache
• Parallelism
• SARGABILITY
• Implicit conversion
• UDFs
• NOLOCK

All attendees will receive a USB flash drive with a copy of the PowerPoint presentation and all of the demonstration SQL scripts. Although not necessary for the class, if you bring a laptop configured with a USB port and SQL Server 2016, you can execute the scripts along with the presentation.

https://www.eventbrite.com/e/t-sql-for-performance-and-accuracy-tickets-41172854115?aff=es2