sql server

SQL Server 2017: Unpublished Gems

Last Updated May 12, 2017

SQL Server 2017

Sneaky, sneaky, sneaky Release notes never seem to tell the full story, do they? At some point, you have to take matters into your own hands and go hunting for, as Paul Harvey would say, “The Rest Of The Story”. So what did I find? New stuff! Brand. New. Stuff. For you. If you ever…

SQL Server 2017: Quick Rundown

Last Updated May 12, 2017

Erik Darling

SQL Server 2017

ICYMI Playing with SSMS in the lab Microsoft released CTP 2 of SQL Server vNext (well, yesterday today, but who knows when this thing will hit?). There’s some great new stuff in the Engine in this release that I’m just crazy about getting to know better. Going through the Release Notes made me feel things…

Is it ever worth adding indexes to table variables?

Last Updated April 16, 2017

Erik Darling

Execution Plans, Indexing

Disclaimer I found this totally by accident, and it even surprised me. You can probably guess the TL;DR on this is yes, huh? Dude, where’s my table variable? I was trying to come up with a demo for something totally different. Don’t ask. Seriously. It’s top secret. Okay, so it’s just embarrassing. Anyway. I had…

Replacing ISNULL In A WHERE Clause

Last Updated April 7, 2017

Erik Darling

Execution Plans, T-SQL

I like blogging Really, I do. But sometimes I like a change of pace. Instead of blogging about this question, I posted it over on dba.stackexchange.com. Also, I thought it was an interesting question, and apparently many other people agreed. Not just because I wanted some more points, but because blog comments are notoriously sucky…

Fun With Logging Dynamic SQL

Last Updated April 7, 2017

Erik Darling

T-SQL

While working on a demo I thought this might make a fun aside to share on the blog, because working with dynamic SQL can be challenging. Especially if you don’t have a monitoring tool or application profiling to figure out who ran what and when, most of the time you’ll have no idea what went…

Decrypting Insert Query Plans

Last Updated March 25, 2017

Erik Darling

Execution Plans, SQL Server

We’ve all been there Staring at a simple query that has a three mile long execution plan. After the first time, you learn to always look to make sure there’s not a secret view involved. After looking at query plans for modifications involving indexed views, I decided to look at some other fairly common table…

CTEs, Views, and NOLOCK

Last Updated March 25, 2017

Erik Darling

Humor, SQL Server

This is a post because it surprised me It might also save you some time, if you’re the kind of person who uses NOLOCK everywhere. If you are, you’re welcome. If you’re not, thank you. Funny how that works! I was looking at some code recently, and saw a CTE. No big deal. The syntax…

This Is Your Backup Schedule On Drugs

Last Updated March 25, 2017

Erik Darling

SQL Server

I’ve seen things Terrible things. I mean, not just working here (which, as I write this, I’m three days away from the two year mark, fingers crossed!), but also out there on the internet. Once you leave the safe confines of Brent Ozar Unlimited, you’re subject to the ravages of space. Strangers. Radiation. Strangiation. Your…

From The Mailbag: DBCC CHECKDB And Read Only Databases

Last Updated March 23, 2017

Erik Darling

CHECKDB and Corruption, SQL Server

We got an email!!! It was weird. It was from an Australian, and it was right side up. I could hardly believe it. Normally we encourage people to ask us questions during Office Hours, or over on Stack Exchange. That way lots of people can benefit from the answer. But since this reader is on…

Connect Item: Query Plan Hash in sys.dm_exec_query_stats vs Query Plan Hash in the query plan

Last Updated March 18, 2017

Erik Darling

SQL Server

Shifty Henry said to Bugs You know those moments when you’re looking at something for so long, and it’s still not working, and you’re pretty sure you’re losing that final grip on your sanity? Well, that feeling was multiplied this week by an odd bug that I found, where Query Plan Hashes in system views…

sp_BlitzCache Tip: Cutting Through Heavy Dynamic SQL Use

