Blog

Always On Availability Groups, Backup and Recovery, Clustering, SQL Server

One of the most popular things in our First Responder Kit is our HA/DR planning worksheet. Here’s page one:

Page 1 - how our servers are doing now, and what the business wants — Page 1 – how our servers are doing now, versus what the business wants

In the past, we had three columns on this worksheet – HA, DR, and Oops Deletes. In this new version, we changed “Oops” Deletes to “Oops” Queries to make it clear that sometimes folks just update parts of a table, or they drop an entire database. We also added a column for corruption (since your protection & recovery options are different than they are for Oops moments).

When people first see this worksheet, they usually scoff and say, “The business is going to tell me we never want to lose data, and we’re never allowed to go down.” No problem – that’s where the second page of the worksheet comes in:

Find the amount of data you’re willing to lose on the left side, and the amount of downtime you’re willing to tolerate across the top. Where the boxes match up, that’s a rough price range of the solution.

In this version, we added an asterisk to a lot of supposedly synchronous solutions aren’t – for example, Always On Availability Groups don’t actually guarantee zero data loss. I still keep that sort of thing in zero data loss because most of the time, it’s zero data loss, but you just need to understand it’s not a guarantee.

I like printing those two pages front and back on the same piece of paper because it helps management understand that requirements and costs are two sides of the same coin. It’s management’s job to pick the right box (price range), and then it’s IT’s job to build a more detailed estimate for the costs inside the box. The third and final page of the worksheet breaks out the feature differences for each HA/DR option.

If you’re one of the tens of thousands of folks who’s signed up for email alerts whenever we update our First Responder Kit, then you’ve already got an email this week with these new changes. If not, head on over and pick it up now.

How to Contribute Code to the SQL Server First Responder Kit (Github)

Last Updated December 5, 2017

First Responder Kit, SQL Server

So you’d like to fix a bug or contribute code to the First Responder Kit, but you’re new to Github. Let’s get started.

1. Open a new issue on Github.

Go to the Github issues list for this repo, and start a new issue. Here’s the example that I’m going to work on today:

In the issue title, put the name of the script, and some basic details so someone can see at a glance what’s going on.

In the body of the issue, put as much details as you can including:

Description of the symptom
What you wanted the code to do
What it actually did instead
Ideally, how you want to fix it

And submit the issue. If you’re going to work on it yourself, assign yourself to it if you have the permissions. (If you don’t, join the Slack channel, then hop in and ask.)

2. Get the Github issue number.

After you add the issue, the number will be in the title, and at the end of the URL:

Getting the Github issue number – here, #324

Now it’s time to work on some code!

3. Fork & download the repository.

First, get a Github account and install the Github desktop app. While this can all theoretically be done through the web, it’s a bit painful.

Then go to the First Responder Kit’s page, and click the Fork at the top right to fork this repo into your own account. (It’s kinda like doing a File, Save As to edit your own copy of something.) Then in your forked version of the repo, click Clone or Download button at the bottom right of this screenshot:

That will open your Github app and let you pick where to save the files locally.

4. Create a branch for your code.

In the Github desktop app, click on the new-branch button to create a branch for the specific issue you’re working on.

For the name, use the issue number from Github, plus a slash and your name. This indicates that it’s your code for that particular issue – remember that other people may end up working on this same issue.

5. Write your code, test it, and check it in.

For code guidelines, check out the CONTRIBUTING.md file in the repo.

Test your code against a case-sensitive instance of SQL Server 2008 and 2016, the oldest and newest supported versions. Other testers will also repeat your work, and automated testing will kick in later, but trust me, you want to do a sanity check first. Don’t get your pants pulled down in public.

Then click on the “1 Uncommitted Change” button at the top of the Github desktop (and there may be multiple changed files) and review your changes. They’re color-coded as to which lines have been removed from scripts, and which lines have been added.

Only check in changes and files that relate to your issue. If you accidentally changed a file that you didn’t mean to, in the Github app, right-click on that file name and click Discard Changes.

At the bottom left is your check-in title and description.

The title should:

Start with the issue number, like #324 – this is Github magic to link to an issue
Include the names of the files that were changed – this is helpful to others when they’re scanning a long list of titles of check-ins
A brief description of the issue

Then click Commit, and click Publish at the top right to sync your changes with the rest of the world. This doesn’t merge your code with the toxic sewer that is the dev branch just yet – you’re still on an island by yourself, but at least other people can get to your code.

7. Announce that your code is ready for testing.

Go back to your Github issue, and add a comment (don’t close it) that says you’re ready for testing. Include a link to your code, and any notes on how people can reproduce the issue, or know that your code works.

In the Labels dropdown on the right side, add the label ReadyToTest so folks can find your work easier.

Someone else has to test your code before it gets merged into the main branch, so it’s in your best interest to make it as easy and friendly as possible for someone to know that your code works.

8. Ready to merge? Add a pull request.

After someone else has tested your code, and you’re both happy that it works, open the Github app and click Pull Request at the top right:

For the remote branch, pick the Brent Ozar Unlimited First Responder Kit repo, the DEV branch. The check-in title and description will flow into here automatically, and click Send Pull Request. The maintainers will take a look at it and make sure things are kosher.

Stuff you don’t have to do

Release notes and changelog – you’ll notice that the top of each script has a changelog, version number, date, and release notes. Those are added when we consolidate a group of pull requests into a single release.

Documentation – if you don’t feel like writing it, that’s totally okay. We’re happy just to get code in at least, and we understand that the kinds of folks who write code don’t always overlap with the kinds of folks who like to write documentation. If you write things like a new check for new SQL Server problems, just know that we may not merge your code in with the dev branch until it’s also been documented.

For questions about the process, hop into the Slack channel. If you’re not already a member, sign up here. And thanks!

Announcing Dell DBA Days 2016: The SQL

Last Updated July 19, 2016

Head on over and register now for free. Space is limited!

This August, we’re goin’ back to Texas.

https://www.youtube.com/watch?v=qHv_4tHs0i0

Last year, we flew the team out to Round Rock, Dell’s HQ, and ran all kinds of SQL Server experiments in their hardware labs. We broadcasted a whole bunch of webcasts live on the net for you to watch and see what we learned.

This year, we’re going back again, and the lineup includes sessions on:

How to Measure SQL Server
The Unbearable Lightness of BEGIN
Downtime Train
Performance Overhead of TDE, Query Store, BPE
And everybody’s favorite from last year: Watch SQL Server Break and Explode

Well, That Sucked: Laying Off Half the Company

Last Updated February 13, 2017

Company News, SQL Server

I always wanna be honest with you, dear reader, and let you see how running a lil’ consulting shop goes. It’s been fun sharing a lot of our happy growing moments along the way. This one, though, is a lot less fun to share.

Yesterday, we had to let go of Angie, Doug, and Jessica.

The background: we basically sell emergency room services for SQL Server, and we’ve consistently been backlogged with work. In early 2015, we figured that me doing sales was holding the company back. If we hired a salesperson, we believed we’d be able to acquire more customers faster, and sell more work to past customers. So we hired Jessica, and staffed up on consultants.

This year, we’ve learned that a salesperson can’t really bring in more emergency room work (seems obvious in retrospect, but we were hoping for followup work), so I had to make a tough decision. I had to right-size the company back to where we’re regularly busy, maybe even turning some work away, just to make sure that we’re profitable overall. The training side of the business is still doing really well, and the company overall isn’t in trouble – but it would have been soon.

I’m bummed out, obviously, because it’s a failure on my part. These people are my friends, and I wanted to build a place where they could thrive for the rest of their working careers if they so chose. I’d always heard it’s tough to bridge the chasm between a 3-FTE consulting shop and a 10-FTE one, and now I really understand why.

Wanna keep in touch with them? Here’s their emails:

Angie Rudduck in Portland, Oregon – if you’re looking for an incredibly upbeat, fast-learning junior DBA or SQL Server support person, I’d highly recommend Angie. She did a great job streamlining our backup/recovery/CHECKDB education process.
Doug Lane in Cedar Rapids, Iowa – want to make your SQL Server faster, fast? Doug has been doing high end performance tuning for the last few years, and he’d be a huge asset to any SQL Server shop that needs a DBA who can build bridges between developers and SQL Server.
Jessica Connors in Chicago, Illinois – in the database world, salespeople often have a reputation for being slimy, but Jessica is totally different. She listens to your pains and matches you up with the right relief. She was a pleasure to work with.

New SQL Server First Responder Kit for 2016-07

Last Updated July 15, 2016

First Responder Kit, SQL Server

SQL Server 2012 SP3, 2014 SP2, and 2016 users are going to find a lot of stuff to love in here. The new per-query memory grants fields in the plan cache are exposed in sp_BlitzCache, and sp_BlitzFirst also shows memory grants for live running queries in @ExpertMode = 1. Here’s the details:

sp_BlitzCache v3.1:

Show cost for stored procedures. #339
Warn about trace flags added at the query level, and global trace flags. #361
Add warnings about Remote Queries. #315
Do not show Forced Plans warning if the real cause is forced parameterization. #343
Fix divide-by-zero error if Cost Threshold for Parallelism is 0. #358
Fix warning for unparameterized query. #334

sp_BlitzFirst v25 (The Artist Formerly Known as sp_BlitzFirst):

Add new memory grants columns to 2012-2016 live queries output. #362
Add SQL login to live queries output. #354
Filter Perfmon counter display to skip counters with zeroes. Still logged to table though. #356

sp_Blitz v53.1:

Warn about 2016 Query Store cleanup bug in Standard, Evaluation, Express. #352
Updating list of supported SQL Server versions. #344
Fixing bug in wait stats percentages. #324

sp_BlitzIndex v4.1:

Compression information in @Mode = 2. #18
Use recently-modified check to improve indexes-not-in-use recommendations. #220
Alphabetical sort for @GetAllDatabases = 1, @Mode = 2 output. #351
Remove per-day cost filter for missing indexes in @Mode = 4. #338
Missing index benefit is now labeled per-day to make it more obvious. #330

To get involved with the development & testing, head over to the Github repository, or download the full First Responder Kit with our checklists & guides.

[Video] Office Hours 2016/07/13 (With Transcriptions)

Last Updated July 17, 2016

Brent Ozar Unlimited Team

SQL Server, Videos

This week, Erik, Tara, Jessica, Doug, and Angie discuss queries, installing multiple instances of SQL server on a Windows VM, using DENY Database rules, migrating databases to Amazon RDS, availability groups, using filtered indexes, and more!

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Office Hours Webcast – 2016-07-13

How should I convert a database from Access to SQL Server?

