HW – if all you do is rack & stack hardware, you can manage thousands of boxes. You see this at Facebook/Google type scales.
OS – when you’re responsible for troubleshooting operating system problems (as opposed to simply re-imaging the box from scratch), you can manage hundreds of boxes or VMs.
SQL – if you have to install, configure, and troubleshoot standalone instances of SQL Server, you can manage 50-100 instances.
HA/DR – when you have to design, build, and troubleshoot high availability features like clustering, Availability Groups, replication, etc, then you can manage 10-50 SQL instances. (Here, it’s important to know that we’re talking about boxes critical enough to have a high availability solution.)
PERF – if you have to tune queries and indexes for performance, you can do a great job on 1-5 instances of SQL Server. Doing a good job of performance tuning means understanding the underlying data model of the databases you’re tuning, plus having a good relationship with the application developers.
These numbers aren’t absolute: tools like training and automation can raise them, and a lack of those tools can make matters worse.
52 Comments. Leave new
Interesting article, Brent. I’m curious– does the “PERF” category imply a certain maximum number of databases per instance? There’s certainly a huge different between five instances with one DB each and one instance with, say, hundreds of DBs, especially for that category.
Zach – typically when I see hundreds of databases on the same SQL Server instance, AND the company is worried about performance tuning, then all of the databases are the same app. For example, SaaS companies with one database per client. In that case, it’s not much additional work to tune hundreds of the same-schema database as opposed to one ginormous database with all clients in it.
In fact there are sometimes advantages to hundreds of same-schema databases.
For example, I can measure now how serious a parameter sniffing problem is (e.g. 3% of our databases picked plan X for a query but account for 90% of logical reads).
When a bad query comes it not only affect a database or instance but all the shared hardware. And tuning it save the Server,instance,database… DBA breakfast
Simple, Clear & No Comment! – that’s it!
Thanks for sharing – this pic here comes from experience and it presents simplicity to understand
Nice analysis and very good comment about automation. Automation makes life easier.
According to this graph/chart….I am the greatest DBA on the planet 😉
Fun read though!
True, but keep in mind that everything is not black and white, it is in Gray scales!
I concur with the figures given, though a DBA usually has to do all of these things concurrently and more. Managers don’t seem to understand that assigning you to deep dive a performance issue means that those days you likely aren’t going to have time to watch, investigate, and remediate the problems that inevitably occur on all of the other servers in the fleet.
I find that performance tuning is *the* biggest time sink. Even if it isn’t always required, by the time you have people banging on the door for it, the problem has to have become quite serious and it’s not always as simple as an incorrect parameterisation, one index, or a bad query; the architecture is broken and the developers are running queries directly in production without their underwear on.
And even with the amazing DMVs and free tools (MDW, sp_WhoIsActive, Profiler, XE, PowerShell), it becomes almost impossible to get the facts from a non-trivial server running multiple workloads. It also becomes frustrating and downright demoralising. And while you’re wasting time banging your head against the wall the rest of the now neglected servers start simmering ready to boil over.
That’s why I’ve started to come down hard on management and demand a budget for just a few licenses for proper performance tuning tools that I can deploy ad-hoc. After using a trial for a little while it’s night and day; the best that I can pull out manually with days/weeks of effort barely scratches the surface of what a professional tool immediately reveals in the opening dashboard, and with drill downs.
At first they resisted. Then when projects came to me I would tell that we don’t offer this manual service anymore and if they want it then they need to pony up and buy a license, which led to the inevitable inter-department turf war of, “But why should we pay for it, your department should pay for it.” To which my response is that we have diversified into multiple levels of service; everything is free until you get to performance tuning, which is a value-add which costs money.
I know it sounds harsh but a DBA has to make tough decisions, and with an eye to the server fleet rather than just making individuals happy, and sometimes a business needs incentive to do the right thing.
You win at DBA. +1,000 XP
Very enjoyable and a great approximation. When doing PeopleSoft on SQL Server that was pretty much more than a fulltime job for performance tuning. Not too mention nobody wanted to actually approve any useful change because the new management pretty much wanted to migrate to ORACLE. Argh!
If they are mostly ‘smaller’ databases probably not much in the way of problems. Large and highly active databases are a lot of special cases and much more work.
Love the chart Brent and completely agree!
Oh, some will quibble that you can do a lot of performance tuning on an instance in just a few days, heck, make it a few weeks, especially if good monitoring is set up with the infrastructure. So you can do *some* tuning on a bunch of systems, as opposed to *great* tuning on just a couple.
And on the other hand, one big, complex one can keep you busy 150% of the time permanently.
“Big league” performance tuning means getting into the entire application architecture, and that can take a lot of time. It’s only justified on major systems. Lots of shops have hundreds of little systems where you “performance tune” it with a quick look and a pat on the head.
But that’s all too complex, and for an overview, I love that chart!
Agree w/your assessment, if not the exact ranges. But worth considering when looking at DBA job, the number of servers could give you a good indication of how deeply you’ll be involved in any one server, and what level of work you’ll be doing.
I think the graph relates to what would be the best way of distributing the work – aka: A wishlist.
Having come from government where there was over 300 instances on 190-something servers watched over by a team of 11 DBAs where 7 of those DBAs were next-to-useless… the workload for those who did want to do the job and do it well was astronomical.
Thankfully I have (again) given up on trying to bring good practice to government and are back in private enterprise 🙂
Matt – as you noted, you can fail with any range of numbers. This blog post is about success. 😉
Agreed.
One should always plan to succeed (just don’t forget the planning part 🙂 )
As one of the useful people who has been saddled with utterly useless and senseless (even dangerous) idiots before, who nobody knows how they ever became DBAs, I know exactly what you mean, and I salute you.
Cody, Dude, that attitude is toxic. There’s always legacy, there’s always history.
There certainly are times when people would be more effective to an organization by staying home. But everyone’s doing the best they can with the information and skills that they have available to them.
If you’d like to disagree with my experience then you are welcome to. However I take umbrage with your attempt to invalidate my experience.
I’ve worked with DBAs who, upon not knowing what a production system does, in an environment where outages can cause death, literally shut down the service, “To see who would complain”. They had to be told why that was unacceptable.
Or when the team is together building a guide and scripts on how to build a new system, would then go off and repeatedly build a new system without following the guide. They would be instructed in why that was wrong. And then they’d do it again. And again. And again.
You can assign them tasks and they simply won’t get done. “The backups on this server aren’t functioning, it caused an outage on the weekend, can you please investigate it before next weekend.” Next weekend comes. “The backups are still broken. You ran the scripts to install the backup solution but then didn’t execute them, and so didn’t notice that the backup directory doesn’t exist? Can you please fix it?” Next weekend. “It failed AGAIN. You created the directory but didn’t run it to confirm it worked, and you didn’t give it permissions.”
These people exist and sometimes in Senior DBA positions with 6 figure salaries. If you haven’t dealt with them then that’s fantastic and I am so happy for you. But they exist and they are horrible, and managers find it hard to let them go.
We have just under 500 servers and we are responsible for installing/maintaining SQL and guide or development teams for tuning. We do this with a staff of 4 DBAs (well, 3 and a manager). Seems we may be a tad understaffed. We do what we can do.
Bill that sounds about max capacity. What do you use for your overall health monitoring; paid tools or rolling your own scripts?
@Cody
I hear you man! Many times a new DBA has been brought in or made lead and they proceed to f things up and then leave and you are the one cleaning up the messes of these ‘senior DBAs’ that management favored over you. It happens. One time on an ERP system using Tuxedo for batching they killed the process because things were running slow. Half a days worth of transactions were rolled back!
As you said, some of that is expected and we all make mistakes and sometimes run into our ‘own messes’ we have to clean up, but if its time and again that is another thing. But unfortunately you really don’t have the power to change it and that can be very stressful.
As agreed the provided chart is a great approximation and it is great that Brent has pointed that out which really speaks to ‘what is a DBS?’ which most management really doesn’t understand our jobs. And educating them is not easy because frankly most of the time they just do not have the time and it actually isn’t that useful of information to them. Just the way it is. So some of the tips here on communication are also useful as well.
Thanks for your support Ron. It means a lot to me. I felt extremely upset being labelled with a toxic attitude for pointing out a sad but relatively rare reality.
But I think Michael misunderstood me and in turn I misunderstood Michael. I think Michael believes I’m slagging off DBAs for prior poor work; “Why did they do this, this is garbage!” I’m not like that; I get that things change, first designs are never the best, and people often have severe time constraints. I’m always reasonable.
What I care about are not accidents or mistakes which we all make, but:
* Wilful neglect that risks human safety.
* Repeating the same mistakes over and over with no discernible improvement.
* Ignoring instructions (not refusing instructions, just ignoring, especially if they have input to and can choose to amend or improve the instructions)
* Not testing anything. If you create a backup job, why would you walk away without running it once to make sure it’s going to work?
Everyone gets numerous chances for improvement, and the help and assistance they need. But some people just don’t want to improve.
I’m apologize Cody. I think I understand you better now and I’m sorry I misunderstood.
You’re correct. I’ve never worked with idiots. I have worked with a large number of people who were out of their depth. And it was this category of people I thought you were slamming.
Yes running a job to test it is a basic thing that should always be done if at all possible which it usually is. Especially maintenance jobs on smaller databases. Once in a while anybody can get distracted and forget. But time and again forgetting is very annoying. And then finding no alerts so ‘hey this database backup has never completed’!
Perhaps this should lead into an ‘annoying things’ article. 🙂
@Cody:
Reading through your posts, I was almost wondering if I had written them.
I take great pride in my skills but will always accept that there is more I can learn. I also take great exception to people who want to question my integrity as a DBA or think they somehow know better and ignore the processes and rules. To be honest, I have people currently saying that I don’t show enough urgency when it comes to a priority-1 situation. If there is something I have learned over many years of being in this trade, it is that running around like a headless chook does not help in resolving a situation.
I apologise to all who may have thought that my original post was negative in nature – and, to a degree, it was.
I swore that I would never go back to government several years ago – but an opportunity to expand my skills in certain areas of technology as well as being a team leader attracted me. The problem was that I was faced with people in their early-to-mid 20’s who were in the government mindset of, “That’s how we’ve always done it. That’s how we’ll always do it.” Sometimes you don’t fix things that aren’t broken (for good reason) but if you refuse to listen to alternatives of how to do things better….
The team consisted of just 1 decent DBA who I still say would be an asset to any organisation who is lucky enough to grab her. She was working with ‘friends’ though and that was holding her back where she wasn’t willing to expand her horizons. Her choice and I respect that.
Of the others, there was a young male who thought he knew it all. He created major problems by ignoring directions on how to perform tasks and would then go to the (female) DBA to fix it. He refused to learn the system catalog and, when asked to create a new secondary data file using TSQL rather than the wizard, he whined when taken to task about it. That is not a professional DBA who was under the pump. That was a useless team member who refused to learn.
Then there was a “senior” DBA in her mid-20’s (who lied to get the position) and then demanded that since she was a senior for another RDBMS engine, then she should also be the same for SQL Server where her skills were equivalent to that of a junior. I offered to teach her but that wasn’t good enough. She would complain if anyone dared to say that she didn’t have the skills for the job. I have no problem with women being more knowledgeable than myself and are happy to learn from anyone – but this girl clearly did not have the skills and very few of them had been in a position where several critical systems were down and needed fixing.
All of this is different to being under the pump… To be in a role where someone senior has left – or more the case – a contractor has left after doing something specific and leaving absolutely no documentation for a production system and it gets lumped onto some sucker to pick it up and run with it. I’ve lost count of the number of times that has happened.
As Ron Sexton says: How about an Annoying Things article?
Mine would start with business – and particularly government departments – who bring in a contractor to do a build of the latest and greatest stuff; they generally don’t know and learn on the job; they document nothing; they hand over NO information; and it’s the poor sods left behind who have to support a system they don’t know.
If anything, contractors who come in should do the day-to-day “monkey work” as a female colleague of mine called it years ago – and the permanent staff do the training to come up to the next level and implement the “fun” stuff.
OK – I guess the flames will start ….. now!
Matt – while it’s fun to vent about others in a public forum, you may want to think about how future employers might read this kind of thing, especially written in a public forum.
No matter where you go, you’re going to have at least one person around you who’s not as qualified as they think, or not as helpful as you’d like, or not in the right position for their capabilities, etc. No job is perfect, not even mine. 😉
I’m not saying you should skip around humming How Great Thou Art while counting your blessings. Of all people, I’m all for a good rant. You just have to know that when you say negative things about your coworkers in a public forum, there’s going to be folks who see that in a negative light. I speak from experience – I’ve had plenty of times where I’ve said very negative things in a public forum, and folks think I’m a jerk. (There are times when I’m comfortable with that and it’s worth the price, like when I’ve taken PASS to task about security issues or election issues, but maybe bashing former coworkers in public might not be worth that price.)
Brent,
Thank you for your advice as you are indeed correct.
The thing is that I wasn’t doing it for fun – also, the people of which I speak are no longer colleagues and I would not change employer if I knew those people were there. They played politics and had a Director for a friend who thought it acceptable to try and ruin my career because I wouldn’t kowtow to their lazy wishes (who, I might add also reprimanded me for refusing to break the law!). They also do no technical research so the chances of them seeing this and knowing it was them is virtually nothing.
I am far from being the world’s greatest DBA – but I try to do the best I can and I always stand up for my team. The old principle stands of accept the losses and share the winnings – and I’ve taken the blame for team members who’ve tried to do the best they could.
Sometimes the lazy and useless ones need to be noticed for the work they put onto others – and if people think I am obnoxious for saying things the way they are, it wouldn’t be the first time. I’m there to do right by the business, the team, and to keep the systems running – not to pander to ineptitude.
LOL! It’s all good.
Just remember that those are still valuable team members. Because many times if for some reason they leave or are ‘encouraged’ to leave/let go, management may take forever to replace them or not replace them at all. And I have found that even if someone is not the best resource, they are still usually much better than no resource. For example if there is only two of you and you have to have coverage, do you want to be able to take vacation?
As for the individual, leading is a very challenging thing at times. Especially when you are not the ‘official’ lead which happens often. But developing those skills can help quite a bit as to how to influence your team mates even though only so far. But always best to keep it on a friendly footing if possible. I am not saying you still will not have difficult situations. Such as a new lead was brought in that was somewhat biased against older white DBA’s that claimed I was a dinosaur and stated he wanted to get me fired. Fortunately the other team mates didn’t really see it that way and he also was slightly chiseled on pay he was promised so this new lead guy then immediately proceeded to find another job, flip off the management and leave. I then had to clean up a number of things cluster wise he messed up. So I do understand. I couldn’t do anything about him positive or negative. But the other DBA’s helped mellow this situation out a bit. Emotional IQ is something we always need to cultivate as well as understanding leadership skills. I prefer collaborative and lead by example.
Another example, they were all saying we shouldn’t back up the model database, it wasn’t necessary. I was saying if we need to recover it is needed and it is only 2mb and so no trouble to back up, and sometimes has been customized and we would not want to lose that. Seems I lost that debate at the time and they were not happy I would not agree and go along with them. There are difficult times where we almost in spite of everything must do a sometimes heroic effort to get along.
That particular point in time resulted in there being only two of us to manage a couple hundred SQL servers (250+) and lot of instances in different domains.
Sometimes you have to simply lower your expectations, take a deep breath, and grin and bear it! 🙂
And again, almost always, some resources is better than no resources.
To contribute more to this thread, nothing was said about how to share the load for all those SQL servers being managed. Primary responsibility for some? By domain? Switch it up once in a while? Free for all? 🙂
Brent,
I’ve thought about this and you are welcome to delete my posts. I have probably (and inadvertently) hijacked your thread and what you were trying to achieve.
You have my apologies for that.
I was just wondering about this myself lately. Thanks!
I’ve only got two, but I’m also the sole app designer, developer, tester and everything else, along with taking care of the servers. They are plenty to keep me busy. I read about people managing entire farms, and always wondered how they did it, and why my two are almost more than I can handle. This makes me feel a little better.
Can I favourite this post or have it XSS’ed into our companies homepage? Please.
You can favorite it, but not copy the content elsewhere. Thanks!
Another dimension to consider is the “Rate of Change”. A relatively static DB requires little attention. That could be cooked into the HADR piece, but it is still “different” enough that it may warrant its own dimension. “Change” isn’t just data changing, but code and use of that data as well. But, I like your graph!
Would you care to elaborate how you created those numbers?
I personally think that “number of servers” isn’t adequate. I think that even with query tuning you can manage even millions. What about the developer that tunes a query in a big product, didn’t she then manage (potentially) millions of installations to run better?
Martin – as you wrote, that person is a developer, not a database administrator.
Your title is “How many servers can a *person* manage”.
In my experience lots of developers do tune their code (and queries are part of the code) if they know that there is a problem.
Still I think the numbers are a bit misleading. You start with racking servers and end with a very specialized function of a DBA specifically. It seems a bit like comparing different numbers
Martin – we both agree that developers do tune their code. That doesn’t mean they’re *managing a server*.
Thanks for stopping by, though!
Wait, a developer tuned a query? Which country are we talking about? And 2015 or the one before?
We might need to call Guinness.
It’s called “collaboration”.
I’ve learned to love that concept when different people in different roles are actually trying to work together on a common goal. 🙂
There are the cases where it ran fine when they coded it (a few thousand rows in tables) then months later there are millions or hundreds of millions of rows. Doesn’t run so good anymore and as contractors they are also long gone and nobody wants to touch the code. Tuning…yeah.
OK, now that’s just funny — a bit nasty towards developers — but still funny.
Spare a thought for the developers who become accidental DBAs because of the idiots in the hierarchy who think that DBAs do nothing and that a monkey could do the same job.
As someone who considers hiimself more of a developer than an admin, I have to agree with you. I believe it’s actually linked to how some frameworks/tools (LINQ, PHP/Laravel, etc.) isolate the developer from the actual sql being ‘generated’ for them. Most only tune if there’s a big problem. Some shops enforce the use of views/procs to access/update data, which can help offload the tuning to a dedicated sql developer, and take this off the hands of others who don’t have the skills for that. I always cringe when I see dynamic sql…
I found a product called Conductor4SQL that really helps manage many SQL servers
“HA/DR – when you have to design, build, and troubleshoot high availability features like clustering, Availability Groups, replication, etc, then you can manage 10-50 SQL instances. ” – I assume this included Test & Production ?
Q: Does it matter how many databases are on an instance? Kronos (time keep application) is one database. Great Planes and SharePoint are multiple databases 8-17, perhaps more. PeopleSoft 9.2 with over 23,000 tables. Indexes, procedures and such not included.
Q: Does size matter? Twenty 30Gb databses v.s. ten 200+Gb v.s. twenty 1.3 +Tb database [A federal gov position] does not seem like an equal load.
Yep, that’s why they’re ranges and not exact numbers.
You mentioned this in your “https://www.brentozar.com/archive/2024/04/video-office-hours-career-data-qa/” today, here is the five year update (can not believe its been that long) HA/DR database was upgraded to 2019 in 2023 due to vendor support (20 databases on two instances). Great Planes is dead and gone here. Server names are now random letters and numbers “to prevent snooping and a Hacker from knowing which server does what [also works for the DBAs involved] )
Next step is “data warehouse” So as a follow on question,
Q: How many Data Warehouses can a DBA manage? [guessing 1-5, which may be wrong.]
Asking a genuine question: what factors make you think a data warehouse server is easier or harder than a typical consolidated server (multiple databases, multiple applications) of a similar size?
What factors. Vendor package comes with some support. Data Warehouse (DW) does not.
Vendor package is a set solution the user agrees to. DW grows and stays in flux – in part because vendor packages change. In part because my users feel they can add or delete stuff since “this is an in house solution without any outside vendors needed”
From very limited experience – we have a few consolidated servers running “vendor applications” Vendor apps run well and there is a user group to help with oddities of this or that app. Set up and tuning is the most interesting part. Growth management and maintenance is fairly simple. Keeping up with the changing “Points of Contact” and the ” hearding cats ” slow crawl to upgrade a server is a constant challenge – but not that bad. A tad on the boring side.
Creating and running a data warehouse has, in my case, been more “trial and error” along with BI gateways. There are few support groups. The SSIS programs to ETL or load into temp tables to do the data scrubbing are in flux. There is much more involvement. Mine could be a “one off case” Other people may have a ‘Data warehouse in a box” solution. My gut says no. Why? Each company has a different assortment of vendor / home grown applications.
Due to documentation and trying to keep a consistent meta data catalog it feels harder than performance monitoring.
What do you think? Am I mis guided or just overlooked the obvious ?
It’s absolutely wonderful that you’re having that experience, but most folks are not that lucky.
As Brent said “but most folks are not that lucky” – that is part of what keeps us DBAs employed.
Was not sure how to respond. You mainly see “crisis” situations. So I do feel lucky or fortunate to have so few problems. My shop is not high volume nor is there a push for sub second response time. Sometimes it is real (OLTP, data acquisition and flight control) – other times it is an artificial pressure created by management (no names)
Thank you for the response. Sadly the next round of upgrades are from security mandate instead of SQL 2022 “optimistic locking” which makes SQL a bit more like ORACLE in my opinion. I’d rather upgrade for the advantage than the mandate to be “as current as possible” What ever moves the ball forward is good, right?