Development

I love building demos for our training classes because I’m constantly in a battle of the minds with SQL Server. I try to guess what he’s going to do next, and I love it when he surprises me. I started by building a new parameter sniffing demo with the Stack Overflow database, and in this…

Indexed View Matching With GROUP BY And DISTINCT

December 5, 2018

Bit Of A Kick I’ve been playing with indexed views a little bit lately for some demos in my Bits Precon. There are a whole bunch of limitations in creating indexed views. One of them is that you can’t base the query on DISTINCT. Fair enough, but you can do GROUP BY. And what’s pretty…

What Is SQL Injection?

December 1, 2018

T-SQL

Say we have a stored procedure that queries the Stack Overflow database. We have two separate parameters, @DisplayName and @Location, so folks can search for people by name, location, or both. For performance reasons, we decide to build dynamic SQL: Transact-SQL CREATE OR ALTER PROC dbo.UserSearch @DisplayName NVARCHAR(40) = NULL, @Location NVARCHAR(100) = NULL AS…

Tales From Overindexing: Too Many One Column Indexes

November 29, 2018

Execution Plans, Indexing, SQL Server

Master Plan Sometimes you see a query, and it’s hitting one table, and then the query plan looks like a vase full of spaghetti. Usually, there’s a view involved. Usually. Sometimes, there’s just really weird indexing. Here’s A Thing, Look At It Promises This is a query against one table. No views, no functions, no…

How to Troubleshoot Blocking and Deadlocking with Scripts and Tools

November 28, 2018

Deadlocks, Monitoring, sp_BlitzLock

When you need to find which queries are blocking other queries, your decision comes down to when the blocking happened. Is it happening now, recently, or coming up soon? During a live emergency, start with sp_WhoIsActive. Adam Machanic’s excellent free sp_WhoIsActive replaces Activity Monitor, sp_who, and sp_who2, and it’s way more powerful. The documentation is extensive –…

Getting Sneaky With Forced Parameterization

November 27, 2018

Silly Rules I’ve blogged about some of the silly rules about where Forced Parameterization doesn’t work. One rule that really irked me is this one: The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT…INTO, or FOR XML clauses of a query. TOP and FOR XML, get used, like, everywhere. TOP is pretty obvious in its usage.…

Not So Forced Parameterization

November 26, 2018

Asking The Wrong Question Sometimes, when you wanna turn on a feature, you spend so much time wondering if you should, you don’t bother asking if it’ll even work when you do. There are a long list of things that are incompatible with Forced Parameterization, on a page that’s pretty hard to find. Now, there’s…

Adventures In Foreign Keys 5: How Join Elimination Makes Queries Faster

November 16, 2018

FINALLY… This is the last post I’ll write about foreign keys for a while. Maybe ever. Let’s face it, most developers probably find them more annoying than useful, and if you didn’t implement them when you first started designing your database, you’re not likely to go back and start trying to add them in. Depending…

Adventures In Foreign Keys 4: How to Index Foreign Keys

November 15, 2018

This week, we’re all about foreign keys. So far, we set up the Stack Overflow database to get ready, then tried to set up relationships, and encountered cascading locking issues. Dawn Of The Data I don’t know how long the recommendation to index your foreign keys has been a thing, but I generally find it useful…

Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly

November 14, 2018

Execution Plans, Locking, Blocking, and Isolation Levels

Legendreary In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up. I didn’t even get to the point where I wanted to test all those relationships. But there’s an even worse surprise waiting for you if…

Adventures In Foreign Keys 2: Common Foreign Key Errors

November 13, 2018

This week, we’re all about foreign keys. Yesterday’s post covered scripts to set up the Stack Overflow database to get ready, eliminating data that would violate FK relationships. You Had Two Jobs! Let’s say I wanted to implement Foreign Keys to do two things If a user deletes their account, all of their badges, comments, and…

Adventures In Foreign Keys 1: Setting Up Foreign Keys in Stack Overflow

November 12, 2018

In A Foreign Key, In A Foreign Table Much of what people want from foreign keys, like referential integrity and join elimination, are only as guaranteed as much as SQL Server can trust your constraints (and even then…). The same goes for check constraints, too. Thankfully, things like Primary Keys and Unique Constraints are sort…

What’s New in SQL Server 2019 CTP 2.1: Faster Functions

November 7, 2018

SQL Server 2019, T-SQL

A while back, we talked you through a public whitepaper about how Microsoft was working on making user-defined functions go faster. Now that the next preview (CTP2.1) of SQL Server 2019 is out, you can start getting your hands on Froid, the performance-boosting feature. Here’s the documentation on it – let’s see how it works.…

The Curse of Cursor Options

November 2, 2018

Red Skies At Night I know it’s hard to believe, but I still see a lot of people using cursors when they shouldn’t. Other times, there’s some scary dungeon part of the code that someone wrote eons ago that no one wants to go anywhere near to fix. Sometimes there’s a decent reason, something like:…

How Check Constraints MIGHT Improve Your Queries and Missing Index Requests

October 31, 2018

The more SQL Server knows about your data, the better your query plans can get. Say you’ve got an app that’s designed to store multiple companies in a single database – but you don’t actually use it that way. All of the data in a given database is actually for the same company. We’ll use…

SUM, AVG, and arithmetic overflow

October 30, 2018

T-SQL

You Shoulda Brought A Bigger Int Sometimes you run a query, and everything goes fine. For a while. For example, if I run this query in the 2010 copy of Stack Overflow, it finishes pretty quickly, and without error. Transact-SQL SELECT u.Id, u.DisplayName, SUM(p.Score) AS SumPostScore, AVG(c.Score) AS SumCommentScore FROM dbo.Users AS u JOIN dbo.Posts…

Filtered Indexes vs Parameterization (Again)

October 26, 2018

At First I Was Like… This won’t work at all, because parameters are a known enemy of filtered indexes. But it turns out that some filtered indexes can be used for parameterized queries, whether they’re from stored procedures, dynamic SQL, or in databases with forced parameterization enabled. Unfortunately, it seems limited to filtering out NULLs…

Batch Mode For Row Store: Does It Fix Parameter Sniffing?

October 25, 2018

Batch Mode, Execution Plans, Indexing

Snorting The Future SQL Server 2019 introduced batch mode over row store, which allows for batch mode processing to kick in on queries when the optimizer deems it cost effective to do so, and also to open up row store queries to the possibility of Adaptive Joins, and Memory Grant Feedback. These optimizer tricks have…

Batch Mode For Row Store: What Does It Help With?

October 24, 2018

Batch Mode, Memory Grants