33 Tips and Scripts: The Secret Toolbox for Performance Tuning and Optimizing Queries

09:00 - 17:00

This demo-oriented, day-long session will answer the questions which DBA and Developers often wonder about Performance Tuning and Query Optimization. We will discuss 33 important concepts which revolve around different versions of SQL Server.

Workshop outline:

This demo-oriented, day-long session will answer the questions which DBA and Developers often wonder about Performance Tuning and Query Optimization. There are some problems which have straight forward answers, but there are some problems which are often invisible or needs attention without even a problem statement. This day-long session will be divided in these three major components

1) Identifying common patterns and anti-patters for database performance tuning
For example: a) slow running queries, b) IO Intensive operations, c) CPU bottlenecks, d) memory optimizations e) tempdb contention and much more

2) Resolving optimization hindrances and issues proactively
For example: a) indexing strategies, b) file growths, c) server deployment best practices, d) statistics, e) wait stats and queues and much more

3) Avoid query performance surprises when upgraded to the latest version of SQL Server 2014 and 2016
For example: a) new cardinality engine, b) column store indexes best practices c) live plans, d) azure enhancements, e) in-memory OLTP optimizations and much more.

We will discuss 33 different tips and tricks along with scripts in this session and every attendee will have those scripts handy upon completion of the session.

The session will have a unique storytelling style of presentation. We will have a big story arc (an experience DBA with Jr. Intern) with three sub story lines. 33 total scripts and tips will be discussed. Stay alert for pop quizzes.

Session Prerequisites:
Basic understanding of SQL Server concepts.

What you’ll take away:

Session Goal 1:
Identifying common patterns and anti-patters for database performance tuning
For example: a) slow running queries, b) IO Intensive operations, c) CPU bottlenecks, d) memory optimizations e) tempdb contention and much more

Session Goal 2:
Resolving optimization hindrances and issues proactively
For example: a) indexing strategies, b) file growths, c) server deployment best practices, d) statistics, e) wait stats and queues and much more

Session Goal 3:
Avoid query performance surprises when upgraded to the latest version of SQL Server 2014/2016
For example: a) new cardinality engine, b) column store indexes best practices c) live plans, d) azure enhancements, e) in-memory OLTP optimizations and much more.

Additional Notes:
This session will be a demo heavy session with a pop quiz at every hour to keep attendees attentive. We will have a big story arc (an experience DBA with Jr. Intern) with three sub story lines. 33 total scripts and tips will be discussed.

Pinal Dave

Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.