[Video] Office Hours 2017/05/03 (With Transcriptions)

This week, Brent, Tara, and Richie discuss choosing batch sizes, SQL 2017 on Linux, how much memory to leave on the OS for the SQL server, parameter sniffing, allocation unit size of SQL Server, triggers, X-Files, aliens and much 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.

Enjoy the Podcast?

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

Office Hours Webcast – 2017-5-3

 

How should I update 50 million rows with minimal impact?

Brent Ozar: Jam says, “Do you guys have a good blog post that I can use to update 50 million rows in a SQL Server table? Should I use like the top clause?” I guess a bigger – I wonder like how many rows are in it, if you’re trying to update all of them or if it’s a percentage, what else would you ask?

Richie Rump: Indexes? How many indexes are on there and constraints?

Tara Kizer: Yes, do you need to drop them? I’ve had to do these at EMS, I don’t know if we have a blog post on the website or not but I’ve had to do massive data changes at times. You might want to break it up into batches and use top clause and keep going until you’re done. That’s going to run slower but the idea is to run slower at times so you don’t cause so much blocking. But if you have a maintenance window and if it will fit in the transaction log as one big transaction, you could do it as one, but I don’t think I’d recommend it as one just because it’s so many rows and in case it does rollback you don’t want to be you know, having to wait a long time. So make sure you test it somewhere, but yes, even if you’re in a maintenance window and if your system is 24 by 7 and you don’t want to cause too much blocking, you can break it up into say, 10,000-row chunks. That’s usually what my batch size is, 10,000.

Brent Ozar: Michael J. Swart has a great post on picking your batch sizes, if you search for Michael J. Swart and batch sizes, he’s got a great like AB test graph where he shows all kinds of different batch sizes. The idea isn’t to use his sheet to find the right number for you; it’s to use that methodology to find the right number for you. I’m with Tara, I just use like 5000 or 10,000 and I call it a day.

 

Can you use Azure SQL DB for your HA/DR?

Brent Ozar: Trisha says, “Is it even possible to…” Aliens. “Do an HADR type solution using built-in functionality between an on-premises server and something like Azure SQL DB?”

Tara Kizer: Availability groups, right? You can…

Brent Ozar: No.

Tara Kizer: You can’t? Is that only when you do the other way?

Brent Ozar: You have to build a VM. So if you build a VM you can, but they don’t let us do a replica up in Azure SQL DB yet and I know somebody in their grandma’s basement – no offence to Tara, nothing to do with – she’s at her mom’s, but somebody and specifically the basement with no windows…

Tara Kizer: We have no basements in San Diego.

Brent Ozar: That’s true.

Richie Rump: Or Miami.

Brent Ozar: Chicago actually – where is it that they actually have basements? Somebody in mom’s basement is going to say, you can do replication using transaction replication, but it’s not DR. That’s designed for a one-way move and you don’t come back.

Tara Kizer: On my Brent Ozar Unlimited profile, I think I say replication is not an HA or a DR solution. I’m adamant about that. I don’t care if you’ve used it for HADR, it is not.

Brent Ozar: It is so true. Tara had me at “It is not a…”

 

Will SQL Server on Linux catch on?

Brent Ozar: Andrew asked an interesting question, “How do you guys foresee the uptake for SQL 2017 on Linux and do you think that Microsoft will now start to attract the Oracle enterprise database market share?” What do you guys think?

Tara Kizer: I don’t know that we’re going to – that’s going to attract the Oracle market share. I don’t know. Maybe the other database technologies out there but Oracle, I don’t know. I don’t see that happening.

Brent Ozar: Richie what’s your thought? Reading tea leaves. Which character is that that you were flying in front of the webcam there?

Richie Rump: That’s Star Lord in the Milano.

Tara Kizer: Don’t even know what that is.

