Blog - Brent Ozar

SQLBits

Erik Darling is raising money for the Mercury Phoenix Trust, fighting AIDS worldwide to help save lives.

If we raise $5,000, he’ll present his SQLbits indexing session dressed like Freddie Mercury:

If I hit my goal on this, I'm gonna deliver my @SQLBits indexes session dressed like a proper Freddie. Working hard on my AY-YO.
https://t.co/zwEutkWD8M pic.twitter.com/QqY1E5vzS2
— Erik Darling Data (@erikdarlingdata) January 18, 2019

I would absolutely love to see this (well, I won’t be there, so I’ll have to settle for photos) so I’ll match the first $1,000 of donations.

Let’s make this happen, party people: make your donations and let’s make this world the fun, helpful place we want it to be.

Upcoming Free Training Webcasts

January 17, 2019

Videos

Hey, wanna learn about SQL Server for free?

Feb 7 – PASS Data Expert Series: PASS is taking the top-rated Summit 2018 recordings and replaying them live, for free, on a webcast. I’ll be in the chat room too, taking your questions. Register here, and the lineup includes:

SSMS & T-SQL Tricks with Bob Pusateri – 11AM Eastern
Fix My Functions with Kendra Little – 12:20PM
Getting Better Query Plans with me – 1:40PM
Performance Tips and Tricks with Pinal Dave – 3:00PM
SQL Server with Containers with Bob Ward – 4:20PM
Building Trust in Teams with Richard Campbell – 5:40PM

Jan 25 – How to Think Like the Engine: You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this 90-minute class, you’ll learn how the SQL Server engine looks at your indexes and builds your query results.

Feb 1 – Getting Started with the Consulting Toolkit: SQL Server consultants: you need an easy way to run an app, gather lots of diagnostic data, save it to a portable spreadsheet that you can analyze later. Even better, you want an app that your clients can run when their server is having problems but you’re not around – and then send that spreadsheet to you so you can diagnose problems after they’ve happened. That’s my new Consultant Toolkit. In this webcast, I’ll show you how to get it for free, start using it, and teach your clients how to use it.

Feb 8 – An Introduction to GitHub for DBAs: Distributed source control is really intimidating: branches, pull requests, merges – will somebody just take my code, for crying out loud? Why does it have to be so complicated and involved? I’m with you: I’m Brent Ozar, and I hated GitHub. For years, I struggled with it, but I’ve come to a gradual truce. I’m not a GitHub pro by any means, but in the span of one session, I can explain the most important terms to you in a way that’ll make sense for non-developers. I’ll show you how to contribute to someone else’s open source project, and how to get started putting your own scripts under source control.

Feb 15 – How to Capture Baselines with sp_BlitzFirst: You’re responsible for performance tuning SQL Server, and you’ve heard the same advice a bunch of times: “You should set a performance baseline.” But what does that even mean? And how do you do it? What metrics should you gather? How often should you gather them, and how much history should you keep? I’m a performance tuning DBA with a couple decades of experience, and I’ll show you how to capture baselines by setting up the free, open source sp_BlitzFirst to run in an Agent job every 15 minutes. You’ll also learn how to use the free Power BI Dashboard to read the baseline metrics.

Email*
Name*
First Last
Things I want*
- First Responder Kit (scripts & guides)
- Subscribe to email updates (3-4 per week)
- I would like a pony

SQL Server Perfmon counters that are still interesting in 2019

January 16, 2019

Monitoring

“Page Life Expectancy? That belongs in a museum!“

(Cross-posted from the PASS Blog.)

Years ago, my first go-to tool for performance tuning was Performance Monitor: it was available everywhere, worked in all versions, and gave me a lot of useful information without paying for a monitoring tool. Since then, I’ve moved on – wait stats are a much better starting point – but there are still 3 good Perfmon counters that I rely on.

No, not Page Life Expectancy, Buffer Cache Hit Ratio, and Disk Queue Length. Those are still hot garbage.

1. Forwarded Fetches per Second

When your tables don’t have clustered indexes, you’ve got a heap: a table organized in random order. Heaps are notoriously bad ideas if you do updates on variable-length fields because as SQL Server runs out of space on a page, it’ll move a row to another page where there’s available space for your new update. The more of these you have, the higher penalty you pay when scanning a table.

The Perfmon counter SQLServer:Access Methods: Forwarded Records/sec tracks how many of times SQL Server had to jump around while reading. (Think of these as extra logical reads to deliver query results.) The higher this number is, the more extra logical reads you’re doing. I don’t gauge the number by itself – I look at it as a percentage of the reads we’re doing overall. If I can cut the logical reads we’re doing by, say, 10% just by rebuilding the heap, great! That’s an easy win.

ENHANCE

With a name like “Forwarded Records/sec” you would reasonably assume that querying this number would get you the, uh, forwarded records that occurred in any given second. Nope. It’s a cumulative number from when SQL Server last restarted. That means in order to get actionable data, you need to measure this over time, and do differentials between measurements.

2. Memory Grants Pending

When your query starts executing, it usually needs a memory grant to do things like sorts, joins, and parallelism. I find this topic really intriguing, and you can probably tell – we’ve got a whole category of memory grant posts.

SQL Server:Memory Manager: Memory Grants Pending tells you the number of queries that are waiting on a memory grant before they can even START executing. When this number > 0, your apps are probably getting query timeouts, and the sysadmins are confused because CPU time looks low, but SQL Server is slow.

3. Compilations per Second

When you execute your beautifully hand-crafted query, SQL Server has probably never seen anything like it before – well, at least not since it restarted – and has to build a query execution plan.

With a regular transactional app, the same queries probably come in over and over, just with different parameters. (Data warehouses and reporting systems with a lot of truly unique queries are different – we’ll set those aside for now.)

“WHY AM I NEXT TO A HELMET?”

The SQLServer: SQL Statistics: Compilations/sec counter measures the number of times ~~per second~~ (hahaha, you wish) that SQL Server has had to compile a plan for a query it hasn’t seen before. The best way to evaluate this is to frame it in terms of the SQLServer:SQL Statistics: Batch Requests/sec counter, which, as you can probably guess, is also not measured per second. Totally up to you to do a differential, or to use the open source sp_BlitzFirst to do the differentials for you.

In a transactional system, I expect to see 90% (or higher) query plan reuse – so Compilations/sec should be 10% (or less) of the Batch Request/sec measure.

These 3 Perfmon counters shouldn’t be the first place you look – wait stats are still a better starting point – but sooner or later, you’re going to hit one of these issues that require Perfmon counters to solve. Ol’ trusty Perfmon is still there for you.

How to Load Test a Database Application

January 15, 2019

Development

I stay in the nicest hotels

It sounds really simple:

Capture a bunch of production queries
Replay those same queries over in a load test environment

So what’s the big deal? Why is it so hard to find good guidance on how to pull this off? How hard can it be?

Problem 1: many queries aren’t repeatable.

When you try to replay this query:

DELETE ProcessingQueue
  WHERE JobID = 12345;

1 2	DELETE ProcessingQueue WHERE JobID = 12345;

The rows have to actually be there in the table, or else your delete isn’t doing any work. That means you can’t replay that same query several times in a row. You’re going to have to restore the database after each load test, starting it back at the same point.

Deletes aren’t the only commands with this problem, either: for any query with a WHERE clause, we need the data to exist in the state we want:

SELECT *
  FROM ProcessingQueue
  WHERE IsProcessed = 0 AND JobID = 12345;

SELECT *

FROM ProcessingQueue

WHERE IsProcessed = 0 AND JobID = 12345;

In both cases, the rows for JobID 12345 have to already exist, so for a successful load test, our setup steps at the beginning of the post need to look more like:

Take a backup of the production database(s)
Capture a bunch of production queries
Restore that same backup to the load test environment
Replay those same queries over in a load test environment

(I can already hear the security crowd groaning, saying, “No no no, you’re never allowed to restore production data somewhere else.” Put a sock in it: we’re already talking about capturing queries in production, and as you know, those have personally identifiable data already. Read through til the end of the post before you get the flamethrower out.)

Problem 2: amplification introduces false blocking.

Managers think that to simulate more load, they can just take the production queries and replay them multiple times, simultaneously, from the replay tool. We’ve already talked about how you can’t reliably replay deletes, but even inserts and updates cause a problem.

