John Deardurff
SQLMCT.com
Indianapolis, Indiana, United States
Actions
John has been a Microsoft Certified Trainer (MCT) since the summer of 2000 and specializes in teaching Azure SQL and SQL Server workshops. He is currently a Data Cloud Solutions Architect for Microsoft, a former MCT Regional Lead for the Eastern United States, and a Data Platform MVP alumni.
Area of Expertise
Getting Started: Writing Stored Procedures
In this demonstration-heavy session, we will take a journey into writing a stored procedure. We will start by discussing the differences between auto-commit and explicit transactions. Next, we dive into batch-terminating vs statement-terminating errors, and how to handle them effectively. Finally, we will compare ad-hoc queries vs stored procedures, and when to use each one.
By the end of this session, you will have a solid understanding of how to write an efficient and effective stored procedure that will help you get the most out of your SQL Server database.
Getting Started with Non-Clustered Indexes
Non-clustered indexes are a powerful tool for optimizing query performance in SQL databases. However, creating effective non-clustered indexes requires understanding how they are structured and how they interact with the query optimizer.
In this session, you will learn how to use dynamic management views and execution plans to inspect how non-clustered indexes are stored and organized. You will also learn how to design non-clustered indexes that cover your queries, how to choose the optimal order of columns in multi-column indexes, and how to compare the benefits of index seeks vs scans. By the end of this session, you will be able to apply best practices for creating and using non-clustered indexes in your own databases.
Getting Started with Accelerated Database Recovery
SQL Server has redesigned the database engine recovery process to improve database availability. In this session, we will explore how Accelerated Database Recovery (ADR) provides lightning-fast database recovery, instantaneous transaction rollback, and aggressive log truncation. We will also discuss how to enable ADR, how to specify a filegroup to contain the Persistent Version Store (PVS), and best practices for managing and configuring ADR. By the end of this session, you will have a better understanding of how to leverage ADR to improve database availability.
Getting Started with Intelligent Query Processing
Intelligent Query Processing (IQP) is a family of features that aim to optimize query performance by automatically adapting to the data and workload characteristics. IQP features can have a broad impact on improving the performance of existing workloads with minimal implementation effort. However, IQP is not a single feature, but a collection of features that have evolved over time.
In this session, we will walk through the history of the IQP family and how these features can help you solve common performance problems. We will begin with Adaptive Query Processing features that were introduced in SQL Server 2017, such as adaptive joins, memory grant feedback, and interleaved execution. Then, we will step through the new features of both SQL Server 2019 and 2022. Finally, we will discuss best practices for managing and configuring IQP features through compatibility levels and database scoped configurations.
Business Continuity in Azure SQL Databases
Azure SQL Database is a cloud-based relational database service that offers high availability, scalability, and performance for your applications. But what is the correct service tier when it comes to business continuity for your workload and budget?
In this session, you will learn about the differences and trade-offs between the General Purpose and Business Critical service tiers in terms of Failover Clustering, Always On Availability, and Zone-Redundant capabilities. Whether you are new to Azure SQL Database or an experienced user, this session will help you understand and leverage the business continuity options that Azure SQL Database offers.
Getting Started with Query Store
This performance tuning feature introduced in SQL Server 2016 allows you to record query plans to provide a historical look at the efficiencies or regressions of your queries. In this session, we will provide an overview of the Query Store, how to configure it for your databases, and how to use the built-in reports and catalog views. We will also discuss best practices for managing and configuring the Query Store. By the end of this session, you will have a better understanding of how to leverage the Query Store to improve query performance.
Getting Started with SQL Server Data Security
Data security is a crucial aspect of database management, especially when dealing with sensitive or confidential data. SQL Server offers three features that can help you protect your data from unauthorized access or exposure: Row-Level Security, Dynamic Data Masking, and Always Encrypted.
In this session, you will learn how to use these features to implement different levels of data security in your SQL Server databases. By the end of this session, you will be able to apply these features to your own data security scenarios.
Getting Started with Execution Plans
Execution plans are one of the most powerful tools for SQL Server developers and administrators. They show how SQL Server executes your queries, what resources are used, and where the potential bottlenecks are. However, reading execution plans can be intimidating and confusing, especially for beginners.
In this session, we will introduce how to read execution plans in SQL Server Management Studio. We will cover the basics of execution plan anatomy, how to use graphical and textual plans, and how to compare different plans. We will also identify the most common operators used in accessing both heaps and clustered indexes, discuss the three physical join type operators (Loop, Hash, Merge), and finally what to look for in an execution plan when troubleshooting queries.
By the end of this session, you will have a better understanding of how to read and interpret execution plans to improve query performance. You will also learn some tips and tricks to make your execution plan analysis easier and more efficient.
Getting Started with Locking and Concurrency
Locking is a mechanism that ensures data consistency and integrity in SQL databases. However, locking can also affect query performance and concurrency, depending on the level and type of locks used.
In this session, you will learn how locks are placed at the row, page, or table level, and how the Locking Manager decides when to escalate locks to prevent excessive locking overhead. You will also see examples of shared and exclusive locks, and how they affect concurrent transactions. By the end of this session, you will be able to identify and avoid common locking problems, and optimize your queries for better performance and scalability.
Performance Tuning with Waits and Queues
Are you tired of waiting for your SQL Server to respond? Do you want to identify bottlenecks and improve performance? In this session, we will discuss how to use waits and queues to identify where SQL Server is spending a substantial amount of time waiting for resources. We will also explore how waits and queues are used in the task execution model and how to use Dynamic Management Objects to identify current wait type activity. By the end of this session, you will have a better understanding of how to optimize your SQL Server performance.
The PROCESS of Query Optimization
Have you ever wondered what happens when the relational engine processes a query? How does the query optimizer choose an appropriate execution plan? In this session, we will answer both of those questions and more as we take a journey into the PROCESS of query optimization. Topics of discussion include how queries are Parsed, Resolved, Optimized, Complied, and Executed to return SQL Sets (PROCESS), we will step through the optimization phases from Trivial plans through Full optimization, and finally how the Max Degree of Parallelism (MAXDOP) and Cost Threshold of Parallelism (CTOP) settings are used in plan optimization. By the end of this session, you will have a better understanding of how to optimize your queries and improve query performance.
Please note that Sessionize is not responsible for the accuracy or validity of the data provided by speakers. If you suspect this profile to be fake or spam, please let us know.
Jump to top