Jessica Connors: From Steven Mitchell, he says, “For converting MS Access 2010 to SQL 2012 would using SSMA tool or upsizing tool be preferred? Or some other method?” I have a feeling that we don’t know.

Tara Kizer: We don’t use Access.

Angie Rudduck: Sorry.

Jessica Connors: That’s important.

Doug Lane: Having used Access in the past, I would shy away from that because Access has a nasty habit of rewriting things in a way that just really makes no sense. It might be logically equivalent but you’re probably better off just importing. Like if you want to do the quick and dirty way, just do import data from Access data source. Go through all the tables, import the data that way. Because you’re going to end up with—and maybe you want this, but probably not—like keys coming through, constraints, weirdness. You’ll end up with fields that get converted into datatypes you don’t want. So you’re probably better off just importing that data and manually rigging up how you want those columns to come in.

Jessica Connors: All right, enough with Access.

When will Microsoft release an updated BPA?

Jessica Connors: Question from Henrico. He says, “When will MS release BPA for 2014?” Do we know?

Erik Darling: Ask MS.

Jessica Connors: Microsoft.

Doug Lane: I’m not even sure what BPA he’s referring to.

Tara Kizer: It’s the Best Practice Analyzer but it’s already out. I used it at my last job. Maybe the question is about 2016 since that was just released?

Erik Darling: Probably.

Jessica Connors: Do not know.

What is free virtual memory?

Jessica Connors: Wow, James really wants us to answer this question. He asked it twice, three times. “What is free virtual memory? What is free virtual memory? How to resolve the issue?”

Tara Kizer: What are you trying to solve? We need some context.

Jessica Connors: Yeah, anyone know what is free virtual memory? Is that a proper question?

Erik Darling: I guess it’s when you get virtual memory for free from the virtual memory store.

[Laughter]

Erik Darling: It was on sale that day.

Angie Rudduck: Yeah.

Jessica Connors: All right, James, we need more context.

Should I enable Lock Pages in Memory on VMs?

Jessica Connors: Onward to Edward. He says, “I see conflicting posts on the internet for lock pages in memory versus VMs. Thoughts? Pros and Cons? I’m thinking of adjusting the minimum memory to one half of max.”

Tara Kizer: I always set lock pages in memory as just say standard across servers. I worked in large enterprises so we just had a document to fall on, lock pages in memory was always set on the dedicated database servers. As far as min memory to half of max, that’s also what are standard was at these large corporations.

Erik Darling: Yeah, I think for VMs more specifically though you have to be careful because if you’re not setting reservations at the host level, then I think stuff can get ripped away anyway.

Tara Kizer: Yeah, we never even worried about that on VMs but maybe that’s just… I don’t know.

Erik Darling: I mean, it’s a VM, you don’t care about performance anyway.

Doug Lane: Didn’t this come up as a question last week where Brent answered it too and he said I could go either way but I usually do it?

Tara Kizer: That’s right, yeah.

Doug Lane: Rings a bell.

Angie Rudduck: He said it was not the first thing to worry about or something like that.

Doug Lane: Yeah, yeah.

Angie Rudduck: If that’s the only thing you have to worry about, good job.

What’s the fastest way to copy data between servers?

Jessica Connors: Question from J.H. He said, “Would select * into be the fastest way of copying large data, or some type of bulk method, or something else from one server to another via linked server?” He says, “My tests look like select into is very fast.”

Erik Darling: It depends on what version you’re on. SQL Server 2014 and up select into can run parallel which is a nice added bonus for that. If you’re going to move a large amount of data, I would probably shy away from select into and probably use a method that sort of respects your transaction log a bit more and breaks it up into batches.

Tara Kizer: I would actually use SSIS for this since it can do it in bulk. It has a fast way of transferring data between servers.

Doug Lane: What do you guys think about minimal logging in that case?

Erik Darling: If it works, it works. It doesn’t always work. Don’t forget that TABLOCK.

Doug Lane: Right.

Tara Kizer: The problem with minimal logging is you’re reducing your recovery points though. So, yeah, you can minimally log things but you don’t have recovery points in that window.

Erik Darling: Yeah, so if it’s a one-time data move and you don’t care about anything during that window, then go for it. But otherwise, if your database is in full recovery mode, you’re not going to get much out of minimal logging.

What’s the best way to recover free space from data files?

Jessica Connors: I’m going to butcher this name—I don’t know how to say it. “What is the best way to regain the free space in data files after deleting a large amount of data?”

Tara Kizer: Well, I mean, it’s shrinkfile. So a one-time shrink is okay if you have to delete a lot of data and you want to reclaim that space. If you don’t need to reclaim that space, leave it there. If you have enough storage, just leave it there because when you shrink the file down you’re going to be causing some performance issues, fragmentation. One time shrink though, go ahead and do it. DBCC SHRINKFILE.

Erik Darling: The thing with shrinking data files, at least when I’ve done it is that if you only shrink it to the point where you have free space, like if you just sort of truncate it to where your data ends or a little bit over that, like a gig or 512 megs over that, you don’t really see the fragmentation problems because you’re just getting rid of empty space at the end of the file. So you kind of don’t see the same problems, at least I haven’t. I just shrunk down a bunch of databases on my availability group VM thing because I blew up Stack Overflow with some horribleness. But I shrunk it down to just sort of where the [while 00:05:48] things ended and didn’t see much fragmentation change. So maybe that’s a 2016 thing though. Be careful with that.

Tara Kizer: Maybe. I’ve always just shrunk it down so that there was 10 percent or 20 percent free space that way autogrows didn’t have to occur immediately after we’re done shrinking.

Erik Darling: That’s a good plan.

How can I run cross-server queries without linked servers?

Jessica Connors: Question from our friend Justin. He says, “Other than using a linked server, do any of you SQL super geniuses know of a way to run a query from instance A against instance B?”

Tara Kizer: You can use OPENQUERY but usually we want you to do this work in the application. Have the application query the two different data sources. OPENQUERY, you can use OPENQUERY, it’s just like a linked server.

Erik Darling: It’s not necessarily better, it’s just different.

Tara Kizer: Yeah. It does have a benefit of getting around an issue where with a linked server it can query the entire table even though you have a where clause on it. So if you have a ten million row table and your results set is only going to be ten rows, linked server can pull over those ten million first and then do the filtering. OPENQUERY can get around that issue.

Erik Darling: There’s a really good talk by Conor Cunningham, one of the bigwigs at Microsoft in the SQL Server department, on the SQLBits website. So the SQLBits conference, they put all their videos online. They did one a few years back on distributed queries where he talks a lot about the pros and cons of different methods of querying across servers and how it works behind the scenes. You should definitely check that out if you’re really interesting in querying data across servers.

Why shouldn’t I install multiple instances in a VM?

Jessica Connors: Question from Raul. He says, “Are there any downsides to installing more than one SQL Server instance on a Windows VM?”

Tara Kizer: How many do you have, Erik?

[Erik puts up 10 fingers]

Tara Kizer: 10?

Erik Darling: More. I’d stick my foot up, I don’t want to gross anyone out.

Tara Kizer: On a test system, go for it. But on a production system, we don’t recommend it.

Erik Darling: Yeah, stacked instances in any environment, whether it’s physical or virtual is just bad news. If you’re already virtualized, why are you stacking instances? Spin up a new VM, it’s right there anyway.

Jessica Connors: I feel like we get that question a lot.

Angie Rudduck: Yep. That and shrinking, or replication, which there’s already one in there too.

Jessica Connors: Or, what are your favorite monitoring tools?

Erik Darling: I don’t mind answering that. That’s a nice question.

Jessica Connors: But it never changes. I mean, has it changed?

Doug Lane: Well, there all nice questions.

Jessica Connors: There’s no such thing as a dumb question.

Erik Darling: I just assume when someone asks that question it’s a vendor plant. “They always mention us! Do it again!”

[Laughter]

Doug Lane: “You don’t believe me? Watch, I’ll ask.”

Erik Darling: It’s the same thing every time, it’s amazing.

When would you use the DENY database roles?

Jessica Connors: Question from Samuel. He says, “What would be a scenario where you would use the DENY database roles?”

Erik Darling: Developers.

Angie Rudduck: Yeah, I think he says two things. He says, “Why does DENY even exist if you give datareader isn’t it the same as denydatawriter?” But not necessarily true. I think the key, my understanding anyway, is that SQL is the most restrictive. So if there’s a denydatawriter, even if you have datawriter as well, you’re getting denied. So I think it’s kind of safeguard, but I don’t know.

Doug Lane: It’s an easy way of making exceptions security wise. So you say, “They should be able to write across all these databases, except I’m going to DENY this particular one.” So the DENY is very explicit whereas it can be used as sort of an exceptions tool to just kind of cut out, “All right, don’t give them this. Give them this, give them this. But they can do everything else that the other permission that I gave them allows them to do.”

Tara Kizer: We used the DENY at my last job for sensitive data. We had active directory groups. We had a lot of people in certain active directory groups and a lot of people in a different one. One specific group got the denial on certain columns in a table, like address information, emails, sensitive information. The other group was allowed to see it but if you were in both groups, you got the denial because DENY overrides the grant.

Angie Rudduck: I like that.

Doug Lane: I was going to say you’ll get a lot of questions on that if you ever take a Microsoft certification exam.

Tara Kizer: Oh really?

Doug Lane: Yeah.

How do I get data into Amazon RDS?

Jessica Connors: Question from Kimberly Lu. She’s migrating to Amazon RDS. She says, “My first experience migrating a database to Amazon RDS has been painful because I could not use backup/restore. I had to backup/restore to a local server, generate scripts, recreate users, import/export data using wizard. Do you have a less painful process?”

Tara Kizer: Nope. It’s RDS. If you’re using Amazon EC2 it would be easier since it’s a virtual machine, but yeah, RDS, it’s like Azure SQL, right? Limited features.

Erik Darling: Yep.

Angie Rudduck: Somebody else asked about going to the cloud and how to do it. I think that that’s the key. If you’re doing the VM versus the hosted database, if you go to the hosted database at all, you have to script all of your data into or out of. There’s no backup/restore to hosted databases. So if you’re not prepared for that, maybe don’t go to a hosted database.

Erik Darling: Yeah, the one sort of exception is going to Azure. Well, you can use it for RDS too I believe. There’s something called the SQL Database Migration Wizard. It’s available on CodePlex. It just does sort of a BCP out and in to Azure. I’m pretty sure you can use it for RDS as well but I haven’t tried. So it’s okay for that but it’s still slow if you’re migrating a lot of data because you are doing a BCP out, writing a file, blah, blah, blah. Not the fastest thing in the world.

