You think locking and blocking may be slowing down your application, but you’re not sure how much of a problem it’s causing. Learn how to find lock waits, decode what they mean, and identify which tables and queries are involved in your worst blocking problems.
Want to use the tools mentioned in the webcast? Get tools, scripts, and more articles on locking here.
You’ve been working with SQL Server for a couple of years, but you’re not really sure what’s going on in the System Databases folder. What gets stored in Master, Model, and MSDB? What processes use them? In the event of a crash, should you restore them? Brent Ozar will give you an introductory tour in this half-hour video:
Are you still rebuilding all of your indexes every week – or heaven forbid, every night – and still having performance problems? Odds are, you’re not actually making things better – you’re making them worse! I explain why in this video:
For more, read my post: Stop Worrying About Index Fragmentation.
Open up Performance Monitor (aka Perfmon), then:
- Windows 2008 & newer – expand Monitoring Tools, click on Performance Monitor
- Windows 2003 & older – click System Monitor
This brings you to the graph. Click on the metrics at the bottom and click the red X at the top to remove each of them. We want to start clean.
Then click the + button to add a new counter. At the top, where it says “Select counters from computer”, put in the name of your SQL Server starting with two backslashes, like \\MYSQLSERVER and hit the Tab key. Perfmon will pause for a few seconds while it gets the list of Perfmon counters on that server, and then shows them in a list below.
Add these counters:
- Memory – Available MBytes
- SQLServer: Buffer Manager – Page Life Expectancy
- SQLServer: Memory Manager – Memory Grants Pending
- SQLServer: Memory Manager – Target Server Memory
- SQLServer: Memory Manager – Total Server Memory
- SQLServer: SQL Statistics – Batch Requests/sec
- SQLServer: SQL Statistics – Compilations/sec
While data starts to come in to the graph, watch our 30-minute video on How to Prove Your SQL Server Needs More Memory. I explain what the counters mean and how to interpret them:
The takeaways, arranged from easy to hard:
If your server is slow and you’ve got less than 64GB of memory, learn how to explain business costs as I explain in the video. It’s smarter to buy $500 worth of memory rather than spend days struggling with a problem and making risky changes to your server. Sure, the business is going to argue against you – that’s their job, and it’s your job to clearly explain the pros and cons of each side. You have to persuade.
If Memory Grants Pending is averaging over 0, queries can’t even start because they can’t get enough memory. You may have queries that have incorrect memory grants, or you may just desperately need more RAM.
If Compiles/Sec is over 10% of Batch Requests/Sec, SQL Server may not be able to cache execution plans. This causes increased CPU use and slower queries. There’s a lot of gotchas with this one, though – now the hard work starts, because you have to spend time analyzing your workload to see if the execution plans can even be reused.
If Page Life Expectancy is low, you may need memory to cache more data pages. In the video, I explain how to start with tuning indexes and queries first (since that’s typically a lower cost than buying more memory once you’re over 64GB). I mention these tools:
When us geeks want something, we just point at it, look at our manager and say, “I want this. Buy it for me.”
Oddly, it doesn’t seem to work.
To get what we want, we have to think like a manager.
Step 1. Define a user’s Pain Point.
You need someone else on your side – not another geek, but a business person who can stand up for you and say, “There’s a problem causing us pain, the business needs a solution.”
Here’s some sample pains – and notice how they have both the person AND the pain point:
- The users want the app to go faster.
- Our developers need to implement more advanced features that require more from the database.
- The CFO is tired of us throwing hardware at the problem.
- The users want more uptime and less maintenance windows.
If you’re not sure whether or not training will solve your pain point, ask the trainer. For example, if you want to know if our performance training is going to fix your performance issues, email us at Help@BrentOzar.com and describe the issues you’re facing. We’ll tell you the fastest way to get those problems solved, and often, it’s not the training classes. (I don’t want someone eagerly showing up at our classes only to find out they’re in the wrong one.)
Step 2. List the free solutions you’ve already tried.
Your manager isn’t dumb: she solves problems with Google just like you. If you bring any pain point to her office, she’s going to turn to her computer, put it into Google, and ask you if you’ve tried those methods.
You need to head her off at the pass by listing what you’ve already tried:
- Watching free training videos about the problem
- Attending local user groups
- Posting messages on DBA.StackExchange.com and SQLServerCentral.com
And here’s my favorite phrase to seal the deal: “I’ve tried all the free easy buttons, and we’ve seen some relief, but now we need to take it to the next level.” That makes it clear that you haven’t been sitting idle all day – you’ve actually done small things and gotten small return – but now you need resources.
Step 3: Give your manager 3 options for pain relief.
When you shop for a new laptop or a new camera, you comparison-shop. You look at all the options out there, and you figure out which one is best for your needs.
Your manager wants to do the same thing, but she wants to have the choices laid out clearly. She wants to know that you’ve done your homework, because she knows there’s more than one option.
Here’s how dot-com sites pull it off – they give you three options from their own product offerings so that you feel like you’re making a decision, even though there’s only one choice – them! Here’s an example from WPengine.com, a hosting company:
It’s kind of small, medium, and large. It’s the Goldilocks approach. It lets your manager quickly see the differences between the options, and pick the one that suits your needs.
Here’s how you can present options to your manager:
YOU get to pick the options, and you should only pick options on there that you truly believe will get the problem solved. (After all, you don’t want your manager to pick something you don’t actually want.)
Make sure the choices really are different, like a small/medium/large. Don’t give them 3 options that cost the same thing, because those aren’t really choices – it’s your job to find the best option in each price category before you show the options to management.
Have your research ready for each choice – I like giving a “more info” link that goes to the product’s page. After all, your main goal is for your manager to forward this up the chain as evidence that you made a good decision, and somebody along the way is going to want to click on a link.
Download the spreadsheet now and casually walk into your manager’s office. Your job isn’t going to get any cooler by itself. There might be some leftover budget money this year – get your hands on it!
Some things in SQL Server are hard – but some of ‘em are actually pretty easy. In this video, we’ll talk about the pros and cons of SQL Server features in a single slide including multi-master replication, the remote DAC, Resource Governor, filtered indexes, filegroups, column store indexes, and more:
Want to watch more of our webcasts live? We just updated our upcoming webcast list – hit up BrentOzar.com/go/tuesday.
Sometimes our identical twin turns out to be evil – and sometimes our SQL Server does, too. When we encounter data corruption, hardware failure, and OS errors, we need to fail over from our principal to our better mirrored twin. I’ll show you how to handle database mirroring failovers in both high safety and high performance setups in this week’s webcast:
Have you ever wished your SQL Server could have an identical twin, holding the same data, in case you ever needed it? SQL Server mirroring provides just that, and you can choose if you want it for high availability or disaster recovery. If you’ve ever been curious about what mirroring really is, and what the pros and cons are, this is the session for you.
From the Dept of Corrections: During the webcast a viewer asked in Q&A if automatic page repair was a one way or two way street. Kendra answered that if the mirror gets an IO error it will go into a suspended state. This is somewhat correct but incomplete– the mirror will also try to correct the issue with a page from the principal afterward and attempt to resume mirroring. More info here.
Occasionally you check out job listings and wonder, “Could I have a better job?” If you’ve been working as a database administrator for a few years, it’s time to learn how to tell a dream job from a potential nightmare. Join Kendra Little for a 30 minute guide on how to read hidden messages in job listings and find the right next step for your career.
Need to practice for your next interview?
We’ve got training! For $29 you get 18 months of access to videos helping you prepare and practice for interviews with 100 practice DBA interview questions on on your desktop, laptop, iPad, or iPhone. Learn more or buy it now.
Liked this video? Check out our upcoming free webcasts.
Are you frustrated by third party applications that you can’t change, but you have to support? Tired of beating your head against the wall when your users complain about things you can’t fix? In this 30-minute session, Brent Ozar will show you his favorite tricks to get the most performance without losing support. He’ll show you how to interact with vendors and get what you want – without getting heartburn:
Like that video? We’ve got half a dozen more scheduled for upcoming Tuesday lunches. Click the boxes you want and sign up for free.