Richie Rump: Guardians of the Galaxy volume two, coming out, actually today and that’s why Balls IM’d me because he had an extra ticket in Orlando and I’m like, I’m not in Orlando, like I thought you were in Orlando, I’m not going to freaking Orlando, but I considered it. I think the Linux thing is interesting, the question is not to go to Linux from SQL Server, but why would someone on Linux go to SQL Server as opposed to Postgres Oracle? So that’s the question rather than anything else. So I mean, are they going to lower the price so now we’re half as price as Oracle, maybe then that’s a question?

Tara Kizer: We are already. We’re cheaper than Oracle because in order to use any feature in Oracle, you have to pay for it, whereas SQL Server, you do that one payment or whatever, enterprise or standard and then you’re done. Oracle, you have every single feature, you have to add, add, add, add. It’s super expensive in Oracle. Even enterprise edition SQL Server is cheaper than Oracle.

Richie Rump: Yes, so now the question is if it’s not the cost, so is it Linux really the big driver of people going to a different database?

Tara Kizer: It is for one of my companies. They were anti-Microsoft, I mean, every desktop was Windows but servers, they wanted all Linux and we were still a bit SQL Server shop but maybe for companies like that, it might make sense but if they’re anti…

Richie Rump: I have not run to one of those companies yet, so – and I’ve been bounced around a lot like a tennis ball, so the question is why wouldn’t I go to Postgres at that point, right? So if I have a Linux machine, why am I not going to Postgres? What features are in SQL Server that are not in Postgres that I could use or some other database engine? I kind of dig Postgres, I’ve been doing it a little bit, I’m doing a little Postgres dance…

Tara Kizer: Not going to the dark side.

Richie Rump: Oh, it’s dark too and it’s kind of musty but – and it’s different, and it’s definitely different, but if I’m looking at SQL Server on Linux, my question would then be why or what’s – why am I doing that? Because you know the first release isn’t going to be as good as it’s going to be on Windows, so why are we doing it? I don’t know, I just don’t see the end game.

Tara Kizer: [crosstalk] either that hardly any of the features are in the product.

Richie Rump: Yes, exactly. I don’t see the end game, maybe in three years I will but I don’t see a reason why me as a CTO would go to Linux on SQL Server yet. The carrot’s not there yet.

Tara Kizer: Wait a minute; did Richie get to promote it to CTO? Did he get a pay raise with that?

Riche Rump: Actually I had to pay Brent.

Tara Kizer: Maybe I should look at that senior network job on LinkedIn.

Brent Ozar: I’m the same way. The only edge case I’ve ever heard about it is with somebody wants – they have an ISV app and they it’s only certified to use SQL Server and the company refuses to use Windows. That’s the only thing I’ve heard. That’s it.

Brent Ozar: Wesley says, “I know why SQL Server on Linux – because I am a SQL Server lover who is a closeted Linux lover, then I can be like, yes I do that at our local user group meetings.”

Tara Kizer: You’ll be the only one.

Brent Ozar: Exactly. What about all your co-workers? Imagine that you got production SQL Servers on Linux and they barely know Linux and they barely know SQL Server and now they’re herp-a-derping they’re way through both.

Richie Rump: Well, I mean look how quickly adoption is for 2016.

Tara Kizer: Yes, that’s true.

Richie Rump: How quickly is adoption going to be for SQL Server on Linux on a completely different platform?

Brent Ozar: And I always want to show that just because it’s so interesting. I’m going to…

Tara Kizer: I can never remember the website address for that.

Brent Ozar: Yes, SpotlightEssentials.com is Quest’s free version of Spotlight and if you click on Collective IQ up at the top, they have the stats for all of the people who are using Spotlight Essentials, so out there in the wild, they’re seeing zero percent 2016…

Tara Kizer: It’s not zero, around eight.

Brent Ozar: Just rounding, but then 2% of SQL Server 2000, so yes, that’s – and it’s not – to be really clear, all of us like 2016. I think it’s the best release yet, I would put it into production myself, big huge fan. And 2000…