Say we’re load testing Stack Overflow queries, and our app does this:

UPDATE dbo.Users
  SET Reputation = Reputation + 1
  WHERE Id = 22656;

UPDATE dbo.Users

SET Reputation = Reputation + 1

WHERE Id = 22656;

If try to simulate more load by running that exact same query from 100 different sessions simultaneously, we’re just going to end up with lock contention on that particular user’s row. We’ll be troubleshooting a blocking problem, not the problem we really have when 100 different users run that same query.

We really need to simulate activity across lots of DIFFERENT users, so you end up writing code to randomize parts of the load test. Now our load test routine looks like:

Take a backup of the production database(s)
Capture a bunch of production queries
Analyze the production queries to see which ones need to be randomized, and write that code (or require it as part of the development process)
Restore that same backup to the load test environment
Replay those same queries over in a load test environment using a tool that can randomize the parameter calls

It’s hard work, but it’ll pay off because we can reuse our load test over time, right? Well, wait..

Problem 3: production is running yesterday’s code.

Load testing is (theoretically) useful to know that our new code changes are going to perform well when we deploy ’em. However, if we’re capturing production queries…they’re not running the new code yet, nor does the production database have our new columns and tables.

There goes your week

So now our testing routine looks like:

Take a backup of the production database(s)
Capture a bunch of production queries
Analyze the production queries to see which ones need to be randomized, and write that code (or require it as part of the development process)
Restore that same backup to the load test environment
Apply the deployment scripts for our new code
Replay ~~those same queries~~ a mix of old code and our new production code over in a load test environment using a tool that can randomize the parameter calls

That’s why it’s so hard to load test databases.

Everybody starts down the analysis path, gets about an hour into the planning, and realizes it’s still a big challenge in 2019. When you take all of these problems together, plus the risks of using production data in development, the capture-and-replay technique just flat out doesn’t make financial sense for application load tests.

But I was careful there to say application load tests.

Capture-and-replay load tests can still make sense when you want to test new hardware, different compatibility levels, or new indexing strategies. Just make sure to restore the database to the right point in time, and don’t try to amplify your workloads lest you introduce blocking problems.

How Should We Show Statistics Histograms in sp_BlitzIndex?

January 10, 2019

Indexing, sp_BlitzIndex

If you’re a graduate of my free How to Think Like the SQL Server Engine course – and you’d better be, dear reader – then you’re vaguely familiar with DBCC SHOW_STATISTICS. It’s a command that shows you the contents of a statistics histogram.

When I’m doing the first two parts of the D.E.A.T.H. Method – (D)eduplicating identical indexes and (E)liminating unused indexes – I often wanna check the selectivity of the first field in the index. However, running DBCC SHOW_STATISTICS was painful because it’s not set-based – I couldn’t easily dump out the contents of lots of indexes at once.

That’s where sys.dm_db_stats_histogram comes in really handy. I’ve added a new section to the bottom of sp_BlitzIndex to show its contents when you’re examining a single table with the @TableName parameter:

sp_BlitzIndex’s new statistics histograms at the bottom

In that screenshot, I’m looking at the indexes on the Stack Overflow Users table, in particular, an index on DisplayName. The bottom section shows the histogram for that index, and if you keep scrolling down – in the real output, not the screenshot, silly – you can see the histograms for the rest of the indexes.

Now, this is a brand spankin’ new feature – it’s only in the dev branch of the First Responder Kit, and sys.dm_db_stats_histogram is only available on SQL Server 2016 SP1 CU2, 2017, and newer.

Right now, the stats on user-created indexes show up first, followed by the user-created stats, followed by the auto-created stats, all in alphabetical order. This feature also only works when you’re looking at a specific table with the @TableName parameter – it doesn’t really make sense to dump out all histograms across an entire database.

My question for you, dear reader, is: are there any changes you would want to this feature before I roll it out as part of the main branch? If you want to show other data, don’t just give me the wish list – give me the problem you’re trying to solve with the additional data, because there might be a better way to render it than what I’m showing.

You can leave a comment with any changes you’d like to see, or, if you’d like to edit the code and contribute changes yourself, check out the contribution guide.

Consultants: want a utility to gather SQL Server data?

January 8, 2019

Consultant Toolkit

When you’re a consultant, you need to get diagnostic data from your clients.

When you’re talking to a new sales prospect – do you waste a lot of time asking basic investigation questions like what version they’re on, how much data they have, what kinds of workloads they have? You know they’re not really giving you accurate answers because often they just flat out don’t know.

Instead, imagine just emailing them a diagnostic app that gathers data and packages it into an Excel spreadsheet they can send you. You can review their server before your sales call even starts, and you can give them real value right from your first interaction.

When you’re working on a client’s SQL Server – do you waste a lot of time running diagnostic queries, saving their outputs to a file, and then trying to shuffle that data around from place to place? Do you feel like you’re constantly having to update your diagnostic queries each time you find a new problem, and you don’t really wanna be in the writing-basic-scripts business anymore?

Instead, imagine the diagnostic app running deeper diagnostic queries, like gathering the customer’s top queries by reads, CPU, duration, executions, and more, saving those execution plans out to files that you can review later. You’ll spend less time hitting F5, and more time doing the kinds of analysis that your customers really pay you for.

When a client has an emergency and you’re not around – isn’t it frustrating that they ask you, “hey, SQL Server was slow yesterday around 6pm – do you know why?” You ask them questions, but…of course they didn’t gather any diagnostic data, or if they did, it’s a useless Profiler trace with no insight. You can’t give them answers, and everybody’s frustrated.

Instead, imagine the customer running this diagnostic app whenever they have problems, and emailing you the spreadsheet saying, “Hey, when you get time, can you analyze this and see why our SQL Server was slow at this moment?” The data would include running queries, wait stats, memory metrics, and more.

When you hit a wall and you need help – do you email your network and kinda fumble around, not sure how to pass the data around? Everybody seems to use slightly different ways of gathering and viewing data, and it’s really hard to interpret someone else’s data.

Imagine being able to pass a standard spreadsheet around to your network – after redacting client-sensitive stuff – and asking, “Hey, you’ve seen this metric setup before – do anything about these metrics look weird to you? What would you do next in my shoes?” And imagine being able to just book a spot on my calendar to get that second opinion.

This app will make you more money.

This was the very first app I had Richie build when he started with us, and it’s been a core part of our business for years. It shaved hours off every consulting engagement we had and helped us do a better job every time. I can’t imagine consulting without it, and I’m excited to share it with you to see what you think.

To get it, sign up for a free trial of the Consultant Toolkit, and the download link will be on your receipt and in your My Account page. Extract it to your local drive, like c:\temp. You don’t have to run this on the SQL Server itself – you can run it from any desktop or laptop with at least .NET 4.5.2. Then, to gather diagnostic data on one of your servers, go to a command prompt and run:

Windows authentication:

AppName.exe --datasource MyServerName

1	AppName.exe --datasource MyServerName

SQL Server authentication:

AppName.exe --datasource MyServerName --userid MyUserName --password MyPassword

1	AppName.exe --datasource MyServerName --userid MyUserName --password MyPassword

If you use tricky characters in your server name, user name, or password, like quotation marks or spaces, you’ll need to surround it with double quotes. For example, if my password is @#!, I might want to use “@#!”.

It’ll take about 5-15 minutes to run a bunch of DMV queries, and when it’s done, check out the Output folder. There’s a zip file with your server’s data.

By default, it runs a relatively limited set of queries. When you’re working hands-on with a client’s server yourself, and you want even more, add –deepdive (that’s two dashes, not one) as a parameter, and it’ll run more. If you go into the Resources folder, you’ll see the queries it runs: things like sp_Blitz, sp_BlitzCache, sp_BlitzIndex, and other utility queries we’ve written over the years.

“Wait, what’s with this AppName.exe stuff?”

The real app name is shown in the readme file, but I’m purposely not mentioning it here, dear reader, and I never will. (You’ll see it in screenshots here from time to time, but never typed out.)

See, if you’re a consultant, you’re going to be giving this app to your clients to run for diagnostic purposes. I don’t want your clients Googling the app’s name and finding me. This is your tool, the thing that’s gonna give you an edge over the amateurs who are still copy/pasting data out of SSMS.

