Development

How Scalar User-Defined Functions Slow Down Queries

Last Updated November 30, 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

Last Updated November 30, 2020

Brent Ozar

Statistics

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.

Last Updated November 30, 2020

Brent Ozar

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

Last Updated November 30, 2020

Brent Ozar

T-SQL

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.

Last Updated November 30, 2020

Brent Ozar

Statistics

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

Brent Ozar

T-SQL

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

Brent Ozar

T-SQL

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

Brent Ozar

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

Brent Ozar

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

Brent Ozar

T-SQL

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

Brent Ozar

Development

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…

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

Last Updated September 8, 2020

Brent Ozar

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

Brent Ozar

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

Brent Ozar

T-SQL

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

Brent Ozar

Statistics

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

Brent Ozar

Development

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

Brent Ozar

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

Brent Ozar

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

Brent Ozar

Development

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

Brent Ozar

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…