In our last episode, I'd expanded our query to include DisplayName and Age - two columns that weren't in our nonclustered index: [crayon-6ae4631db9a68336780653/] So as a result, I was getting key lookups in the execution plan: And I spent a lot of time talking about the overhead that each key lookup incurs. Astute readers among…
In our last episode, we added a nonclustered index, and now even though this query has a filter and an ORDER BY, it hardly did any work at all - just a lightweight index seek:
Ah, index seeks - nothing better than that, right?
In the first post in my How to Think Like the Engine series, I started by doing a pretty simple query:
But I noted that if you were following along on your computer, you might see a different execution plan. If I tweak just one thing about my SQL Server, I see a different plan:
It's a little bit of a trick question: what could make a good query plan turn bad?
In the last episode, we used sp_BlitzCache to spot the most resource-intensive, longest-running query plans in your server's cache. Go run those queries again, right now, and I bet you're going to see plans in the cache that use parameters. Maybe they're stored procedures, or maybe they're parameterized SQL.
Those of you who follow me on Instagram, Twitter, or Facebook know I'm taking most of August off for vacation. Erika and I spent the last couple of weeks in Telluride, had a brief stop in Sacramento for SQL Saturday, and this week we're touring wineries in Napa and Sonoma. As part of recharging, I…
In the last episode, while talking about the basics of executing queries, I said that SQL Server caches execution plans in memory, reusing them whenever the same query gets executed again. I've also talked about the dynamic management views (DMVs) that let you query what's in SQL Server's memory. Let's put those two things together and find out what queries have been running on the server lately.
Up til now in the DBA Training Plan, we've been taking inventory of our servers, making sure the data's well-protected, and understanding the basics of how the data's stored in indexes inside those data files. Now, let's start looking at performance, and let's start by zooming really far out to think about how SQL Server runs a query.
Precisely 80.3% of you are going to learn something in this post.
Earlier this week, I asked what you thought the arrows in estimated and actual query plans meant. I asked you to just guess without doing any research, and here's what you answered:
Start with the Stack Overflow database, with no nonclustered indexes. If you've been playing around with demos, run my DropIndexes proc just to clear things out, which leaves the clustered indexes in place.
Say you run this query:
[crayon-6ae4631dc790c988300213/]
By default, with a clustered index on the Id field (identity, starts at 1 and goes up to a bajillion), your results will probably look like this, ordered by Id:
Somewhere along the way in your career, you were told that:
Index seeks are quick, lightweight operations
Table scans are ugly, slow operations
This week, I've been writing about how SQL Server 2019's bringing a few new features to mitigate parameter sniffing, but they're more complex than they appear at first glance: adaptive memory grants, air_quote_actual plans, and adaptive joins. Today, let's talk about another common cause of wildly varying durations for a single query: user-defined functions.
So far, I've talked about how adaptive memory grants both help and worsen parameter sniffing, and how the new air_quote_actual plans don't accurately show what happened. But so far, I've been using a simple one-table query - let's see what happens when I add a join and a supporting index:
[crayon-6ae4631dc93b6044364857/]
(Careful readers will note that I'm using a different reputation value than I used in the last posts - hold that thought. We'll come back to that.)
My last post talked about how parameter sniffing caused 3 problems for a query, and how SQL Server 2019 fixes one of them - kinda - with adaptive memory grants.
However, the post finished up by talking about how much harder performance troubleshooting will be on 2019 because your query's memory grant is based on the last set of parameters used, not the current set.
This week, I'm demoing SQL Server 2019 features that I'm really excited about, and they all center around a theme we all know and love: parameter sniffing.
If you haven't seen me talk about parameter sniffing before, you'll probably wanna start with this session from SQLDay in Poland. This week, I'm going to be using the queries & discussion from that session as a starting point.
To warn you ahead of time, this post is an exploration without an answer (yet). There's some interesting stuff in here, but no conclusions. If that's not your kind of post, feel free to skip it. If it is, well, here goes nothin'... Wild, Wild Life Creating indexes is kind of a funny thing. By…
!erehT iH
I know what you're thinking. Another post about how you should just never do this one thing and all your queries will magically end up faster.
Just watch the video.
A couple years back, I realized there were at least Ten Ways to Set MAXDOP. These days, as I write and tune queries, I realize there are an awful lot of ways to change the Cardinality Estimator (CE) and Query Optimizer, too. Let's take a quick survey of five intriguing options. 1. Change your SQL…
Gosh Darn
Now, look, it's no secret that I think Query Store is cool. Heck, I went and wrote sp_BlitzQueryStore so you can examine it similarly to how sp_BlitzCache looks at the plan cache.
For a variety of reasons, though, we run into people who can't or won't turn it on, or who had to turn it off.
Signs and Numbers
When tuning queries, or even finding queries to tune, there's a rather misguided desire to look for queries with a high cost, or judge improvement by lowering query cost. The problem is that no matter what you're looking at, costs are estimates, and often don't reflect how long a query runs for or the actual work involved in processing the query.
I love building demos for our training classes because I'm constantly in a battle of the minds with SQL Server. I try to guess what he's going to do next, and I love it when he surprises me.
I started by building a new parameter sniffing demo with the Stack Overflow database, and in this case using the 50GB 2013 size. I wanted to demo a search stored procedure looking for the first few words of question titles.
