Development

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: 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 preview 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. Using the…

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

Execution Plans, Indexing

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

Execution Plans, Indexing

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

SQL Server 2019 Introduces something pretty cool: the ability for row store indexes to be processed in batch mode. Batch mode is, of course, the preferred processing realm of column store indexes. If you’re still in the dark about column store, you might wanna start by reading up on Niko Neugebauer and Joe Obbish’s blogs about it.…

How to See Execution Plans in Azure Data Studio

October 23, 2018

Azure Data Studio, Execution Plans

Azure Data Studio is Microsoft’s cross-platform tool for T-SQL developers. It’s still relatively young, so it has some missing areas of functionality. When you run DMV queries to look at query plans, they show up as XML, which isn’t very friendly. For example, here I’m running sp_BlitzCache in my Azure SQL DB: It works, but…

Is Cost Threshold for Parallelism Measured in Seconds?

October 19, 2018

SQL Server automatically chooses when to divide your query’s work across multiple CPU cores. It makes that decision based on your query’s cost. To see it, let’s throw 1,000,000 tiny rows in a table: Transact-SQL CREATE TABLE dbo.Timeless(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing VARCHAR(20)); INSERT INTO dbo.Timeless (Stuffing) SELECT TOP 1000000 'Stuff' FROM sys.all_columns…

Skewing Parallelism For Fun And Profit

October 17, 2018

What Is Skewed Parallelism? When queries go parallel, some assumptions get made: There’s enough work to keep multiple threads busy Each thread will get an equal amount of work to do The ‘equal amount of work’ part is particularly important, because in a parallel plan, each thread gets an equal share of memory up front.…

Using NOLOCK? Here’s How You’ll Get the Wrong Query Results.

October 16, 2018

Locking, Blocking, and Isolation Levels

Slapping WITH (NOLOCK) on your query seems to make it go faster – but what’s the drawback? Let’s take a look. We’ll start with the free StackOverflow.com public database – any one of them will do, even the 10GB mini one – and run this query: Transact-SQL UPDATE dbo.Users SET WebsiteUrl = 'https://www.BrentOzar.com/'; 12 UPDATE…

Index Tuning Week: Missed Missing Index Opportunities

October 12, 2018

Execution Plans, Indexing

A Reminder With a new baby in the house, I often find myself comparing her to various things. Barnacles. Ham. Pugs. No, I swear, I love her. SQL Server Has A Baby, Too That baby is the optimizer, and when it starts crying, it uses missing index requests to tell you something is wrong. Usually.…

Index Tuning Week: Getting Blocking? Play “Hot or Not.”

October 11, 2018

Deadlocks, Indexing

This week, we’re all about tuning indexes. So far, we’ve covered Brent’s 5 and 5 Rule and The D.E.A.T.H. Method. Today, let’s talk about reducing blocking and deadlocking. Normally, when we think about the causes of blocking or deadlocks, we use badly written queries that modify different tables in the wrong order, resulting in a Mexican…

Pour One Out for Database Diagrams, and Good Riddance

October 7, 2018

Development

Update 2019/06/11: they’re back! Microsoft heard your pleas, and Database Diagrams are back in SSMS 18.1. On the left hand side, you see SQL Server Management Studio 17.9. On the right side, 18.0. Something’s missing in the new 18.0: Rest in peace, Database Diagrams SSMS 17.9 on the left has Database Diagrams at the top.…

Making The Query Plan Profile Hint More Useful

October 5, 2018

Way Back When I blogged about the new USE HINT that logs query plans that use it to a new XE session. The use hint and session both share the same name — query_plan_profile. There are currently some rather unfortunate limitations to the event in the GUI. Right now, there’s no query plan tab so you…

SQL Server Management Studio 18’s Execution Plans Will Change The Way You Look At Plan Tuning

October 4, 2018

The preview of the next version of SQL Server Management Studio is out, and it has a radical improvement to query plans that will shock and amaze you. It’s best to just show you: Estimated vs actual rows You can see the estimated and actual number of rows right there on the query plan just…

We Need to Talk About the Warnings In Your Query Plans.

October 3, 2018