execution-plans

Last week, I presented on the topic of TSQL User Defined Functions (UDFs) in SQL Server at the PASS Summit.

Sometimes it’s useful to know how to cause a problem.

Maybe you’ve never encountered the problem, and want to get hands-on experience. Maybe you’re testing a monitoring tool, and want to see if a condition flags an alert. Maybe you’re testing out a new client tool, and want to see how it displays it.

User defined functions are fairly simple to create in SQL Server, but figuring out exactly how they are being used can take a little time - even in a simple execution plan. This is because the functions can be tucked away into lots of different operators in the plan, even join operators.

The good news: once you learn what to look for, it’s not terribly complicated. There are some fancy terms involved, but the definitions are pretty simple.

You know how they say “don’t sweat the small stuff”?

That’s good advice for lots of things, but when it comes to query tuning - I WANT TO KNOW MORE when something is inconsistent. I just can’t let it go. I go total X-Files: The Truth Is Out There.

It’s tough to keep track of which features work in each version of SQL Server, and which Editions support them.

My memory told me that the new Adaptive Joins feature in SQL Server 2017 was Enterprise Edition only…  and that’s correct, but I didn’t realize that the fancy new feature to make Multi-Statement TVFs smarter has much wider licensing.

The new Enterprise Automatic Tuning feature in SQL Server 2017 may sound intimidating at first – one question I get a lot lately is whether or not there’s a future for DBAs. Will Auto-Tune mean we don’t need any more human tuning?

Well, not anytime super soon.

It’s time to write a blog post, so I can save time when I go searching for this in a couple years!

I recently wrote a fairly complicated post, called “Forced Plan Confusion: Is_Forced vs Use Plan = True.”

You do NOT need to go read that post to understand this one. I think I’ve found simpler way to explain the most important part of that post - and why it’s A Very Good Thing.

Identifying that a query plan has been bossed around in Query Store can be a bit tricky, because it can appear in different ways.

I had a great time giving a session recently for the 24 Hours of PASS - Summit Preview Edition.

When you modify the indexes on a table, SQL Server needs to reconsider how it executes queries that reference that table. But the way this appears when you’re looking at your execution plan cache is far from obvious: Impacted query plans are NOT removed from cache at the time you change an index. The next time the query is executed, it will be recompiled, and the new plan and related execution information will be placed in cache.

It can be tricky to remember that SQL Server doesn’t short circuit on IF statements in stored procedures. Even when you know this, sometimes it’s hard to recognize the impacts.

I’ve never claimed to be great at math, but until recently I thought I knew how to count to one. Zero… one. That’s what we learned in kindergarten.

Apparently SQL Server didn’t go to kindergarten.