Development

This Isn’t A Trick Question Hopefully it’ll get you thinking about your tables, and how they’re designed. One of the most consistent problems I see with clients is around wide tables. I don’t mean data types, I mean the number of columns. Going back to Michael Swart’s 10% Rule, if your tables have > 100…

Tall Tales From Table Variables

August 23, 2018

Execution Plans, Indexing

Secret Squirrel When you modify a table with multiple indexes, SQL Server may choose either a narrow plan, if it doesn’t think all that many rows are going to change, or a wide plan if it thinks many will. In narrow plans, the work SQL Server has to do to modify many indexes is hidden…

How Trace Flag 2335 Affects Memory Grants

August 15, 2018

Memory Grants, SQL Server

This trace flag is documented in KB #2413549, which says, “Using large amounts of memory can result in an inefficient plan in SQL Server.” The details are a little light, so let’s run a quick experiment with: SQL Server 2017 CU 8 (14.0.3029.16) VM with 4 cores, 32GB RAM, max memory set to 28GB Stack Overflow…

How to Check for Non-Existence of Rows

August 9, 2018

Execution Plans, T-SQL

You’re writing a query, and you wanna check to see if rows exist in a table. I’m using the free Stack Overflow database, and I wanna find all of the users who have not left a comment. The tables involved are: In dbo.Users, the Id field uniquely identifies a user. In dbo.Comments, there’s a UserId field…

Why Does My Select Query Have An Assert?

August 8, 2018

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

No Comments

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

No Comments

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