Development

Execution Plans, SQL Server

Get Yourself Together This is one of the most common patterns that I see in stored procedures. I’m going to simplify things a bit, but hopefully you’ll get enough to identify it when you’re looking at your own code. Here’s the stored procedure: Transact-SQL CREATE OR ALTER PROCEDURE dbo.you_are_not_clever ( @cd DATETIME NULL) AS BEGIN…

Should You Use the New Compatibility Modes and Cardinality Estimator?

September 12, 2018

For years, when you right-clicked on a database and click Properties, the “Compatibility Level” dropdown was like that light switch in the hallway: You would flip it back and forth, and you didn’t really understand what it was doing. Lights didn’t go on and off. So after flipping it back and forth a few times,…

Can Forced Parameterization Go Wrong?

September 11, 2018

Execution Plans, SQL Server

App Like That If you’ve got the kind of application that sends bare-assed strings to SQL Server, you may end up with a weird choice. Brent will be sitting on one shoulder telling you to use Forced Parameterization. I’ll be on the other shoulder asking if you’ve really thought this whole thing through while you…

Forwarded Fetches and Bookmark Lookups

September 7, 2018

Execution Plans, Indexing

Base Table When you choose to forgo putting a clustered index on your table, you may find your queries utilizing forwarded fetches — SQL Server’s little change of address form for rows that don’t fit on the page anymore. This typically isn’t a good thing, though. All that jumping around means extra reads and CPU…

Locks Taken During Indexed View Modifications

September 5, 2018

Locking, Blocking, and Isolation Levels, SQL Server

Frankenblog This post has been nagging at me for a while, because I had seen it hinted about in several other places, but never written about beyond passing comments. A long while back, Conor Cunningham wrote: This same condition applies to indexed view maintenance, but I’ll save that for another day :). AFAIK he hasn’t…

What Kind Of Statistics Updates Invalidate Plans?

August 30, 2018

Statistics

Basics If you update statistics when the underlying objects haven’t been modified, plans won’t be invalidated. That makes total sense if your statistics update doesn’t also change the statistics. But what about when they do? That seemed like a no-brainer to me. What if you used a higher sampling percentage and got a more accurate…

Do You Have Tables In Your Tables?

August 28, 2018

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

Development

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

Development

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