Development

Query Plans: Memory Grants and High Row Estimates

Last Updated January 8, 2019

Execution Plans, Memory Grants, SQL Server

SQL Server needs to have a reasonably correct estimate of how many rows your query is going to handle. If it doesn’t, things can go terribly, terribly wrong. Most often, the problem is that SQL Server under estimates its work. In this case, the query optimizer thinks it has just a little bit of work…

SSD RAID Load Testing Results from a Dell PowerEdge R720

Last Updated December 9, 2018

Brent Ozar

Hardware, Load Testing, Storage

159

We’ve got a client that does big batch jobs every day, loading hundreds of gigabytes of data or more in short bursts. They were frustrated with slow performance on the batch jobs, and after we performed our SQL Critical Care® with ’em, it was really clear that their hardware was the bottleneck. They were using…

The Elephant and the Mouse, or, Parameter Sniffing in SQL Server

Last Updated January 5, 2019

Jes Schultz

Development

Imagine you work for a zoo, and you need to ship an animal to another zoo. If you are told one will be a mouse and one will be an elephant, you will need to handle them differently. The Detroit Zoo used a moving van to move two elephants, while mice can be put in…

Optimize for… Mediocre?

Last Updated May 26, 2020

Kendra Little

Parameter Sniffing, SQL Server

Some query hints sound too good to be true. And, unfortunately, usually they aren’t quite as magical as they might seem. Frustration with unpredictable execution times People often learn about parameter sniffing when query execution times stop being predictable. Occasionally you’ll hear about a stored procedure taking much longer than normal, but the next time…

7 Things Developers Should Know About SQL Server

Last Updated April 3, 2022

Brent Ozar

Development, SQL Server

163

Hi. I’m a former developer who’s moved into database administration, and here’s what I wish somebody would have told me when I got started. 7. SQL functions rarely perform well. Good developers like to reuse code by putting it into functions, and then calling those functions from multiple places. That’s a great practice in the…

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide

Last Updated September 26, 2022

Kendra Little

Locking, Blocking, and Isolation Levels, SQL Server

145

How to change isolation levels without losing your marbles. A client said the coolest thing to me the other day. He said, “We talked before about why we would want to start using optimistic locking in our code. How do we get there?” If you’re not a SQL Server geek, that comment probably doesn’t even…

Introducing the SQL Server Plan Cache (and a Better sp_Blitz®)

Last Updated February 13, 2017

Brent Ozar

Execution Plans, SQL Server, Videos

When you send your beautifully hand-crafted organic T-SQL statement to SQL Server, the database engine takes a moment to appreciate the poetry of your work. Before it turns your request into results, it has to build an execution plan. SQL Server considers: Which tables it needs to join together What subqueries it needs to execute…

Simulating OLTP Queries: How to Run Load Tests Against SQL Server with HammerDB

Last Updated February 14, 2017

Kendra Little

Load Testing, SQL Server

Do you have a SQL Server that’s not getting enough action? Maybe you have a new piece of production or lab hardware that you’d like to benchmark, to see how it compares with other gear in your environment. Or maybe you just want to run some activity against your own test SQL Server instance to…

The Art of the Execution Plan

Last Updated June 18, 2020

Brent Ozar

Execution Plans

There’s beauty everywhere – even in SQL Server. I was just staring at this plan when I was struck by its beauty: Stored Procedure Part 1 Oh, sure, it’s ugly, and I recoiled in horror at first, but when you get over that reaction, there’s an amazing amount of stuff there. Someone – probably many…

My 3 Favorite Connection String Tips

Last Updated February 9, 2017

Brent Ozar

Always On Availability Groups, Development

Connection strings – boring topic, right? Well, it turns out there’s a few things that can save your behind. Tip 1: Build your application with 3 connection strings. If you want to scale, write your application from the start with 3 different connection strings: Writes and real-time reads Reads that can tolerate data older than…

