Development

Development, Videos

Several months ago, Erik said something like, “We should do a performance art piece where one person does the talking, while another person does demos that contradicts everything they say.” I thought it was a brilliant idea. We just had to find the right place and time to try it, and find the right topic.…

Building SQL ConstantCare®: How We Use Feature Toggles

June 25, 2018

Development, SQL ConstantCare

In SQL ConstantCare®, you send in your SQL Server’s diagnostic data, our systems analyze it, and we email you a list of tasks you should perform to make your SQL Server faster and more reliable. Some recommendations are really straightforward: for example, hey, you should turn off Priority Boost. It’s really easy to determine if Priority…

A Surprising Simplification Limitation

June 19, 2018

Execution Plans

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: Transact-SQL SELECT COUNT(u.Id) FROM dbo.Users AS u JOIN dbo.Users AS…

Can Non-SARGable Predicates Ever Seek?

June 15, 2018

Development, Indexing

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. Wrapping columns in functions: ISNULL, COALESCE, LEFT, RIGHT, YEAR, etc. Evaluating predicates against things indexes don’t track: DATEDIFF(YEAR, a_col, b_col),…

Why You Should Stop Depending On SQL Server Versions In Code

June 14, 2018

Development

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. That’s not so true anymore. Microsoft has…

Indexing Strategies For Cross Apply

June 12, 2018

Execution Plans, Indexing

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. Transact-SQL SELECT u.Id,…

Building SQL ConstantCare®: Why People Aren’t Using Query Store

June 11, 2018

Query Store (QDS), SQL ConstantCare

When Microsoft first talked about Query Store, I was wildly excited. It’s basically a black box flight data recorder for SQL Server, tracking which queries run over time, how much work they do, and how their execution plans change. And then it came out, and…hardly anybody used it. We thought it was a good idea,…

Demoing Latch Waits with Stupid Tricks

June 8, 2018

Development

Say you’ve got PAGELATCH_UP or PAGELATCH_EX waits, and you’re wondering what might be causing ’em. I’m going to keep the brutally simple approach of building a stored procedure to simply dump hard-coded values into a table at high speed. I don’t want to select from other tables since they might introduce some other types of…

How Table Variables Mess With Parallelism

June 7, 2018

Execution Plans, SQL Server

This Is A Parallel Query Hi There This Is A Temp Table Transact-SQL CREATE TABLE #Posts (OwnerUserId INT); 1 CREATE TABLE #Posts (OwnerUserId INT); This Is A Parallel Insert Into A Temp Table Sort of asterisk (The Insert isn’t parallel, but the part of the plan leading up to it is) This Is A Table…

Stupid T-SQL Tricks

June 6, 2018

Bad Idea Jeans, T-SQL

Presented without comment: Transact-SQL CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT); GO SELECT [SELECT] FROM [FROM] WHERE [WHERE] LIKE [LIKE]; GO 1234 CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT);GOSELECT [SELECT] FROM [FROM] WHERE [WHERE] LIKE [LIKE];GO Next up, can you break up a query with spaces? Yep: Transact-SQL SELECT * FROM…

How Many Kinds Of Joins Are There?

May 30, 2018

Adaptive Joins, Execution Plans

Quite So There are three in SQL Server, not counting the Adaptive Join which is just a placeholder for either Nested Loops or Hash Joins. If you look closely at each of those joins, you’ll see all sorts of interesting things pop up. Hopefully none of them will be Sorts. NOT EXISTS Nested Loops has:…

Behind Every Trivial Plan Is A Good Demo

May 29, 2018

Execution Plans, Indexing

A While Back I started this Q&A on Stack Exchange about ISNULL. It’s one of the most common client issues I see, and putting stuff like that out in a different community to get more opinions on it is valuable. Also, I’m saving up points for a Stack Overflow Swiss Army Knife. That’s what you…

A Strange Place For A Memory Grant

May 24, 2018

Execution Plans, Memory Grants, SQL Server

If You Hang Around Fetish Bars Long Enough… You learn things about memory grants in execution plans. Stuff like the things that usually need memory Hash Join Hash Match Sort Parallelism Exchanges But there’s something that rarely gets discussed, even in the deepest, darkest, dankest dungeons. Let’s loosen the clamps and look at it. Keys…

How to Reduce the CPU Overhead of Dynamic SQL

May 23, 2018

Tara Kizer

Development, T-SQL

Dynamic SQL is a good choice for catch-all type queries, but you have to be careful how you execute the dynamic string. For frequently executed queries, “EXEC sp_executesql @sql”is a good choice but at the risk of encountering parameter sniffing issues. For queries that aren’t executed very often, “EXEC (@sql)” can be used, but you…

Trivial Plans, Simple Parameterization and Check Constraints

May 22, 2018

Execution Plans, Indexing

Trivial Plans Are Weird They’re the optimizer’s “Yes, Dear”. The thing is, sometimes “Yes, Dear” can miss some important details. Cost based decisions about operators Potential index improvements Contradiction detection for check constraints Simple parameterization, according to… uh… Official Microsoft documentation? Can only occur in a Trivial Plan. Which makes sense. Simple parameterization is considered…

How to Create Deadlocks and Troubleshoot Them

May 17, 2018

Deadlocks

Watch in awe and wonder as I create a deadlock, then use sp_BlitzLock after the fact to show you which queries and tables were involved: Here are the scripts to run in the left hand window: Transact-SQL CREATE TABLE dbo.Lefty (Numbers INT PRIMARY KEY CLUSTERED); INSERT INTO dbo.Lefty VALUES (1), (2), (3); CREATE TABLE dbo.Righty…

Quick Tips For Debugging Large Stored Procedures

May 16, 2018

T-SQL

Hell Here! Get Your Fresh Hell, Here! Let’s face it — as far as developer tools go, SSMS is pretty bad. Intellisense? The jokes write themselves. Don’t get me wrong, SSMS is a good management tool, but it’s not a good development tool (and what kind of maniac sticks debug — F6 — next to execute —…

How to Screw Up Your Database Updates in 3 Simple Steps

May 15, 2018

Alex Yates

Development

Alex Yates Hi. I’m not Brent. My name’s Alex, and I care about databases, DevOps and data protection. Last year I gave a session on GroupBy all about how to do Database DevOps right. Now, dear reader, I’m going to give you a cheerful, three-step crash course about how to screw it all up. All…

Building SQL ConstantCare®: I Love Unit Tests

May 14, 2018