Blog

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.

Our new Triage Specialist will be using tools, forms, and methodologies that we build for our SQL Critical Care® clients, but we also make a lot of those tools available to the public for free in our First Responder Kit.

Here’s how to do your own quick health assessment for free:

First, start your health check with sp_Blitz®. It’s a stored procedure that gives you a prioritized list of problems on your SQL Server. If the databases are all third party apps that you can’t control, use the @CheckUserDatabaseObjects = 0 parameter to focus the results on the things you can change. sp_Blitz® works on SQL Server 2005 and newer, but it doesn’t work on databases in SQL Server 2000 compatibility mode.

If you don’t think one of the resulting rows is important, or if you don’t understand the warning, copy the contents of the URL column into your browser and spend a few minutes reading the details. For example, at least once a month, we run across a server having performance problems, and it turns out not all of the CPU and memory is actually available to SQL Server. Sure enough, when we look at sp_Blitz®, it warns about CPU schedulers being offline, and the admin says something to the effect of, “Oh, I saw that in sp_Blitz®’s output, but I wasn’t really sure what it meant because I didn’t read the URL.”

Next, check your server’s bottlenecks with sp_BlitzFirst®. This stored proc was designed for live real-time performance troubleshooting, but it has another cool use. Use the @Seconds = 0, @ExpertMode = 1 parameters, and the second result set will show you your server’s primary wait stats since SQL Server was restarted (or since someone cleared the wait stats.) This helps guide you toward the type of performance bottleneck you’re troubleshooting – is it a CPU bottleneck, storage issue, or locking?

Armed with that knowledge, check your top resource-intensive queries with sp_BlitzCache®. Once you know the kind of bottleneck you’re looking for, use the @sort_order parameters to get to the right queries:

• Looking for high CPU consumers? @sort_order = ‘cpu’
• Tracking down the source of PAGEIOLATCH waits due to queries dragging a lot of data back from disk? @sort_order = ‘reads’
• Wondering who’s getting blocked for long periods of time? Try @sort_order = ‘duration’

Then make the queries more efficient with sp_BlitzIndex®. Now that you know the worst queries and the database they’re in, run sp_BlitzIndex® in that database and get a psychological profile of your indexes.

• Dastardly deadlocks? Look for the Aggressive Indexes warnings about high lock waits on an index.
• Slow selects? Check out the high-value missing index warnings.
• Intolerable inserts? Read the Hoarder warnings about tables that have a high number of nonclustered indexes that aren’t getting used.

We slather trademarks all over these tools because we’re really proud of ’em. We use them every single day in our consulting work, and we want you to use them in your triage work too. You might even wanna just practice using ’em in case we ever ask you to use ’em during our job interviews. (That’s a hint.)

Since we’re hiring again, it’s a good time to explain what we do and how it’s evolved over the last four years.

When your SQL Server’s performance or reliability is causing you business pain, and you can’t get the relief you want, you call us for a SQL Critical Care®. We use specialized tools we’ve built to quickly get to the root cause. Then we explain it to you in clear, friendly terms, and train you how to get permanent pain relief.

Notice how we keep using medical terms? That’s no coincidence – we’re very much like a doctor’s office. Over the course of performing hundreds of these, and because the waiting room keeps filling up, we’ve scaled this to where we treat it like a hospital.

Remember that time I broke my arm saving a nun from a car wreck carrying a flat panel monitor? I went into the hospital, the triage nurse made sure I wasn’t having a heart attack, then got me into radiology for scans, then a doctor reviewed the situation, and he brought in an orthopedic surgeon for a consult on whether they should operate. The hospital had all of these experts available, and they rotated in and out of the room based on my needs.

Because of our scale, we’re able to bring that same approach to SQL Server pain relief.

First, a Triage Specialist reviews your RPO and RTO objectives, learns how you use SQL Server, collects diagnostic information, and prescribes changes to avoid data loss. Then, based on what the Triage Specialist finds, they bring in different specialists. The situation calls for plan guides? You’ll get a T-SQL surgeon who’s used that unusual trick before. Need pain relief for an AG in EC2 that randomly fails over? You’ll be able to work with an AlwaysOnologist who’s built up specialized tools to diagnose it.

We couldn’t use this approach when we only had a few consultants, but now that we’re doing so many of these, fun options open up.