When SQL Server is purely making an estimate up out of thin air, with no freakin' idea what the real answer is, it doesn't give you any kind of warning. It just produces an estimate that looks scientifically accurate - until you know how it's actually getting calculated. Let's ask SQL Server how many users have…
I need to be up front with you, dear reader, and tell you that you're probably never going to need to know this. I try to blog about stuff people need to know to get their job done - things that will be genuinely useful in your day-to-day performance tuning and management of SQL Server.…
Sure you can - parameter sniffing is everywhere. Anytime that you have one piece of code that needs to handle a varying number of rows, you're probably gonna have to worry about parameter sniffing. I'll start with any Stack Overflow database and run a couple of non-parameterized update statements. I'm using literals here, not parameters:…
When you have a stored procedure or application query that has multiple queries inside of it, how can you tell which query to focus on?
For starters, do NOT believe the percentages you see in execution plans.
Here's how Books Online describes the Max Degree of Parallelism setting: You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. And here's what the SQL Server 2019 setup screen says: When an instance of SQL Server runs on a computer that has more…
Years ago, when troubleshooting performance, I stumbled across this Microsoft documentation on parallel query processing that says: Certain types of statements cannot be processed in parallel unless they contain clauses, however. For example, UPDATE, INSERT, and DELETE are not normally processed in parallel even if the related query meets the criteria. But if the UPDATE…
This morning, I had the honor of presenting virtually at SQLSaturday Oslo. The session title, Watch Brent Tune Queries, explains it all. Enjoy!
https://youtu.be/IVqvwNlwXuI
Date tables help users filter data based on day of week, holiday, this year vs last year, and other kinds of filters that business users usually wanna add to their reports. If you haven't used a date table before, here are a couple of primers on how to create one and use it:
Creating a date dimension table by Aaron Bertrand
How to create a date_calendar table by Sean Smith
While I was building lab queries for my all-new Fundamentals of Parameter Sniffing course - first live one is next week, still time to get in - I ran across a query with delightfully terrible behavior.
I'm always torn when I build the hands-on labs. I want to make the challenges easy enough that you can accomplish 'em in the span of an hour, but hard enough that they'll ... well, challenge you.
When you're doing DUI operations against tables with millions of rows, you have to be really careful about SQL Server's estimates. Ideally, you want your delete/update/insert (what - what'd you think I meant?) queries to be as simple as possible - even a simple join can cause SQL Server to do wildly, wildly incorrect estimates, which affects memory grants, parallelism, wide vs narrow plans, and more.
I know this is gonna sound crazy, but let's say you had a table where people stored all kinds of things in one column: dates, integers, file names, sale prices, file names, you name it. And let's say your application frequently ran a query looking for dates in that column, like this: [crayon-6a6b4a334f446328765042/] Even if…
Ever wonder somebody else does it? Watch over my shoulder as I spend 9 minutes in PowerPoint explaining the big picture, and then about 40 minutes working on this stored procedure in the StackOverflow2013 database:
https://www.youtube.com/watch?v=7hv4vD7Cfy0
The #1 fastest way to tune queries is in the production database, on the production server. I know. Put the knife down. You're not happy about that, but hear me out: I'm specifically talking about the fastest way to tune queries. In production, you can guarantee that you're looking at the same data, hosted on the…
Most of the time, you'll hear advice that you should select as few columns as possible so that SQL Server can do less work.
However, one of my clients hit a neat edge case scenario where SELECT * queries actually ran faster than picking a subset of columns.
When you run a SELECT COUNT(*), the speed of the results depends a lot on the structure & settings of the database. Let's do an exploration of the Votes table in the Stack Overflow database, specifically the 2018-06 ~300GB version where the Votes table has 150,784,380 rows taking up ~5.3GB of space.
I'm going to measure each method 3 ways:
In the database world, when we say something isn't sargable, what we mean is that SQL Server is having a tough time with your Search ARGuments. What a sargable query looks like In a perfect world, when you search for something, SQL Server can do an index seek to jump directly to the data you…
In our last episode, I introduced the concept of scan predicates: execution plan operations that weren't able to seek directly to the rows they needed. Let's take another query:
[crayon-6a6b4a3351ca4840180631/]
If we ONLY have the gray pages index on LastAccessDate, Id, DisplayName, and Age, our query plan looks like this:
I'm going to narrate this from bottom up because it makes for easier storytelling:
We've been working with the clustered index of the Users table, which is on the Identity column - starts at 1 and goes up to a bajillion:
And in a recent episode, we added a wider nonclustered index on LastAccessDate, Id, DisplayName, and Age:
[crayon-6a6b4a3352984854844097/]
Whose leaf pages look like this:
In our last episode, I'd expanded our query to include DisplayName and Age - two columns that weren't in our nonclustered index: [crayon-6a6b4a3353501182615273/] 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?
