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

Course info

Rating

(222)

Level

Intermediate

Updated

Oct 19, 2012

Duration

3h 20m

Description

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.

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 and her interests include Internals, Performance Tuning, High Availability and Disaster Recovery. Erin is an active member of the SQL Server community as a presenter and blogger.

Section Introduction Transcripts

Performance MonitorHi, this is Erin Stellato from SQLskills. com. And I'm recording this course for Pluralsight. This course is about SQL Server Benchmarking and Baselining. And this is module 3, Performance Monitor. Performance Monitor is one of my favorite utilities if you haven't already figure that out from some of my previous comments. And part of it is probably just that old habits diehard. I use PerfMon a lot when troubleshooting issues in the SQL Server 2000 days. But there are other reasons that I still like it. First, it's built into Windows and has been shift with Windows since NT351. And this is great because it's a free utility that you can use to understand what's going on with server and it's available on every Windows machine you access. It isn't anything you have to purchase or download. It's always there. And when you're working with different client systems, it's nice to just know that what you need and what you're familiar with is going to be there every time as oppose to having to download or perhaps convince someone to purchase a tool. Second, PerfMon allows you to look at current performance or you can capture information over a period of time and then review it. There's great flexibility in terms the data you can capture, how often and for how long. And again, if we're talking about baseline for SQL Server this is great, because in addition to capturing information about SQL Server itself, we can also look at the utilization of hardware resources and we can see how the operating system is performing. Finally, Performance Monitor allows us to capture data on a schedule basis or in responds to a particular threshold being reached. And you also have the ability to automate processing of that data in addition to do it manually.

Capturing QueriesHi! This is Erin Stellato from SQLskills. com and I'm recording this course for Pluralsight. This course is about SQL Server Benchmarking and Baselining and this is Module 4, Capturing Queries. Whenever I hear someone talk about baselines, my first thought goes to numbers. Think back to the example I gave, way back in the beginning where you go to your doctor and then measure your pulse, blood pressure, height, and weight and then compare the numbers the next time you go in. The numbers are needed because a physician can't just say, "Hey, how you're feeling? ", and then use a response, fine, lousy, amazing, as a baseline. That response is qualitative and what your doctor really needs to measure change is quantitative data. And this is true for SQL performance. The words most often used to describe query performance are probably fast and slow but those words really don't tell us anything, the numbers do. Therefore, we need to take query performance and quantify it. We need to understand, not just how a query executes in terms of its query plan, we also need to understand its resource utilization as the resources are what ultimately determine the capacity of a system and the better we use those resources, the more we can do. So we can compare query performance quantitatively but it's a two part process. We have to capture the queries and then we have to analyze the output. And capture is really the easy part. We can do that with SQL Trace, Extended Events or the DMVs. And note that the DMVs will be covered in the next module. Analysis, this is a little more tricky. And for that we need to use Profiler, XML, or other third-party utilities. And this is what we will cover in this module.

Pulling It All TogetherHi. This is Erin Stellato from SQLskills. com. And I'm recording this course for Pluralsight. This course is about SQL Server: Benchmarking and Baselining, and this is Module Six, Pulling It All Together. At this point you might think I've said everything I had to say about baselining, and you would almost be right. But I'm big on closure; so I want to do a final recap. And I really want you to start this endeavor off on the right foot, with focus and a clear goal. So in this short module, we will discuss what you need to do before you start. And I say that, even though I bet many of you have already have tried to implement things in your environment because you just couldn't wait, and I get that and that's cool, but I'm still going to talk about it. We're going to review at a high level the different options of data available. And finally, we'll revisit what you need to consider as you create this methodology. ( Pause )