Can I use different drive letters with Availability Groups?

Jessica Connors: Availability group question, question from Tim Smith. He says, “One of the benefits of availability groups is not having to have identical hardware. Does that include drive/drive letters? I tested and it looked like you could have a database on different drives on each node but a recent outage has me questioning that.”

Tara Kizer: I’m pretty sure you can. I’ve always had the same drives across because it’s just so much easier with restores but I’m pretty sure it acts just like mirroring where you can have different drives because you’re telling it what to do during the restore command. I’m not too sure what issue he’s referring to on the failover though. But I’ve always used the same drives. I’m pretty sure that it supports it across different drive letters.

Erik Darling: Yeah, anytime that I’ve had to build a cluster or anything else, I’ve always made sure that the drive—even like different servers using the same sort of hardware, I always have the drive letters be the same and do the same pathing for my database and log files, just so that at least it’s consistent across all my stuff and I know where to expect things to be. So you can do it, but it’s not really a best practice to have things be totally different across servers like that, especially when you are counting on a one-to-one copy when you get over there.

Tara Kizer: Yeah, we would need more info on what happened when you did the failover.

Angie Rudduck: He said, “On the restore of the database after failover the NDF file was gone.” I feel like there was a different problem there. Like maybe you had a problem with your drive and somebody actually deleted something. Maybe there wasn’t just a failover. Maybe that’s why you had a failover.

Tara Kizer: I wonder if the NDF came later after the availability group was set up? If another file was added and it didn’t get added to the secondaries? I don’t know. I haven’t tested that.

(Postscript: yes, this is where different drive letters will fail.)

Can you turn off auto-updating for SQL Server 2016?

Jessica Connors: Question from Scott Kelly. Isn’t that an astronaut? Scott Kelly? Didn’t he spend a year in space?

Erik Darling: Yeah.

Jessica Connors: Scott Kelly is here, guys.

Doug Lane: That’s Commander Scott Kelly to you.

Erik Darling: How’s your bone density?

Jessica Connors: Yeah, they did an interesting test on him and his twin. Let’s see here. “Is SQL 2016 auto updating not able to be turned off?”

Doug Lane: Oh, boy.

Angie Rudduck: Like Windows updates but SQL updates?

Erik Darling: You can turn SQL updates off. You can turn automatic updates off for anything. Have you had an update? Because I haven’t.

Doug Lane: So there’s the phone home thing that I think in—what is it—Developer and in Express editions you can’t turn it off. If I remember right. But I don’t know if that’s the same as the auto update. I think you can disable auto updates from SQL Server.

Erik Darling: Yeah, the Customer Experience Improvement Program.

Jessica Connors: Yeah. Scott Kelly wants us to know that he did not work for NASA.

Angie Rudduck: No, he did. He’s not the astronaut but he did work for NASA.

Doug Lane: How about that.

Jessica Connors: Oh, he did work for NASA. That’s awesome.

Tara Kizer: I went backpacking with a guy who currently works for NASA this past weekend. He’s a physicist or something like that. It was really cool.

Angie Rudduck: Did he do better at the hike?

Tara Kizer: He stayed back with the person that was having high-altitude issues, or altitude sickness issues, I should say. He actually had to carry her out on his back because it was so bad. Yeah, I’ll tell you guys later.

Jessica Connors: You did a fourteener, right? You made it to the top…?

Tara Kizer: I completed it. It was rough. It was really rough.

Jessica Connors: Good job, Tara.

Can you do columnstore indexes on top of GUIDs in SQL 2012?

Jessica Connors: From Joshua. He says, “Is there any workarounds that allow columnstore indexes with a G-U-I-D in SQL 2012?”

Tara Kizer: GUID.

Jessica Connors: I’m like, it’s not GUI.

Tara Kizer: I don’t know, Erik?

Erik Darling: What is the question? “Are there any workarounds to using a columnstore index with a GUID?”

Doug Lane: Do they not allow GUIDs in 2012? I don’t even know.

Tara Kizer: I don’t like GUIDs so I don’t use them.

Erik Darling: Yeah, columnstore in 2012 in general is kind of bogus. You couldn’t update it and there were a lot of limitations so probably not. I haven’t tried explicitly to use a GUID with it and I certainly haven’t tried to use a GUID with it in 2012 because that was just a no fun version of columnstore. It was just lackluster.

Do you recommend Converge Solutions?

Jessica Connors: Question from Julius. He says, “Do you folks recommend running SQL Server on Converge Solutions? Any experience, drawbacks, benefits, etc.?”

Tara Kizer: I don’t even know what that is. Do you guys know?

Doug Lane: I have never heard of it.

Jessica Connors: All right, never heard of it, Julius.

Doug Lane: Sorry.

Tara Kizer: Our answer is no.

[Laughter]

Tara Kizer: I’ll have to look that up.

(Postscript: I’m guessing they weren’t referring to a brand name, and instead just general converged infrastructure, which is a pretty big/broad topic.)

Why is only one of my TempDB data files growing?

Jessica Connors: Ronny… oh boy, this looks long. Do you want to read it, Angie?

Angie Rudduck: Oh sure. So Ronny says, “I have multiple tempdb files and only one file is growing, actually the second file that was created. All other files are still reflecting the initial memory size.” He has trace flags 1118 and 1117 enabled—oh, no, just 1118, and wants to know that he also needs to add 1117. Does he need to delete the other files and start over before adding the additional trace flag?

Erik Darling: No. What you need to do is make sure that you grow all the other files to the same size as the second file. Elsewise, all of your queries will still be attracted to that larger data file and it will keep growing. So if you can, grow out your other files to match the size of the second file. If not, you’re going to have to deal with the kind of painful and possible corrupting experience of shrinking a tempdb data file.

Tara Kizer: They actually are saying it hasn’t been a problem—or, Paul Randal said corruption on shrinks has not been a problem in several years. They fixed it with 2012 I believe he said, or maybe even earlier. They just never made it public.

Erik Darling: Oh, okay, well, that’s good. I hope you’re using 2012 or later then, pal.

Tara Kizer: It might have been earlier. I can’t remember if it was an email or what it was.

Angie Rudduck: Yeah, if only they publicized when corruption is not happening anymore.

Erik Darling: They should just tell people that shrinking databases will cause corruptions so everyone will shut the heck up about it.

Doug Lane: So you also want to make sure that your file growths are set to zero, otherwise your tempdb files will keep growing. You don’t necessarily want that. It’s better to set it at the right size and then just kind of let it go with that because that may be why you ran into trouble in the first place is that you’ve got file growth on that one file and not on the others.

Tara Kizer: I actually quote/unquote disagree with that.

Doug Lane: Okay.

Tara Kizer: It’s not that that’s bad, it’s just it has to do with monitoring. So on the Brent Ozar website it says to grow your files all the way out to whatever that drive is for tempdb. If you do that, then your monitoring software is going to be alerting, the on-call DBA is about to get a disk space issue. Now of course, you could have an exception for the tempdb drives but I try to avoid exceptions. So instead, grow your files out to what they need to be and then keep autogrowth on. Have the file sizes be identical and you start out with the same exact autogrowth and they should grow proportionally based upon free space. Should not have an issue. If you do have an issue, I wonder what’s going on. You might have some kind of weird thing going on. I like to keep 20 percent free space to avoid alerts. Then when you do get an alert, grow out the mount point or drive.

Angie Rudduck: That’s for good IT teams that have alerts.
Tara Kizer: Yes.

Angie Rudduck: If you don’t have alerts, Doug has a good point. If you never allow them to grow, you never have your disk go over space, but your tempdb could go offline.

Tara Kizer: That’s the problem with that solution is that if you have a query, this wild query that runs, it’s going to fail if it needs more space in tempdb. Whereas if you had free space, well, if you have alerts in place, DBAs are going to start receiving alerts that we’re running out of space and you might be able to add the space before the query fails.

How can I move all my nonclustered indexes to another filegroup?

Jessica Connors: Speaking of running out of disk space, Gordon asks, “I’m running out of disk space for a one terabyte database with a single data file. So the plan is to move clustered indexes to a new file and a new filegroup. Given that everything is currently on the primary filegroup, would it be a good idea to move all the non-clustered indexes as well?”

Erik Darling: I would set up two different filegroups actually because if you’re doing this sort of work and you’re going to get some benefit out of it and you want to be able to restore the database as files or filegroups separately, you’re not going to want everything in the primary file group because you’re going to need that primary thing online first in order for the database to be accessible and restore other files and filegroups. So I would actually create one for clustered and one for non-clustered and move them off into one into each so that I can keep the primary part small and just have like the system databases and stuff in there. Be careful. Depending on what version you’re on because not all of the rebuilds are able to be online if you have max column types prior to 2012. So if you’re planning on a seamless move, mind your datatypes.

Are there any gotchas with SQL 2014 SP2?

Jessica Connors: Did SQL Server 2014 SP2 just come out? I thought they weren’t doing that anymore. You guys just told me they weren’t doing that anymore.

Tara Kizer: It will be rare.

Erik Darling: Starting in 2016.

Jessica Connors: You’re liars. Gotcha. “Any issues with this SP that y’all are aware of?”

Tara Kizer: Not yet.

Erik Darling: No, it’s a lot of fun.

Tara Kizer: It’s too new.

Angie Rudduck: It is too new.

Erik Darling: A lot of the stuff in Service Pack 2 was oddly catching SQL Server 2014 up to Service Pack 3 of 2012. It did add a couple of other things but most of it was just playing catch-up there with a lot of like the stuff that you can see execution plans and finding out about memory grants.

Tara Kizer: Oh, the memory grant issue. Memory grant columns are in Service Pack 2?

Erik Darling: They are and I’m going to be updating stuff.

Tara Kizer: Yeah, I’ve got a client that’s been waiting on those columns so I’ll let them know. Just real quick on the Service Pack 2 thing. When I said it’s too new to know about issues, just remember that service packs are fixing hundreds or thousands of bugs. So by not installing it, you’re more at risk of not installing it than you are of installing it. So service packs, hotfixes aren’t necessarily a bad thing and you don’t necessarily have to wait a while to see how other people are doing.

Erik Darling: If you’re worried about it, install it on a dev server. Let it hang out for a month and do your thing on it and then push it to prod. If you don’t have a dev server, then quit your job.

Are there any downsides to filtered indexes?

Jessica Connors: Question from Joe. He says, “Are there any particular downsides to using filtered indexes? I’ve recently been performance tuning some queries that have non-parameterized search arguments and they’re like magic.”

