Development

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. Assert operators are used to check Foreign Key and Check Constraint…

How I Configure SQL Server Management Studio

August 1, 2018

SQL Server Management Studio

Ever go into Tools-Options? SSMS has a stunning number of options these days. Here are some of my favorites: Documents options On the Documents options, I uncheck “Check for consistent line endings on load” because I constantly get scripts with all kinds of wacko line endings. That warning is a pain in the butt. On…

A Query That Should Be Contradicted

July 31, 2018

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: Transact-SQL SELECT v.PostId, SUM(v.UserId) AS whatever FROM dbo.Votes AS v WHERE v.UserId IS NULL GROUP BY v.PostId HAVING…

Common Entity Framework Problems: N + 1

July 30, 2018

Richie Rump

I wanna dance with common problems One of the most common issues that I’ve seen with Entity Framework isn’t technically an Entity Framework problem at all. The N + 1 problem is an anti-pattern that is a problem with ORMs in general, which most often occurs with lazy loading. There was a lot going on…

[Video] “Actual” Execution Plans Lie for Branching Queries

July 27, 2018

In which I show why you can’t trust “actual” execution plans for branching stored procedures, and you’re better off using sp_BlitzCache to figure out what branches you need to tune: Demo code: Transact-SQL CREATE TABLE dbo.TableA (ID INT); CREATE TABLE dbo.TableB (ID INT); CREATE TABLE dbo.TableC (ID INT); CREATE TABLE dbo.TableD (ID INT); CREATE TABLE…

CXCONSUMER Is Harmless? Not So Fast, Tiger.

July 24, 2018

Execution Plans, SQL Server

In Theory 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. You may also stumble upon this issue accidentally if your query happens to capture populations like that by accident. For our purposes, let’s look at a query that…

Election Injection

July 20, 2018

cyber-trickery

Setting politics aside (Lord knows I’d like to), this ABC 7 Chicago news story covers how Russians hacked the Illinois State Board of Election in 2016: SQL, an acronym for Structured Query Language, is a database programming language. An “SQL injection” is a common piece of cyber-trickery used to illegally gain access to government, financial,…

A Quirk In Parallel Hash Join Plans

July 18, 2018

Bailing, Takes Me Away Both of these queries will return zero rows, in the 2010 version of the Stack Overflow database. Transact-SQL SELECT COUNT(*) AS records FROM dbo.Votes AS v JOIN dbo.Posts AS p ON v.UserId = p.OwnerUserId WHERE v.CreationDate >= '2011-01-01'; SELECT COUNT(*) AS records FROM dbo.Votes AS v JOIN dbo.Posts AS p ON…

#TSQL2sday: How Much Plan Cache History Do You Have?

July 10, 2018

#tsql2sday, t-sql tuesday

For this month’s T-SQL Tuesday, Bert Wagner asked, “What code have you written that you would hate to live without?” I bet you think I’m going to say sp_Blitz, and I would indeed hate to live without that, but you already know about that one, dear reader. Lemme show you something else cool. Before I…

Why SQL Developers Keep Making The Same Mistakes

July 6, 2018

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…

Why Performance Tuning Needs Expiration Dates

[Video] Why Performance Tuning Wisdom Needs Expiration Dates

June 26, 2018

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

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

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

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