Erik Darling, Author at Brent Ozar Unlimited®

Performance Tuning

Missing Index Impact and Join Type

March 27, 2018

Just Another Way
No matter how you delve into missing index requests -- whether it's the plan level, DMV analysis, or (forgive me for saying it), DTA, the requests will generally be the same.

They'll prioritize equality predicates, the columns may or not may be in the right order, the columns may or may not be in the right part of the index, and the impact...

So You Wanna Debug SQL Server Part 1

March 24, 2018

Start here!
This is a gentle introduction to getting set up and started.

I'm nowhere near as good at this as I want to be, but I've been inspired by a couple wizards: Bob the Ward and Paul the White.

Performance Tuning

Table Valued Parameters: Unexpected Parameter Sniffing

March 23, 2018

Like Table Variables, Kinda
Jeremiah wrote about them a few years ago. I always get asked about them while poking fun at Table Variables, so I thought I'd provide some detail and a post to point people to.

There are some interesting differences between them, namely around how cardinality is estimated in different situations.

Performance Tuning

SQL Server 2017 CU5: Finding Problems With Parallelism

March 22, 2018

Snow Is Temporary
Cumulative Update 5 dropped for SQL Server 2017. Hooray.

Poor Taryn.

Performance Tuning

If You Can’t Index It, It’s Probably Not SARGable

March 20, 2018

Thumbs Rule OK?
When writing queries, it's really common to want to take shortcuts to express some logic.

I know, it "works fine", and the results are "right", and you have "more important" things to do.

Production DBA

How to Restore a SQL Server Database into Azure SQL DB Managed Instances Redux

March 16, 2018

Anything Brent Can Do
There's some things you can do with Managed Instances, and some ways you can look at data that you can't do elsewhere.

For instance, you have the ability to start an restore ASYNC -- that means if your session drops, the restore will continue headlessly in the background.

Azure SQL DB Managed Instances: We’re All GUIDs

March 16, 2018

Six is having problems adjusting to his clone status
Some funny things happen when you create databases up in a Managed Instance.

For Instance (HEH!), you may expect this to yield some fruitful results, but it Manages (HAH!) to defy logic.
[crayon-6af74a9c86d54318724723/]
But we get blank results! Mon Dieu!

Azure SQL DB Managed Instances: Trace Flags, Ahoy!

March 16, 2018

When you talk to people from Microsoft
They're all "aw shucks" about Trace Flags.

"Don't really need'em!"
"Eh, haven't used one in years."
"Sometimes they're good for troubleshooting, but..."

Performance Tuning

Why sp_prepare Isn’t as “Good” as sp_executesql for Performance

March 15, 2018

sp_prepare For Mediocre
You may remember me from movies like Optimize for… Mediocre? and Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)!

Great posts, Kendra!

Performance Tuning

I Most Certainly Do Have A Join Predicate

March 9, 2018

FREAK OUT
You wrote a query. You joined tables.

You have the right ON clause.

Performance Tuning

Of Windowing Functions And Where Clauses

March 2, 2018

Seen One, Seen'em All
This isn't about indexing! I promise. In fact, I've dropped all my indexes for this. It's just that, well, I've seen this happen with two clients in a row, so maybe it's time to blog about it.

If you run this query, you get a fairly obvious plan.
[crayon-6af74a9c89236513371105/]

Performance Tuning

Adaptive Joins, Memory Grant Feedback, and Stored Procedures

February 28, 2018

Not Exactly The Catchiest Name
There's a TL;DR here, in case you don't feel like reading the whole darn thing.

Batch mode memory grant feedback works with stored procedures
It takes several runs to adjust upwards to a final number
It seems to only adjust downwards once (in this case by about 40%)

Fifteen Things I Hate About ISNUMERIC

February 26, 2018

Yello!
[crayon-6af74a9c8aaf6568976357/]
Thanks for reading!

Brent says: the funny part to me is that if you just try to union all of them, SQL Server throws its hands up:
[crayon-6af74a9c8ab08511946553/]
Result:
[crayon-6af74a9c8ab0c156721935/]
COME ON SQL SERVER YOU JUST TOLD ME THEY WERE ALL NUMERIC, dammit, convert to numeric for me if you're so smart.

Performance Tuning

Computed Columns and Cardinality Estimates

February 23, 2018

:thinking_face:
When most people think about computed columns, they don't think about cardinality estimates.

Heck, I'm not sure most people think about cardinality estimates. At all. Ever.

Performance Tuning

The Many Levels Of Concurrency

February 19, 2018

"Concurrency is hard"

When designing for high concurrency, most people look to the hardware for answers. And while it's true that it plays an important role, there's a heck of a lot more to it.
Start Optimistic
Like, every other major database vendor is optimistic by default. Even the free ones. SQL Server is rather lonesome in that, out of the box, you get the Read Committed isolation level.

Performance Tuning

Then Why Doesn’t SQL Always Seek?

February 16, 2018

Scan The Man
There seems to be a perpetual battle waged against the Index Scan.

At some point it was declared that scans were inferior to seeks, and all energy should be dedicated to eradicating them.

Performance Tuning

Memory Grants: SQL Server’s Other Public Toilet

February 15, 2018

Sharing Is Caring
When everything is going well, and queries are behaving responsibly, one need hardly think about memory grants.

The problem becomes itself when queries start to over and under estimate their practical needs.
Second Hand Emotion
Queries ask for memory to do stuff. Memory is a shared resource.

T-SQL & Development

The Annals of Hilariously Bad Code, Part 2

February 9, 2018

You're Crazy
In Part 1, I showed you code that I think has some anti-patterns in it.

In case you didn't recognize it, it's actually code that Microsoft wrote. It's from sp_delete_backuphistory, and it is plum awful.

T-SQL & Development

The Annals of Hilariously Bad Code, Part 1: Critique the Code

February 6, 2018

Hey Beavis
That's the first time I've used "annals" correctly. I will be 40 sooner than later.

Sometimes when we blog, we get inspiration from clients, students, other bloggers, questions on Stack Exchange, nightmares, or just drinking heavily.

A Gentle Introduction To the Stack Overflow Schema

February 2, 2018

Stack: Dumped

Wanna learn the schema of the free Stack Overflow public database? If you want the Full Nelson, it's all documented here. That's a great resource if you're looking to learn more, but generally when you're looking at a data dump and building up demo queries, you wanna get an idea of where the data skews, and how the tables relate.