How SQL Server compiles, optimizes, and executes individual queries.
573 associated posts292 primary posts
Getting developers production data stinks DBAs are stuck in this annoying cycle where they need to give developers production, or production-like data, but... Production data can be huge. Hundreds of gigabytes if you're lucky, several terabytes if you're not. Then once it gets there, you have other considerations. Either you lock down Dev, or you…
Most of the time, when you run a query, SQL Server prefers to use just one index on a table to do its dirty work.
Let's query the Users table in the StackOverflow database (I'm using the March 2016 version today), looking for people with a certain reputation score OR a certain number of upvotes:
Everyone knows Implicit Conversion is bad
It can ruin SARGability, defeat index usage, and burn up your CPU like it needs some Valtrex. But what about explicit conversion? Is there any overhead? Turns out, SQL is just as happy with explicit conversion as it is with passing in the correct datatype in the first place.
No, not #tables - actual tables. Here's how: [crayon-6a70bed04ff2b873879076/] The first one disappears when my session is over, but the latter two persist until the SQL Server is restarted. Why would you ever do the latter two? Say you need to share data between sessions, or between different applications, or staging tables for a data…
This week, Brent, Erik, Jessica, Richie, and Tara discuss database modeling tools, how to learn about database corruption, the new cardinality estimator, and the only question that will be on our certification exams.
Here's the video on YouTube:
If there's one thing living in Texas has taught me It's that people are very paranoid that you may Mess With it. Even in Austin, where the citizenry demand weirdness, they are vehemently opposed to any form of Messing, unless it results in mayonnaise-based dipping sauce. Me? I like Messing With stuff. Today we're going…
These things used to confuse me so much
Despite having worked at a Market Research company for a while, I know nothing about statistics, other than that project managers have all sorts of disagreeably subjective phrases for describing them. Vast majority, convincing plurality, dwindling minority, et al. Less talky, more picture.
When I started getting into SQL Server, and learning about statistics, I heard the same phrases over and over again, but wasn't exactly sure what they meant.
Every single time
Really. Every single time. It started off kind of funny. Scalar functions in queries: no parallelism. Scalar functions in computed columns: no parallelism, even if you're not selecting the computed column. Every time I think of a place where someone could stick a scalar function into some SQL, it ends up killing parallelism. Now it's just sad.
This isn't about performance
If you're interested in performance tests, you can get in the way back machine and read a couple posts by Adam Machanic here and here. I'm also not talking about the difference between them. There are a million articles about that. They're obviously different, but how does SQL handle that internally?
This post isn't going to solve any problems for you It was just something I stumbled on that struck me as funny, while working on a demo for something else. Brent didn't believe it at first, so I thought I'd share with the class. Blog. You, Your Name Here. Hiya! So there I was, creating…
SQL Server 2016 is adding some really awesome stuff Not the least of which is a ton of super cool information in execution plans, and an Extended Events session to expose the same information for all queries. The details are detailed in detail over on this blog post from MS. For those of you with…
With all the trouble NULLs cause...
You'd think people would be more inclined to avoid them. Slap a NOT NULL constraint and a default value on your column and call it a day. I'm perfectly fine with bizarro world canary values. If it's an integer column, some really high (low?) negative number. If it's date-based, why not have it be the lowest value your choice accomodates?
Oh God, he's talking about statistics again Yeah, but this should be less annoying than the other times. And much shorter. You see, I hear grousing. Updating statistics was bringin' us down, man. Harshing our mellow. The statistics would just update, man, and it would take like... Forever, man. Man. But no one would actually…
Confession time This started off with me reading a blurb in the release notes about SQL Server 2016 CTP 3.3. The blurb in question is about statistics. They're so cool! Do they get fragmented? NO! Stop trying to defragment them, you little monkey. Autostats improvements in CTP 3.3 Previously, statistics were automatically recalculated when the…
Trace flags are special switches that, when you flip them, enable different behaviors in your SQL Server. Back in the SQL Server 2000-2005 days, these were super-rare undocumented tricks that were handed out by Microsoft support to fix rare cases.
Over time, some trace flags have become so commonplace that they worked their way into some best-practices setup checklists, like:
Scalar functions in computed columns cause all queries that hit that table to execute serially. But it gets worse!
Scalar functions in computed columns
cause index maintenance to go single-threaded.
If you're running Expensive Edition, index rebuilds can be both online and parallel. That's pretty cool, because it keeps all your gadgets and gizmos mostly available during the whole operation, and the parallel bit usually makes things faster.
With each new version of SQL comes a slew of new stuff
While some changes are cosmetic, others bewildering, and the rest falling somewhere between "who cares about JSON?" and "OH MY GOD TAKE MY MONEY!", but not really my money, because I only buy developer edition. Aaron Bertrand has done a better job finding, and teaching you how to find new features than I could. Head over to his blog if you want to dive in.
I love stuff like this!
Even though it's not on my list of dream features, it's pretty neat. Getting new views into what SQL is doing when queries execute is pretty cool. You can read the short and gory details at the KB here: Improved diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2012
I found a quirky thing recently While playing with filtered indexes, I noticed something odd. By 'playing with' I mean 'calling them horrible names' and 'admiring the way other platforms implemented them'. I sort of wrote about a similar topic in discussing indexing for windowing functions. It turns out that a recent annoyance could also…
Have you got a SQL Server that usually performs just fine, but every now and then, everything falls to pieces? Users complain about performance, and then minutes - or hours - later, the problem just mysteriously solves itself?
Here's a few things to look for:
