Development

Execution Plans, SQL Server, T-SQL

In most coding languages, functions are often-used blocks of code that can be reused from multiple locations, leading to less code – and cleaner code. SQL Server also lets us create functions that can be used the same way. They are reusable blocks of code that can be called from multiple locations. So, if you…

Comparing Estimated and Actual Execution Plans in SQL Server

Last Updated May 12, 2019

Execution Plans, SQL Server

Let’s say you want to have an addition built on your house. You contact a contractor, who comes to your house, looks at your lot, looks at what exists, and asks you questions about exactly what you want. He then gives you an estimate of the work – approximately how long he thinks it will…

Stabilizing Execution Plans: Plan Guides and NORECOMPUTE

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

What could go wrong? Sometimes you end up in a good plan / bad plan situation: an important query runs just fine most of the time. The query is parameterized, a good execution plan gets re-used, everything is cool. But sometimes, a “bad plan” gets compiled and starts to be reused. This is “bad” parameter sniffing. “Bad plans” can…

Capturing Deadlocks in SQL Server

Last Updated February 13, 2017

Locking, Blocking, and Isolation Levels, SQL Server

What’s a deadlock? Well, let’s say there’s a fight going on between Wonder Woman and Cheetah, and, in the same room, a fight between Batman and Mr. Freeze. Wonder Woman decides to help Batman by also attempting to throw her lasso around Mr. Freeze; Batman tries to help Wonder Woman by unleashing a rope from…

Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)

Last Updated May 26, 2020

Parameter Sniffing, SQL Server

There’s an important rule for tuning stored procedures that’s easy to forget: when you’re testing queries from procedures in SQL Server Management Studio, execute it as a stored procedure, a temporary stored procedure, or using literal values. Don’t re-write the statemet you’re tuning as an individual TSQL statement using local variables! Where it goes wrong…

Five Reasons a Production DBA Should Learn to Read Execution Plans

Last Updated August 7, 2019

Execution Plans

While chatting with attendees before my Don’t Fear the Execution Plan webcast, a question was posed: “As a DBA who mainly worries about backups, index fragmentation and checkdb, should I also be looking at execution plans?” YES! Here’s why. Performance is important. A production DBA’s main focus should be the safety and security of the…

Are Table Variables as Good as Temporary Tables in SQL 2014?

Last Updated February 13, 2017

In-Memory OLTP (Hekaton), SQL Server, TempDB

There’s a couple of new features in SQL Server 2014 that provide options for how you work with temporary objects. Will inline index creation or memory optimized temporary tables forever change the way you code? Let’s take a look! Inline Index Creation SQL Server 2014 brings us a TSQL improvement called “inline specification of CLUSTERED…

Refactoring T-SQL with Windowing Functions

Last Updated February 14, 2017

Doug Lane

SQL Server, T-SQL, Videos

You’ve been querying comparative numbers like Year To Date and Same Period Last Year by using tedious CTEs and subqueries. Beginning with SQL Server 2012, getting these numbers is easier than ever! Join Doug for a 30-minute T-SQL tune-up using window functions that will cut down dramatically on the amount of code you need to…

Finding One Problem Query With Extended Events

Last Updated February 13, 2017

Jeremiah Peschka

Execution Plans, SQL Server

It’s easy to find problem queries on your SQL Server after they’ve happened. You can open your favorite monitoring tool or look in the plan cache. Those options are great for finding things after they’ve happened, but what if you want to find problems as they happen? Server Side Trace You could set up a…

Finding Blocked Processes and Deadlocks using SQL Server Extended Events

Last Updated March 14, 2019

Jeremiah Peschka

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

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

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

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

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

#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

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

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