Now, back to what the app does.

You get in-depth diagnostic data that makes you look like a SQL Server surgeon.

You can download a sample zip file from my lab. Here’s what the output folder looks like after a run:

The output includes a spreadsheet plus query plans

The top line is a log file with any error messages from the app.

The second line is a spreadsheet chock full of diagnostic data organized in tabs.

The remainder are the most resource-intensive query plans as reported by sp_BlitzCache. (The query metrics are in the spreadsheet.)

However, note that in the output, we’re NOT showing things like sp_Blitz’s URL column – because it links back to BrentOzar.com. When you hand this app to a client, trust me, they’re going to run it and look at the spreadsheet contents. They’re going to be amazed at the amount of great diagnostics you’re getting. However, I don’t want them seeing links back to me – again, this is your app. (Besides, as a trained SQL Server professional, you know the place to find more information about each of those warnings already anyway. You’re here, reading my stuff. You’ve already found me. Carry on.)

These queries support SQL Server 2008 and newer. Older database compatibility levels will throw errors as it runs, but you’ll still get a lot of useful data.

It’s by no means a monitoring app – if you’ve got $1,000 per server to spend, I’d highly recommend a full blown monitoring application. Those will get you cool features like email alerting, historical playback, and a database repository. This is a much more lightweight, one-off utility designed to solve the 3 specific use cases at the top of this post – especially in situations where clients just don’t have the budget or approval to put in monitoring tools.

This app is our new Consultant Toolkit.

We’re offering a free trial during our early access period, and the download link will be on your receipt and in your My Account page.

The toolkit includes documentation for how to best use it as part of your pre-sales process, building a health check, and giving instructions for your clients as to how to run it during an emergency if you’re not around.

Get it, play around with it, and let us know what changes & improvements you might need in order to use it during your daily consulting work by emailing us at Help@BrentOzar.com.

The 2019 Data Professional Salary Survey Results

January 6, 2019

Download the 2019, 2018, & 2017 results in Excel.

Salary

How much do database administrators, analysts, architects, developers, and data scientists make? We asked, and 882 of you from 46 countries answered this year. Y’all make a total of $84,114,940 USD per year! Hot diggety. (And at first glance, it looks like on average, y’all got raises this year.)

I make my query bucks the hard way

A few things to know about it:

The data is public domain. The license tab makes it clear that you can use this data for any purpose, and you don’t have to credit or mention anyone.
The spreadsheet includes the results for all 3 years. We’ve gradually asked more questions over time, so if a question wasn’t asked in a year, the answers are populated with Not Asked.
The postal code field was totally optional, and may be wildly unreliable. Folks asked to be able to put in small portions of their zip code, like the leading numbers.

Hope this helps make your salary discussions with the boss a little bit more data-driven. Enjoy!

[Video] Office Hours 2019/1/2 (With Transcriptions)

January 5, 2019

Brent Ozar Unlimited Team

Videos

This week, Brent, Erik, Tara, and Richie discuss whether you need to premake a destination database in order to run sp_databaserestore, restoring production statistics on dev servers to simulate productions behavior, dealing with duplicate SPNs, tools for detecting all SQL Servers in a production environment, SQL performance monitoring, dealing with very large indexes, and Brent’s recent cruise.

Here’s the video on YouTube:

You can subscribe to our podcast to listen on the go.

If you prefer to listen to the audio:

Podcast: Play in new window | Download

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast – 2019-1-2

Do I need to create a database before I restore one?

Brent Ozar: Mark asks, “Should I have a premade destination database in order to run sp_DatabaseRestore, or does it create one itself?”

Erik Darling: Well, I mean, the restore should create the database. If it’s not then you’ve got other things going on, perhaps a lack of permissions. If the database already exists and maybe you’re trying to overwrite it – I don’t know. That’s an odd one.

Brent Ozar: Or users have it open for a query maybe. In a perfect world, you don’t want to have the database already there when you go to restore.

Should I copy stats from prod to dev?

Brent Ozar: Pablo asks, “What do y’all think about restoring production statistics on development servers in order to try to simulate production’s behavior?” Have y’all ever moved statistics from one server to another?

Erik Darling: Yeah, but, like, as a joke.

Brent Ozar: What the hell kind of joke is that?

Erik Darling: Like I was messing with someone, I don’t know. No, I mean, I like the idea of it, but I mean, practically, I think you lose out on some aspects of being able to tune queries when you do that. You just don’t have the volume of data in there and you miss out on some of the metrics that might be making queries weird somewhere else or might be making them interesting somewhere else.

Richie Rump: Yeah, you also need developers to know what to do with that information. So they’re creating queries with the statistics that re for another system and they can’t figure out why everything is so slow and they may just go ahead and reset the stats or just rebuild indexes all over the place, and then you’ve got it out of whack a little bit. So they have to understand what statistics are and how you use them and how SQL Server uses them and go through all that. So if you’re willing to teach them about that then by all means, go and do that.

Erik Darling: Once you rebuild indexes, everything goes away.

Richie Rump: Yeah, I prefer creating queries and testing them on real data sizes if I’m doing any sort of perf type stuff. If I’m not, then, you know, we’ll wait until we get into the real thing and we’ll tune it from there.

Brent Ozar: I never understood it, the moving of statistics around, because all statistics are used for is for estimated plans. I mean, sure, they’re used for actual plans too, but all you’re going to be doing is comparing estimated plans. And if the estimates were right, you probably wouldn’t be in trouble to begin with. Usually, where your queries go to hell in a handbasket is where the estimates are wrong in plans and where stats aren’t helping you; non-SARGable queries, people doing LTRIM, RTRIM, stuff like that.

Erik Darling: Yeah, or if you’re dealing with spills or just some other weird problem…

Brent Ozar: Locking…

Erik Darling: Yeah, lots of stuff that, you know, only having the statistics won’t help you figure out, unless you’re truly troubleshooting a cardinality estimation issue, like purely that, then the value kind of dive-bombs.

Have you used Kerberos Config Manager?

Brent Ozar: Teschal asks, “Kerberos Config Manager tells me I have duplicate SPNs. Have y’all used the tool Kerberos Config Manager to get rid of the duplicates?”

Tara Kizer: I’ve never done that, but I’ve certainly dealt with the duplicate SPNs, and I just use the command line. I mean, it’s just simple to do; set SPN whatever it is.

Brent Ozar: I just have one bookmark set up for set SPN, and I always just end up going here, 2008…

Tara Kizer: Oh no, there goes your bookmark. Just set SPN/? in the command line. It will tell you what you need to do.

Brent Ozar: Damnit, I only had one. Oh no, there’s another one. Robert Davis’s kind of works. Yeah, so if you search for set SPN Robert Davis, that will get you there too.

How can I find SQL Servers in my environment?

Brent Ozar: Back over on the questions, Edwin asks, “What are the…” and I’m laughing not at Edwin’s question. I’m laughing at my one bookmark is trash now. Edwin says, “What are the tools or T-SQL scripts that I can use to detect all SQL Servers in my production environment?”

Erik Darling: Microsoft has that Discovery tool. That’s the only one – I know there used to be PowerShell scripts out there that would go and search the network and catalog stuff, but I couldn’t tell you the name of them or who wrote them.

Richie Rump: The one that Kendal wrote. I forget the name of it.

Erik Darling: Oh yeah, power something…

Brent Ozar: Power Doc on Codeplex, which, of course, is dead.

Erik Darling: When’s the last time that thing got updated?

Brent Ozar: You know, he did like a year ago. A year ago, he updated something in here, but I don’t know if it was that he moved it to GitHub or not. There’s somebody else who moved it to – but yeah, SQL Power Doc is another one. Quest had Discovery Wizard. I don’t know if they still do or not.

Richie Rump: So if the software is completed, why do we need to update it?

Erik Darling: Software is never completed, Richie.

Brent Ozar: the developer confuses me there for a second. I’m like…

What functions are involved in performance monitoring?

Brent Ozar: Ronnie says, “Can you give me a brief overview of the functions involved in SQL Server performance monitoring? What data or information am I analyzing to determine if SQL Server is performing better today than it was yesterday or last week?” That’s a good question. How do you know if your SQL Server’s better or worse?

Erik Darling: The way it smells…