Last Updated November 16, 2017

Erik Darling

First Responder Kit, sp_BlitzCache, T-SQL

It keeps me up at night Out of the box, sp_BlitzCache will give you enough stuff to work on in your server’s plan cache to last a month of leap days. This is great for most people, but if you use a ton of dynamic SQL in stored procedures, it can make things difficult. What’s…

Indexed Views And Data Modifications

Last Updated March 9, 2017

Erik Darling

Indexing

Love/Hate When indexed views help, they make aggregation queries super fast. There are some kind of horrible limitations and requirements. When they hurt, your modifications slow to a crawl because SQL has to not only keep your horrible indexes synchronized, it also has to keep your indexed view indexes in sync now, too. But SQL…

No seriously, don’t use SQL_VARIANT for that

Last Updated March 9, 2017

Erik Darling

Development, T-SQL

I’d been meaning to write this for a while I half-stumbled on the weirdness around SQL_VARIANT a while back while writing another post about implicit conversion. What I didn’t get into at the time is that it can give you incorrect results. When I see people using SQL_VARIANT, it’s often in dynamic SQL, when they don’t…

Why Cost Threshold For Parallelism Shouldn’t Be Set To 5

Last Updated March 3, 2017

Erik Darling

Configuration Settings

CURRENT_YEAR() Here it is, 2017. A full 20 years since SQL Server 7. Back then, there was a developer named Nick. Nick was on the development team, and apparently the query-costing-magic development was done on his machine. All of it. The magical metric was that for every second a query ran, it would cost another…

Max Worker Threads: Don’t Touch That

Last Updated February 15, 2017

Erik Darling

SQL Server

More isn’t faster I’ve had people give me all sorts of janky reasons for changing Max Worker Threads. I’ve even had people tell me that someone from Microsoft told them to do it. The thing is, all changing that setting does is help you not run out of worker threads. It doesn’t make queries run…

Crappy Missing Index Requests

Last Updated November 16, 2017

Erik Darling

Execution Plans, Indexing, sp_BlitzIndex

When you’re tuning queries It’s sort of a relief when the first time you get your hands on it, you get the plan and there’s a missing index request. Even if it’s not a super high-value one, something in there is crying for help. Where there’s smoke, there’s a bingo parlor. But does adding missing…

Simulating Workload With ostress And Agent Jobs

Last Updated February 14, 2017

Erik Darling

Load Testing, SQL Server

This question comes up a lot Especially during Office Hours, and the answer is usually… not great. You can spend a lot of money on people and complicated software to design, run, and monitor workloads against test environments, or you can throw together tests with some free tools like SQL Query Stress or Microsoft’s RML…

Inline Table Valued Functions: Parameter Snorting

Last Updated May 26, 2020

Erik Darling

Parameter Sniffing, SQL Server, T-SQL

You’ve probably heard about parameter sniffing But there’s an even more insidious menace out there: Parameter Snorting. It goes beyond ordinary parameter sniffing, where SQL at least tried to come up with a good plan for something once upon a compile. In these cases, it just plain gives up and throws a garbage number at…

Memory Grants and Data Size

Last Updated January 8, 2019

Erik Darling

Execution Plans, Memory Grants, SQL Server

What does memory do? In SQL Server, just about everything. We cache our data and execution plans in it (along with some other system stuff), it gets used as scratch space for temporary objects, and of course queries use it for certain operations, most notably sorting and hashing. And of course, now Hekaton comes along…

High Compiles and Multi-Statement Table Valued Functions

Last Updated February 14, 2017

Erik Darling

SQL Server, T-SQL, TempDB

Way back in 2016 I wrote about what to do if sp_BlitzFirst warns about high compiles. During GroupBy, Adam Machanic gave a great talk on new features in SQL Server 2016. It reminded me of a blog post I wanted to write about one common culprit of high compiles: Multi-Statement Table Valued Functions. Leaving aside…