Tara Kizer: The issue is that starting with 2008, SQL Server is a really rock solid product. We can compete with Oracle as far as performance goes and there’s a lot of features in there, so why upgrade if it’s working?

Richie Rump: Because it’s six years old.

Tara Kizer: Well, I agree, but that’s what customers are thinking – people who are using 2008. I don’t know what our percentage is for clients. I think 2008 R2 for me might be, I don’t know, 40%, somewhere around there. Most around 2012, 2014. I’ve had one client that’s been on 2016. I’m like, uh-oh, I don’t know if I know how to use it.

Richie Rump: It’s so interesting because it’s so different than from the open source community where we’re getting new versions on a weekly basis. I spent almost all day yesterday upgrading a project because I upgraded one piece of open source project and then it cascaded down to all these other projects that I had to update, and I had to update the unit test, I had to go check all this other stuff just because I updated one, you know? And it was because of a security issue. I needed to update it and then all of a sudden I needed to go down that path of updating everything else and that’s just the way it is in open source, and so not updating your servers, which has things like that and getting the new features and all that, it doesn’t make sense to a developer’s head, which is why I think that we have a lot of disconnect between the way DBAs think and developers think.

 

Can I do cross-platform Availability Groups?

Brent Ozar: Andrew, I’m going to – I see two things in there that tell me a whole lot about who you are and what you’re doing and I’m not going to mention last names but you’ll see why here in a second. You said for Linux availability groups, he says for one cross-platform availability groups except for the only way that that’s useful is for reading. It’s not useful for automatic failover between those, as far as I know, like the last time I looked at it for the CPT2, you couldn’t do automatic failover back and forth between Windows and Linux.

Tara Kizer: Wait a second, so availability groups now will support another server being Linux just for reading?

Brent Ozar: For reading, that was the last thing I saw on CTP2.

Tara Kizer: Okay, because in 2012 and 2014, probably 2016, you couldn’t even have a different version of Windows. Windows 2012 R2 and Windows 2012, that was not allowed.

Brent Ozar: Yes, starting with Win 2016 and SQL 2016, you can for upgrade purposes.

Tara Kizer: But just reading, okay.

Brent Ozar: Yes, and the other thing he says is try docker containers for your apps. He’s talking about apps that he’s building though, that’s the thing is that when you upgrade one part of it.

Richie Rump: Right, so this is all like MPN pack – I’m going to do this in a database thing because that’s what I’m going to do. So it’s an MPN package, one little piece of software. I’m actually running this app server-less. I don’t worry about the OS, I don’t worry about the runtime, it’s all given to me. This is the actual pieces of software that I downloaded from MPN where one little piece started a cascading effect on everything else underneath it and that’s just the way it works, right? Either you don’t upgrade and you stick to where you’re at or you have to continually change your code and update as the pieces of code that you bring into your application updates. I mean, I got a whole talk on this if you want man, I could go off, but I’m not going to do that.

Brent Ozar: Not today.

Richie Rump: No.

 

Why is my SQL Server rolling transactions forward on startup?

Brent Ozar: Don says, “Good morning, we had a power failure a few weeks ago and I noticed on SQL Server start-up that there were many hundreds of transactions rolled forward on all databases including master and model. Why were there so many transactions rolled forward when it was off hours and no user transactions were involved?”

Tara Kizer: How many had it completed? I mean, start logging your activity to who is active and see what’s going on. If it were my system at one of my jobs, I would immediately suspect a hibernate issue where the application loses track of transactions and is leaving open transactions. So at previous jobs we’ve had it set up automated stop-to-kill transactions because eventually it starts causing tons of blocking, so maybe you’re experiencing – maybe you’re not using hibernate but maybe you’re experiencing transactions being left open and not being committed or rolled back.

I love it.

 

How much memory should I leave free, and why?