Brent Ozar: Describe what I high-performance SQL Server smells like when things are bad.

Erik Darling: Burning dust. What I usually do is lick a thumb and put it up against the CPU fans. And if it feels like it was blowing harder than last week, I know something is a problem. I’ve probably been running queries in the application instead of SSMS.

Brent Ozar: Makes sense. When we talk about a SQL Server, we say it really sucks or it blows, that’s the thing that we’re talking about there.

Erik Darling: Absolutely. That’s when I know it’s time to ask Brent for more RAM.

Brent Ozar: Alright, Tara, do you have a better answer?

Tara Kizer: I don’t know. I mean, for me, a lot of it’s based upon users. If no one’s contacting me then I know that performance is either fine or tolerable. And when they are contacting me, it’s because it’s bad. My three past jobs always had expensive monitoring tools in place that made it easy to take a look at that stuff. There’s a dashboard, you can see it, and bells and whistles all over the place.

Brent Ozar: Yes, and if you were going to go pick a monitoring tool, what are some of our favorite monitoring tools out there?

Erik Darling: I like the old Sentry One. That’s a nice one. It’s got a nice dashboard. I like that you can highlight sections of the graph and zoom into what was running then or what other stuff was going on. So, nice little things that help you correlate things that are actually happening on the server. I find that a lot monitoring tools have disparate information, so it’s really hard to put the puzzle pieces together. It’s like, there are waits and there are queries and there’s a graph and there’s some plaid pants. And you’re like, I don’t really know which one to go with.

Brent Ozar: Like, I think the same way when you talk about functions; what are the monitoring tools supposed to do for me? I want to be able to isolate units of time. Show me what it was like at 8am Tuesday. Now, what was it like at 8am Tuesday three weeks ago? And it shouldn’t just be based on clock time or day of weeks. Sometimes it’s based on business processes. What was the close of month last time? Or you have bursty loads that happen at different times, depending on what was going on, or you want to see what was going on. I think the whole functionality of time replay is huge.

Erik Darling: You know, if you’re just peeking at wait stats as things are happening, you’re going to lose all the granularity that monitoring tools collect for you. They just aggregate and aggregate and aggregate. They’re not per database. They’re not, like, for any window of time. And especially for bursty workloads, servers can look really, really bored when they just have two or three busy hours a day or four to six busy hours a day, because it just kind of blurs out over time. It just kind of smoothes that line out.

Should a 32GB table have a 26GB index?

Brent Ozar: Mike asks, “I’ve found a 26GB index on a 32GB table. This index has 28 includes and two of them are varchar max. This smells bad to me. Where do I start?”

Erik Darling: Drop index – do you need me to spell one of those for you? So that smells to me like it either has DTA in the name, or it’s going to be name of missing index sysname. I would put a guess on one of those. That sounds like one of the missing index request things that comes in where you’re just like, I should never add that. But not everyone realizes that there are downsides to creating. That being said, if there’s one other index on the table and it’s that big, screw it. If it’s the clustered index and then that, I would just leave it alone.

Brent Ozar: the other thing you could look at is could you just create the index without all the includes? Just create it with just the key fields and if it’s being used a lot, then that can end up helping you there. Mike says, “Or crazy developers.” I think, kind of by definition, you have to be crazy to be a developer, or development drives you crazy because there’s so much debugging work involved. Holy moly…

Tara Kizer: Like Erik said, it smells of being a missing index, maybe for an entity framework type query where, by default, they just return all columns, and that’s what you’re going to get from a missing index.

Brent Ozar: Yeah, and, folks…

Erik Darling: The moral equivalent of select star.

Brent Ozar: And, folks, that’s all the technical questions y’all have this week. So y’all are off to a slow start. I’ll give you another minute or two to see if you want to enter in any other technical questions, otherwise, we will disappear off into eating our Christmas leftovers. What did y’all do for Christmas? Did you do home food? Did you go out somewhere?

Tara Kizer: I hosted Christmas Eve for 20 people or so…

Erik Darling: What? You know 20 people?

Tara Kizer: I have a big family. We’re Catholic…

Brent Ozar: Damn, that’s what happens when you get a new house. You have enough space, everybody’s like…

Tara Kizer: Yeah, and Christmas day was at my sister’s.

Richie Rump: That doesn’t sound like Christmas. That sounds like Nochebuena. Where do you come from? I don’t know. Okay, well come on in.

Brent Ozar: Well thanks, everybody, for hanging out with us this week. Adios!

From The Server To The SAN

January 3, 2019

Hardware, SQLBits, Storage

Enter SANdman

When people buy SANs, it’s often quite a large investment. Whether it’s all SSD, all flash, or there are tiers of storage that different types of data live on, those disks aren’t cheap. When people visualize their SAN, it’s usually just the server and the pool of drives.

But there’s some important stuff in between the Server and the SAN — SAN doesn’t stand for Storage Abstraction Nerglefwomp.

It’s Storage Area Network, and the “Network” part is what causes a lot of problems.

What’s going on in there?

Those wires, especially when they’re attached to a VM Host, have to manage traffic for a lot of different things at once.

It’s really easy to overwhelm even Fibre Channel wiring, when it’s either single-pathed, or when enough concurrent activity hits a multi-pathed setup.

https://en.wikipedia.org/wiki/Fibre_Channel

Round The Way SAN

Talk to most people setting up a new SAN, and they’re (hopefully) going to be using 8-10GFC (or Ethernet).

But if you’re moving hundreds of gigs, or terabytes across those wires, you better be darn sure you’ve got plenty of bandwidth. Let’s take a best case scenario, where you’re moving 1 GB across 10 GFC unhindered. It’ll take 800 milliseconds.

But there are 1000 GB in 1 TB, which’ll take around 13 MINUTES.

Let’s use my new hard drive as an example. I’ve got a VM with SQL Server running on there.

I am the manager.

When I use Crystal Disk Mark to benchmark my storage, the results are pretty okay for an external SSD.

stop judging me

I’m doing around the 4GFC mark for sequential reads and writes.

But if I run a workload that generates a lot of I/O for various reasons, and re-run the benchmark, things tank significantly.

bullies

Sharing Ain’t Caring

When multiple requests all had to go across my USB 3 connection, my speeds got cut in half, or much worse.

Now take some time to think about how many things you’re asking your storage networking to handle concurrently.

Think about when you have stuff like backups, checkdb, and index maintenance scheduled.
Think about when you’re doing ETL, or any bulk data activity.
Think about if you’ve got AGs or Mirroring set up.

When you ask the SAN admin to take a look at the storage dashboard to see why things are slow, they’re not gonna see a blip. Data simply isn’t arriving at the disks fast enough for them to be burdened.

If you had a bar with 100 people in it, 1 waiter, and 10 bartenders, that one waiter wouldn’t be able to take orders to the bartenders fast enough to keep them busy. Your bartenders would look bored, but your waiter would be a wreck.

What’s The Name Of His Other Leg?

If you like learning about this kind of stuff, and you’re going to the lovely and talented SQLBits in 2019, come to my precon.

I might even be bar tending.

Thanks for reading!

The Pleasure Of Presenting With Joe Obbish

January 3, 2019

Execution Plans, GroupBy Conference, Indexing, Videos

Grouple On

At the December 2018 GroupBy I had the honor of co-presenting with one of the smartest guys around, Joe Obbish.

Here’s the video of our session, “The Problems With Optional Operators”, and a link to the resources. Enjoy!

Resources:

Slides and Demos

Database Design Choices To Consider When You’re Worried About Scale

January 3, 2019

Architecture

Tipping the Kilter

I’m intentionally avoiding physical/hardware choices in this post, like how much hardware to buy, and scaling out vs. scaling up.

It’s not that I don’t care about those things — I do, and they’re important — but most new apps these days are starting in the cloud, or an on-premise VM. In the cloud, you’re not making terribly specific hardware choices, and the hardware choices you make for VM hosts are different than what you’d make for a bare metal server.

CPU choice is on the top of that list, for a lot of reasons. A CPU’s clockspeed is a serial query’s speed limit, which made the typical wisdom for a bare metal server to buy a lower number of cores to get a higher clockspeed. This saves you licensing bucks, and lets your server function reasonably well.

