Product has been added to your cart.

Development

Statistics Aren’t Guarantees: SQL Server Still Checks.

Last Updated November 30, 2020

When I’m reviewing plans with folks, I get this response a lot: But SQL Server should know there’s no data that matches! It has statistics! Statistics are guideposts, not guarantees. We’ll start with the Stack Overflow database and put an index on LastAccessDate, which also creates a statistic on that column. We’ll check the min…

If Your Trigger Uses UPDATE(), It’s Probably Broken.

Last Updated September 8, 2022

In yesterday’s blog post about using triggers to replace computed columns, a lively debate ensued in the comments. A reader posted a trigger that I should use, and I pointed out that their trigger had a bug – and then lots of users replied in saying, “What bug?” I’ll demonstrate. We’ll take the Users table…

Using Triggers to Replace Scalar UDFs on Computed Columns

Last Updated October 20, 2020

Your database is riddled with computed columns whose definition includes a scalar user-defined function. Even up to & including SQL Server 2019, which boasts faster scalar function processing, any table that includes a scalar function cause all access to that table to go single-threaded. In that case, a trigger can actually be a great replacement.…

Free Webcast on Thursday: Avoiding Deadlocks with Query Tuning

Last Updated October 11, 2020

Locking, Blocking, and Isolation Levels

To fix blocking & deadlocks, you have 3 tools: Have enough indexes to make your queries fast, but not so many that they slow down delete/update/insert operations. (I cover that in the Mastering Index Tuning class.) Use the right isolation level for your app’s needs. (I cover that in the Mastering Server Tuning class.) Tuning…

I Would Love a “Cost Threshold for Recompile” Setting.

Last Updated May 5, 2022

Parameter Sniffing

I’m tired of sniffing your parameters. In environments where complex queries can get bad plans due to parameter sniffing, it would help to say that all queries with an estimated cost over X should be recompiled every time they run. For example, in environments where most of my workload is small OLTP queries, I’m fine…

How to Create a Table with a Partitioned Clustered Columnstore Index

Last Updated September 16, 2020