Brent Ozar: Guillermo says, “What’s the correct formula” – we have all kinds of ideas, I don’t know if any of them are correct – “and what’s the correct formula to determine how much memory to leave on the operating system for a SQL Server? I thought 4GB to 6GB for Windows would be enough but I recently heard it should be more on a 380GB of RAM server.”

Tara Kizer: We have a blog article on that. I don’t know if you want to post the link but it says 10% or 4GB, whichever is higher. So for you, that’s 38.4GB left to the Windows and the other processes and then after that, there’s an article that Jonathan Kehayias has over at SQL Skills that he says after you’ve done that, monitor your available megabytes and for a system of 384GB, you just want to make sure the Windows and other processes always have at least 1GB of free. So let it run, after you’ve given 10% back to the OS, let it run for a couple weeks and see what available megabytes are, and so you can possibly can get back more to SQL Server. But right now you need 38, according to the best practice.

Brent Ozar: Great example for why I was working with a guy in our senior DBA class and he said nothing could possible use that much memory and I said, wait a minute, didn’t you just say you had a Fusion-IO drive in there for tempdb? And he goes yes, and I said, you know they use RAM for their drives that keep a map in memory of where all the stuff is? He said it can’t be that big, and we popped open the manual and I said well, based on the size of your drive and the model, it needs 58GB of RAM per drive. Per drive.

Tara Kizer: Wow.

Brent Ozar: Yes, not a small amount of memory, so definitely watch how much memory you have available there.

Richie Rump: Oh my gosh.

Tara Kizer: That’s insanity.

Brent Ozar: Intels don’t do that but SanDisk, the Fusion-IO stuff keeps maps in memory of where your data lives.

Tara Kizer: Man, I had no idea. One of my jobs, we – and that system is still in production, it’s using Fusion-IO and that was a 256GB of RAM and I would have configured it to the 10% best practice, oops.

Brent Ozar: And you really got to dig to find this out. Like you got to go digging through best practice manuals and go hey, how much does it need. It’s based on the NTFS allocation unit size and the size of the drive.

 

Why is my stored procedure’s query plan changing?

Brent Ozar: Mark says, “Hi guys. I have a stored procedure that sometimes changes its plan based on an unusual parameter passed into it and then it runs badly for normal parameters. I’ve been using traces to see execution time for that stored procedure and then if I see it increasing I clear the plan and let it recreate with the normal parameter.” What should he do differently?

Tara Kizer: Well, I mean why is it happening? What is causing the good plan to go out of the plan cache? I’ve spent hundreds of hours on this topic in parameter sniffing and I put plan guides in place in 2012 and greater to make sure we get the right plan. I know in older versions one of the solutions was to create a job and that would just run the stored procedure every minute or so, so that if the good plan got out of the plan cache, there was a chance for another good plan to get loaded because they had tested the parameter values. But you can’t do that when the stored procedure is using inserts, updates, and deletes. But look into index ins, plan guides, as well as, you know, option recompile. If you can, depending upon how often and query runs.

Optimize for, that’s a really good one. So if you know what parameter values work best when it gets optimized for a certain set of parameter values then optimize for can be a good choice. I like optimize for the best and I have – instead of putting that directly into the stored procedure code, I have put that into a plan guide just because I didn’t have access to the source control, stored procedure stuff and I can put a plan guide on top of a stored procedure.

Brent Ozar: It works beautifully.

Richie Rump: Do you think Stack Overflow had that problem with John Skeet, and they created their own John Skeet stored procedure for that?

Brent Ozar: Yes, there’s so many good stories around that, especially over at Stack. We did some branching code, if the user ID is this, go run this code, otherwise, run this other code. It has to be in different stored procedures for the store. If you want to deep dive way deeper, go to GroupBy.org. GroupBy.org is our totally free community conference, it’s online, the next one is coming up on June 2th and 9th but Guy Glanister last week had – two weeks ago, had a really good session on how to use parameters like a pro and boost performance. It’s like 90 minutes long, so go to GroupBy.org and click Watch Past Sessions, then go to Parameters down there and really good fast paced session. He goes really quick in there.

 

