In the real world when we face any performance problems, we do not have the luxury to open PowerPoint and study theory. What helps us during the time of crisis are handy scripts which help us fix our performance problems.
In this quick paced, demonstration oriented learning workshop, we will see how we can immediately help our application with performance if we just know what exactly to do.
Though for demonstration we will use SQL Server 2017, most of the learning will work just fine for any previous version of SQL Server.
About Pinal Dave
Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant at SQL Authority. He has been a part of the industry for more than 16 years. During his career, he has worked both in the USA and in India (Technology Evangelist at Microsoft). He received his Master of Science from the University of Southern California and a Bachelors of Engineering from Gujarat University. He has been a regular speaker at many international events like TechEd, SQLPASS, SQL Saturday, SQL Live, Techorama, DevSum and countless user groups.
Module 1: Indexing, Worst Practices, and Solutions
Indexes are often considered a silver bullet to solve performance problems, but the reality is far from the age-old myth. The right index can help improve performance, but the wrong indexes often play party spoiler and reduce your server’s performance. Identifying which indexes to create and which indexes to delete can be cumbersome (or near impossible) if we do not know how to do workload analysis.
In this module of the workshop, we will see some neat tricks related to how to create useful indexes which last longer. We will also build a preventive auto-healing system which can help you focus on more complicated performance issues.
We will cover following essential aspects:
- Unused Indexes
- Missing Indexes
- Duplicate Indexes
- Importance of Clustered Indexes
- Scripts to see Number of Indexes on Table
- Columnstore Index with In-Memory Table
Module 2: Optimal Server and Database Settings for Efficiency
Any good singer needs the support of the decent sound system and cooperative band to be successful. Similarly for any index to be successful, it requires a supportive environment. It is incredibly critical to set optimal configurations for SQL Server Instance and Database. We will also understand methods to maintain our indexes at the optimal health all the time.
In this module of the workshop, we will cover server and database settings which directly affect the performance of indexes and overall system health.
We will cover following essential aspects:
- Min and Max Memory
- Index Creation Memory
- Default Index Fill Factor
- Max Degree of Parallelism and Cost Threshold of Parallelism
- Compatibility Level
- Auto-Create and Auto Update Statistics
- Index Maintenance
Module 3: Kick Start! SQL Server 2017 Performance Tips and Tricks
Every new release of SQL Server brings a whole load of new features that an administrator can add to their arsenal of efficiency. SQL Server 2017 has introduced many new features. This session is carefully crafted to help overcome the beginner’s inertia for DBAs who are using SQL Server for a while but not sure how they can get started with the new features introduced in SQL Server.
Here is the glimpse of the features we will cover in this session.
- Adaptive query plans
- Batch mode adaptive join
- New cardinality estimates for optimal performance
- Indexing Improvements
- Automatic Tuning
- Memory optimizations
At the end of the session, DBA will learn 3 essential processes which can be game changers for their career. After implementing the suggestions discussed in this session, DBA would have extra time to handle more pressing issues which they were earlier not able to focus.
Module 4: WhichIndex – An Interactive Game with WHERE clause and Multi-Column Indexes
WhichIndex is the interactive game that gives a new experience to teach you how to understand how multi-column indexes work with queries with multiple conditions.
This game follows the format of open book exams where every user begins with answers to the questions. Once we understand our solutions, we start our journey to this interactive game. At a different interval of this game, users have to answer the question WhichIndex to the quiz master.
In this self-scored game, the winner is the person who gets the maximum answers wrong. If you have not understood the basics of indexes and performance tuning concept, you will solidify that concept while we play this game.
On a side note – cheating is not only allowed but is heavily encouraged as well!
At the end of this game, users will have an understanding of three essential rules related to Indexes, which YOU will never forget.
Module 5: Mythbusters, Questions, and Answers
In this final part of the module, we will open the floor for questions and answers. We will start our module answer following three critical issues and will continue to answer user questions like:
- Do we have to update statistics after rebuilding indexes?
- What should we do if our index rebuilding is taking forever and creates performance issues?
- When to use INCLUDE column and what columns we should include in that clause?
2 years of experience of working with T-SQL, Indexes and Query Writings.