Five Ways to Fight Blocking Video

Last Updated February 13, 2017

Kendra Little

Locking, Blocking, and Isolation Levels, SQL Server

What do *you* do when your database has a problem with blocking? Most people reach for the “NOLOCK” hint. Bad news: that may introduce other problems, and it doesn’t truly resolve the root problem in your application code. In this video Kendra Little summarizes five big-picture application changes that fight blocking long term. This 300…

The Difficulty with Deadlocks

Last Updated February 13, 2017

Jeremiah Peschka

Locking, Blocking, and Isolation Levels

Deadlocks can kill an application’s performance. Users will complain about the app being slow or broken. Developers will ask the DBA to fix the problem, DBAs will push the problem back on developers. The next thing you know, the office looks like Lord of the Flies. What is a Deadlock? A deadlock occurs when two…

Windowing Function Examples for SQL Server

Last Updated November 26, 2020

Jeremiah Peschka

SQL Server, T-SQL

Aggregations and grouping can be a pain in the rear for the novice SQL developer. Way back in 2003, the ANSI/ISO standards people figured this out and added windows and ranking functions to the standard. In 2005, Microsoft added a few of these functions (ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()) as well as the OVER() clause…

SQL Server Locking and You!

Last Updated February 13, 2017

Jeremiah Peschka

Locking, Blocking, and Isolation Levels, SQL Server

Did you know that SQL Server’s locking has a name? It’s called two-phase locking. If we’re really getting specific about it, SQL Server uses what’s called strong strict two-phase locking or SS2PL. We’ll get there in a few minutes, right now we’re going to take a look at what makes up two-phase locking. But First,…

Well, it was good, but I dunno about amazing.

Seeks, Scans, and Statistics in the Grocery Store

Last Updated July 14, 2019

Brent Ozar

Indexing, T-SQL

Building an execution plan is a lot like going shopping. Before I leave the house, I need to think about a few things: How many items do I need? I take a glance at my grocery list to see roughly how many things I’m shopping for. What stores am I going to visit? I can’t…

It’s a Lock: Due Diligence, Schema Changes, and You

Last Updated February 13, 2017

Kendra Little

Locking, Blocking, and Isolation Levels

It’s morning standup, and someone says, “It’s no big deal, we just need to add a couple of columns. It’s already in the build, it works fine.” The next time this happens, stop and say, “Let’s take a closer look at that.” When you write schema changes for a relational database, take the time to…

Sargability: Why %string% Is Slow

Last Updated December 29, 2017

Brent Ozar

T-SQL

People love to search. Google has us addicted to fast, easy search functions. Users expect every application to have a built-in blazing-fast search functionality. To pull that off, developers build search queries that let users enter a string, and we ask SQL Server to find matches. For example, say our users need to find some…

Why Use Schemas?

Last Updated February 14, 2017

Brent Ozar

Development

100

The ever-insightful Buck Woody (Blog – @BuckWoody) wrote a post telling you to use database schemas. He writes: “But I still see a lot of applications, developed by shops I know as well as vendors, that don’t make use of a Schema. Everything is piled under dbo…. But if you’ll use them properly you can…

Tuning SQL Server Transactions – and Meetings

Last Updated February 13, 2017

Brent Ozar

Locking, Blocking, and Isolation Levels

Meetings are like bad blocking queries – they tie up precious resources and stop other people from getting work done. Today, let’s talk about how to optimize both of ’em by doing homework ahead of time and minimizing the number of objects in the transaction.

Simon Sabin’s talk on query performance

Last Updated March 10, 2021

Brent Ozar

Execution Plans

At #SQLbits, Simon Sabin (Twitter) talked about car crash queries: queries that suddenly have bad performance out of nowhere, yet if you copy/paste them into SQL Server Management Studio, they run quickly. The culprit is often parameter sniffing: the engine looks at the query and builds a plan, but if the parameters in the WHERE…