Why Logical Reads Are A Bad Metric For Query Tuning In SQL Server
To summarize the video a little bit:
High average or total logical reads isn’t a guarantee that a query is slow
Looking for high average CPU and duration queries is a better metric
You may see logical reads go up or down as you make queries faster
For I/O bound workloads, you’re better off looking for queries with a lot of physical reads
The more query tuning work I did, the more I came to realize that logical reads fall into the SQL Server 2008 mindset, like PLE, fragmentation, page splits, and other metrics that don’t necessarily indicate a performance problem.
Thanks for watching!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
What Else Happens When Queries Try To Compile In SQL Server: COMPILE LOCKS!
Thanks for watching!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.
I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.
I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.
Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Perhaps one of the most exhausting parts of my job is disabusing developers of the notion that common table expressions hold some weight in gold over any other abstraction layer in SQL Server.
Think of it like this:
Views are like a permanent home
Common table expressions are like a mobile home
You can put equally terrible queries in either one and expect equally terrible results.
It’s sort of like installing a toilet: If you just throw it in the middle of the kitchen floor without any consideration as to architectural design or pipe locality, it’s going to look bad immediately, and stink eventually.
Of course, since views are programmable objects, they have a bit more depth of character with creation options. Common table expressions, being temporary views, don’t have much of a story arc in SQL Server.
For example, you can index a view, but you can’t index a common table expression. Either one is entirely capable of using indexes on the underlying table(s), of course.
What I find particularly curious is how well received common table expressions are, while views are often looked down upon, despite have many functional equivalences.
Developer 1: This stored procedure has hundreds of common table expressions in it
Developer 2: Wow that’s amazing you’re so good at SQL
vs
Developer 1: This stored procedure database has hundreds of common table expressions views in it
Developer 2: Wow that’s amazing horrible you’re so good at SQL a shabby degenerate and a skunk of a human being
Surprise!
Do That Viewdoo That You Do So Well
One thing that I see in a lot of views (STILL!) is SELECT TOP (100) PERCENT, which is a pretty good sign that I’m going to have to fix a lot of other problems from the 2005-2012 era of SQL Server development.
As a slight digression, I find it odd how many myths pervade SQL Server development from the 2005-2012 era. It was the golden age of SQL Server books.
All this great knowledge was out there, but everyone seemed to find a blog post that was like “in my testing the estimated batch cost for the table variable was way lower than the estimated batch cost of the temp table, so they’re always faster”.
The dogma is real.
I know this worked in like SQL Server 2000, but that’s not excuse for keeping it around today:
CREATE OR ALTER VIEW
dbo.u
AS
SELECT TOP (1)
u.*
FROM dbo.Users AS u;
GO
CREATE OR ALTER VIEW
dbo.u_p
AS
SELECT TOP (100) PERCENT
u.*
FROM dbo.Users AS u;
GO
You’ll notice the query plan for the 100 percent view is quite conspicuously missing a TOP operator at all. The optimizer throws it away because it knows it needs to get everything anyway.
you don’t get a top
Shocking news from 20 year ago, I’m sure.
Equivalency
Getting back to the main point of the post here, common table expressions are the same as a vanilla view — unindexed, with no special create options.
WITH
ThisIsTheSameAsAView
AS
(
SELECT
u.AccountId,
c = COUNT_BIG(*)
FROM dbo.Users AS u
JOIN dbo.Badges AS b
ON b.UserId = u.Id
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
JOIN dbo.Comments AS c
ON c.UserId = u.Id
AND c.PostId = p.Id
JOIN dbo.Votes AS v
ON v.PostId = p.Id
GROUP BY
u.AccountId
)
SELECT
*
FROM ThisIsTheSameAsAView AS view1
JOIN ThisIsTheSameAsAView AS view2
ON view2.AccountId = view1.AccountId;
GO
CREATE OR ALTER VIEW
dbo.ThisIsTheSameAsACommonTableExpression
AS
SELECT
u.AccountId,
c = COUNT_BIG(*)
FROM dbo.Users AS u
JOIN dbo.Badges AS b
ON b.UserId = u.Id
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
JOIN dbo.Comments AS c
ON c.UserId = u.Id
AND c.PostId = p.Id
JOIN dbo.Votes AS v
ON v.PostId = p.Id
GROUP BY
u.AccountId;
GO
SELECT
*
FROM dbo.ThisIsTheSameAsACommonTableExpression AS cte1
JOIN dbo.ThisIsTheSameAsACommonTableExpression AS cte2
ON cte2.AccountId = cte1.AccountId;
If you were to capture execution plans for both of these, you’d see the same query plan pattern where the query contained by the view and the common table expression is executed twice, so the results from one reference can be joined to the results of the other reference.
I’ve talked about this many times in the past with common table expressions, but not in comparison to views.
oh dear me.
If you’re one of those developers who disdains views for their very existence, you shouldn’t feel any differently about common table expressions in practice (at least from a performance perspective).
One place where views edge out common table expressions is that the code is contained in a module that can be referenced by any other query or module (stored procedure, function, etc.). If you fix query performance for a view, everything that references it also gets faster. Doing that for a common table expression that gets used frequently is cool but… then you have to go find and replace it all over the place.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
sp_PressureDetector: Now With PerfMon Counters And Server Sampling
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.
I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.
I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.
Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Okay, so like, maybe not all that hard at work, and we I had some help with a funny bug, stemming from a recent Pull Request. The Read Me file has been updated to reflect the newest parameters.
Let’s start with giving some credit, here, since I hate an overdue bill.
ReeceGoding reported and very capably fixed an issue with sp_QuickieStore. The problem only occurred when you ran the procedure with @get_all_databases and the new @escape_brackets parameter set to true. Each loop would add an escape character to the search string, which could end up looking like this: %\\\\\[AnyStringStartingAndEndingWithSquareBrackets]\\\\\]%'
The good news is that it’s fixed now. A big round of applesauce for that. It’s the only substantial change to sp_QuickieStore.
Now, on to the other stuff!
sp_HealthParser
I had to make a bunch of adjustments in here. I was testing out some stuff and hoping that I could make it work, but the math just wasn’t there. Or the maths just weren’t there, if you’re into multiple maths.
For the wait stats sections, I’ve removed the “total wait time” column. It just didn’t make any sense, because the only information that I had was the total number of waits, the average number of waits, and the maximum wait time. I was hoping I could get sensible numbers by multiplying the total and average, and adding the max wait time to that. The problem is that the numbers were huge. Enormous. Senseless.
The other thing I had to do some work on was in the “waits by duration” section. It seemed like the results would just re-report on every collection cycle, even if no new waits had occurred. It would make things look like you had the same waits in every grouped block of time. Via the magic of windowing functions, I weed out absolute duplicates, and only show where the wait stats report something new. As part of this, I also filter out waits with a low average duration (less than 500ms by default). You can change this behavior by using the @wait_duration_ms parameter.
Another thing that I set a threshold on was in the “cpu task details” section. In there, it would show sections with a warning logged to the system health extended event. The problem was mainly that a warning would be logged whenever there was even a single pending task (query waiting to get on a CPU). Having just one of those is not a very interesting sign of CPU pressure, so there’s a parameter called @pending_task_threshold that defaults to 10 which you can use to decide how many pending tasks matter to you.
sp_PressureDetector
This is perhaps the biggest update! It hadn’t changed much in a while, and I wanted to get a little more out of what SQL Server has to offer.
The big changes are:
Added in some useful PerfMon counters to the results
Added the ability to sample server activity
When you run sp_PressureDetector the “normal” way, it’ll show you perfmon counter activity since startup. I only collect specific ones though, and only specific counter types. See, there are a variety of types of counters.
The math to figure out how much of something happened during a period of time is about the same for all of them, but I found looking through the various categories and specific counters, that only those that come from types 272696576 and 65792, and only certain ones within those.
The new section will look something like this:
perf, mon
If you decide that you don’t care about perfmon counters, you can skip that section by using the @skip_perfmon parameter.
The sampling code was inspired by running into some client issues where hitting F5 was okay, but remembering what all the numbers were before to compare them to after. You can run the sampling code by doing this:
EXEC sp_PressureDetector
@sample_seconds = 5;
Not every section supports this yet. I started with the ones that I thought would benefit the most:
Wait stats
File stats
Perfmon counters
I may add it to more later, but these are good enough for now. The easy way to think about it is that only sections with a “sample_seconds” column in them are sampled. The rest are current.
Look for a video walkthrough of the latest round of changes.
If you’d like to report an issue, request or contribute a feature, or ask a question about any of these procedures, please use my GitHub repo. Specifically, check out the contributing guide.
As happy as I am to get emails about things, it makes support difficult for the one-man party that I am. Personal support costs money. Public support is free. Take your pick, there!
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
SQL Server Query Transformations With ROW_NUMBER And ANY Aggregates
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