That line doesn’t work on a VM host, where you need to have a bunch of different things co-habitating, and co-scheduling. You’re gonna sacrifice core speed for girth.

Up in the cloud, most published CPU speeds are in the 2.4-2.6 GHz range. That’s because the cloud’s really all VMs where providers had to sacrifice core speed for girth.

See? This is why I’m not talking about hardware. Any advice I give you is gonna be outdated in a quarter, and I have to keep saying “girth”.

Stick To The Ribs

With that out of the way, let’s talk about some more logical (ha ha ha) choices you can make that might save your tail later on.

Be More Optimistic

I can’t begin to tell you how many terrible things you can avoid by starting your apps out using an optimistic isolation level. Read queries and write queries can magically exist together, at the expense of some tempdb.

Yes, that means you can’t leave transactions open for a very long time, but hey, you shouldn’t do that anyway.

Yes, that means you’ll suffer a bit more if you perform large modifications, but you should be batching them anyway.

Just think of all the folly you’ll save yourself, falling into the NOLOCK trap, and the countless nights you’ll spend wondering if READ UNCOMMITTED is different.

(It’s not.)

Partition It Just A Little Bit

No, not because of some magical performance unicorn that you’ll never saddle, but because eventually someone will say: “Hey, we should archive some data”

And you can say: “No problem”

Because moving partitioned data out in chunks is a lot easier than coming up with your own strategy to move, verify, and delete data.

Partitioning isn’t a fit for every table. Things like lists of customers or users don’t often have a good partitioning key that you’d use to swap data around by. I really mean it when I say that partitioning should be considered a data management feature. If you’re going to implement it, make sure you can benefit from it.

Bite The Four Bytes Bullet

“Hey, you’re close to running out of INTs for this identity column.”

“Yeah, we don’t wanna use BIGINTs though, they’re four bytes bigger than INTs.”

“I also noticed all these first and last name columns are NVARCHAR(MAX)…”

Stop with the four byte drama. If your concern is for a database that’s going to grow into the terabytes anyway, you’re going to run out of INTs faster than you think.

I don’t mean you should throw out domain knowledge about the range of values you’re allowing in a column, but if you’re defining identity columns or sequences that don’t have any particular relational value or business meaning, just go big. It’s not worth the pain of changing later, or having to monitor and reseed.

Compress To Impress

Disk is cheap, but it’s not infinite. Unless you’ve got an Infinite Storage Repository®, of course. While not as good as columnstore, I think row and page compression are still legit choices to smush more data onto your disks.

This doesn’t help you with memory, because data is decompressed when it leaves disk, but if you’ve got tiered storage, it can help you keep more hot data on the good stuff.

Speaking of which…

Files and Filegroups

There’s a hard limit of 16TB for a single file in SQL Server. I’ve had to add files on a few occasions, because primary was filling up. Again, if you’re concerned about the future size of your data, this is important to consider: don’t put stuff in primary, and have a well-defined strategy for what-goes-where.

This isn’t a performance thing, really, it just gives you some freedom with how you backup and restore data, how you run DBCC CHECKDB, and being able to switch any archive data to read only. You can also do some neat tricks with moving data with no downtime.

At larger database sizes, you’re likely going to flip from taking native backups to taking SAN snapshots. I’ve heard from people on both sides of the fence, here. Some people swear they can make native backup and restore scale to be as fast as snapshots, and that snapshots have caused issues with corruption, or have silently failed in odd ways.

I haven’t personally run into either of those, but it goes to show: no matter what your backup strategy is, make sure it fits your RPO and RTO goals, and make sure it works.

Left Out

On top of hardware choice, I also left out SQL Server version and edition choices. I did that for a couple reasons.

If you’re serious about scale, Enterprise is kind of a given
Everything I mentioned here is also available in Standard Edition, starting with 2016 SP1

If you’re starting work on an application today, it doesn’t make sense to use a version of SQL Server prior to that, full stop.

I also didn’t go into different architecture choices, like database per customer, schema per customer, etc. Finding the right fit there can be tough, and if you’re on PaaS like Azure or RDS, you have to abide by their individual limitations (30 database limit in RDS, no cross-database stuff in Azure SQL DB, etc.).

Thanks for reading!

Why Do Some Indexes Create Faster Than Others?

January 3, 2019

Execution Plans, Indexing

To warn you ahead of time, this post is an exploration without an answer (yet). There’s some interesting stuff in here, but no conclusions. If that’s not your kind of post, feel free to skip it. If it is, well, here goes nothin’…

Wild, Wild Life

Creating indexes is kind of a funny thing. By the time you find the query that needs one, figure out which index will help, ~~go through change management, and check it into source control~~ and roll it out in production whenevs, you’re almost not even worried anymore. You’ve already been through h*ck, why would SQL Server make you suffer more?

Now, to be fair, all sorts of things can contribute to indexes creating slowly. A busy server, blocking, crappy I/O, not enough memory, creating really wide indexes, etc.

All totally legit reasons for it, too. It’s a database, not a genie here to grant your wishes because you’ve got a magic rub.

Ruling Reasonable Things Out

On my desktop, I don’t have any of those problems. I’m the only user, I’m not blocking myself, I have a 1TB NVMe card, and I’m creating, in these examples, single column indexes on integer columns.

To further rule out any hanky panky, I’m gonna create the indexes offline. Creating indexes online is a bit more complicated, though doing this exhibits the same pattern.

Here are the indexes I’m going to create.

CREATE INDEX ix_whatever ON dbo.Comments (UserId)
    WITH (ONLINE = OFF, MAXDOP = 6);
GO 
CREATE INDEX ix_apathy 
    ON dbo.Comments (Score) 
	WITH (ONLINE = OFF, MAXDOP = 6)
GO 
CREATE INDEX ix_ennui ON dbo.Comments (PostId)
    WITH (ONLINE = OFF, MAXDOP = 6);
GO

CREATE INDEX ix_whatever ON dbo.Comments (UserId)

WITH (ONLINE = OFF, MAXDOP = 6);

CREATE INDEX ix_apathy

ON dbo.Comments (Score)

WITH (ONLINE = OFF, MAXDOP = 6)

CREATE INDEX ix_ennui ON dbo.Comments (PostId)

WITH (ONLINE = OFF, MAXDOP = 6);

Why MAXDOP 6? Because I have 6 physical cores in my desktop. There are those among us who loathe hyperthreading, and would surely cast their cursed stones upon my Great Post.

The Comments table has 68,343,743 rows in it. Not a lot, but not a little, either. It’s a moderately sized table, depending on who you talk to.

Heat Rash

Starting with no indexes on the table, I’m going to create these in order. This isn’t a case of indexes helping indexes. They all do the same number of reads, but…

/*UserId*/
Table 'Comments'. Scan count 7, logical reads 2850853, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 109906 ms,  elapsed time = 19565 ms.

/*Score*/
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Comments'. Scan count 7, logical reads 2850853, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 109357 ms,  elapsed time = 57679 ms.

/*PostId*/
Table 'Comments'. Scan count 7, logical reads 2850853, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 62283 ms,  elapsed time = 11133 ms.

/*UserId*/

Table 'Comments'. Scan count 7, logical reads 2850853, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 109906 ms, elapsed time = 19565 ms.

/*Score*/

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Comments'. Scan count 7, logical reads 2850853, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 109357 ms, elapsed time = 57679 ms.

/*PostId*/

Table 'Comments'. Scan count 7, logical reads 2850853, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 62283 ms, elapsed time = 11133 ms.

What in the heck did that index on Score do? Why does it take a full minute to get created? And… Why did it need a worktable?!

Size Isn’t Everything

Plans, Man

The first clue is in the query plans.

*blinks internally*

You may notice something slightly different about these plans. The top and bottom indexes, the ones that are created quickly, are fully parallel. The slow index on Score starts out parallel, but ends up serial for the insert into the index. That’s also the one that needed the worktable.

This is upsetting. That Gather Streams and serial insert cause the index creation to take a much longer time. Why could that be?

Could It Be NULLs?

At first glance, I thought I had something, but this isn’t the answer. UserId and Score are both NULLable, but only Score results in an early-serial plan.

*blinking intensifies*

Imagine if I could give you a really good reason like this to use default values.

But this ain’t it. Throw that dream away.

Density Vector?

