All of these are doable with scripts, tools, and/or elbow grease, mind you – they’re just not as easy as they sound at first glance until you know about the existence of those scripts, tools, and/or free people waiting around with spare elbow grease.
- “Go through the log and find out who ran this query.”
- “Apps sometimes time out when connecting to database server. Why?”
- “Build a new server with the same configuration as the old server.”
- “I just ran an UPDATE without a WHERE clause. Undo just that one query.”
- “Show me everyone who can read from this table, including groups and app users.”
- “Run a load test on these two servers and compare them.”
- “The reports server needs a synced copy of the accounting server’s customer table.”
- “What were the slowest running queries yesterday?”
- “Why is this query slow in production, but fast in development?”
- “Apply the latest updates for the cluster.”
As you read through that list and say to yourself, “Hey, I know how to do some of those really easily,” stop and congratulate yourself. You’ve learned things over the years, and you’re better at databases than when you started.
32 Comments. Leave new
Nice list, Brent. I’m pretty good with some of these, and horrible at others. This actually looks like a pretty good list of interview questions and tasks to find out how a candidate might approach some of these issues, giving insight into the depth and breadth of their DBA experience.
I’d say number 4 is the worst of these requests, at least in cases where someone has read the data that got updated and then written more data based on what they read.
Another one that comes to mind is performing a timely and successful database recovery when you’ve never actually tested your backup strategy to verify that it really works by spending some weekend or holiday (i.e. down time) to perform one when it does not count.
Another one I’ve seen a few times is being asked for auditability of what data was queried (not just the query, the query results).
Great list – so long as those are the topics of your next 10 blogs!
Hear, hear!
Yes, please!
I agree, would love to see a review of them!
I was going to ask where the cheat sheet for all of these are!
If there was an easy cheat sheet, then it wouldn’t be called, “10 Database Tasks That Sound Easy, But Aren’t,” hahaha.
Thx. I am fine with some and some make me go “wow I wouldn’t know how to do that” Scary. I feel like this is a setup for a forthcoming “Groundhog Day” post
I can relate to #2. True, it’s not always easy to find out why an app times out.
Item 2: Annoying because it can be failing long before it reaches the SQL Server, but it seems everybody always assumes it’s the SQL Server. It’s like being Trump…guilty.
Item 4: Every once I a while I need to remind a developer that a database restore is not replacement for “undo” in the application.
Item 9: Our prod and test servers were identical VMs. For some reason, A prod core at 100% CPU usage did as much work as test core at 10% CPU usage. Trying to analyze query plans for queries taking too much CPU time was just the wrong thing to do. A new prod VM solved the issue.
Item 11: Hi, I need to know who accessed this data last week.
#2 OMG, I actually had a dev tell me “I changed this query and now it’s taking 5 minutes from the website, but when i just run the query it takes 1 second longer than it did before, what’s wrong with SQL Server?” o.O
Right there with ya. We have that sometimes happen as well. Runs fantastic in SSMS, runs forever from the web page. We end up dumping the cache for the database, and poof! running great again.
in this case, they had introduced a nested loop into the webpage /facepalm
Can anyone help a ‘Accidental DBA’ with maybe a few of the scripts/tools for some of the items on this list? I don’t need to know how to use them step by step, a simple reference would help get me in the right direction.
#1, has not happened thankfully.
#2, yeah, dealing with this one off and on, cannot nail it down either :(.
#3, working on this, love the question with it that is along the lines of “how long will it take?”
#4, I have trained our developers and BAs to know that if they do that, they have one of two options: 1) fix it themselves; 2) if they come to me to fix it for them, it is a restore of the database, and they have to do the walk of shame up and down the aisles explaining why I am doing the recovery.
#5, I have a script for that.
#6, skipping
#7, I have a script for this.
#8, I have a script for this.
#9, normally, a bad query plan or missing index (that someone added to the other servers)
#10, No sweat! Give me that cluster! 😀
#1 – When your business uses SQL Server logins, this is absolutely no fun.
#4 – I know what to “google” to find that script.
#8 – And this is why you should let me install monitoring and/or the Query Store.
#10 – You don’t let me touch that area. Talk to the hardware people.
On #10: Unfortunately for me, the Network guys hated to deal with the SQL cluster. Made them very nervous. So, I stepped in and said I would take care of it. We have not had a problem with cluster updates since. And this was after I gave them step by step instructions on what to do.
#2 gets tricky when it’s really a connection timeout and not a command timeout (the two get mixed up all the time). For the connection timeout, I’ll usually use Powershell to log a continuous ping between the app and database server and check if spikes in latency or timeouts correspond to the timeouts. Other times it’s a problem with load on the app server and some app server metrics are clarifying, or there’s a TCP stack problem on the app server and it’s running out of free ports to use for more outbound connections.
On the other hand if it’s really a command timeout and not a connection timeout (the two get mixed up all the time), Query Store or a trace with the settings in this answer usually get me the info I need: https://dba.stackexchange.com/a/80252/7157
(XE could be good here too, but I didn’t find a great way to use XE to get info about command timeouts.)
I think it would be neat to have a linked blog or solution to each of these:
maybe 1) https://stackoverflow.com/questions/5299669/how-to-see-query-history-in-sql-server-management-studio
As a Junior DBA I’d love to know the best practice answers to these questions!
As an experienced DBA, I’d love to have the best practice answers to these questions.
“Review the database design and fix whatever’s needed.”
“without breaking any apps”
“Can you restore just this one table from our backups?”
“What do you mean it will take some time? I just need this one table, not the whole 2TB database”
Its the only real reason to use one of the alternative backup programs.
Actual argument I’m having with our developers: “This application is crazy slow. It MUST be your (Oracle) database server that’s slow!”
So I ran full auditing, recording every SQL statement for a set block of time in Dev while I tested all the slow parts of the application. Then I wrote some scripts to parse the hundreds of megs of trace files to extract the various times it tracked for each query, purged the system queries to isolate the load down to just the applications, and then showed that NONE of those remaining queries took more than 100 or so milliseconds.
He still hasn’t figured out why it’s slow. But at least I know it’s not the database.
Yah, I had a gig once, brought in to help with database speed, so I set up the SQL Server profiler – and the first query didn’t even *reach* the server for 30 seconds. Once it arrived it executed quickly, no problem.
Number 9.
I know you aren’t doing office hours any more, but did you really just have an itch to say “Slow in the app, fast in ssms” or “Erland Sommarskog”?