Tara Kizer: They are like magic. The only issue is if you have a query that has the opposite of what your filtering condition is, then it’s going to have to do a scan. But you know, you want your filtered indexes to be what your queries are usually going to do. Like is deleted equals zero for instance. If you have an is equals one, probably that’s going to be a maintenance job that runs at night to clean out data. It’s okay for those things to do a scan.

Doug Lane: The higher the cardinality on whatever it is you’re filtering on, the more effective that index will be. So if you’ve got data that’s split 50/50, it will be helpful. But if you’ve got data where you’re going to be looking for something where the filter condition there is down to say ten or fewer percent of the rows, then you’re in really good shape. Incidentally, I just put out a statistics course that does include filtered stats, which you get with filtered indexes.

Tara Kizer: All right.

Doug Lane: So you can check that out too.

Jessica Connors: I think about three months ago we just included access to our entire SQL training library for anyone that does the SQL Critical Care. If you want any of these guys to take a look and assess the health of a database, let me know. Reach out. For some reason, July and August are slower for us. I think it’s because summer.

Tara Kizer: Everyone is vacationing.

Angie Rudduck: Too much beer and barbecue.

Jessica Connors: Yeah, vacations. So if you are on this webinar and you reach out to me, Jessica@BrentOzar.com, we are supplying some special deals within the next two months for our SQL Critical Care product. So not only do you get the Critical Care but also the additional training, access to our SQL training library.

Angie Rudduck: Isn’t Brent come out with a new one? Not us three… but the other Brent?

Tara Kizer: Not these fabulous Brents.

[Laughter]

Angie Rudduck: Isn’t he coming out with a new video, Doug? I just expect you to know.

Doug Lane: I think so. I know I’ve got another one coming.

Angie Rudduck: Oh, see. There we go.

Tara Kizer: He painted his wall green so he must be working on something soon.

Doug Lane: Oh.

Tara Kizer: Not you—Brent.

Doug Lane: Oh, Brent, yeah. I’m thinking, “Is my tint that bad?” More stuff on like execution plans and querying.

Angie Rudduck: Nice.

Jessica Connors: Cool beans. All right, guys, that is all the time we have for today. I’ll let you go. Adios.

Erik Darling: Later.

Why Not Just Create Statistics?

Last Updated February 13, 2017

Indexing, SQL Server, Statistics

Here at Brent Ozar Unlimited

We have a proud tradition of not blaming index fragmentation for everything. There are points you should deal with it, but they’re probably not 5% and 30% and 1000 pages. But that’s not what this blog post is about. I’m hoping to clarify why we’re more interested in up to date statistics, and also why statistics outside of indexes aren’t really the most helpful thing. If they were, we’d all just create statistics and every query would magically blaze from select to offset without a care in the world.

Statistics: It’s What’s Inside That Counts

Statistics are what SQL Server uses to figure out how many rows to expect from accessing a data structure. You can do some things that fundamentally break this, like using functions in joins or where clauses, using local variables or optimize for unknown, using table variables without recompile hints, and a sniffed parameter can just allow SQL to run a query without guessing at all. It already guessed once. No tag backs. As you may imagine, this is important information for SQL to have for running your queries optimally.

Indexes: Pride and Vanity

Indexes of the nonclustered variety contain subsets of your table or view’s data. Clustered ones are your table or view data ordered by the clustering key(s). Focusing on the nonclustered variety, they’re the “and the [band name]” to your clustered index’s “[lead singer name]”, and they’re great for providing SQL with a more appropriate data structure for your query.

If you don’t need to select all the columns, and you do need to filter, join, order by, or aggregate a column not in the key of the clustered index, nonclustered indexes get a solo after the chorus. Give the drummer some.

Nonclustered indexes will, under normal circumstances, get a statistics object created with rolling density information for the key columns going left to right, and a histogram on only the leftmost column.

With that out of the way

Why do we care more about statistics being updated than indexes being fragmented? Largely, because reading pages with some empty space from a fragmented index is oh-so-very-rarely the root cause of a performance issue. Especially if those pages are already in memory. Out of date statistics can allow SQL to continue to make some really bad guesses, and keep giving you a lousy execution plan no matter which way you tune your query.

The bigger your table gets, the worse the problem gets. Prior to 2016, if you don’t turn on Trace Flag 2371, about 20% of your table’s rows need to change before an automatic statistics update kicks in. For a 100 million row table, this can be a long ways off. Poor cardinality estimates here can really sink you. Rebuilding indexes for a 100 million row table is a B-U-M-M-E-R.

Log Shipping? Mirroring? Availability Group? Good luck with that.

Crappy server? Low memory? Slow disk? Dead man walking.

You may ultimately spend more time and expend more server resources defragmenting indexes than your queries will spend reading extra pages from fragmented indexes. Rebuilding or reorganizing large indexes can be a special kind of brutal.

Consider the process

Read a bunch of index pages with sys.dm_db_index_physical_stats to figure out if there’s fragmentation, reorganize or rebuild based on feedback.

Reorganize is online but single threaded and can take FOREVER on big tables, especially if you’re compacting LOBs.
Rebuild is offline and single threaded in Standard, online and potentially parallel in Enterprise, but you better hope you’re patched up so you don’t corrupt anything.

Is that worth it? Every night? For every index on every table in every user database? Only if you can prove it.

The one upside to Rebuilding is that it also updates statistics with a full scan. Think about this the next time you say something like “rebuilding the index fixed the problem”, you may have an epiphany on the way.

Wait for it.

Statistics with no indexes

SQL Server doesn’t make easy work of getting information about your Statistics, or finding out which statistics get used. Even at the query level, you have to use a spate of Trace Flags to find out what gets loaded and looked at. Cached plans don’t fare much better.

No wonder everyone cares about indexes and their fragmentation. Microsoft has made information about them easy and abundant, while Statistics are kept hidden in the basement next to piles of soft bones and a bowl of hot blood.

Head rush moment: SQL may use information from histograms outside of the index it chooses for cardinality estimation.

Back to earth: If you just create a bunch of statistics instead of indexes, you’re (at best) using your Clustered Index for everything (which is still bad), or you’re using a HEAP for everything (which is usually worse). You’re still generally better off creating good indexes for your workload. They’ll get statistics objects created and associated with them, and if SQL thinks another column is interesting, it will create a single column statistics object for it, as long as you haven’t turned off auto create stats.

Sure, you can put on your black cloak and goat mask and create some multi-column or filtered statistics, but in the words of a wise man (Doug), you end up with more stats to maintain and understanding query behavior gets more difficult.

Filtered statistics suffer from a problem where they don’t automatically update based on the filtered row count, but rather the table row count. Imagine you have a 100 million row table, and your filtered index is on 1 million rows. All million of those rows might change, but the statistics on that index won’t. 1 million is not 20% of 100 million. You’ll have to update the statistics manually, or rebuild the filtered index.

Multi-column statistics are hardly a replacement for a multi-column index, and it’s not like you get an enhanced histogram that includes the second column. It’s just like a normal histogram. All you get is the density information for the columns you throw in there. Boo hiss.

Moral of the story (B-B-B-B-B-B-BENNY WITHOUT THE JETS)

Indexes are a really important factor for performance, but index fragmentation very rarely is. Statistics are super helpful when they’re not out of date, and getting them up to date is much easier on your server’s nerves. Though not perfect, I’d rather take my chances here. Updating statistics can also cause a bad execution plan to get flushed out of the cache. On their own they can sometimes help queries, but you should only end up here after you’ve really tuned your indexes.

Unless you can establish a metric that makes nightly index defragmentation worthwhile, don’t jump to it as the default. Try just updating statistics. You may find that nothing at all changes, and you now have many extra hours a night to do maintenance. Like run DBCC CHECKDB. If you think index fragmentation is a performance problem, try corruption sometime. That’s not what Brent meant when he said “the fastest query is one you never make.”

Thanks for reading!

Can Adding an Index Make SQL Server 2016…Worse?

Last Updated April 9, 2017

Execution Plans, SQL Server

Using the StackOverflow database, let’s check out Krock’s query. He’s a competitive fella, and he’s looking to find users who signed up for StackOverflow after he did, but who have a higher reputation than he does.

I’m going to simplify the query a little here:

SELECT me.Id, u.Id
FROM dbo.Users me 
JOIN dbo.Users u on 
u.CreationDate &gt; me.CreationDate
and u.Reputation &gt; me.Reputation
WHERE me.Id = 557499;

SELECT me.Id, u.Id

FROM dbo.Users me

JOIN dbo.Users u on

u.CreationDate > me.CreationDate

and u.Reputation > me.Reputation

WHERE me.Id = 557499;

The Users table has a clustered index on the Id field, so the initial seek on “me” (Krock’s row, Id = 557499) is super-fast. However, to find all the users with a higher creation date and reputation – man, that sucks scanning the clustered index for that, and it brings back a lot of rows. Here’s the execution plan:

execution_plan_1_details — Bringing back 5.3mm rows

Hover your mouse over the clustered index scan, and you get:

Estimated number of rows = 5,277,830
Actual number of rows = 5,277,831

Nice job, SQL Server, close enough for government work. Now it suggests a missing index – rightfully so – and let’s add it, because we want this query to go faster.

After adding the index, here’s the execution plan:

The query runs faster, make no mistake – but check out the estimates:

Estimated number of rows = 1
Actual number of rows = 165,367

Those estimates are built by SQL Server’s cardinality estimator (CE), and there have been major changes to it over the last couple of versions. You can control which CE you’re using by changing the database’s compatibility level. This particular StackOverflow database is running in 2016 compat mode – so what happens if we switch it back to 2012 compat mode?

It’s a differently shaped plan – 2012 decided to go parallel with this query. Its estimated subtree cost is 7.37 – higher than my SQL Server’s Cost Threshold for Parallelism.

The motivating factor can be discovered by hovering over that index seek:

SQL Server 2012’s cardinality estimator guessed that 475,005 users would have a newer creation date and higher reputation than Krock.

This query has so many fun lessons to share.

Your database options matter.
What database you run a query in matters.
Neither 2012 nor 2016’s cardinality estimator is “right” in this case, they’re just differently wrong
Indexes create statistics on their columns, but that doesn’t mean SQL Server has perfect information on what’s inside those columns
Besides, SQL Server can’t know Krock’s creation date or reputation until the plan is built and the query’s already running

I find statistics to be one of the coolest topics inside the engine, and that’s why I love Doug’s newest course, Statistics: SQL Server’s Guessing Game. I bet you’re gonna love it too – go check it out.

New Course: Statistics – SQL Server’s Guessing Game

Last Updated April 9, 2017

Closed captioned for your statistical pleasure