What’s different about AGs in Azure VMs?

Brent Ozar: Ben says, “My company is about to set me up with an Azure infrastructure services, including” – nothing against – you know, just if they’re going to set you up, like a Porsche or a Ferrari, not like a server – “including always on availability groups on SQL Server 2012. What should I watch out for with always on availability groups and how is Azure different from private Cloud VMs?” If you were going to give somebody a shorter starter guide on what to watch out with for AGs, what would you warn them about?

Tara Kizer: Well, they’re specifically asking about Azure, so I mean, if this were – to me, it doesn’t matter what the – if it’s on-prem or Azure or somewhere else. What I would look for, for availability groups, is making sure I have the cluster configuration properly, make sure I have a witness, make sure that I have the votings set up properly because, in my environments, we’ve had disaster recovery sites, servers participate in availability group at another site, so voting and [inaudible 0:16:54.2] are the two big ones for me as far as making sure you don’t cause an outage due to misconfiguration. But that’s on-prem or Azure or EC2 or GCE, doesn’t matter. I don’t know specifically about Azure, I don’t know if you have something on that brand.

Brent Ozar: Yes, up on the screen, I’ve got – if you go to simple-talk.com, simple-talk.com is Redgate’s community blog thing and they had two – Joshua Feierman, I believe is how you pronounce his name, or Feerman maybe, had two really good posts a couple, few weeks ago about Azure load balancers and Azure networking. It’s way harder to do this – I say way harder, yes, I think it’s way harder to do this in Azure than it is AWS or GCE. You have to create a load balancer and you have to know how their sub-nats work so that the listener isn’t managed by SQL Server, it’s actually managed by Azure. So he has a great two-part write up in there about Azure load balancers.

Richie Rump: Yes, I found that to be true kind of overall in most things in Azure, that AWS is easier to manage than an Azure – it feels like Azure kind of adds in this enterprise layer that for most people, kind of don’t really need.

 

Does NTFS allocation unit size affect performance?

Brent Ozar: Guillermo says, “What’s the performance impact in SQL Server of an allocation unit size of 4K versus 64K?”

Tara Kizer: I think I asked that question when I attended SQL skills training years ago and I forget which one it was, Kimberly or Paul, but they didn’t think that that one was as big of a deal as people had made it. We still made sure that we were using 64K for our mount points that had the database files and stuff that had the smaller files we would use the default, but I still do 64 just as a best practice.

Brent Ozar: It’s way less of a deal than it used to be. When we had dedicated magnetic spinning rust then it made more of a difference, but like these days on modern sans, net app does everything internally in 4K chunks anyway for example, so it matters less. Just check with your storage vendor. For example, with Google compute engine, they actually recommend 32K, and that’s actually for SQL Server. I don’t know why, I mean just yes, oddball, they get the best performance that way. Also too, I’m like if that your biggest problem, you’re in pretty good shape. Usually, you see this when people have like 8GB of RAM.

Tara Kizer: If you’re creating a new server you might as well get it right the first time because after the thing goes live and you realize you didn’t set it up right, it’s not easy to fix. We’re talking about reformatting that driver mount point and you know, bringing the files back.

 

How do I monitor what’s on my SQL Server’s clipboard?

Brent Ozar: I love questions that none of us are going to know how to answer and I’m really curious to see because I don’t know the way to do this. Greg says, “Are third party SQL Server app uses speech recognition and dictation to enter information in the SQL Server and it stores all the text in the clipboard? Do you guys know of any way to monitor and manage the clipboard?”

Tara Kizer: I feel like that this needs to be thrown out. Is this the clipboard on the database server or on the client side? I would hope that this is client side, because, man, I would hope that you have the hardware in place on the database server to – that’s going to be using a lot of memory.

