Most of the time, when you need to change SQL Server's behavior - like how it handles memory or parallelism - there are easy-to-use switches to get the job done. In SQL Server Management Studio, just right-click on the server's name, go into Properties, and there are all kinds of things you can mess with. Most of the time, you're going to make things worse, but whatever.
I'm not talking about Agent alerts when jobs fail. That's fine when you can't get the business to spring for a monitoring system.
I'm talking about using SQL Server to send emails to confirm a customer's order, alert that a product is low on stock, or other business processes.
Microsoft releases SQL Server Cumulative Updates about every 60 days. This is a part of your job that you're going to be doing a lot, so let's get good at it! Here's the strategy I like to use.
Pick what patch you're going to apply. Generally speaking, you should be on the most recent Cumulative Update available for your version. (Years ago, folks only applied Service Packs, but starting with SQL Server 2017, Service Packs are gone. Microsoft only ships Cumulative Updates now.)
A DBA makes sure the databases are backed up, well-configured, patched, secure, and fast. I explain my job to acquaintances by saying, "You know how some web sites are really slow, and their checkout times out, or it loses your data? It's my responsibility to make sure those problems don't happen. I'm not the only…
You've got a stored procedure that calls other stored procedures that build dynamic SQL that call other functions, and there's so much calling going on that you would like to phone a friend.
Your query plans look like piles of spaghetti, and when you zoom in, you just see more tiny piles of tiny spaghetti.
Database professionals say, "That's slow because it's a size-of-data operation."
A size-of-data operation is when the database has to write all of the affected data again. What you're asking for might seem small, but for the database, it causes a lot of work.
In my Twitch & YouTube live streams, some questions seem to come up every week. I'm not expecting this post to stop 'em by any means - most folks on the live stream don't seem to read the blog regularly - but I thought I'd share 'em here. Q: Why is the same query sometimes…
Let's say you need to troubleshoot someone's query performance, and they're using temp tables. You want to peek in at their temp table contents from another session while their query is running. SQL Server makes that a little bit challenging. Nic Neufeld showed how to use DBCC PAGE to do this, but today I'm going to cover another way: reading their statistics histogram.
Paul White (Blog, @SQL_Kiwi) is a legendary SQL Server blogger who's written a ton of good material. You should read all of it. You won't, and I can say that with authority because...I don't read it all either. It's too much.
But when your job eventually requires you to understand temp tables way better than you do today, read these 3 posts in this order - which is probably different than what Paul would recommend, but I think they flow better this way:
In the back of a restaurant, there's a walk-in cooler (or a few of 'em.)
When a chef needs to make something, she goes into the walk-in cooler, gathers the necessary ingredients, and then comes out to the kitchen to do the cooking.
You're a systems administrator or developer who's been stuck in charge of the SQL Server. You have a couple of production SQL Servers, no high availability or disaster recovery, and around 100GB of data or less on each server.
You've never been to a "real" SQL Server training class, and you've been Googling your way through problems alright, but you're starting to realize that this SQL Server thing is kinda complicated.
You've got a bunch of users on the same SQL Server, and you wanna know who's using the most resources. SQL Server doesn't make this easy. You can query some management views to see which queries or databases have been using the most resources, but you can't intuitively tell which USERS are.
To solve this problem, we're going to use Resource Governor.
I was cleaning out a folder of presentations and I realized there are a whole slew of problems that we had to worry about ten years ago, and they just hardly ever come up today. Virtual Log Files (VLFs) - it's been years since I've run across a server where this was an issue. I…
Download Azure Data Studio on your desktop. Do this on your desktop, not the SQL Server itself. You always want to run management tools from your own machine to reduce the overhead on the SQL Server itself. After it downloads, install it - just accept the defaults. During the installation process, it may ask: You…
In the big picture, you need to:
Download the free Microsoft SQL Server Management Studio and install it, accepting the defaults
Connect to your SQL Server
Create a low-privileged login so that you don't accidentally drop objects
Learn to switch back & forth between the low-privileged account and your regular one
When your SQL Server restarts, how do you find out?
I've seen a few senior DBAs do a really smart trick: they write a stored procedure to send them an email, and then they mark that stored procedure as a startup stored procedure so that it runs automatically whenever SQL Server starts up. (You could also try this with an Agent job scheduled to run at Agent startup, but ... Agent may not actually start.)
You've heard that shrinking a database is bad because it introduces both external and internal fragmentation, it causes blocking, it causes transaction log growth while it runs, and it's slow and single-threaded. You understand that if it's just a matter of 10-20-30% of a database, and the database is only 100-200GB, you might as well just leave the space there, because you're gonna end up using it anyway.
Page Life Expectancy is a Perfmon counter that's supposed to track how long pages stay in memory before they get flushed out to make room for other pages that are needed instead.
Paul Randal has blogged this a few times, and rather than rehash 'em, I'd rather point you to a couple of his roundups:
I happened to see this DBA.StackExchange.com question about killing a big delete, and I really felt for the admin involved: Four days ago a user ran the command below on a table with 400,000,000 rows. It's still running and the log file is increasing in size. Hoo boy. When you kill a query, it's hard…
When someone asks you, "Is this database read-intensive or write-intensive?" you probably look at sys.dm_io_virtual_file_stats to measure the file activity - but that isn't really correct. After all, your users run a lot of queries all day that are simply satisfied from the buffer pool. SQL Server caches your data so it doesn't have to hit the disks, but ... those are reads, right? (Write? Get it?)