You have to make queries go faster, and you suspect that outdated or inaccurate statistics are hurting your execution plans.

Doug Lane is here to help with his new video course, Statistics: SQL Server’s Guessing Game.

It’s phenomenally detailed – every single person on our team learned stuff from this class (me for sure!), and I know you will too.

Here’s the modules he covers:

A Tale of Two Plans (7m) – We look at two execution plans for the same query, talk about why they’re different, and figure out which one makes the most sense.
Getting to Know Statistics (15m) – We pop open SSMS to look at what statistics drove an execution plan’s estimates. First, we use a query to find which statistics focus on which columns, and then bust out DBCC SHOW_STATISTICS to understand statistics contents.
Combining Multiple Single-Column Statistics (8m) – In the last module, we looked at statistics independently. Now, what happens when our query references multiple columns, and we have to combine multiple statistics to guess how many rows will return. You’ll also see how both the old (pre-2014) and new cardinality estimators shape up.
What is the Cardinality Estimator? (5m) – The CE’s job is to use statistics to determine how many rows will come back for any given operation in a query plan. SQL Server’s Cardinality Estimator changed a lot in 2014, and you need to understand which CE you’re using before you analyze your statistics.
Multi-Column Statistics and the Cardinality Estimators (14m) – In the real world, your indexes often cover multiple columns. We’ll see how the density vector has multiple rows now, and see how the pre-2014 and 2014+ cardinality estimator have huge differences.
Filtered Statistics: ENHANCE! (15m) – Filtered statistics help you get ENHANCED statistical coverage of small parts of your table.
The Ascending Key Problem (11m) – In data warehouse scenarios where you constantly load in new data every night, and then go query the data you just loaded, you often get really bad execution plans.
10 Rules for Managing Statistics (12m) – Now that you understand how SQL Server uses statistics, we need to cover how to manage your statistics.

And just like Doug’s T-SQL Level Up course, it’s beautifully shot, and explains some really challenging topics with great visual examples.

Head on over and check out the trailer.

SQL Server 2014 SP2: DBCC CLONEDATABASE

Last Updated July 12, 2016

Getting developers production data stinks

DBAs are stuck in this annoying cycle where they need to give developers production, or production-like data, but… Production data can be huge. Hundreds of gigabytes if you’re lucky, several terabytes if you’re not. Then once it gets there, you have other considerations. Either you lock down Dev, or you come up with a process to scramble data. This poses another set of problems because once data is scrambled, you lose some of the nuances of your actual data. Pattern matching scrambled data is useless and I hate it.

One alternative has been to script out a statistics only copy of your database. Which is cool, but kind of clunky. Microsoft has sought to ease that a bit with… yet another DBCC command.

That doesn’t solve any of those problems.

There’s gonna be a lot of HOOP-la!

SQL 2014 SP2 introduces DBCC CLONEDATABASE! Let’s get this straight. it’s not really meant to solve those problems. It’s meant to give you a minimal copy of your data to reproduce and diagnose performance issues with, without adding further load to your production server. But it suffers from the same problem that any statistics only copy of your database will. Let’s go through some of the issues.

Get your clone on

The syntax is really simple. Command. Database name. Clone database name.

DBCC CLONEDATABASE(StackOverflow, StackClone)

1	DBCC CLONEDATABASE(StackOverflow, StackClone)

This will produce a Read-Only ‘copy’ of your database on the same server as the database you cloned.

Here’s where the problems begin!

Production values are still visible

If you run DBCC SHOW_STATISTICS, or go through the GUI, you can see the steps in the histogram. For my StackClone database, the command looks about like this.

DBCC SHOW_STATISTICS(Users, _WA_Sys_00000005_0519C6AF)

1	DBCC SHOW_STATISTICS(Users, _WA_Sys_00000005_0519C6AF)

And there’s a bunch of names! Not all the names, but enough to be dangerous. I’m only showing the top 10 because a picture of the whole thing would be tacky and useless, like a condom in your wallet.

TIME and IO wait for no man

And they certainly don’t register anything with only statistics to reference.

SET STATISTICS TIME, IO ON 
SET NOCOUNT ON 

SELECT	u.Id, u.DisplayName
FROM dbo.Users AS u
WHERE u.DisplayName = 'Brent Ozar'

SET STATISTICS TIME, IO ON

SET NOCOUNT ON

SELECT u.Id, u.DisplayName

FROM dbo.Users AS u

WHERE u.DisplayName = 'Brent Ozar'

On real data, we get back this:

Table 'Users'. Scan count 1, logical reads 52412, 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 = 593 ms,  elapsed time = 594 ms.

Table 'Users'. Scan count 1, logical reads 52412, 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 = 593 ms, elapsed time = 594 ms.

On the clone, we get back this:

Table 'Users'. Scan count 1, logical reads 0, 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 = 0 ms,  elapsed time = 0 ms.

Table 'Users'. Scan count 1, logical reads 0, 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 = 0 ms, elapsed time = 0 ms.

This immediately narrows the range of issues you can troubleshoot down. If you want to do any sort of meaningful performance tuning, you’re already short a couple of the best indicators that you’re on the right track.

Read-Only Means Read-Only

When your database springs to life, like what probably happened to Lazarus, it’s all grey and sticky.

Okay, maybe not Lazarus. Maybe that dude Tooms from X-Files that eats livers and takes wicked long naps. The down side here is that new queries won’t auto-create statistics. So if you run a query, and you don’t already have statistics for a column, you just get a warning.

I mean, yeah, you can make it Read-Write to get around this, but you should probably move it off to another server before doing this. I’m guessing you don’t want a database sitting in prod that’s virtually indistinguishable from your other databases.

Updating statistics and Rebuilding indexes ruins everything

The other downside of making it Read-Write, is that if a maintenance task comes along and checks the data, it wipes out your existing statistics. This is also true of index Rebuilds, which if I haven’t beaten it into you already, updates statistics with a full scan. And hey, forget about adding new indexes. SQL is hip to your empty table game. You’ll have to add them in Prod, and then re-clone your database, or script that single statistic out and add it to your database clone. Sound like fun? Yeah? You’re a real sick puppy.

Questions and answers

This is pretty handy for a few different scenarios. Just be aware of the limitations and risks. If you’re out there reading this, and you’ve got a good process in place for scrubbing production data, leave a comment. I’m sure lots of other people out there could use the advice.

Thanks for reading!

Brent says: DBCC CLONEDATABASE is just one of many cool things in SQL 2014 SP2. There’s new memory grant columns in sys.dm_exec_query_stats, tons more stuff in execution plans, and even faster performance on partitioned tables. The dev teams are pouring a lot of effort into backporting awesome improvements into 2014. I’ve never been so excited about Microsoft’s commitment to SQL Server.

New Sample Databases From Microsoft

Last Updated July 4, 2016

Brent Ozar Unlimited Team

In celebration of AdventureWorks being out of business for a decade

Microsoft has released a couple new databases. Wide World Importers is a fresh-faced start up, probably a drug smuggling front, run out of a basement office next to a temp agency.

There’s some JSON involved. No one ordered bikes. It’s a hoot.

The OLTP database is about 120mb, and the data warehouse is around 50mb. Hopefully no inflation will be necessary.

Head on over and check them out.

Thanks for reading!

Brent says: as a presenter & author, I gave up on the Microsoft sample databases and switched over to StackOverflow’s public database. It’s elegantly simple (just a handful of tables), has real-world data distribution, there’s plenty of interesting sample queries available, and is big enough that you can demonstrate real performance tuning issues. (120MB? Really?)

[Video] Office Hours 2016/07/06 (With Transcriptions)

Last Updated April 9, 2017

Here’s the video on YouTube:

This week, Brent, Jessica, Richie, Doug, and Angie discuss snapshot replication, file and table-level restores, whether you should enable lock pages in memory, redistributing data across files or a database, and Doug’s new video class.

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Office Hours Webcast – 2016-07-06

Why is snapshot replication always running?

Jessica Connors: Replication, that’s the first question I see. Trizzle has a question. He or she says, “I have a snapshot replication that runs once a day but it is running all the time. Is it possible to run it on a scheduled time only, not the whole time, because it is blocking when it runs all the time.”

Brent Ozar: Sounds like a big snapshot. I mean, growing, growing bigger and bigger kind of a deal. Snapshot replication you’re pushing the whole thing across. Do you really want to take snapshots all that often or do you maybe want to just incrementally push across smaller objects? What I’d ask is how big is that table and is it time to start doing something other than snapshot replication. Tara, have you ever used snapshot replication for anything?

Tara Kizer: I have not, our requirements have always been close to real time replication. So we’ve always used transactional replication. Anytime where we could have a 24-hour delay in data, we just used backup and restore instead of snapshot replication.

Brent Ozar: Yeah.

Tara Kizer: Now of course, that’s dependent upon your technologies and database size how you’re going to get that backup and restore done but I think you’re right about the snapshot replications, the size and it’s the blocking that occurs when it has to do I believe it’s a schema lock to make sure that the schema hasn’t changed.

Brent Ozar: The only time I’ve ever seen it make sense and I’ve never used it, I’ve just heard other people talking about it, when you have a ginormous database but you only have like one tiny table or a couple tiny tables that you want to replicate out somewhere, so it’s quick to take the snapshot but as soon as that starts growing to any percentage or size of the database then you’ve got to start doing some change management.

Tara Kizer: Yeah.

How can I decimate tempdb?

Jessica Connors: Question from Justin, he says, “For someone who doesn’t want to create the query, does anyone know of a good site that has queries to decimate tempdb?”

Tara Kizer: Decimate? I have to go to Google real quick…

[Laughter]

Brent Ozar: Data.stackexchange.com has all kinds of crappy queries on it that are written against the Stack Overflow database. The problem is you actually have to have the Stack Overflow database. The funny part is that Erik and I write queries like this all the time because we’re always trying to break stuff so what I would do is create a table that only has two fields in it. It has like an identity key and then it also has a char 6000 field. Some people say “char,” some people say “care,” I say “care” because I’m a caring kind of guy. A char 6000 field which means it’s going to take a whole page, a whole 8k page to store every single record you put into there. Then just do select from system tables, like sys.all_columns or sys.all_objects and just insert stuff into there. You don’t even have to fill that char 6000 with anything just put like your name in there and it’s going to take a whole page to store every object. Man, you can dump a buttload of tempdb into there super fast. Then just run it from a whole lot of sessions or cross joins sys.all_columns to itself in order to produce a buttload of records. You can knock a server over pretty fast with that.

Tara Kizer: Or just have a business user run a query. That’s what we had at my last job. There was a query that took a half terabyte of tempdb space. Half terabyte.

