Development

A client asked a great architecture question that deserved its own blog post: Should we use more “stored procedures” for select, insert, update, delete or should that just be sent as queries from the application? If you ask a junior database administrator where to put something, she’ll probably say “in the database” because that’s the…

How to fix the error “String or binary data would be truncated”

Last Updated January 18, 2020

Development

To fix this error, patch to SQL Server 2016 SP2, CU6 or newer (including SQL Server 2017), and then turn on trace flag 460. You can enable it at the query level or at the server level. First, let’s see the error happen: let’s create a table with small fields, and then try to insert…

You know what your deadlock graphs need? Animation.

Last Updated February 21, 2019

Deadlocks

In SQL Server Management Studio, plain ol’ deadlock graphs look like this: Two thumbs down, would not lock the dead again BOOOOO-RING. That’s why I prefer opening deadlock graphs in SentryOne Plan Explorer, which presents a much better visual in the form of a circle…a circle, let’s just stop there: Deadlock graph in SentryOne Plan…

[Video] An Introduction to GitHub for DBAs

Last Updated February 8, 2019

Development, Videos

Distributed source control is really intimidating: branches, pull requests, merges – will somebody just take my code, for crying out loud? Why does it have to be so complicated and involved? I’m with you: I hated GitHub. For years, I struggled with it, but I’ve come to a gradual truce. I’m not a GitHub pro…

Watch Brent Tune Queries: Fixing Nested Functions

Last Updated January 24, 2019

When I do performance tuning for clients, I really pride myself on making as few changes as possible in order to make a tremendous difference. I consider it a failure when I have to tell someone to rewrite a bunch of queries from scratch. However, there are some cases where I just can’t work around…

Tuning Dynamic SQL by Hand with Short Circuits

Last Updated January 18, 2019

When we think about building dynamic SQL, we usually think about a stored procedure like this that takes input parameters, builds a string, and then executes that string. Here’s a simple example: Transact-SQL CREATE OR ALTER PROC dbo.usp_SearchUsers @SearchDisplayName NVARCHAR(40) = NULL, @SearchLocation NVARCHAR(100) = NULL, @SearchReputation INT = NULL AS BEGIN DECLARE @StringToExecute NVARCHAR(4000);…

How to Load Test a Database Application

Last Updated January 14, 2019

Development

I stay in the nicest hotels It sounds really simple: Capture a bunch of production queries Replay those same queries over in a load test environment So what’s the big deal? Why is it so hard to find good guidance on how to pull this off? How hard can it be? Problem 1: many queries…

The Pleasure Of Presenting With Joe Obbish

Last Updated November 26, 2020

Execution Plans, GroupBy Conference, Indexing, Videos

Grouple On At the December 2018 GroupBy I had the honor of co-presenting with one of the smartest guys around, Joe Obbish. Here’s the video of our session, “The Problems With Optional Operators”, and a link to the resources. Enjoy! Resources: Slides and Demos

Why Do Some Indexes Create Faster Than Others?

Last Updated January 2, 2019

Execution Plans, Indexing

To warn you ahead of time, this post is an exploration without an answer (yet). There’s some interesting stuff in here, but no conclusions. If that’s not your kind of post, feel free to skip it. If it is, well, here goes nothin’… Wild, Wild Life Creating indexes is kind of a funny thing. By…

When Does SARGability Matter Most?

Last Updated August 9, 2019

Execution Plans, Indexing

!erehT iH I know what you’re thinking. Another post about how you should just never do this one thing and all your queries will magically end up faster. Just watch the video. For more reading, check out these posts: Sargability: Why %string% Is Slow Optional Parameters and Missing Index Requests Computed Columns: Reversing Data…

5 Ways to Change Execution Plans Without Tuning

Last Updated December 26, 2018

Execution Plans

A couple years back, I realized there were at least Ten Ways to Set MAXDOP. These days, as I write and tune queries, I realize there are an awful lot of ways to change the Cardinality Estimator (CE) and Query Optimizer, too. Let’s take a quick survey of five intriguing options. 1. Change your SQL…

Do Functions Stop You From Using Indexes?

Last Updated December 5, 2018

Indexing, T-SQL

Say I’ve got a function in my WHERE clause: Transact-SQL SELECT DisplayName FROM dbo.Users WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; 123 SELECT DisplayName FROM dbo.Users WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; If I have an index on DisplayName, will SQL Server use it? Sure: Function in the WHERE clause Even though SQL Server can’t seek to “Brent Ozar,” it will…

Query Store And Cruel Defaults

Last Updated December 19, 2018

Execution Plans, Query Store (QDS)

Gosh Darn Now, look, it’s no secret that I think Query Store is cool. Heck, I went and wrote sp_BlitzQueryStore so you can examine it similarly to how sp_BlitzCache looks at the plan cache. For a variety of reasons, though, we run into people who can’t or won’t turn it on, or who had to turn…

Sniffed Nulls and Magic Numbers

Last Updated May 26, 2020

Parameter Sniffing, SQL Server

I Sniff Your Milkshake Building off of A Simple Stored Procedure Pattern To Avoid, I wanted to talk about a similar one that I see quite often that is not nearly as clever as one would imagine. I goes something like this: If this variable is passed in as NULL, substitute it with something else.…

Comparison Review: Microsoft SSDT vs Redgate SQL Source Control

Last Updated November 14, 2020

Alex Yates

Development

Hi. I’m still not Brent. My name’s Alex, and I care about databases, DevOps and data protection. Last year I delivered a session on GroupBy all about how to do Database DevOps right, and back in May I wrote a blog post for BOU about how to screw it up. Now, dear reader, I’m going…

Never Judge A Query By Its Cost

Last Updated December 12, 2018

Execution Plans

Signs and Numbers When tuning queries, or even finding queries to tune, there’s a rather misguided desire to look for queries with a high cost, or judge improvement by lowering query cost. The problem is that no matter what you’re looking at, costs are estimates, and often don’t reflect how long a query runs for…

“Surely this one will get a clustered index scan.”

Last Updated December 7, 2018

Execution Plans

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

Last Updated November 29, 2018

Execution Plans, Indexing

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?

Last Updated November 29, 2018