Product has been added to your cart.

Development

Eyes up here, kid

How to Set & Get the Next ID Without Serializable Isolation

November 25, 2020

Locking, Blocking, and Isolation Levels

Say for some reason you can’t use identity columns or sequences, or maybe your application was built a long, long time ago – and you’ve got a table with a list of IDs to use next: DROP TABLE IF EXISTS dbo.Ids; CREATE TABLE dbo.Ids (TableName NVARCHAR(255) PRIMARY KEY CLUSTERED, CurrentId BIGINT); INSERT INTO dbo.Ids (TableName,…

How to Insert Rows and Get Their Identity Values with the OUTPUT Clause

November 24, 2020

Say you’ve got a two-step process where you’re: Inserting rows into a table that has an identity column, then Querying that table to figure out what identities you got There’s a a faster way that doesn’t require hitting the table twice: the OUTPUT clause. I’ll demonstrate it with the Badges table from the Stack Overflow…

Finding the One Query to Tune in a Multi-Query Batch

November 23, 2020

Execution Plans

When you have a stored procedure or application query that has multiple queries inside of it, how can you tell which query to focus on? For starters, do NOT believe the percentages you see in execution plans. Let’s take a classic performance tuning issue: scalar functions. I’ll start with the Stack Overflow database – if…

When You’re Troubleshooting Blocking, Look at Query #2, Too.

November 19, 2020

Locking, Blocking, and Isolation Levels

When I’m troubleshooting a blocking emergency, the culprit is usually the query at the head of a blocking chain. Somebody did something ill-advised like starting a transaction and then locking a whole bunch of tables. But sometimes, the lead blocker isn’t the real problem. It’s query #2. Here’s a sample scenario: A long-running select with…

How Scalar User-Defined Functions Slow Down Queries

November 18, 2020

When your query has a scalar user-defined function in it, SQL Server may not parallelize it and may hide the work that it’s doing in your execution plan. To show it, I’ll run a simple query against the Users table in the Stack Overflow database. SELECT TOP 100 DisplayName, Location, Reputation, Id FROM dbo.Users ORDER…

How Bad Statistics Cause Bad SQL Server Query Performance

November 17, 2020

SQL Server uses statistics to guess how many rows will match what your query is looking for. When it guesses too low, your queries will perform poorly because they won’t get enough memory or CPU resources. When it guesses too high, SQL Server will allocate too much memory and your Page Life Expectancy (PLE) will…

MAXDOP Isn’t Really MAXDOP. It’s More Like DOP.

November 10, 2020

Execution Plans

Here’s how Books Online describes the Max Degree of Parallelism setting: You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. And here’s what the SQL Server 2019 setup screen says: When an instance of SQL Server runs on a computer that has more…

Why Full Text’s CONTAINS Queries Are So Slow

November 7, 2020

SQL Server’s full text search is amazing. Well, it amazes me at least – it has so many cool capabilities: looking for prefixes, words near each other, different verb tenses, and even thesaurus searches. However, that’s not how I see most people using it: I’ve seen so many shops using it for matching specific strings,…

Statistics Aren’t Guarantees: SQL Server Still Checks.

November 4, 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.

October 21, 2020

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

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

October 14, 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.

September 23, 2020

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

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”

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”

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

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.

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

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?

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…