Development

Home
Development

Finding Blocked Processes and Deadlocks using SQL Server Extended Events

Last Updated March 14, 2019

Locking, Blocking, and Isolation Levels, SQL Server

A lot of folks would have you think that Extended Events need to be complicated and involve copious amounts of XML shredding and throwing things across the office. I’m here to tell you that it doesn’t have to be so bad. Collecting Blocked Process Reports and Deadlocks Using Extended Events When you want to find…

UPDATE STATISTICS: the Secret IO Explosion

Last Updated February 13, 2017

Kendra Little

Index Maintenance, SQL Server, Statistics

I first knew something was up when I looked at the job history for a simple maintenance plan. It had two steps: Rebuild all the indexes in the database – this took 10 minutes each night. Update statistics – this took 2-3 hours each night. What was going on? Statistics in SQL Server are small,…

Exploring the Magic of the Plan Cache

Last Updated February 13, 2017

Jeremiah Peschka

Execution Plans, SQL Server

The plan cache holds a lot of secrets about what’s going on inside SQL Server. In the First Responder Kit we shared one of our plan cache scripts to find the top resource consuming queries. That query works well, but over time we’ve added some additional functionality to the query. I figured it was time to share the…

How to Cache Stored Procedure Results

Last Updated February 13, 2017

Brent Ozar

SQL Server, T-SQL

Say you run an online store, and on each item’s page, you need to show related items that people purchased. Let’s take an Amazon page for my favorite rechargeable AA batteries: Frequently Bought Together In a perfect world, we would cache this data in the web/app tier – but back here in the real world,…

RECOMPILE Hints and Execution Plan Caching

Last Updated April 2, 2021

Kendra Little

Execution Plans, Parameter Sniffing

The DBA version of an XKCD classic. When you identify that parameter sniffing is a problem, you need to test whether implementing ‘recompile’ hints will help plan quality. (Not sure what parameter sniffing is? Learn from this blog post or this 50 minute free video.) You must decide: what hint or command do you use,…

What Developers Need to Know About SQL Server

Last Updated February 13, 2017

Brent Ozar

Development, SQL Server

What Developers Need to Know About Designing Databases Understand that changing database design at a later date is harder than changing code. Getting it right the first time is important #dellsql — Gail Shaw (@SQLintheWild) October 24, 2013 One of my favorite tips, and I never thought of it that way. Code is agile, and…

#SQLPASS #Summit2013 Professor DeWitt Keynote

Last Updated August 11, 2019

Brent Ozar

#SQLPass, In-Memory OLTP (Hekaton), SQL Server

This morning at the PASS Summit, we have the pleasure of listening to Professor David DeWitt talk about Hekaton internals. I’m actually not going to liveblog this – I’m going to sit back and take in the presentation as an attendee because it’s going to be so damn good, and I’m not going to be…

High Availability Doesn’t Fix Poor Performance

Last Updated February 13, 2017

Jes Schultz

Development, High Availability

Imagine this: you have a database, and it’s on a SQL Server instance, which is on a server. That server may be physical or it may be virtualized. The database, or the instance, or the server, has a high availability solution implemented. It might be failover clustering; it might be synchronous mirroring; it might be…

Query Plans: Memory Grants and High Row Estimates

Last Updated January 8, 2019

Kendra Little

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

143

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…

Development

Save your cart?