Development

SQL Server 2016 SP1 Bug with Graphical Execution Plans

Last Updated February 13, 2017

Early adopters: if you’re rolling on SQL Server 2016 SP1, you may have noticed that when you click on query plans in sp_BlitzCache or sp_WhoIsActive, they sometimes render as ugly XML rather than the beautiful graphical showplan. Here’s a 3-minute video showing the bug: To work around it, save the XML plan as a file…

Index Scans: Not Nearly As Bad As You Might Think

Last Updated February 13, 2017

Brent Ozar

Execution Plans, SQL Server

Using our good old buddy the StackOverflow demo database, let’s look at the Users table. I want to get the first 10 users in the table ordered by Id, the primary clustered key, which is an identity field. Here’s the actual execution plan: https://www.brentozar.com/pastetheplan/?id=HyuSDxZye Ooo – clustered index scan – that’s bad, right? Not so…

What’s the Difference Between Estimated and Actual Execution Plans?

Last Updated January 8, 2019

Brent Ozar

Execution Plans, Memory Grants, SQL Server

I’m going to use the dbo.Users table in the StackOverflow demo database and run a pretty simple query: Transact-SQL SELECT * FROM dbo.Users WHERE DisplayName LIKE 'Brent%'; 123 SELECT *FROM dbo.UsersWHERE DisplayName LIKE 'Brent%'; First, hit Control-L in SSMS and get the estimated execution plan. Here it is: Estimated execution plan: https://www.brentozar.com/pastetheplan/?id=H1TeRlZke Click on the…

Initial Thoughts on 2016 SP1

Last Updated February 13, 2017

Erik Darling

In-Memory OLTP (Hekaton), Licensing, Partitioning, SQL Server

A really odd choice I’ve talked a bit about my annoyance with hardware limits for Standard Edition here and here. If you’re a bunch of SQL-savvy developers who understand the Enterprise Edition features that are now available in 2016 SP1, great. You’re a very small minority of shops. I’m talking about places like StackOverflow and…

A Little Fun With Math

Last Updated February 14, 2017

Erik Darling

SQL Server, T-SQL

I’ve never been much at math But I’ve always liked reading about it. It’s super interesting, and it always blows my mind. Like, guaranteed. Once in a while I’ll even try my hand at solving problems I read about in SQL. Not because SQL is a particularly good language for it; but just because sometimes…

Using Plan Guides to Remove OPTIMIZE FOR UNKNOWN Hints

Last Updated May 26, 2020

Brent Ozar

Execution Plans, Parameter Sniffing, SQL Server

Say you’ve got an application that has tons of OPTIMIZE FOR UNKNOWN hints in the T-SQL, and you’re getting bad query plans. We’re going to use the same StackOverflow query (and the same index on Reputation) that I demoed in the post Why Is This Query Sometimes Fast and Sometimes Slow? This technique produces a…

SQL Server 2016 Standard Edition Now Has Many Enterprise Edition Features.

Last Updated February 13, 2017

Brent Ozar

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

Starting with today’s release of SQL Server 2016 Service Pack 1, Standard Edition now has a lot more of the features of Enterprise Edition. Here’s the list from Microsoft’s announcement post: Performance features – in-memory OLTP (Hekaton), in-memory columnstore, operational analytics Data warehousing features – partitioning, compression, CDC, database snapshots Some security features – Always Encrypted,…

Why Is This Query Sometimes Fast and Sometimes Slow?

Last Updated May 26, 2020

Brent Ozar

Execution Plans, Parameter Sniffing, SQL Server

You swear you didn’t change anything, but all of a sudden the SQL Server is going doggone slow. What happened? Parameter sniffing might be the problem, and to explain it, let’s see how it works. I’m going to use the StackOverflow database – particularly, the Users table that I demo in How to Think Like the…

Why You Should Test Your Queries Against Bigger Data

Last Updated February 14, 2017

Erik Darling

Load Testing, SQL Server

I Like Testing Things Especially Brent’s patience. And I like watching people test things. SQL Server is sort of perfect for that. You make some changes, you hit f5, and you wait for something to finish. If it’s faster, you ship it. Right? I mean, we all know you just added NOLOCK hints anyway. A…

Silent Demo: The Plan Cache

Last Updated November 16, 2017

Brent Ozar

Execution Plans, sp_BlitzCache, Videos