Doug Lane: That’s more than decimating, that might be like “centimating” it or “millimating” it.

Tara Kizer: Yeah, we got an alert that the tempdb mount point was running out of space. I was the on-call DBA so I took a look. Checked WhoIsActive and what was running, it was just like this massive query. All the data in the world.

Brent Ozar: Who does this stuff?

Richie Rump: Career-limiting query.

Angie Rudduck: End users, that’s who does it.

Tara Kizer: Yes, exactly.

Why do two inserted records get the same identity?

Jessica Connors: Question from Joe O’Connor, he says, “Have you ever encountered a situation where two records inserted into a table get the same identity back? Our developer is claiming that this has happened but I thought they were supposed to be unique.”

Tara Kizer: How are they querying it? Is it @@IDENTITY? What are they using to query it?

Brent Ozar: Yeah, there are bugs where @@IDENTITY returns someone else’s identity. You want to use, if I remember right, SCOPE_IDENTITY, right?

Doug Lane: Yeah,

Brent Ozar: @@SCOPE IDENTITY. Because you could…

Tara Kizer: No, no @@. Just SCOPE_IDENTITY and it might be ().

Brent Ozar: That’s it, especially because if you have like triggers that are firing, you can get the wrong identity, like the identity that happened from the trigger too.

Tara Kizer: Yeah. I’m not even too sure why they use @@IDENTITY if it doesn’t produce the right result all the time. Just get rid of that variable then.

Brent Ozar: Job security, for people like us who got burned by it.

Doug Lane: Sequence is still part of SQL Server, right? They didn’t yank that did they?

Richie Rump: As of 2012.

Doug Lane: Okay, because that had issues, I remember that but I never hear about anyone actually using it so I wondered if maybe they’d even pulled it, but nope.

What is Doug’s new training video about?

Jessica Connors: Speaking of you, Doug, Greg has not seen your new video.

Doug Lane: It just went out the door. So don’t feel bad.

Brent Ozar: Yes, if you go to brentozar.com and click training up at the top, why don’t you talk about what your new class is about?

Doug Lane: This one is on statistics, in particular how SQL Server makes guesses about the number of rows that are going to be involved in an operation and an execution plan. I talk about single column stats, multi column stats, multiple single column stats, ascending key problems, filtered stats, all kinds of stuff like that that if you’re wondering how SQL Server comes up with some of the oddball guesses that it comes up with, there’s answers there. There’s also stuff on—cardinality estimator is in that one too, right?

Brent Ozar: Yep.

Doug Lane: So differences between the 2012 and 2014 cardinality estimator are included too. So you get a better idea of why some guesses are different between the old and the new.

Brent Ozar: It’s super dense. Like everybody is going to learn something out of that. It’s a 90-minute series of classes and I guarantee everyone is going to learn something out of that. Everybody on the team did. It was really good.

Jessica Connors: Is it as entertaining as Level Up?

Brent Ozar: Not as many costumes.

Doug Lane: Yeah, it’s entertaining. There’s more stuff following that theme that is going to be coming. It has sort of a gameshow theme but you don’t see as much of it in this particular video.

Jessica Connors: Gotcha.

How do I restore a single table from backup?

Jessica Connors: Question from J.H. He says, “For a table level restore on a huge database, which there’s no temporary server to restore the database, would filegroup restore be the only way and do you think SQL Server will ever support table level restores?”

Tara Kizer: I’m going to show you my age or how long I’ve been using SQL Server but SQL Server 6.5 used to have table level restores. I’m not too sure why they pulled it out or if it was just tricky to do, but yeah, 6.5 used to have it.

Brent Ozar: I didn’t know that. Wonder why they yanked it.

Doug Lane: Is it LiteSpeed that does table restores?

Tara Kizer: I think all of them do, yeah.

Doug Lane: Do they? Okay.

Brent Ozar: And they’re so cheap. So Dell LiteSpeed, Redgate SQL Backup, Idera SQL Safe, they’re all like between $500 and $1,500. Leave it to a consultant to call that “so cheap” but compared to SQL Server licensing, it’s really, really cheap.

Tara Kizer: Per server.

Brent Ozar: Per server, yeah. So then that way, it just restores the individual table. So much faster, especially faster than shuffling stuff around. Second part, do we think that SQL Server is ever going to offer it? I had this thought that SQL Server was kind of dying on the vine there for a couple of years and then all of a sudden it’s like Microsoft woke up and went, “Oh my god, there’s a lot of money here. We should keep developing this.” They’ve been adding all kinds of stuff, like the one Erik just wrote a blogpost about, compressing encrypted data. I was like, “What? They’re actually doing that? That’s amazing.” So it’s definitely possible.

What’s the new name of sp_BlitzFirst?

Jessica Connors: Speaking of blogposts, I saw that we renamed sp_BlitzFirst.

Brent Ozar: Yes.

Richie Rump: Get that Brent out of here, get it out.

Jessica Connors: I was just looking through the blogpost and I saw that we asked the community what the new name should be.

Brent Ozar: Yeah. Wow, we got a lot of… what were you guys’ favorite entries? We renamed sp_BlitzFirst to make it open source because we wanted it to have a less Brent-y name, just more community-ish name. So you guys who looked through the answers, what were your favorite suggestions over there?

Tara Kizer: Well, BlitzNow of course, since that was mine. The BlitzyMcBlitzyFace or whatever it was.

Brent Ozar: Yeah, that was good.

Angie Rudduck: I liked BlitzPerf.

Doug Lane: Yeah, I was a big fan of BlitzNow too just because you’re answering the question, “What’s going on with my server right now?”

Brent Ozar: Yeah. There were so many good answers. BlitzyMcBlitzFace was one of my favorites as well.

Jessica Connors: Nothing offensive came out of that though, huh?

Brent Ozar: A lot of offensive things.

Angie Rudduck: Wasn’t there sp_BlitzWTF?

Brent Ozar: Yes, yes, so many offensive things. I’m proud of our readers. They did not let us down.

When should I install multiple instances of SQL Server?

Jessica Connors: Question from Samuel. He says, “In what scenarios would you have multiple SQL instances on one server? In the era where individual VMs can be split up easily, I’m struggling to argue for a case for them.”

Doug Lane: Good.

Angie Rudduck: Good, yeah. Don’t argue the case, argue against them.

Doug Lane: Instance stacking can lead to problems because you want to know how much of your resources are being consumed by SQL Server and if you’ve got multiple servers on there, when you query one instance, it only knows about itself. It doesn’t know about any other instance. So if you’re asking, “How much memory are you using up? How busy are you keeping the CPUs?” You’re only getting part of the picture. So that’s the big problem that I see with instance stacking is that it makes it really difficult to troubleshoot. Okay, if I’m starved for resources, where is that coming from? You have to examine all the instances on that server. It’s just so much easier to only have the one.

Tara Kizer: I don’t mind it in a non-production environment where maybe you need to support multiple versions on the host. But production, don’t instance stack.

Angie Rudduck: Someone is claiming it’s cost effective, but it’s not really cost effective if you already have the VMs, right? I mean, I guess you might… you still have to license both instances if you put them on the same box, right?

Brent Ozar: No, so this is kind of tricky. I want to say you have to have 49 instances if you do instance stacking, I can’t remember if it’s 49 or 50. So you could do Standard and then instance stack like crazy. But come on now.

Angie Rudduck: That’s really bad but I was trying to figure out what cost effectiveness they thought they meant. So maybe that’s it. They want 49 instances stacked.

Brent Ozar: We won’t use your name, the person who suggested that, but we will say this: There’s never been a better time to look for a job. It’s a great time in the DBA market. If your company is under that much pressure, it’s probably time to go check. Because they’re probably also not sending to you like training classes or to conferences.

Angie Rudduck: Which is why you’re here for the free Office Hours.

Brent Ozar: Yes, yes. That’s probably true.

How do I create a table with all countries in the world?

Jessica Connors: This is an interesting one from Wes. He says, “I need to create a table that contains every country in the world. It needs to have all three letter abbreviations of the countries as well as the name of the country. What is the best way to go about getting the names and abbreviations without having to manually type them all out?”

Tara Kizer: I would actually just post the question on stack exchange and I’m sure someone already has a table like this and they could get you the data.

Doug Lane: There’s also—I can’t remember the name of it—it’s that data service you can just go through Excel.

Brent Ozar: Melissa Data?

Doug Lane: Is it Melissa? Well, it used to have a different name.

Richie Rump: That’s one of them but they’re kind of pricey.

Doug Lane: Yeah, there’s something where—marketplace—you can go through the marketplace and you can actually purchase a dataset of something like that. I don’t think they’re that expensive, especially for what you’re talking about. That’s not going to be, you know, millions of rows. It’s going to be like 200 or something. There might actually be something in the—it used to be like Azure DB Marketplace or something crazy like that. It was an Excel plugin though that was built in. You could just click on it and go explore.

Richie Rump: Yeah, I’d also check out the USPS, the United States Postal Service. They have some datasets that are out there. Some of them for purchase, so maybe they’ve got something out there for you.

Brent Ozar: I’m so bad, so here’s what I’m going to do. I’m going to go google for a list of countries somewhere, like either Wikipedia is going to have one or USPS, something like that. They’re going to give it to you in either a comma delimited format or an Excel file and then I’m just going to put in additional columns that say “insert into:” so that I can then copy all of that, paste it into SSMS and then I’m done. Or, I would go to open source, there’s a ton of projects out on GitHub that have dropdown lists for countries and they’ll have inserts into a table that does the same thing. WooCommerce is one of the ones that I use a lot. Most insert statements are going to be not database specific, so you can cannibalize from there.

Doug Lane: Looks like people are answering that in the questions with links.

Brent Ozar: Nice.

Doug Lane: One of them is Wikipedia, that’s why I laughed. Showed up right as you were talking about it.

Angie Rudduck: Can any of the attendees see the links?

Brent Ozar: Nope.

Angie Rudduck: Yeah, they can’t.

Brent Ozar: We’re keeping them secret.

Richie Rump: We will keep the links to ourselves, thank you.

Brent Ozar: I sent the Wikipedia one out to everyone, so now you can see it.

Doug Lane: Okay, there you go.

Where are all these transactions coming from?

Jessica Connors: Question from Graham Logan. He says, “Transaction count in master and user database is consistently over 200 transactions per second. Database is only 4 megabytes but log is 180 megabytes. No long run queries in either databases, open transactions, blocking issues, or tempdb growth. Awaiting response from vendor regarding crazy transaction count. What could be causing this high transaction count? Crappy queries executed by the vendor application?

