A Common Query Error
So Many Choices
When you only need stuff from a table where there's matching (or not!) data in another table, the humble query writer has many choices.
Joins
In/Not In
Exists/Not Exists
Author: Erik Darling
Restoring tempdb since GETDATE(). Now blogging at ErikDarlingData.com.
Setting Up SQL Server: People Still Need Help
I Like What's Happening
I wanna start off by saying that I like what Microsoft has been doing with the setup process -- it made a lot of sense to add the tempdb configuration screen, and having a checkbox to turn on Instant File Initialization was amazingly helpful.
Even in the cloud, people still need to install SQL Server, and even in the cloud, not everyone installing SQL Server is a DBA.
Performance Tuning
Why Does My Select Query Have An Assert?
You And Ert
This is a quick post because it came up with a client. I like having stuff to point people to -- that's sort of like automation, right?
Anyway! Lots of plans have Assert operators in them. But they're usually performing modifications.
Performance Tuning
Wait Stats Should Be Easy By Now
Why Is My Query...
We've all started a question with a close approximation of those words. No matter how you finish that sentence, there's some basic information that you need to collect to figure it out, like:
Query plan
Wait stats
Other server activity
Performance Tuning
A Query That Should Be Contradicted
Innocent Enough
I was writing another query, and became enamored with the fact that HAVING will accept IS NULL or IS NOT NULL as a predicate.
What I ended up writing as an example was this query:
[crayon-6aa5c33b746b7016058412/]
Why this query?
Do I Have A Query Problem Or An Index Problem?
Party Up
When someone says "this query is slow", and you can rule out contextual stuff like blocking, odd server load, or just an underpowered server, what's the first thing you look at? There's a lot of potential culprits, and they could be hiding in lots of different places.
After several minutes of thinking about it, I decided to call my method QTIP, because I like to look at the:
Performance Tuning
Is the CXCONSUMER Wait Type Harmless? Not So Fast, Tiger.
Let's say you've got a query, and the point of that query is to take your largest customer/user/whatever and compare their activity to smaller whatevers. If SQL Server doesn't balance that work evenly across multiple threads, you can experience the CXCONSUMER and/or CXPACKET wait types. To show how SQL Server ends up waiting, let's write…
Performance Tuning
A Quirk In Parallel Hash Join Plans
Bailing, Takes Me Away
Both of these queries will return zero rows, in the 2010 version of the Stack Overflow database.
[crayon-6aa5c33b759fb982675404/]
Querying a couple of the larger tables, with no indexes on the join columns leads to hash join plans.
If you're wondering why I'm forcing the second plan serial, using the MAXDOP 1 hint...
A New tempdb Trace Flag Appears!
Coming Around
Microsoft has been doing a good job of taking stuff that used to be Wizard Hat and making it part of out of the box functionality.
Starting with SQL Server 2016, you didn't need to keep these in your setup checklist:
#TSQL2sday: +
For this month's T-SQL Tuesday, He of Perfect Hair, Bert Wagner asked, "What code have you written that you would hate to live without?"
There are a lot of pieces of code I frequently need to reuse when working on the First Responder Kit, like string splitting, XML PATH-ing, and converting milliseconds to some human consumable elements.
Building SQL ConstantCare®: Analyzing Query Plans
XML Delenda Est
Last week, I talked about how we're going to be collecting query plans from users who opt to have us do so (and only users who opt in! No surprises, here) by leveraging code we already have written in sp_BlitzCache, and how we're going to do the XML analysis on our end using a Real Programming Language®.
T-SQL & Development
Why SQL Developers Keep Making The Same Mistakes
Dead Horses
I read a lot of SQL blogs, both new and old. What's striking is how many blogs seem to cover the same subjects from different angles, over and over again.
This isn't to knock anyone's blogging at all -- but what I do want to do is try to proffer an explanation as to why this happens.
Ola Hallengren’s Scripts Keep Getting Better
You Know Him, You Love Him
You trust your database backups and integrity checks to him, and he has been KILLING IT on new features lately.
A while back, Ola decided to Open Source his scripts on GitHub (Soon to be known as Microsoft LiveHub for Business Essentials). As more and more people embrace GitHub and are getting used to how it works, the feature suggestions have been rolling in.
Building SQL ConstantCare®: Collecting Query Plans
Coming Soon
In an upcoming release, we'll start collecting query plans from your servers. Since this may result in the transmission of PII, it will be opt-in only. We won't be getting this by default, because we don't want anyone to be uncomfortable with the level of data we're collecting from their servers. This is an important goal.
Production DBA
How The Cloud Turns Performance Tuning Into Cost Savings
Barely Metal
In Ye Olde Olden Dayyes, companies would outline hardware budgets and refresh cycles.
Every 3-5 years, they'd lick a thumb and hold it up to the CPU fans. If they felt a soft breeze, they'd buy the same number of CPUs. If they felt a hard breeze, they'd buy a few more. This made the hardware buy more expensive, and of course drove up licensing costs.
Performance Tuning
A Surprising Simplification Limitation
When It Comes To Simplification Rob Farley has my favorite material on it. There's an incredible amount of laziness ingenuity built into the optimizer to keep your servers from doing unnecessary work. That's why I'd expect a query like this to throw away the join: [crayon-6aa5c33b771e1039838953/] After all, we're joining the Users table to itself…
Performance Tuning
Can Non-SARGable Predicates Ever Seek?
Cheating At Candy Crush
The short answer is that yes, they can. But only with a little extra preparation.
Before I show you what I mean, we should probably define what's not SARGable in general.
T-SQL & Development
Why You Should Stop Depending On SQL Server Versions In Code
It Used To Be
That when you wrote scripts that had to work across different versions of SQL Server, you were pretty safe.
Microsoft would release a new version, or a Service Pack that had something new in it, and it wouldn't start showing up in older versions.
Performance Tuning
Indexing Strategies For Cross Apply
Slicked Brad The apply operator remains one of my favorite tools, but much like CTEs and windowing functions, they're not totally magical unless you look at your indexes. Starting with a dead simple query on the Users table, it'd be easy to come up with an ideal index for this one thing. [crayon-6aa5c33b79594809482467/] If you…
Performance Tuning
How Table Variables Mess With Parallelism
This Is A Parallel Query
This Is A Temp Table
[crayon-6aa5c33b7b61d446229546/]
This Is A Parallel Insert Into A Temp Table