Shhh, and pay close attention. In 76 seconds, I’ll teach you something intriguing about the way SQL Server caches execution plans:

PasteThePlan Update: See the Most Recently Pasted Plans

Last Updated February 13, 2017

Brent Ozar

Execution Plans, PasteThePlan.com

Just another terrifying query PasteThePlan.com lets you paste in execution plans and get a link to share them with other people. In our company chat room, we get an alert whenever a new plan comes in, and I’m not ashamed to admit that we’re geeky enough that we usually jump straight into it and start…

Searching Strings in SQL Server is Expensive

Last Updated June 25, 2018

Brent Ozar

Execution Plans, Licensing

In the classic spirit of my How to Think Like the Engine class, let’s go take a look at the StackOverflow.Users table and find all the users named Brent. There’s a DisplayName field, and I’m going to be querying that a lot in this blog post, so I’ll go ahead and create an index on…

Set Statistics… Profile?

Last Updated February 13, 2017

Erik Darling

Development

Forgotten, But Not Gone Two of the best built-in tools to figure out if your query tuning efforts are headed in the right direction are SET STATISTICS TIME ON and SET STATISTICS IO ON. If you’re really fancy, you can use SET STATISTICS TIME, IO ON. By fancy I mean lazy. By lazy I mean…

Are You Load Testing for Best Case Scenario, or Worst Case?

Last Updated February 14, 2017

Brent Ozar

Cloud Computing, Load Testing, SQL Server

In preparation for a recent load testing engagement, we started talking about the company’s goals. The DBA said something really insightful: “We want to get to 1,000 page requests per second, which is around 5,000 batch requests per second. We’re testing with all of our caching turned off, because we want to simulate what might…

PasteThePlan.com Winners and Sample Plans

Last Updated April 9, 2017

Brent Ozar

Execution Plans, PasteThePlan.com

When we announced PasteThePlan last week, we wondered what kinds of plans you folks would paste in. Six of you won free Everything Bundles: Ajay Dwivedi Samuel Jones Stefan Gabriel Stephan Schon Steve Armistead Vesa Juvonen But rather than stepping through their plans (which are awesome too), I’m going to show a few bits and…

Query Tuning Week: How to Run sp_BlitzCache on a Single Query

Last Updated August 29, 2020

Brent Ozar

Execution Plans, Parameter Sniffing, sp_BlitzCache

The most popular way of using sp_BlitzCache® is to just run it – by default, it shows you the top 10 most CPU-intensive queries that have run on your server recently. Plus, it shows you warnings about each of the queries – if they’re missing indexes, experiencing parameter sniffing issues, running long, running frequently, doing…

Query Tuning Week: What’s the Difference Between Locking and Blocking and Deadlocking?

Last Updated April 9, 2017

Brent Ozar

Locking, Blocking, and Isolation Levels, SQL Server

If Erik takes out a lock on a row, and no one else is around to hear it, that’s just locking. There’s nothing wrong with locking by itself. It’s perfectly fine. If you’re the only person running a query, and you have a lot of work to do, you probably want to take out the…

Question From Office Hours: SQL Handle vs. Plan Handle

Last Updated February 13, 2017

Erik Darling

Execution Plans

Great question! We recently added some columns to sp_BlitzCache to help you remove undesirable plans from the cache. Doing this will force SQL to come up with a new plan, or you know, just re-create the old plan. Because that just happens sometimes. I answered it, but I wasn’t happy with my answer. So here’s…

How to Start Troubleshooting Parameter Sniffing Issues

Last Updated May 26, 2020

Brent Ozar

Execution Plans, Parameter Sniffing, SQL Server

When a query is sometimes fast and sometimes slow, for the same input parameters, and you swear nothing else in the environment is changing, that’s often a case of parameter sniffing. After SQL Server starts up and you run a stored procedure, SQL Server builds an execution plan for that proc based on the first set…

Query Tuning Week: How to Start Troubleshooting a Slow Stored Procedure

Last Updated April 9, 2017

Brent Ozar

Execution Plans, SQL Server

When you need to find out why a stored procedure is running slow, here’s the information to start gathering: Check to see if the plan is in the cache. Run sp_BlitzCache® and use several different @sort_order parameters – try cpu, reads, duration, executions. If you find it in your top 10 plans, you can view…