SQL Server: Benchmarking and Baselining
Learn how to benchmark a SQL Server instance and create a performance baseline to compare against for simplified troubleshooting, applicable for developers, DBAs, and anyone who uses SQL Server from SQL Server 2005 onwards
What you'll learn
When you experience a performance problem, how can you easily tell where the problem is without being able to compare against a known-good set of measurements? This course explains how to create a performance baseline for a SQL Server instance using benchmarking techniques. The course begins by exploring the reasons for benchmarking and baselining, along with how to decide what to capture, when and how often to capture it, and where to store it. It then explains how to use the Performance Monitor tool in detail, along with how to capture query information using SQL Trace and Extended Events, and then analyze that information using the free Clear Trace and ReadTrace tools. After that it describes and demonstrates the multitude of Dynamic Management Views that can be used to gather information about SQL Server, before ending with a summary that pulls all the modules together. This course is perfect for those with no experience of benchmarking and baselining, and those with some experience but who want to solidify their understanding of creating and using benchmarks and baselines. The information in the course applies to all versions from SQL Server 2005 onwards.
Table of contents
- Introduction 2m
- Perfmance Monitor Pros, Cons, and Overhead 2m
- Use Performance Monitor When... 2m
- Using Performance Monitor in Real-Time 2m
- Demo: Performance Monitor 7m
- Counters to Capture 2m
- OS/Resource Counters to Capture 3m
- OS/Resource Counters to Capture (2) 2m
- OS/Resource Counters to Capture (3) 3m
- SQL Server Counters to Capture 3m
- SQL Server Counters to Capture (2) 3m
- SQL Server Counters to Capture (3) 2m
- SQL Server Counters to Capture (4) 3m
- Counter Collection 2m
- Data Collector Sets 1m
- Demo: Data Collector Sets 7m
- Processing Performance Monitor Data 2m
- Demo: relog 9m
- Processing Performance Monitor Data (2) 1m
- Demo: PAL 8m
- Summary 1m
- Introduction 3m
- When to Use DMVs 2m
- Capturing DMV Data 2m
- DMVs to Consider for Data Capture 3m
- Demo: sys.dm_os_performance_counters 5m
- DMVs to Consider for Data Capture (2) 3m
- Demo: sys.dm_io_virtual_file_stats 4m
- DMVs to Consider for Data Capture (3) 3m
- Other Data You Can Capture 3m
- Demo: sys.configurations 4m
- WhoIsActive 2m
- Demo: WhoIsActive 7m
- Summary 1m
About the author
Erin Stellato is a Principal Consultant with SQLskills and a SQL Server MVP. She has worked as a SQL Server professional since 2003, spending 12 years of her career at two different software companies. Erin discovered a passion for all things data while completing her M.S. in Motor Control at the University of Michigan. As she entered the IT field, she discovered the power of relational databases. During her IT career, Erin has worked closely with customers, users and colleagues to support bus... more