Richie Rump: I mean, the only thing I use with the clipboard is ditto, which is a clipboard manager, which means I can kind of go through all the stuff that I clip, but it doesn’t monitor and it doesn’t do alerting or anything like that. If you need that, no, it sounds like the weirdest thing ever.

Brent Ozar: That’s so cool, I get excited by that kind of thing. Who comes up with this idea?

Richie Rump: I’m not building it for you.

Tara Kizer: Your phone has the dictation and stuff but that’s client side. It’s not doing that on the SQL Server side, if that’s what the back end is, so I don’t think that this is being done on the back end.

Brent Ozar: I bet it’s like, people are saying things and the app puts it into the buffer and then somehow pastes it into like access or something horrific like that, and I get even more mesmerized by that because like with Apple devices, things that I put on my clipboard on my phone or my iPad switch over to my laptop too. So it would be terrifying if someone is using my phone and it goes into my copy paste buffer and goes into the database server.

 

Does the blocked process report use a lot of CPU?

Brent Ozar: Kelly says, “Is it normal for the blocked process report to use a huge amount of CPU? It’s coming up as who is active as the top consumer and our current blocked process threshold is ten.”

Tara Kizer: I don’t ever use it so I don’t know.

Brent Ozar: Me neither.

Tara Kizer: I use who is active and maybe sometimes if I’m just typing the assist processes on sp_who too, but for blocked processes, I do not use the report.

Brent Ozar: If you want an alternative to it, if you search for – second time Michael J. Swart’s come up on our site – blocked process report viewer. This guy knows a whole lot about the blocked process report. I would go check in with him. He has even written his own blocked process report viewer too. Any time have to troubleshoot any kind of blocking for that long, like I’m either going to log sp_whoisactive to a table or else I’m going to go get a monitoring app that just does it for me.

Tara Kizer: Yes, exactly.

 

I have this trigger and a multi-threaded application…

Brent Ozar: And then the last question that we’ll take is from Chase. Chase says – why don’t I read these questions before I say this? Chase says – alright Chase, we’re committed, let’s do this – “We have a trigger on a table on a project that I inherited. The application is multithreaded and at times two different threads can pick up the same method for modification.”

Tara Kizer: Stop there, something’s wrong with the trigger. That should not be happening.

Brent Ozar: Oh god. “Are there hints that can be added to the trigger to prevent collisions on inserts and updates so that a primary key violation is not throws or is there a way to ignore the primary key?” Yes, take off the primary keys.

Tara Kizer: There you go. I had a recent client who is a vendor application, no primary key constraints in the [inaudible 0:22:52.1], really old app too and they wanted to use transactional replications like, well, you’re going to have to add primary key constraints. But something’s wrong with the trigger. That should not happen. There is no reason that’s a – I’ve worked on systems that had 5000 to 10,000 bat requests per second, very high insert volume in tables that had triggers. No problems like this, so something’s wrong with your triggers.

Brent Ozar: Something odd with an isolation level, something unusual is going on.

Maybe using identity instead of scope identity, you know, things like that, things can get weird in triggers.

Brent Ozar: That’s a great point. So elaborate on that, if you search for add at identity and add at scope identity, you’re going to see…

Tara Kizer: Not add at scope identity. Scope_identity parenthesis, parenthesis, yes, I don’t know why they changed, it’s weird.

Brent Ozar: It’ll say why – you know what, just plain old identity can get overwritten by triggers.

Richie Rump:  Yes, my big question is why were you using triggers for processing like this?

Brent Ozar: He inherited it. He had a nasty grandpa, it was all he got left.

Richie Rump: This smells like a queue, it breathes like a queue, it should be put into a queue in process. It’s – I know he said he inherited it, I’m hoping that’s true, but it’s a lot of things.

Tara Kizer: Chase says, “Yes, so it probably is a queue.”

Brent Ozar: It’s probably not even his real name.

Richie Rump: Sorry Chase, sorry.

 