My next guess was that it was based on the number of distinct values in a column. I don’t think that’s quite true either. Columns with totally different distributions would get the serial insert plan.

For example, the AcceptedAnswerId column on the Posts table:

<a style="background-color: #ffffff; font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Oxygen-Sans, Ubuntu, Cantarell, 'Helvetica Neue', sans-serif; font-size: 16px;" href="https://www.brentozar.com/wp-content/uploads/2018/12/2018-12-07_21-02-17.jpg"><img class="size-full wp-image-197906" src="https://www.brentozar.com/wp-content/uploads/2018/12/2018-12-07_21-02-17.jpg" alt="" width="224" height="158" /></a>

No sir.And the Score column on the Comment table:

I didn’t like it.

These both get the much slower serial insert treatment, but have very different value distributions. There are other columns with similar value distributions that get parallel inserts. I won’t bore you by showing you all of them. Just know that they’re out there, and that I couldn’t discern a pattern.

Sort Of Different

Going back to the query plans for the create index statements on Comments, there’s a strange difference in the Sort operations.

Purdy

The two indexes that create quickly have a Partition ID — this table isn’t partitioned at all.

And there’s also a difference in the wait stats each plan generates.

The fast plans have QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN in the wait types, which is described at the link as:

Occurs in certain cases when offline create index build is run in parallel, and the different worker threads that are sorting synchronize access to the sort files.

Which would explain why the sorts for the parallel inserts have a Partition ID, and why the Gather Streams of the serial insert is order preserving.

Murder By

Last Stop

To verify that the order preserving Gather Streams is where the slowdown hits, I turned on Live Query Plans. Though the plans don’t show up while the index creates are executing, they do preserve the Actual Time statistics of each operator, over in the properties window (hit F4 while looking at a query plan). And indeed, Gather Streams is a full 40 seconds of the 57 second total time.

LAG()

Unfortunately, there’s not a good, practical way to compare a plan that avoids the worktable, and does a parallel insert into the index. One could try messing with STATS_STREAM, if one were feeling particularly big-brained, but that’s a pretty far leap away from “practical”.

Closedown

I wish I had better answers as to why these different plans get chosen. The bottom line is that I can find lots of places that offer differences, but no place that offers insight. I’ve been in the debugger. I’ve been in PerfView. I’ve been in all sorts of Trace Flags. At some point, I gotta give in and write down what I’ve found. Because I mentioned it earlier, here are the plans for the online versions.

It would be nice to have some control over this stuff when creating and rebuilding indexes.

Rebuilding indexes?

Yep.

Something lousy happens there, too.

lol

The ride never ends.

Thanks for reading!

An Idea For Improving DBCC CHECKDB

January 3, 2019

CHECKDB and Corruption

Hard Enough

At this point, we’ve all probably got a database that’s a terabyte or more in size. If you’re anything like I was, you’ve got a ton of multi-terabyte databases, and the SAN admin has a special folder for your emails.

When you’ve got a lot of large databases, normal maintenance is out the window, and free disk space can be tough find.

Fond Memories

There are a lot of reasons that CHECKDB can fail that don’t indicate corruption. That doesn’t make the email alert any less scary. Some examples are:

1823: “A database snapshot cannot be created because it failed to start.”
3313: “During redoing of a logged operation in database ‘%.*ls’, an error occurred at log record ID %S_LSN. Typically, the specific failure is previously logged as an error in the operating system error log. Restore the database from a full backup, or repair the database.”
5128: “Write to sparse file ‘%ls’ failed due to lack of disk space.”
7928: “The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.”
8921: “Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.”

These errors all show up together when the drive that your databases reside on doesn’t have enough room for the snapshot that DBCC CHECKDB takes and uses to be created.

Right now, we don’t have any control over that.

That’s what I wanna change: Option to specify a location for the DBCC CHECKDB snapshot

Bonus: Ola would support it!

Doodily doo

Corruption is a tough cookie. Checking for it with large databases ain’t one bit of fun. Breaking the checks down into smaller parts, or offloading are okay options, but add a layer of complexity that not every team wants to manage.

At least until SQL Server cleans its own room.

Thanks for reading!

Fast Food Code

January 3, 2019

SQL Server

Cheap and Easy

We’ve all done it. We all know what and where it is, because we’ve probably left a comment for ourselves to go fix it later.

It wasn’t me, I swear to myself.

Either it was expedient, it seemed to work locally, it was fast on a small amount of data, or we just didn’t have time to do better.

Maybe we only learned it was a bad idea later on. But there it still sits.

Mocking. Needling. Cajoling.

Like the Ab Roller in the back of your closet.

Bag Of Pudding

Our application that was reliably quick and svelte in it’s teen-gigs is a slow and flabby disaster in it’s midlife gig crisis.

If things keep up, it won’t live to see 65 gigs.

It’s hit a wall, you see. And that steady diet of Fast Food Code is starting to take its toll.

Your monitoring tool has stopped showing you charts and graphs, and has started trying to sell you a burial plot.

The signs have been there for years, but you didn’t pay attention.

XXXL

Confidential data Is delicious

It’s not just about performance. Data safety and security is a big deal, too.

Despite, like, decades of incidents, people keep carrying on writing unsafe dynamic SQL.

Data theft and vandalism are things.

Things that happen.

Things that happen to people a lot smarter than you.

It’s That Time Of Year

No, not the Rapture.

And only tangentially the time of year when I walk into the gym and see people doing plyometrics or some other useless waste of time in a squat rack for some reason.

Let’s make a resolution to stop feeding our databases Fast Food Code. When we see the warning signs, let’s stop and do something about them.

Unsafe dynamic SQL
Functions or calculations in joins and where clauses
Cleaning data on the fly
Cursors for relational tasks
Nested views
Scalar UDFs
Multi-Statement UDFs
Mismatched data type comparisons
Table variables (most of the time)

You don’t have to fix them all at once. Sometimes it’s just a matter of stopping new development with these practices first. Then, as you go through your worst performing code, keep an eye out for the stuff on this list.

To get started, download our FirstResponderKit.zip here, and use sp_BlitzCache to find your naughty bits. It’ll flag a lot of the things on that list (the rest aren’t captured in the plan XML). That’ll make your tuning path a lot more clear.

Thanks for reading!

Erik and Tara Are Heading Out

January 3, 2019

Company News

The rock stars.

About two years ago, I realized I didn’t have the skills to grow the consulting part of the company to become a 10-employee firm, so I laid off Angie, Doug, and Jessica. This year, I made the tough decision to close the consulting side of the business entirely. I’ll be focusing on training and SQL ConstantCare®.

That means Erik Darling and Tara Kizer will be seeking other adventures. I’m going to miss them terribly – they’re just phenomenal people. Yeah, sure, they’re complete SQL Server professionals, but they’re also awesome coworkers, and I can’t recommend them highly enough. They’re amazing.

If you’d like to get in touch with two of the smartest data professionals around, email Erik at edarling80@gmail.com and Tara at tara.kizer@gmail.com.

(Erik is also a blogging MACHINE, and he’s written a lot of good stuff that still needs to see the light of day. That means for the rest of the day, we’ll publish a new post from him every hour until his queue is emptied out. Let the publishing commence!)

How Azure SQL DB Hyperscale Works

January 2, 2019

Managed Instances, Microsoft Azure

Microsoft is starting to talk about the internals for Azure SQL DB Hyperscale, their competitor to Amazon Aurora. Aurora took the open source MySQL and PostgreSQL front ends and hooked them up to much more powerful cloud-first storage engines on the back end. Here’s a quick primer on how Microsoft is doing something similar – taking the front end of SQL Server (the query optimizer and query processor), and hooking them up to a new back end.

When your database server runs on physical hardware, it looks like this:

CPU: an Intel or AMD processor where the database engine’s code runs
Memory: caching layer
Data file: a file on local storage where your data lives, the main source of truth for your database
Log file: a file on local storage where the database server writes what it’s about to do to the data file. Useful if your database server crashes unexpectedly, leaving the data file in a questionable state.
RAID card: a processor that stores data on a Redundant Array of Independent Drives.

(I’m making some assumptions here, like enterprise-grade hardware with redundancy, because that’s the kind of gear I usually see backing SQL Server. You, dear reader, might be a rebel running it on an old Raspberry Pi. Whatever.)