Tara Kizer: What are you trying to solve here? Transaction count isn’t something that I normally monitor anyway. It can be wildly much higher than say batch requests per second. Just because a database is small doesn’t mean that it’s not being used. If you’re looking at the master database, I think that that’s going to show all systems stuff. So you’re going to want to look at this at just the user database and if you don’t think that there’s much activity, just run a trace or an extended event to see what kind of activity there is. But I wouldn’t recommend using transaction count for anything really. I don’t know why it’s useful.

Brent Ozar: I love a 10 second trace and you’ll know the answer right away.

Tara Kizer: Yeah.

Brent Ozar: It could be IntelliSense, it could be your monitoring software. And 200 transactions a second isn’t a big number. In our training classes, we talk through like you can do up to a 1,000 batch requests a second really easy on commodity hardware. I wouldn’t really freak out until I’m in the thousands of batch requests a second and even then, I don’t freak out. 200 isn’t that big of a deal. You asked an interesting question, why is my data file 4 megs and my log file 180 megs? I’d wonder if you’re running log backups. Then I’d wonder how often you’re running them too.
Jessica Connors: All right, here’s an easy one, I think. It’s from Samuel. “Is it best practice to enable lock pages in memory in SQL 2012 or above?”

Tara Kizer: Yes.

Brent Ozar: Then why?

Tara Kizer: I don’t have the why. I don’t remember it. It’s part of the document that we had at various jobs that say enable this when you’re setting up a server. You just do it on every single server.

Brent Ozar: It’s one of the great debates in SQL Server communities. Some people say yes, some people say no. If that’s your biggest problem, you’re in really good shape. Generally, I’m like, I don’t want to change something unless I’m solving a problem but I don’t have a problem with people setting that either.

Tara Kizer: But what about as a standard practice on a new server though?

Brent Ozar: So I don’t because—and Kehayias and I had this big debate about it. He wrote this glorious—Jonathan Kehayias of SQLSkills—writes this big, glorious long post explaining all the reasons why you should do it. He even references me in there. He’s like, “Brent says this and then I say this, and here’s why.” Glorious, long post, all kinds of details. The first comment somebody leaves is, “What happens if this happens?” this particular scenario. Kehayias responds back and he says, “Well, a few things can happen one of which is Windows may crash due to an out of memory error.” So I’m like just generally speaking, I don’t like enabling something by default that causes blue screens of death. I love Kehayias, good friend of mine, this is just one of those personal things where when in doubt, I’m going to error on the side away from blue screens of death.

Tara Kizer: Was that an edge case? That sounds like an edge case, I mean like really, really rare.

Brent Ozar: Well, it’s when, for example, people don’t set max memory on SQL Server. It goes and takes a huge majority and then some application starts to use some and Windows can’t hard trim fast enough, Windows will try to hard trim SQL Server, not be able to, and then you’ll get the crash due to out of memory.

Tara Kizer: Got you. So when I refer to documentation, that document would have said, “Set max server memory.” So you’ve got this whole list of things that you have to do when you’re setting up a new server.

Brent Ozar: Yeah. And I don’t—if people want to set it, I’m cool with it. I’m just saying this why I usually encourage people to do it because—everyone, just close your ears for a second—you’ve seen what kind of servers our clients have. Good god, they’re cesspool sewers. All right, you can listen again now.

Angie Rudduck: I check for that in triage but I’ve never recommended it. I would rather recommend instant file initialization before lock pages in memory. And same thing, at my last place, we had a standard where it was in the GPO where it was just automatically granted to the SQL permissions, that and IFI, but I don’t think it’s as important to be the first thing to look for.

How do I balance out data files of different sizes?

Jessica Connors: Question from Aviola. They say, “I have a database that has multiple data files but one of the data files has grown larger than the other data files. How do I rebalance or redistribute data across other files for this database?”

Brent Ozar: No one jumped up. I thought everyone would jump up.

Angie Rudduck: I would start by checking your file growth settings to make sure that they’re growing the same amount so that you don’t run into this for future. But as for fixing it, I just send everybody to the link that’s like very long and scary. So, defer to the experts.

Brent Ozar: Yeah, assuming that that one is where you’re going to future grow to, grow the rest of the files to match it. Then you can rebuild indexes and it will balance the load across all of them. It won’t be perfect, it will just kind of round robin allocate. Proportional fill is the word I’m looking for. It’s going to proportional fill according to who has the most empty space. Once those other files have empty space, they’ll get used and hammered.

Tara Kizer: I would wonder about how these data files, what’s inside the data files. Is it a filegroup that has certain objects in it and that filegroup doesn’t have the multiple data files? I would take a look at your filegroups as well.

Brent Ozar: I like that.

What do you look for when tuning a query?

Jessica Connors: Another question from J.H. “What are the first things you look for when query tuning an optimizer and does the largest cost percentage usually a place to focus on or is it not necessarily so?”

Doug Lane: The bigger question is, what hurts? If you’re doing the query tuning, start with the ones that are causing people agony as opposed to the most expensive ones. Because a lot of times if you look, like using sp_BlitzCache for example, you’ll see really high cost queries and they will have executed once and it was maybe overnight and nobody saw it. So start with what hurts the most, what people are complaining about. Then from there, you can look at queries that have a higher cost and maybe you just want to tune them up to make a process faster that’s running into other processes or something like that.

Brent Ozar: When you find a query that does suck, the other thing is you might be looking at which percentage or which items in the operators in the plan cost the highest percentage. You’ve got to know that those are the estimates, not the actuals. So when you see a really high cost percentage, that doesn’t mean that’s the thing that’s actually costing the most in the plan. SQL Server’s estimates could be wrong. We actually talk about that in more detail in our performance tuning classes, which is funny, because I’m giving one as soon as I step out of here. We’re talking about this exact instance. If you go to brentozar.com/go/tunequeries, you’ll be able to see a video of me in Copenhagen talking through this exact scenario.

Why does one database keep coming up in recovery?

Jessica Connors: Andrew just migrated SQL 2008 R2, their QA server, from a Windows 2008 R2 server, to a Windows 2012 R2 server. He just did a bunch of migrating. Now whenever the SQL engine service is restarted, there is one database that keeps coming up in recovery. To resolve it, “I put the database in single user mode, then multi user mode, then it comes up fairly quickly. Any ideas what could cause this issue?”

Tara Kizer: I don’t know how switching it from single user mode to multi user mode would have fixed it but it sounds like you have a VLF issue. So when you start SQL Server, databases go through crash recovery, you know, rolling forward, rolling back transactions. If you have a lot of VLFs, that could take a long time. I had a production database take 45 minutes to complete crash recovery because of the number of VLFs. This was many, many years ago and I didn’t know about VLFs back then. This is how I learned about VLFs because this was a mission critical, it had huge, huge SLAs and it was bad. So I would run DBCC LOG_INFO () inside that database and whatever the row count returns, that’s how many VLFs you have on there. If it’s really high, you know, thousands, I’m pretty sure it was a VLF issue, but I don’t know that you could resolve it by doing single user mode to multi user. That doesn’t even make sense.

Angie Walker: Have you checked it for corruption?

Brent Ozar: Oh god, that’s horrible. And run sp_Blitz. sp_Blitz will tell you if a database has too many VLFs and it will also give you a more info link as well. I like that answer though.

Should I be worried about 59ms latency in tempdb?

Jessica Connors: Let’s take one more from Dan. Dan with a temp log question. “We’re experiencing high latency reads above 5-9 milliseconds on tempdb templog.ldf. Is this something to be concerned about?”

Tara Kizer: 5-9 milliseconds isn’t that far off from what your optimal value should be.

Brent Ozar: And it’s reads, it’s not even writes. If you’re reading from tempdb, I’d be like, “Hey, what are you doing reading from tempdb?” So start tuning the queries that are putting stuff into tempdb. Start at wait stats. Run sp_BlitzFirst—see I almost said sp_BlitzNow—run sp_BlitzFirst and it will tell you what the emergency performance problems are on the SQL Server right now. If tempdb’s latency is the biggest deal, that will tell you, but otherwise it will tell you which wait stats are big and then go focus your tuning there.

Jessica Connors: I guess we’ll take one more, how about that?

Brent Ozar: All right, one more, then I’ve got to go get food before my class starts.

Jessica Connors: Oh yeah, okay.

Should NTFS allocation unit size be 64KB?

Jessica Connors: James asks, “Should the allocation unit size be formatted to 64 kilobytes for data, logs, and tempdb drives?

Doug Lane: Are you on a SAN, yes or no? If you’re on a SAN, they might have their own recommendations for that. A lot of SAN firmware is smart enough to say just stick with the default 4096 and we’ll handle it from there. If you’re on physical storage, the recommendation is—or I should say local, physical storage—then the recommendation is yeah, 64k. That hasn’t changed guys, has it?

Richie Rump: Nope.

Brent Ozar: Nope, and it’s still in our setup guide. We find there’s less people who are noticing a difference as you switch to 64k. There’s some controversy about, “Hey, does it really matter that much?” So we were still waiting to see. That’s one of the things I want test at DBA Days actually. Dell DBA Days is coming up.

Tara Kizer: We had it as the standard as part of our GPO as well the last few jobs. I would always on a server that I did not setup, I would check to see what it was because some people would forget to do this step. Did it make a difference? I don’t know. Just make sure that you’re not setting it like on the C drive, a place where you have smaller files. You want this only on your database files. So you want separate mount points or separate drives for these files in order to set this.

Jessica Connors: All right, Brent. Go get some food.

Brent Ozar: Whoo hoo. Thanks, everybody. See you guys next week. Adios.

Last Day Shenanigans

Last Updated July 4, 2016

To follow up on my NY Times Best Selling blog post

On First Day Deal Breakers, here’s something you can do on your last day, as a friendly reminder that everyone is losing a valued team member.

Change the color of everyone’s error messages.

“Change the font too.” –Brent

Probably the meanest part

Is that you have to restart SSMS to change this. So you’ll be long gone before it starts happening. Well, maybe! SSMS is quite accident prone.

On the plus side

This blog post has a really high Flesch Reading Ease score. Over 91.

Thanks for reading!

Availability Group Direct Seeding: Mind Your Backup Jobs

Last Updated February 9, 2017

Always On Availability Groups, Backup and Recovery, SQL Server

I’ll get right to the point

While you’re Direct Seeding, you have to be careful with any other full or differential backup jobs running on the server. This is an artifact of the Direct Seeding process, but it’s one you should be aware of up front.

