Blog

Your users are frustrated because the app is too slow, but you can’t change the queries. Maybe it’s a third party app, or maybe you’re using generated code, or maybe you’re just not allowed to change it. Take heart – there’s still hope.

I do this every week, and I’ll share my proven methodologies to performance tune with indexes, SQL Server configuration switches, and hardware.

In this one-day workshop on Friday, December 4 at the Microsoft office in Chevy Chase, you’ll learn:

• Simplified internals – how SQL Server turns your query into an execution plan
• Wait stats solutions – a list of fixes for most common bottlenecks
• Plan cache analysis – how to discover the most resource-intensive queries so you know if indexes and hardware will work
• Monitoring metrics – what DMVs and Perfmon counters to monitor, and what thresholds to alert on
• Hardware warning signs – how to prove your server is undersized for your workloads
• Tools and scripts – the best free and paid tools to make your tuning job easier

This session is for developers, DBAs, and consultants who have to make SQL Server go faster. You should be comfortable writing queries and creating tables, but not as confident about interpreting SQL Server’s DMVs and diagnostic data. I’ll train you the same way I train my own clients.

This session will be demo-heavy, and you can play along. Bring a laptop running SQL Server 2005 or newer, with at least 100GB of free drive space. We’ll distribute the Stack Overflow demo databases on USB drives for you to restore, and you can follow along with the demos. You’ll get the load test scripts, run them against your laptop, and see how we solve various challenges.

Space is limited to 100 attendees. Get your spot now for $199, and then register for SQLSaturday DC too. See you there!

Doctors Without Borders is a truly awesome charity. Medical professionals volunteer their own personal time to go to war-torn countries and solve issues of world health.

SQL Server community member, MCM, and all-around-good-guy Argenis Fernandez organizes Argenis Without Borders, a giving event to help. Last year, the SQL Server community helped raise $13,000 for this well-deserving charity.

Now it’s your turn. Please donate $25 if you can. It would mean a lot to me, and I know it’d mean a lot to people all over the world who urgently need medical help. You’ve got a great job – it’s your turn to give back.

Donate now. Seriously, right now.

Decisions are emotional, right? Brent loves Sanka and I love Postum. We fight about it all the time. But when we wanted to settle the debate once and for all, we engineered a test to figure out who was right. You can do the same thing to take the personal investment out of technical decisions at work.

Check Yourself at the Door

The first thing you need to remember is that your opinions are just as valid everyone else involved. You need to move those aside and be ready to be wrong.

If the other people involved don’t want to play along, just tell them “Heck, I’d be happy to be wrong because I’ll learn something.” It’s not a bet or a contest, you’re just offering up your willingness to be wrong. Being wrong is great, especially when it’s your turn to be right later on.

Test, Test, Test

The next step to making that decision is to figure out a test. This test has to depend on your different opinions. The purpose of this test is to get your opinions out of the conversation.

Doing this correctly is really hard. You need to figure out:

• What are both sides saying?
• Which metrics will prove both points?
• What won’t prove anyone’s points?
• What’s the fastest way to test both options?
• What’s a realistic scale to for testing?
• What’s the worst that could happen if either side is right?
• What’s the worst that could happen if either side is wrong?
• If you can only run one test, which test should you run?

Hey, You Said This Was Easy!

All of this sounds like a lot of work. It turns out being factually right is just as much work as being factually wrong. If you really want to make sure that you’re choosing the right solution to a problem you need to figure out which option is the most right way to solve the problem – both solutions could be good, but one just might be better. The only way to get to proof is to test everything.

The next time there’s an argument on your team, or between two teams, figure out the best way to test each side’s ideas instead of spending your time arguing about which solution is the best.

Of course, you could always just fight in the parking lot like school kids. I hear that works well, too.

Most of us get caught up in fragmentation, finding the slowest queries, and looking at new features. We forget the little things that make managing a SQL Server easier – like cylcing the SQL Server error logs.

What’s the Error Log?

The SQL Server error log is a file that is full of messages generated by SQL Server. By default this tells you when log backups occurred, other informational events, and even contains pieces and parts of stack dumps. In short, it’s a treasure trove of information. When SQL Server is in trouble, it’s nice to have this available as a source of information during troubleshooting.

Unfortunately, if the SQL Server error log gets huge, it can take a long time to read the error log – it’s just a file, after all, and the GUI has to read that file into memory.

Keep the SQL Server Error Log Under Control

It’s possible to cycle the SQL Server error log. Cycling the error log starts a new file, and there are only two times when this happens.

1. When SQL Server is restarted.
2. When you execute sp_cycle_errorlog

When SQL Server cycles the error log, the current log file is closed and a new one is opened. By default, these files are in your SQL Server executables directory in the MSSQL\LOG folder. Admittedly, you don’t really need to know where these are unless you want to see how much room they take up.

SQL Server keeps up to 6 error log files around by default. You can easily change this. Open up your copy of SSMS and:

1. Expand the “Management” folder.
2. Right click on “SQL Server Logs”
3. Select “Configure”
4. Check the box “Limit the number of error log files before they are recycled”
5. Pick some value to put in the “Maximum number of error log failes” box
6. Click “OK”

It’s just that easy! Admittedly, you have to do this on every SQL Server that you have, so you might just want to click the “Script” button so you can push the script to multiple SQL Servers.

Automatically Rotating the SQL Server Error Log

You can set up SQL Server to automatically rotate your error logs. This is the easiest part of this blog post, apart from closing the window.

To cycle error logs on a regular basis, restart your SQL Server nightly.

Only joking.

You can set up a SQL Agent job with a T-SQL step. All it has to do is EXEC sp_cycle_errorlog. Schedule the SQL Agent job to run as frequently as you’d like and you’re good to go. The upside of this approach is that it’s automatic and the SQL Server error logs will be more granular, making it easier to find the error messages you’re looking for.

It’s Just That Easy!

Cycling the SQL Server error log is easy – you just need a regularly scheduled agent job. Rotating the logs makes it easier to find error messages. Let’s face it – you’re only looking for error messages when there’s a problem. That’s all there is to rotating the error logs.

We just finished up the last of our 2015 training classes, and the reviews have been glowing:

“I have attended many training classes in my 19 years as a DBA, including Oracle and SQL Server. This is the first class where 75+% of the information I learned is applicable to my current work environment.” – Christina Z.

“I thought the class was much more useful than the Microsoft Official Curriculum. Those classes seem to teach to the book and instructors often can’t answer questions that aren’t included in the curriculum. You guys have real-world experience that you can share with the students. Very valuable!” – Kris C.

“They’re really best of the best on the market. And always open to QA sessions. And they asked do you have any questions? And ALWAYS have answers!!!!” – Vlad B.

And now, registration is open for next year’s training class lineup. Drum roll please:

Advanced Querying and Indexing – 5 days, $3,995

• Jan 11-15, Newark
• August 8-12, Portland

SQL Server Performance Troubleshooting – 4 days now expanded to 5 days, $3,995:

• February 22-26, San Diego
• April 11-15, Newark
• June 6-10, Chicago
• August 15-19, Portland
• December 5-9, Philadelphia

The Senior DBA Class of 2016 – 5 days, $3,995

• March 7-11, Chicago
• May 2-6, Denver
• August 1-5, Philadelphia

We’ve been updating each class to reflect new technologies and techniques, and we’re excited to share what we’ve been teaching our clients. And of course, if you’d like to bring one of us onsite to teach a custom class lineup, check out our training catalog and email us.

Change is exciting, and a little scary, too. If you’re looking to upgrade or migrate your SQL Server, head on over to our events page and get registered for our upcoming free webcast on Thursday, Oct 15 at 10:00AM Central, 11:00AM Eastern.

SQL Server Migration Checklist (Sponsored by Veeam)

It’s time to upgrade your SQL Server! But do you know exactly what you need to do? Join this free webinar with Microsoft Certified Master Kendra Little to get her checklist to make your migration go smoothly. You’ll learn what you need to script and document from your current instance, which settings you should change after your migration, required tests for new installations, and better alternatives to in-place upgrades. Whether your SQL Server 2005 instance is hitting end of life or you’re planning an upgrade to the latest and greatest, this migration checklist will save you time and frustration. Register now.

Missed the Webcast, but want the Migration Checklist?

Download the SQL Server Migration Checklist eBook as part of our free First Responder Kit.

You spend at least an hour or two each day writing T-SQL code. You have a pretty good hunch that it’s not as fast or crisp as it should be, and you wonder if you’re missing a few techniques. You’ve heard about date tables and windowing functions, but you’re just not comfortable with ’em yet.

I’m Doug Lane of Brent Ozar Unlimited, and my new online training class is for you. It’s loaded with T-SQL goodies like using computed columns, how to replace cursors, and more. Here’s a sneak preview:

It’s unlike any T-SQL training you’ve ever seen (seriously!), and it’s totally free. I’d love to hear what you think.

Our customers, who got free access to it as a thank-you, have already started leaving reviews:

“Great job on delivering information with a wink and a nod. It really held my interest.” – Debby

“Doug is an exceptional speaker, and uses humour in effective ways to convey difficult concepts. I learnt a thing or two from this course.” – Randolph

“If only l had seen this course when l started out my SQL journey! I love the teaching style it works well for me. Then there are those simple time saving tips slipped in just at the right time. The analogy about banking the coins, genius wish l had thought of it! Teaching pace it timed perfectly. Overall looking forward to seeing more Robert

“This is perhaps the nerdiest thing I’ve seen all year.” – Wulfgar

“Great starter for thinking set-based!” – Gustavo

“I loved the Set Based sections with the examples of the cursors and how you could change the code to make them set based operations. But I think my favorite part was the windowing functions. Now I have good examples of how they work and when they are a good fit.” – Colin

