Development

Home
Development

15 Reasons Your Query Was Fast Yesterday, But Slow Today

April 18, 2019

SQLSaturday Israel 2019 speaker shirt In rough order of how frequently I see ’em: There are different workloads running on the server (like a backup is running right now.) You have a different query plan due to parameter sniffing. The query changed, like someone did a deployment or added a field to the select list.…

Index scans aren’t always bad, and index seeks aren’t always great.

April 16, 2019

Brent Ozar

Execution Plans, Indexing

Somewhere along the way in your career, you were told that: Index seeks are quick, lightweight operations Table scans are ugly, slow operations And ever since, you’ve kept an eye on your execution plans looking for those performance-intensive clustered index scans. When you see ’em, you go root ’em out, believing you’ve got a performance…

Finding Froid’s Limits: Testing Inlined User-Defined Functions

April 11, 2019

Brent Ozar

Execution Plans, SQL Server 2019, T-SQL

This week, I’ve been writing about how SQL Server 2019’s bringing a few new features to mitigate parameter sniffing, but they’re more complex than they appear at first glance: adaptive memory grants, air_quote_actual plans, and adaptive joins. Today, let’s talk about another common cause of wildly varying durations for a single query: user-defined functions. Scalar…

1 query, 3 parameters, 3 different plans

Parameter Sniffing in SQL Server 2019: Adaptive Joins

April 10, 2019

Brent Ozar

Adaptive Joins, SQL Server 2019

So far, I’ve talked about how adaptive memory grants both help and worsen parameter sniffing, and how the new air_quote_actual plans don’t accurately show what happened. But so far, I’ve been using a simple one-table query – let’s see what happens when I add a join and a supporting index: Transact-SQL CREATE INDEX IX_OwnerUserId_Includes ON…

Parameter Sniffing in SQL Server 2019: Air_Quote_Actual Plans

April 9, 2019

Brent Ozar

Execution Plans, Memory Grants, SQL Server 2019

No Comments

My last post talked about how parameter sniffing caused 3 problems for a query, and how SQL Server 2019 fixes one of them – kinda – with adaptive memory grants. However, the post finished up by talking about how much harder performance troubleshooting will be on 2019 because your query’s memory grant is based on…

Parameter Sniffing in SQL Server 2019: Adaptive Memory Grants

April 8, 2019

Brent Ozar

Memory Grants, SQL Server 2019

This week, I’m demoing SQL Server 2019 features that I’m really excited about, and they all center around a theme we all know and love: parameter sniffing. If you haven’t seen me talk about parameter sniffing before, you’ll probably wanna start with this session from SQLDay in Poland. This week, I’m going to be using…

Should we use stored procedures or queries built in the app?

March 27, 2019

Brent Ozar

T-SQL

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”

March 20, 2019

Brent Ozar

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.

February 21, 2019

Brent Ozar

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

February 8, 2019

Brent Ozar

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

January 24, 2019

Brent Ozar

T-SQL

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

January 23, 2019

Brent Ozar

T-SQL

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

January 15, 2019

Brent Ozar

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

January 3, 2019

Erik Darling

Execution Plans, GroupBy Conference, Indexing, Videos

No Comments

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?

January 3, 2019

Erik Darling

Execution Plans, Indexing

No Comments

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?

December 27, 2018

Erik Darling

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

December 21, 2018

Brent Ozar

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?

December 20, 2018

Brent Ozar

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

December 19, 2018

Erik Darling

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…

Comparison Review: Microsoft SSDT vs Redgate SQL Source Control

December 17, 2018

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…