In the screencap below, courtesy of sp_whoisactive there’s a differential backup waiting on LCK_M_U, being blocked by session 89, running NULL. That NULL is the backup/restore process that Direct Seeding is going through. I can tell because it’s waiting on ASYNC_IO_COMPLETION, which is commonly associated with backups writing to disk.

Sup.

Behind the scenes

One of the steps that you can see Direct Seeding go through if you watch it via Extended Events is LIMIT_CONCURRENT_BACKUPS. This isn’t just for Direct Seeding — meaning you can only get Direct Seeding up and running one database at a time — this apparently goes for other backups of the database you’re currently synchronizing.

Reading is for DWEEBS

Who has to be careful?

Basically everyone. Well, anyone whose backup jobs automatically detect new databases. If you’re using Maintenance Plans, you might have finally won a round, here. Whether it’s Ola, Minion, or Dell LiteSpeed, chances are you don’t have one backup job per database, because that would be crazy for anything other than log backups. Your backup jobs likely gather a list of databases, and cycle through them doing ~the necessary~

If your full or differential jobs hit a database while Direct Seeding is doing the initial sync, they’ll be blocked until it finishes. This can really mess with RPO and RTO for your other databases, especially if it takes a while to sync, or you’re syncing a bunch of databases with Direct Seeding, and the backup job keeps getting stuck behind each database synchronization.

Thanks for reading!

The Top 10 Feature Requests for SQL Server on Connect.Microsoft.com

Last Updated July 2, 2016

When a man loves a woman very much, he goes to Connect.Microsoft.com and upvotes her feature requests. I was curious to see what requests were resonating the most with folks, so I went through the list of feature requests sorted by votes:

1. Please fix the “String or binary data would be truncated” message to give the column name – by DWalker, 1136 upvotes. This one is a classic, and good news! On June 17th, Microsoft’s David Shiflet left a comment that developers are motivated to make improvements, and they’re discussing options for the new error message.

2. Add native support for JSON, as in FOR JSON or FROM OPENJSON – by Bret_M_Lowery, 1111 upvotes. This one’s actually already added in SQL Server 2016, but the Connect item isn’t closed yet.

3. Add Visual Studio 2013 support to SSDT-BI – by David McClelland, 731 upvotes. I don’t know enough about SSDT-BI to know where this is at.

4. Add full justification in Reporting Services – by inDigeniCa, 653 upvotes. Lots of angry comments on this one, and doesn’t appear to be making headway.

5. New virtual table “errors” like the deleted and inserted tables – by danholmes, 593 upvotes. Given that the MERGE statement is pretty rough, a virtual “errors” table that we could use in triggers would be really handy.

6. OVER clause enhancement request – DISTINCT clause for aggregate functions – by Itzik Ben-Gan, 514 upvotes. Started back in 2007, there haven’t been a lot of comments on this one, just folks upvoting it.

7. Scalar user-defined function performance is bad – by Andrew Novick, 510 upvotes. I’ve seen so many companies burned badly by this when they encapsulated code in reusable functions without knowing it causes queries to go single-threaded.

8. CREATE OR REPLACE syntax – by BAnVA, 463 upvotes. Unfortunately, the details page link errors out for me on Connect at the moment, but I’m guessing the comments point to SQL 2016’s new drop-if-exists syntax. I would argue that that’s no replacement, but I gotta hand it to Microsoft that they did take a relative action on it.

9. Expand synonym to other entities (database, linked server) – by Aaron Bertrand, 409 upvotes. Synonyms are slick tools that let you move a table to another database, leave a synonym behind, and let your app keep right on trucking without noticing.

10. Regex functionality in pattern matching – by Simon Sabin, 402 upvotes. Regular expressions are powerful ways of doing text matching. I’ve seen folks roll their own with CLR code.

If the highly-voted Connect items are what shape the next version of SQL Server, it’s time to exercise your voting finger. Check out the rankings and make your voice heard. Microsoft is listening – and if you want proof, check out this month’s release of SSMS. Those developers are on fire fixing Connect requests.

TDE and Backup Compression: Together At Last

Last Updated September 21, 2017

Note: THERE ARE BUGS IN THIS FEATURE. Make sure you read all the way through the post to catch the updates from Microsoft as more bugs were found.

TDE is one of those things!

You either need it, and quickly learn how many things it plays the devil with, or you don’t need it, and there but for the grace of God go you. Off you go, with your compressed backups, your instant file initialization, your simple restore processes. Sod off, junior.

But Microsoft maybe listened or figured out something by accident. I don’t know which one yet, but they seem excited about it! And I am too! If you read this blog post that’s probably also being monitored closely by Chris Hansen, you’ll see why.

Backup compression now works with TDE

Cool! Great! Everyone encrypt your data and compress your backups. It’s fun. I promise.

Not satisfied with a few meek and meager data points, I set out to see if increasing Max Transfer Size also increased the degree of compression. Why? This paragraph.

It is important to know that while backing up a TDE-enable database, the compression will kick in ONLY if MAXTRANSFERSIZE is specified in the BACKUP command. Moreover, the value of MAXTRANSFERSIZE must be greater than 65536 (64 KB). The minimum value of the MAXTRANSFERSIZE parameter is 65536, and if you specify MAXTRANSFERSIZE = 65536 in the BACKUP command, then compression will not kick in. It must be “greater than” 65536. In fact, 65537 will do just good. It is recommended that you determine your optimum MAXTRANSFERSIZE through testing, based on your workload and storage subsystem. The default value of MAXTRANSFERSIZE for most devices is 1 MB, however, if you rely on the default, and skip specifying MAXTRANSFERSIZE explicitly in your BACKUP command, compression will be skipped.

It left things open ended for me. Unfortunately for me, it doesn’t help. Fortunately for you, you don’t have to wonder about it.

Check out the exxxtra large screen cap below, and we’ll talk about a few points.

First, the database without a Max Transfer Size at the bottom was a full backup I took with compression, before applying TDE. It took a little longer because I actually backed it up to disk. All of the looped backups I took after TDE was enabled, and Max Transfer Size was set, were backed up to NUL. This was going to take long enough to process without backing up to Hyper-V VM disks and blah blah blah.

The second backup up, just like the blog man said, no compression happens when you specify 65536 as the Max Transfer Size.

You can see pretty well that the difference between compressed backup sizes with and without TDE is negligible.

The most interesting part to me was the plateau in how long each backup took after a certain Max Transfer Size. Right around the 1MB mark, it hits the low 380s, and never strays very far from there afterwards. I could have tried other stuff to make this go faster, but my main interest was testing compression levels.

There you have it

Max Transfer Size doesn’t impact compression levels, but it can help duration. If you want to keep playing with switches, you can throw in Buffer Count, and try striping backups across multiple files to ‘parallelize’ output.

Thanks for reading!

Update (2017/06/16): Bugs!

One of our readers (who’s sharper than us!) noticed that the Microsoft blog post to introduce the new compatibility between TDE and backup compression had an update to it.

Apparently, some combinations of backup options will cause your backups to become corrupted.

Update April 6^th, 2017

We have recently discovered some issues related to the use of TDE and backup compression in SQL Server 2016. While we fix them, here are some tips to help you avoid running into those known issues:

Currently it is not advisable to use striped backups with TDE and backup compression

If your database has virtual log files (VLFs) larger than 4GB then do not use backup compression with TDE for your log backups. If you don’t know what a VLF is, start here.

Avoid using WITH INIT for now when working with TDE and backup compression. Instead, for now you can use WITH FORMAT.

SQL engineering is working on fixes for these issues in SQL Server 2016. We will update this blog post once again once we have further information to share.

So uh, be careful of all that if you’re on 2016 CU5, or 2016 SP1 CU2.

There’s a fix for this in 2016 CU5, and 2016 SP1 CU3.

Happy patching!

Update (2017/09/21): More Bugs & Fixes

There are some scenarios where even the above list of recommendations doesn’t work. The SQL Server Tiger Team explains, and points out that you now need to be on at least 2016 CU7 or SP1 CU4.

Breaking News: 2016 Query Store cleanup doesn’t work on Standard or Express Editions

Last Updated September 21, 2017

Breaking News, First Responder Kit, Query Store (QDS), SQL Server

If you’re using SQL Server 2016’s awesome new feature, Query Store, there’s a new bug with automatic cleanup. Books Online explains:

Automatic data cleanup fails on editions other than Enterprise and Developer. Consequently, space used by the Query Store will grow over time until configured limit is reached, if data is not purged manually. If not mitigated, this issue will also fill up disk space allocated for the error logs, as every attempt to execute cleanup will produce a dump file.

To work around it, you can manually clean up specific plans with sp_query_store_remove_plan and sp_query_store_remove_query, or just clear the Query Store out entirely with:

ALTER DATABASE (MyDB) SET QUERY_STORE CLEAR;

1	ALTER DATABASE (MyDB) SET QUERY_STORE CLEAR;

We’ve already added an sp_Blitz check for this in the dev branch of the First Responder Kit (zip download), but just be careful using dev branch code since it’s not quite as well-tested as the master branch.

Screenshot Interview Question: “What would you do if you saw this?”

Last Updated February 14, 2017

Brent Ozar Unlimited Team

Interviewing

In this week’s episode of our screenshot interview series, you’re applying for a database professional position who will be on call. You’re not necessarily the DBA, but you’re a member of the team on rotation. While you’re on call, you get an email from the help desk asking if there’s a problem on the production data warehouse server, and they attach this screenshot:

Think fast. This SQL Server certainly is doing that.

What do you tell the help desk?
What actions do you take next?

You can leave your answers in the comments, and I’ll circle back in a few days to talk about my thoughts.

Update 2016/07/10 – Great answers, everybody! So many good answers this week. I shouldn’t have been surprised – you folks are exactly the kinds of people who do this for a living, and you’re used to getting woken up on call, I bet.

For the first question, what I tell the help desk, I’d reply with an email that:

Ask what drove the call (were they just responding to an automated alert, or had end users called in reporting speed problems?)
Ask how long it’s looked like this (was it only a 60-second spike, or a 60-minute spike)
Ask if they have a baseline of what’s normal for this server at this day/time (maybe it’s normally 80%, so we’re only looking at an incrementally higher load)
Ask if there were any change control activities scheduled for this box or related boxes tonight (like maybe the data warehouse team is reloading 5 years worth of history)
Ask if there are any other boxes that are experiencing issues (maybe the SSIS box has high CPU too, indicating that there might be a big load going on)

After all, they only emailed me, not call me. Might just be an FYI.

And what do I do? I’d go back to bed. I’m on call, not on email. If you need me, call me.

[Video] Office Hours 2016/06/29 (With Transcriptions)

Last Updated July 2, 2016