Traditional database server high availability

Usually when DBAs sketch out a failover architecture, the drawing involves duplicating this diagram across several independent servers. Data is kept in sync by the database server (SQL Server, Postgres, MySQL, Oracle, etc) by having the database server replicate logged commands over to other replicas:

That works.

A way less popular method (but still technically possible) involves separating out the database server from its file storage. You can put your databases on a file server, accessible via a UNC path.

That way, if the database server blows chunks, you could spin up another database server, attach the data & log files, and keep right on truckin’. This is a fairly unusual approach these days, though, due to challenges with networking, slow time to spin up another database server, etc.

But what if you were going to redo this for the cloud?

Conceptually, separate out the storage.

The RAID card’s responsibilities are:

Accept incoming writes, and temporarily store them in a limited amount of very fast cache (think near memory speed, but safe in the event of a crash or power loss)
Later, dispatch those writes off to persistent storage (hard drives or solid state)
When asked to read data, figure out which drives hold the data
Try to cache data in fast storage (like if it notices a pattern of you asking for a lot of data in a row)
Monitor the health of the underlying persistent storage, and when a drive fails, put a hot spare drive into action automatically

In our new cloud design, we’re going to build up a beefier storage subsystem. We’re going to replace the RAID card with a bigger design, and in this bigger design, we’re going to give the storage some more job duties than it had before (moving up the stack into the database server’s job duties.) We’re going to:

Break this data up into a few network services (which, honestly, are probably going to be a little slower than the absolutely best-configured local flash storage, but faster than poorly configured shared storage)
Replicate the database across multiple data centers (without getting the database server involved)
Make the storage responsible for applying the transaction log changes to the data file

Here’s how the architecture would look:

CPU: (an Intel or AMD processor) a virtual machine where the database engine’s code runs
Memory: local solid state caching layer
Data file: a file on local storage a service where your data lives, the main source of truth for your database
Log file: a file on local storage a service where the database server writes what it’s about to do to the data file. Useful if your database server crashes unexpectedly, leaving the data file in a questionable state.
RAID card: a processor service that stores data on a Redundant Array of Independent Drives.

When you do an insert, conceptually:

The database engine’s query processor tells the log service, “I want to add a row on data file #1, page #300.”
The insert is considered done as soon as that command is written to the log service.
The log service reads the list of work to be done, opens up data file #1, page #300, and adds the relevant row.

This comes with some interesting advantages:

The primary replica needs less storage throughput since it’s not writing changes to the data file.
The log service can make the necessary data file changes in multiple copies of the data file, even in multiple data centers or even continents.
You can add more databases and more replicas of each database with zero additional overhead on the query processor. If you’ve ever dealt with worker thread exhaustion on Availability Groups, or sync commit latency due to an overloaded replica, you’ll know how cool this is.
We can scale data file storage linearly with servers using cheap local solid state storage.

Here’s how Azure SQL DB Hyperscale does it.

In a conventional SQL Server, for large databases, you might create multiple data files, and put each data file on a separate volume.

In Hyperscale, Microsoft does this for you automatically, but in a spiffy way: when you see a data file, that’s actually a different page server. When the last page server is ~80% full, they’re adding another page server for you, and it appears as a new data file in your database. (Technically, it’s two page servers for redundancy.)

Challenges for both Aurora and Hyperscale (I hesitate to call these drawbacks, because they’re fixable):

If we’re running our database today on a physical server with a perfectly tuned IO subsystem, we could get sub-millisecond latency on writes. Let’s be honest, though: most of us don’t have that luxury. For Azure SQL DB Hyperscale, Microsoft’s goal is <2.5 milliseconds for log writes, and as they roll out Azure Ultra SSD, the goal is dropping to <0.5ms.
We’ll lose some database engine features that relied on directly working in the data file – for example, right now Hyperscale doesn’t offer Transparent Data Encryption or bulk logged recovery model.
Caching is going to be just a little bit trickier. The primary replica may still need to make its own changes to the in-memory cached data pages because people are going to want to insert a row, then immediately turn around and query that same row, without waiting for the log service to make changes to the data pages, plus have the primary fetch the newly updated copies of the data pages from cache.
We’ll have a lot more complexity. There are a lot of new moving parts in this diagram, and if we want them to be reliable, we’re likely going to run them in pairs at a minimum. All of these moving parts will need controls, diagnostics, and patching. (This just doesn’t make sense to run on-premises for the next several years unless it’s a sealed appliance like Azure Stack or Amazon Outpost.)
We’re going to spend more money in the short term. I bet some of you are reading this diagram and going, “Holy Licensing, Batman, this is going to be a lot more expensive (and somewhat slower) than my 2-node Availability Group running SQL Server Standard Edition on $1,000 Intel PCIe cards.” And you’re right. It’s not a good fit to replace that. There’s a reason Microsoft calls it “Hyperscale.” Stick with me here.

Both AWS Aurora and Microsoft Azure SQL DB Hyperscale take the same approach here, offloading deletes/updates/inserts to a log service, and then letting the log service change the data file directly.

The next level: scaling out the compute

A few paragraphs ago, I said that the log service could add more databases and more replicas of each database with zero additional overhead on the query processor. Well, why not take advantage of that?

In Azure SQL DB Hyperscale, this is done with an Always On Availability Group listener. When application wants to query a readable replica, the app specifies ApplicationIntent=ReadOnly during their connection, and they’re automatically redirected to one of the readable replicas.

Similarly, in AWS Aurora, applications connect to a different DNS name for read-only connections. AWS has an interesting feature: for some kinds of queries, Aurora will parallelize your query across multiple readable replicas. The DBA in me loves that, but at the same time, if your queries are so rough that you need multiple replicas to accomplish ‘em, you’re already sitting on a ticking time bomb: you’re not going to be able to run a lot of those simultaneously, and AWS charges you for each IO you perform. This is throwing money at bad queries – but hey, sometimes that makes sense.

Caching is of course a little trickier here. With traditional SQL Server Availability Groups, the database engine is receiving each log change, so it knows which pages need to be changed, and there’s no danger of it serving stale data from cache. Now, with the data pages being changed by the log service, there’s a risk that the replica could serve stale data. Me personally, I’m not too worried about that because that was always a possibility with Availability Groups, too. (Async replication traffic can get delayed (or even suspended), and the replica can get behind in applying changes.)

This design has a huge advantage: the replicas don’t need to talk to each other, nor do they need to be physically nearby. As far as they’re concerned, they’re just reading from data and log files. They don’t need to know anything about the magical Internet beans that are refreshing the data and log files right underneath them.

Who should consider Hyperscale and Aurora?

If all of these match, talk to your doctor about AWS Aurora or Azure SQL DB Hyperscale:

You have an existing app with a MySQL, Postgres, or SQL Server back end
The workload of your read-only queries is high enough that it mandates spreading the load across multiple servers (either to make the read-only queries faster, or to free up the primary server to make the writes faster)
Your read-only queries can live with data that’s seconds behind
You can’t implement caching quickly enough or cost effectively enough
Your primary bottleneck isn’t writing to the transaction log (or if it is, your log writes are currently 2ms or longer)
You’re willing to gamble on the vendor’s support (because you ain’t doing any fixing on these technologies – either they work, or you’re filing a support ticket, but there’s nothing in between)

I’m a huge fan of this architecture. We picked AWS Aurora when we built SQL ConstantCare a few years back, but only because there wasn’t anything like Azure SQL DB Hyperscale at the time. Today, I’d have a much tougher time making that decision.

For the record, I’m writing this as an interested database geek, not as a sales or marketing pitch. We don’t specialize in Aurora, MySQL, Postgres, or Azure SQL DB Hyperscale – I’m just sharing it because I find the products really interesting. If you want to learn more about this stuff, here’s where to go next:

Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases (PDF) – detailed white paper explaining the problems with current database storage, and how they ended up with Aurora’s solutions. The solutions are similar enough to Hyperscale that Microsoft DBAs will still find it a useful resource.
Azure SQL DB Hyperscale documentation – I know, I hate linking you to the manual, but this thing is so honkin’ new that there’s hardly any public documentation on it. The Hyperscale product manager, Kevin Farlee (@kfarleee), had a phenomenal slide deck on it at SQL Intersections in Vegas, but it’s not public yet. (Hey, that’s why you go to conferences!)
AWS re:Invent videos: Deep Dive on Aurora MySQL and on Aurora Postgres

