Production DBA
Database Design Choices To Consider When You’re Worried About Scale
Tipping the Kilter
I'm intentionally avoiding physical/hardware choices in this post, like how much hardware to buy, and scaling out vs. scaling up.
Author: Erik Darling
Restoring tempdb since GETDATE(). Now blogging at ErikDarlingData.com.
Performance Tuning
Why Do Some Indexes Create Faster Than Others?
To warn you ahead of time, this post is an exploration without an answer (yet). There's some interesting stuff in here, but no conclusions. If that's not your kind of post, feel free to skip it. If it is, well, here goes nothin'... Wild, Wild Life Creating indexes is kind of a funny thing. By…
An Idea For Improving DBCC CHECKDB
Hard Enough At this point, we've all probably got a database that's a terabyte or more in size. If you're anything like I was, you've got a ton of multi-terabyte databases, and the SAN admin has a special folder for your emails. When you've got a lot of large databases, normal maintenance is out the…
Performance Tuning
When Does SARGability Matter Most?
!erehT iH
I know what you're thinking. Another post about how you should just never do this one thing and all your queries will magically end up faster.
Just watch the video.
Performance Tuning
Query Store And Cruel Defaults
Gosh Darn
Now, look, it's no secret that I think Query Store is cool. Heck, I went and wrote sp_BlitzQueryStore so you can examine it similarly to how sp_BlitzCache looks at the plan cache.
For a variety of reasons, though, we run into people who can't or won't turn it on, or who had to turn it off.
Performance Tuning
Sniffed Nulls and Magic Numbers
I Sniff Your Milkshake
Building off of A Simple Stored Procedure Pattern To Avoid, I wanted to talk about a similar one that I see quite often that is not nearly as clever as one would imagine.
I goes something like this: If this variable is passed in as NULL, substitute it with something else. It has a lot of variations.
[crayon-6aa44c7d74a3e055947893/]
They all have the desired effect: substituting a passed in NULL with a magic number.
Performance Tuning
Never Judge A Query By Its Cost
Signs and Numbers
When tuning queries, or even finding queries to tune, there's a rather misguided desire to look for queries with a high cost, or judge improvement by lowering query cost. The problem is that no matter what you're looking at, costs are estimates, and often don't reflect how long a query runs for or the actual work involved in processing the query.
Production DBA
Have Availability Groups Gotten Easier Lately?
Easy Lover I don't blog a lot about AGs. If we're being honest (and I do try to be honest with you, dear reader), I just like performance tuning topics way more. When new features get announced for AGs, some of you may ooh and aah, but not me. I Make A Face I don't…
Performance Tuning
Indexed View Matching With GROUP BY And DISTINCT
Bit Of A Kick
I've been playing with indexed views a little bit lately for some demos in my Bits Precon.
There are a whole bunch of limitations in creating indexed views. One of them is that you can't base the query on DISTINCT.
Performance Tuning
Indexed View Creation And Underlying Indexes
Accidental Haha
While working on some demos, I came across sort of funny behavior during indexed view creation and how the indexes you have on the base tables can impact how long it takes to create the index on the view.
Starting off with no indexes, this query runs in about six seconds.
[crayon-6aa44c7d76a4d174480374/]
Here's the plan and the query stats:
Performance Tuning
Tales From Overindexing: Too Many One Column Indexes
Master Plan
Sometimes you see a query, and it's hitting one table, and then the query plan looks like a vase full of spaghetti.
Usually, there's a view involved.
Performance Tuning
Getting Sneaky With Forced Parameterization
Silly Rules
I've blogged about some of the silly rules about where Forced Parameterization doesn't work.
One rule that really irked me is this one:
The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO, or FOR XML clauses of a query.
TOP and FOR XML, get used, like, everywhere.
Performance Tuning
Not So Forced Parameterization
Asking The Wrong Question
Sometimes, when you wanna turn on a feature, you spend so much time wondering if you should, you don't bother asking if it'll even work when you do.
There are a long list of things that are incompatible with Forced Parameterization, on a page that's pretty hard to find.
Performance Tuning
Adventures In Foreign Keys 5: How Join Elimination Makes Queries Faster
FINALLY...
This is the last post I'll write about foreign keys for a while. Maybe ever.
Let's face it, most developers probably find them more annoying than useful, and if you didn't implement them when you first started designing your database, you're not likely to go back and start trying to add them in.
Performance Tuning
Adventures In Foreign Keys 4: How to Index Foreign Keys
This week, we're all about foreign keys. So far, we set up the Stack Overflow database to get ready, then tried to set up relationships, and encountered cascading locking issues.
Dawn Of The Data
I don't know how long the recommendation to index your foreign keys has been a thing, but I generally find it useful to abide by, depending a bit on how they're used.
Performance Tuning
Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly
Legendreary
In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up.
I didn't even get to the point where I wanted to test all those relationships.
Performance Tuning
Adventures In Foreign Keys 2: Common Foreign Key Errors
This week, we're all about foreign keys. Yesterday's post covered scripts to set up the Stack Overflow database to get ready, eliminating data that would violate FK relationships.
You Had Two Jobs!
Let's say I wanted to implement Foreign Keys to do two things
If a user deletes their account, all of their badges, comments, and posts will also get deleted
If a user deletes their post, all of the comments and votes will also get deleted
Performance Tuning
Adventures In Foreign Keys 1: Setting Up Foreign Keys in Stack Overflow
In A Foreign Key, In A Foreign Table Much of what people want from foreign keys, like referential integrity and join elimination, are only as guaranteed as much as SQL Server can trust your constraints (and even then...). The same goes for check constraints, too. Thankfully, things like Primary Keys and Unique Constraints are sort…
Performance Tuning
The Curse of Cursor Options
Red Skies At Night I know it's hard to believe, but I still see a lot of people using cursors when they shouldn't. Other times, there's some scary dungeon part of the code that someone wrote eons ago that no one wants to go anywhere near to fix. Sometimes there's a decent reason, something like:…
T-SQL & Development
SUM, AVG, and arithmetic overflow
You Shoulda Brought A Bigger Int Sometimes you run a query, and everything goes fine. For a while. For example, if I run this query in the 2010 copy of Stack Overflow, it finishes pretty quickly, and without error. [crayon-6aa44c7d7cc23884701032/] If I run this query in the full version, it runs for a minute and…