“This course is funny and filled with a lot of good information. A great recap for any DBA. And a gold mine for anybody less experimented with coding T-SQL. The Windowing functions part is especially helpful. I suggest this course to anybody writing T-SQL.” – Benoit

Sure, you’d much rather work with SQL Server 2014, but what’s more prevalent out in the real world? At Dell DBA Days, I saw a chart that shocked me:

That chart shows the number of servers running Dell Spotlight Essentials, a free SQL Server monitoring tool. Note that SQL Server 2014 has 4% of the population, about 1/4 as much as SQL Server 2005.

But the terrifying part: for every 2 SQL Server 2014 instances, there’s one SQL Server 2000 instance still kicking around! That’s not exactly a rapid adoption rate.

I wonder why we aren’t we upgrading our SQL Server 2000 instances? (And I’m not pointing the figure at you – it’s likely the business that’s making you keep it around.)

Diskspeed, or diskspd.exe, is Microsoft’s replacement for SQLIO. While I’m not going to replace our SQLIO tutorial, you can use the information in here to replicate the same type of tests on your systems to get the information you need. During the Dell DBA Days, Doug and I used diskspd as one of our techniques for getting a baseline of raw performance. We wanted to get an idea of how fast the servers and storage before running SQL Server specific tests.

How do I get diskspd?

You can download diskspd directly from Microsoft – Diskspd, a Robust Storage Testing Tool, Now Publically Available. That page has a download link as well as a sample command.

The upside is that diskspd is a fully self-contained download. You don’t need Java, .NET, or anything else installed to run it. Apart from Windows – you’ll still need Windows.

How do I use diskspd?

With the command line, of course!

In all seriousness, although diskspd is the engine behind Crystal Disk Mark, it stands on its own as a separate tool. Download the executable and unzip it to an appropriate folder. There are going to be three sub-folders:

• amd64fre – this is what you need if you have a 64-bit SQL Server
• armfre
• x86fre

I took the diskspd.exe file from the appropriate folder and dumped it in C:\diskspd so I could easily re-run the command. Let’s fire up a command prompt and try it out.

Here’s a sample that we ran: diskspd.exe -b2M -d60 -o32 -h -L -t56 -W -w0 O:\temp\test.dat > output.txt

Breaking it down:

• -b2M – Use a 2 MB I/O size. For this test, we wanted to simulate SQL Server read ahead performance.
• -d60 – Run for 60 seconds. I’m lazy and don’t like to wait.
• -o32 – 32 outstanding I/O requests. This is your queue depth 32.
• -h – This disables both hardware and software buffering. SQL Server does this, so we want to be the same.
• -L – Grab disk latency numbers. You know, just because.
• -t56 – Use 56 threads per file. We only have one file, but we have 56 cores.
• -W – Warm up the workload for 5 seconds.
• -w0 – No writes, just reads. We’re pretending this is a data warehouse.
• D:\temp\test.dat – our sample file. You could create a sample file (or files) by runningdiskspd with the -c<size> flag.
• > output.txt – I used output redirection to send the output to a file instead of my screen.

How do I read diskspd results?

You’re going to get a lot of information back from this command. You’re going to want to close the window and back away quickly. Don’t. This is good stuff, I promise.

The first thing you’ll see is a recap of the command line you used. Then you’ll immediately see a summary of the commands:

That’s a lot easier than trying to read a set of command line flags. Six months from now, I can review older runs of diskspd and understand the options that I used. diskspd is already winning over sqlio.

Next up, you’ll see a summary of CPU information. This information will help you understand if your storage test is CPU bottlenecked – if you know the storage has more throughput or IOPS capability, but your tests won’t go faster, you should check for bottlencks. The last line of this section (and every section) will provide an average across all CPUs/threads/whatevers.

After the CPU round up, you’ll see a total I/O round up – this includes both reads and writes.

Look at all of those bytes!

If the I/O numbers initially seem small, remember that the data is split up per worker thread. Scroll down to the bottom of each section (total, reads, writes) and look at the total line. This rounds up the overall volume of data you’ve collected. The I/Os are recorded in whateverunit of measure you supplied. In our case, the I/Os are 2MB I/Os.

Important Sidebar Your storage vendor probably records their I/O numbers in a smaller I/O measurement, so make sure you do some rough translation if you want to compare your numbers to the advertised numbers. For more discussion, visit IOPS are a scam.

Finally, latency! Everybody wants to know about latency – this is part of what the end users are complaining about when they say “SQL Server is slow, fix it!”

This table keeps the min, max, and a variety of percentiles about how the storage performed while you were beating on. This information is just as helpful as the raw throughput data – under load your storage may have increased latencies. It’s important to know the storage will behave and respond under load.

How often should I use diskspd?

Ideally, you should use diskspd whenever you’re setting up new storage or a new server. In addition, you should take the time to use diskspd when you make big changes to storage – use diskspd to verify that your changes are actually an improvement. No, diskspd doesn’t include the work that SQL Server does, but it does show you how your storage can perform. Use it to make sure you’re getting the performance you’ve been promised by your storage vendor.