How much does an app DBA know about the data?

Brent Ozar: I guess we’ll take one more because this is really interesting and we’ll all have opinions. M.M. says, “In general, how much does an application DBA know about the data content that flows through the servers? Like what’s in various tables and what data is upstream and what data is downstream.”

Tara Kizer: He/she said application DBA, so that’s kind of important to the question. I feel like an application DBA should know all about that. A production DBA may not though. At my last job, even though I was the primary DBA for a system, I never even really learned it in my year and a half there and I barely knew what the business knowledge was for that system because it was just so complex and it had been around for so long, but an application DBA, which I think is also a developer DBA, they need to know that kind of stuff.

Richie Rump: Yes, I’ve never met a DBA that I personally worked with had a title DBA that knew what was going on in the database. It was myself as the app developer/designer that understood what the data was and how it should be formatted and stored and what not.

Brent Ozar: Let’s drill down into that. How many DBAs have you worked with that even understood how SQL Server worked?

Richie Rump: They’re on my hand. Not two, but definitely one.

Tara Kizer: I’ve been fortunate, worked on really great SQL Server DBA teams here. Very, very knowledgeable.

Brent Ozar: Yes, so my feeling on this comes down to this blog post. How many servers can one person manage, and it’s not just the image that’s up on the screen. If you go through and read, I’ve got an explanation of what I’m about to tell you.

When all you do is rack and stack hardware, if you work for Google, Facebook, Amazon, Azure, whatever, you can walk a cart through a data center, anything you see with a red light, you pull it out and replace it with something with a green light. One person can manage thousands of servers.

The instant that you layer on the operating system, Windows, Linux, whatever, one person can manage the patching, configuration, and provisioning of hundreds of servers.

Then when you start to layer in SQL Server, one person can effectively manage 50 to 100 instances of SQL Server, but you’ll notice that as you scale up this ladder, the people up here tend to know less about the stuff down here. They just don’t go into the data center anymore. Then if you layer in high availability and disaster recovery, always on availability groups, and I’m going to say the word replication even though it’s not high availability [crosstalk]…

Tara Kizer: Next you’re going to say recovery mode.

Brent Ozar: Simple recovery mode. One person can manage like 10 to 50 instances of SQL Server and then by the time you get to performance tuning, you have to know database contents, you have to know what the tables mean, what the queries are doing, when it’s safe to change a query. One person can generally manage between one and five applications. Now, that application may be spread across 50 SQL Servers. I’m working on a gig this week where it’s just one app and we’re going to build hundreds of SQL Servers. One person can performance tune all of them because they’ll know the queries and tables, but you could also be on a situation where you have one server with 50 databases on it that are all different apps and you can’t know all of them. You may know the queries that suck but you’re just not going to know the data that flows in and out at all. Every now and then, I’ll also hear someone say, “I have to manage all of that and I have 150 SQL Servers” and I’m like, yes, but when I run sp_Blitz on your servers, they all suck. There are no databases and no back ups, your corruption errors out the wazoo.

Tara Kizer: How did you come up with these numbers? I feel like this is pretty accurate for the jobs I’ve been at.

Brent Ozar: Right? This I wrote in 2015, yes, so it took me years to figure this out. Like how many servers you guys manage and then came up with this over the years.

Richie Rump: He was actually rolling for a character in Dungeons and Dragons.

Tara Kizer: One of my jobs, we had 700 SQL Servers and it was like you know, five full-time SQL Server DBAs and you know, we did have HADR and then I was a performance tuner as well but performance tuning, it really was one – I’d say about three systems that I was really specialized in but we had 700 servers.

Brent Ozar: And it wasn’t that you can’t do it. I mean, you can jump over to another server and start doing performance tuning but every hour that you’re on that, the other ones are falling apart so. Alright, well thanks everybody for coming and hanging out with us this week at Office Hours. We will see you guys next week! Adios.

Tara Kizer: Bye.