Oh, and the diagrams – Apple Pencil, iPad Pro, and the Procreate app. Highly recommended. I can’t promise that my art quality will get any better, but I’ll do more of these to illustrate concepts. It’s fun!

38 Blog Posts We Couldn’t Write in 2018

December 31, 2018

SQL Server

Behind the scenes here at Brent Ozar Unlimited, we’ve got a place where we jot down blog post ideas. Anybody from the company can add ideas, and anybody can pluck those ideas back out and flesh them out into blog posts.

We’ve got a whopping 203 ideas sitting around.

So I cleaned house and deleted a bunch (some of which had been around since 2013!) Here are some of my favorites, and who knows, maybe one of you will go build one of these into a full blown post. Enjoy.

DROP INDEX can drop multiple indexes in one statement. Who knew? Bear Golightly, that’s who – during our Mastering Index Tuning class, he turned in his homework with a comma-delimited list of indexes, and I nearly fell out of my chair. While reading the syntax in Books Online, I also discovered that you can pass in a MAXDOP hint when you need to drop a clustered index. Amazing.
4 T-SQL signs of dangerous technical debt. Using ISNULL/LTRIM/RTRIM because you can’t trust the data in the table, using UPPER/LOWER to work around case mismatches between data and input parameters, pulling specific positions out of strings on a regular basis, and building HTML in T-SQL.
Query Bucks Reward Program. Put your users into Resource Governor workload pools, but don’t cap their usage. Then, send them an email “rewarding” them for all the terrible queries they’ve run.
How to visualize index contents. Wanna see what an index looks like? SELECT (key fields, includes) FROM table ORDER BY (key fields).
Things I Wish Developers Knew About Writing Their Own ORMS. Every now and then, I see somebody reinventing the wheel, and I wish they’d stop and learn a few lessons from Entity Framework, NHibernate, and Dapper first.
Script to check for database birthdays – look at sys.databases to see when each of your databases has an upcoming birthday based on its creation date.
SQL Server 2008 Memorial Guestbook – Eulogize it, talk about the good memories, how it was even better than its parents, and that it’s passed on now. Talk about its surviving relatives, SQL Server 2012 and its children. Encourage folks to leave their memories in the comments. Maybe include a photo gallery of its career.
How the Coast Guard handles risk – before dispatching boats or helicopters to rescue someone at sea, the Coast Guard uses a Green-Amber-Red (GAR) risk assessment method. They look at supervision, planning, team selection, team fitness, environment, and event/evolution complexity to calculate a total risk score. Then it’s up to the base leadership to decide if the mission should go forward as-is, or change one of the variables. This PDF gives you an idea of how it works.
The Biggest SQL Server Annoyances (and How to Fix Them) – maintenance plans should be switched to Ola Hallengren’s scripts, for example.
A Tale of Two Databases – do a riff on A Tale of Two Cities.
Why Higher Maxdop Isn’t Always Faster – take a single query and run it with higher maxdop threads, showing how performance growth isn’t linear (and how it impacts concurrency when multiple queries run simultaneously.)
Things You Can Do with SQL Server (But Probably Shouldn’t) – queueing, using it as a file server, scaling out with distributed views, using it as a caching engine.
The Developer’s Guide to the Plan Cache – what it is, what gets stored, when things age out, why it’s difficult to find specific queries or specific tables.
Bare minimum requirements for setup – like Naked and Afraid. This won’t keep your instance from going offline, but will keep it alive in the jungle for a few days.
“But nothing changed!” – things that will cause performance to slow down without code changes. (Bad plans, slipping plans, bad stats, concurrency changes.)
The Best Command You’ve Never Heard Of – how BACKUP WITH NORECOVERY works, and when to use it.
Are all hints bad? – which query hints are okay to use, and which ones are probably bad ideas that will come back to haunt you later as your data grows and your database changes.
Why Table Modifications Stink – demo changing a clustering key and measuring when it’s faster to start a new table instead and push the data over.
Maslow’s Hierarchy of Needs for Data Features – starting with OLTP design, performance, materialized reports, ad hoc reports, and rich analytics & data science.
Memory Metrics 101 – page life expectancy, cache hit ratios, and stolen pages.
Does updating statistics invalidate or erase the plan cache? – for example, when an object doesn’t meet the thresholds for getting a stats update, is anything affected?
Development environment architectures – how many dev environments should you have, and what should they be configured like? Should they match prod? Where should you test patches and deployment scripts?
OPTION (RECOMPILE) and Recompiles/sec – you would think that OPTION (RECOMPILE) queries would drive up the Perfmon counter for recompiles per second, but purposeful recompiles are compiles.
3 Signs Your Vendor Database was Created on Oracle – optimistic locking, heaps, and query hints.
The performance impact of verifying backups – people just check this checkbox when setting up their backups because it sounds good, but it performs terribly.
Worst Sysadmin Troubleshooting Steps – reboot, set up a job to automate the reboot, defragment the drive, run it under SA….
T-SQL Habits of Experienced Professionals – ending statements with a semicolon, properly specifying N on NVARCHAR strings, error handling, logging, debugging options for dynamic SQL.
Connection String Cheat Sheet – setting the application name, AG parameters for read-only, mirroring secondary, etc.
Your Files are More Secure Than Your Database – trolling title, but it’s amazing how often people lock down file servers but leave the database wide open where everyone’s a sysadmin.
The perils of using Database Mail – especially as a production customer-facing notification system.
The PRIMARY file group is special – things that go into PRIMARY that you can’t control.
Filtered index overhead – whenever we explain filtered indexes, everybody asks the same first question: is there a performance impact during inserts for evaluating the WHERE clause?
Forced parameterization overhead – I have this sneaky feeling that if I built really big strings and executed them with SQLQueryStress, I could conceivably make performance worse. Just a thought exercise though.
What should I do about memory dumps? – a lot of folks seem to think they should be analyzing these by hand, but you don’t have enough time to get good at that, fast enough. Call Microsoft right away.
How do I choose a backup tool? A decision tree based on database size, database quantity, server quantity, and staff workloads.
How can I see temp statistics on my AG replicas? If you’re worried about why queries are getting different query plans on different replicas, this would help.
Database greeting cards – I’m sorry for your loss. Congratulations on the new instance.
April Fool’s: fake KB articles – build a page template that looks like support.microsoft.com and pen unbelievable stuff.

Whew. I’m exhausted just reading that list!

Erik Says: Can you believe there was a blog post I wouldn’t write?

Thoughts About the 2019 Salary Survey’s New Questions

December 30, 2018

Salary

Our annual Data Professional Salary Survey is open now and closes this Sunday. We’re asking a few new questions this year (based on your feedback), and here’s what we’re seeing so far.

At how many companies have you held this job?

This one came about because folks were curious if job hopping paid off:

If you’re doing correlation at home, the next question you’d probably ask is, “How does experience play into this?” Go download the raw data (click File, Download) to find out. I’ll leave it to the data scientists to say for sure, but at first glance, it looks like it might pay to job hop every 3 years.

How many employees does your company have overall?

Do people who work for smaller or larger companies make more money?

I don’t think I’d read too much into the 1-5 employees category – just a low number of responses there. However, it’s interesting to see the salaries drop the larger the company becomes. (This is a question that might be interesting to correlate with other questions, like how much experience someone has.)

How many hours per week do you work, on average?

This is one where the analysts will need to bucket the results:

I’m not surprised that there’s a diminishing return as you get past 60 hours though – at that point, let’s be honest, you’re not doing your best work.

What is the population of the largest city within 20 miles of where you work?

Forgive the sloppy visualizations (not to mention using the averages rather than medians) – racing to crank this out in my spare time:

And yep, looks like it pays more to live in bigger cities. We’ll probably remove that question for next year (along with any others y’all decide aren’t really teaching you stuff) since the less questions we ask, the more likely folks are to fill out the survey.

Want to help? Fill out the survey now.

Your data helps everyone understand whether they’re being paid fairly or not, and the more results we get, the more confident you can be with your analysis. The survey closes Sunday.

20 Questions to Ask About Your Availability Group Design

December 28, 2018

Always On Availability Groups