Performance Tuning
Silent Demo: The Plan Cache
Shhh, and pay close attention. In 76 seconds, I'll teach you something intriguing about the way SQL Server caches execution plans:
https://www.youtube.com/watch?v=KfdfNRrIsU0
Category: Execution Plans
Performance Tuning
Searching Strings in SQL Server is Expensive
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 it, then look for the Brents:
Performance Tuning
Query Tuning Week: How to Run sp_BlitzCache on a Single Query
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 implicit conversion, you name it.
Performance Tuning
Question From Office Hours: SQL Handle vs. Plan Handle
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 a better one!
Performance Tuning
How to Start Troubleshooting Parameter Sniffing Issues
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 of parameters that are passed in.
Performance Tuning
Query Tuning Week: How to Start Troubleshooting a Slow Stored Procedure
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…
Memory Grants Added to sp_BlitzCache and sp_BlitzFirst
Exciting New Doodads When SP3 for 2012 dropped, we were all super excited by the new query tuning-centric features that were at our disposal. Now all we had to do was get people to install SP3! Great features like this make patching an easier sell. Now with SP2 for 2014 out, a lot of those…
Performance Tuning
An Introduction to Query Memory
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 - SELECT * FROM Users:
Performance Tuning
Can Adding an Index Make SQL Server 2016…Worse?
Using the StackOverflow database, let's check out Krock's query. He's a competitive fella, and he's looking to find users who signed up for StackOverflow after he did, but who have a higher reputation than he does. I'm going to simplify the query a little here: [crayon-6a6cde39da29f101475666/] The Users table has a clustered index on the Id…
Performance Tuning
Breaking News: Query Store in All Editions of SQL Server 2016
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 issues, connection settings issues, plan regressions, bad stats, and much more.
Performance Tuning
Improved diagnostics for query execution plans that involve residual predicate pushdown
I love stuff like this!
Even though it's not on my list of dream features, it's pretty neat. Getting new views into what SQL is doing when queries execute is pretty cool. You can read the short and gory details at the KB here: Improved diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2012
SQL Server 2012 SP3 Adds Memory Grant and Performance Features
SQL Server 2012 SP3 has just been freshly released! Not only does this release contain bug fixes, it also contains a slew of performance tuning related features.
Many of these features are about memory grants. This is NOT the memory used in the buffer pool/ data cache by the query -- it's the memory also known as Query Workspace Memory. Start reading more here.
Performance Tuning
The case for Query Store in tempdb
Query Store is so cool
Billed as a flight data recorder for SQL Server, the Query Store is a repository of execution plan information, like the plan cache, except a bit more actionable. And it has a GUI.
You can read all about what it does and what you can do with it around the internet. You can be suitably impressed and enchanted by the promise of data that's persisted between restarts, being able to quickly and easily address plan regression issues, and so forth.
Performance Tuning
Enabling Query Store in Azure SQL Database
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…
Performance Tuning
Why is This Partitioned Query Slower?
It's tempting to think that table partitioning will improve query performance. After all, it's an Enterprise Edition feature-- it must have a lot of magic, right? Table partitioning does have magic for the right situations. It shines when you want to add a large amount of data to a table or remove a large amount…
Performance Tuning
Logical Query Processing
You can't do that on management studio Recently, while working with a client, I did something in a query that they were mystified by. I didn't think much of it, but I thought it might be useful to you, dear readers, as well. Along with an explanation. Here's a sample query that takes advantage of…
Performance Tuning
When does a Query Get Trivial Optimization?
We had some great questions about trivial execution plans in SQL Server in our Advanced Querying and Indexing class a few weeks ago. Here's a little glimpse into what we talked about.
For really simple queries, SQL Server can use "trivial optimization". If there's a very limited number of ways to run the query, why do a bunch of fancy, CPU burning cost-based optimization? Just chuck the plan at the query and let it go!
Performance Tuning
Announcing Live Query Execution Plans
Behold!
This is new in the preview of SQL Server Management Studio 2016, available for download now. It even works when you're connected to SQL Server 2014 SP1 - but not 2012, because it relies on DMVs that were only shipped with 2014 SP1.
Performance Tuning
How many CPUs is my parallel query using in SQL Server?
Parallelism can be confusing. A single query can have multiple operators that run at the same time. Each of these operators may decide to use multiple threads. You set SQL Server's "max degree of parallelism" to control the number of processors that can be used, but it's not immediately obvious what this means. Does this setting…
Performance Tuning
SQL Server Query Store
I spend most of my day tuning SQL Server to make it go faster. I'm usually called in after the fact, when the app has become intolerably slow.
One of the first things I ask is, "What's changed?"
