Product has been added to your cart.

Execution Plans

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

Brent Ozar
Execution Plans, Memory Grants, SQL Server
2 Comments
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…
Read More

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

Brent Ozar
Execution Plans, Parameter Sniffing, sp_BlitzCache
4 Comments
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…
Read More

An Introduction to Query Memory

Brent Ozar
Execution Plans, Memory Grants, Parameter Sniffing, SQL Server
9 Comments
Microsoft has been quietly making some amazing improvements for performance tuners in SQL Server 2012, 2014, and 2016. This week, we’re going to introduce you to just how awesome they are. (They being the improvements, not Microsoft. You already knew they were awesome.) Using the freely available StackOverflow database, let’s start with a simple query…
Read More

Breaking News: Query Store in All Editions of SQL Server 2016

Brent Ozar
Execution Plans, Query Store (QDS), SQL Server
7 Comments
Bob Ward talking Query Store at SQL Intersection Onstage at SQL Intersections in Orlando this morning, Bob Ward announced that Query Store will be available in all editions of SQL Server 2016. This is awesome, because Query Store is a fantastic flight data recorder for your query execution plans. It’ll help you troubleshoot parameter sniffing…
Read More

Stats Week: Do Query Predicates Affect Histogram Step Creation?

Erik Darling
Execution Plans, Parameter Sniffing, SQL Server
2 Comments
Auto Create Statistics is your friend It’s not perfect, but 99% of the time I’d rather have imperfect statistics than no statistics. This question struck me as interesting, because the optimizer will totally sniff parameters to compile an initial plan. If you don’t have index statistics, or system statistics already on a column in a…
Read More

Enabling Query Store in Azure SQL Database

Jeremiah Peschka
Execution Plans, Microsoft Azure, Query Store (QDS)
6 Comments
Enter Query Store Query Store, in short, is a way to track query performance over time. In Microsoft’s words, “The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review”. It’s like sys.dm_exec_query_stats but it persists across reboots! And it has execution plans! The Query Store is a…
Read More