If you need to create a table and you want it to be partitioned right from the start, AND you want it to have a clustered columnstore index, here’s a creation T-SQL example: CREATE TABLE dbo.MyTableName( Id int IDENTITY(1,1), SalesDate datetime NOT NULL, INDEX MyIndexName CLUSTERED COLUMNSTORE ) ON ps_MyPartitionFunction(SalesDate); GO 123456 CREATE TABLE dbo.MyTableName(…

“Not to write any procedure over 50 lines”

Last Updated September 10, 2020

In Joe Celko’s Stairway to Database Design series, he writes: The rules of thumb for T-SQL are not to write any procedure over 50 lines (one page) This seems so radical at first, but it has precedence in NASA’s 10 Rules for Developing Safety-Critical Code: Avoid complex flow constructs, such as goto and recursion. All loops must have fixed…

Bonfire of the vanities

“UPDATE, INSERT, and DELETE are not normally processed in parallel”

Last Updated September 8, 2020

Execution Plans

Years ago, when troubleshooting performance, I stumbled across this Microsoft documentation on parallel query processing that says: Certain types of statements cannot be processed in parallel unless they contain clauses, however. For example, UPDATE, INSERT, and DELETE are not normally processed in parallel even if the related query meets the criteria. But if the UPDATE…

Watch Brent Tune Queries at SQLSaturday Oslo

Last Updated August 29, 2020

Execution Plans, Videos

This morning, I had the honor of presenting virtually at SQLSaturday Oslo. The session title, Watch Brent Tune Queries, explains it all. Enjoy! In the session, I used the 2018-06 Stack Overflow database, but any recent full-sized copy (not the 2010-2013 copies) will work. Here’s the query I worked with: /* Watch Brent Tune Queries:…

Half Of You Don’t Understand Variables and Transactions.

Last Updated August 20, 2020

Do table variables and variables respect transactions? If I set the value of a variable during a transaction, and I roll it back, what happens? DECLARE @MySalary INT = 100000; BEGIN TRAN SET @MySalary = @MySalary * 2; ROLLBACK; SELECT @MySalary; 1234567 DECLARE @MySalary INT = 100000; BEGIN TRANSET @MySalary = @MySalary * 2;ROLLBACK; SELECT @MySalary; I…

Explaining SQL Server Statistics with Playing Cards

Last Updated August 17, 2020

When you run a query, how does SQL Server estimate the number of rows? I’ll explain in this half-hour video demo using playing cards, then showing the same issues in SQL Server Management Studio: Here’s the demo script I use in the video. To learn more, check out our free statistics class on YouTube.

How Do I Know If My Query Is Good Enough for Production?

Last Updated August 12, 2020

When I ask that question, here are some of the things I think about: How many times am I going to run it? Is it a one-off task, or is it going to run thousands of times per second on the front page of a popular web site? What time of the day/week will it…

Date Tables are Great for Users, but Not So Great for Performance

Last Updated August 5, 2020

Execution Plans, Videos

Date tables help users filter data based on day of week, holiday, this year vs last year, and other kinds of filters that business users usually wanna add to their reports. If you haven’t used a date table before, here are a couple of primers on how to create one and use it: Creating a…

[Video] The Top 10 Developer Mistakes That Won’t Scale on Microsoft SQL Server

Last Updated July 30, 2020

Development, Videos

You’re a C# or Java developer building an application that stores data in Microsoft SQL Server, and you want to avoid common pitfalls that will cause you scalability problems down the road. In this session from my live stream, you’ll learn why you probably want to avoid triggers, SELECT *, user-defined functions, dynamic SQL, big…

A One-Slide Summary of the Differences Between TDE and Always Encrypted

Last Updated July 31, 2020

The folks on Twitter liked this, so sharing it here: It’s a one-slide summary from a SQL Critical Care client’s deck, so obviously it’s abridged, but I think it does a pretty good job of summing things up. Some highlights of the conversation: A solid 20 minutes of my talk from last year’s Summit, summed…

Remember “Nothing Stops a Hekaton Transaction?” Yeah, About That.

Last Updated July 29, 2020

In-Memory OLTP (Hekaton)

When In-Memory OLTP first came out, Microsoft liked to go around bragging at conferences that “Nothing stops a Hekaton transaction.” I remember hearing that and kinda chuckling at the time, and I was reminded of it by a recent client who was running into blocking issues with…yep, Hekaton. To reproduce it: Do a large insert…

The 201 Buckets Problem, Part 2: How Bad Estimates Backfire As Your Data Grows

Last Updated July 7, 2020

In the last post, I talked about how we don’t get accurate estimates because SQL Server’s statistics only have up to 201 buckets in the histogram. It didn’t matter much in that post, though, because we were using the small StackOverflow2010 database. But what happens as our data grows? Let’s move to a newer Stack…

The 201 Buckets Problem, Part 1: Why You Still Don’t Get Accurate Estimates

Last Updated July 7, 2020

I’ll start with the smallest Stack Overflow 2010 database and set up an index on Location: Transact-SQL USE StackOverflow2010 GO CREATE INDEX Location ON dbo.Users(Location); GO SELECT COUNT(*) FROM dbo.Users; GO 123456 USE StackOverflow2010GOCREATE INDEX Location ON dbo.Users(Location);GOSELECT COUNT(*) FROM dbo.Users;GO There are about 300,000 Users – not a lot, but enough that it will…

You Can Disable Parameter Sniffing. You Probably Shouldn’t.

Last Updated June 27, 2020

Parameter Sniffing

During my parameter sniffing classes, people get a little exasperated with the complexity of the problem. Parameter sniffing is totally hard. I get it. At some level, it’d be great to just hit a magic button and make the whole thing go away. So inevitably, somebody will ask, “What about the database-level setting called Parameter…

Sit Down. We Need to Talk About Your Scalar Functions.

Last Updated June 25, 2020

Development, SQL Server 2019

Thanks for coming today. I apologize for baiting you in here with a picture of toilet paper. That’s not even in the US – that’s from my Iceland trip, before I flew back here to the US. Look, we all make mistakes. That’s not the point of this post. You see, I’ve been reading this…