A Fine Slice Of SQL Server
Erik Darling dives into a weird key lookup:
This lookup doesn’t produce any rows or columns, that’s why there are 0.0 rows per iteration.
It’s purely to filter data out, and it does that. Slowly.
Look, I’m not defending the choice, I’m just using it to teach you something.
Read on for your moment of understanding.
Comments closedPaul White does some amazing things with T-SQL, news at 11:
I will be looking for distinct values in the
BountyAmountcolumn of thedbo.Votestable, presented in bounty amount order ascending. The Votes table has just under 53 million rows (52,928,720 to be exact). There are just 19 different bounty amounts, includingNULL.The Stack Overflow 2013 database comes without nonclustered indexes to minimize download time. There is a clustered primary key index on the
Idcolumn of thedbo.Votestable. It comes set to SQL Server 2008 compatibility (level 100), but we will start with a more modern setting of SQL Server 2017 (level 140):
Getting the query down from 10.5 seconds to 1ms is crazy.
Comments closedBrent Ozar has some tips if you use RECOMPILE hints frequently:
The first query’s plan stuck around in memory, so it now shows 2 executions, and 2 total rows returned. Its row metrics are correct through the life of the stored procedure’s time in cache.
However, the second query – the one with the recompile hint – has a brand new plan in the cache, but also new metrics. You’re not just recompiling the execution plan, but you’re also not getting query plan metrics here. (That’s fine, and that part I was also kinda aware of.)
But the part that I keep forgetting is that when I’m looking at the stored procedure’s totals in sp_BlitzCache, the total, min, and max values are useless:
If the plan cache isn’t going to help, what will? Brent tells you exactly what.
Comments closedJonathan Kehayias dives into an interesting problem:
A few weeks ago, an interesting question was asked on the #SQLHelp hash tag on Twitter about the impact of execution plans on the ASYNC_NETWORK_IO wait type, and it generated some differing opinions and a lot of good discussion.
My immediate answer to this would be that someone is misinterpreting the cause and effect of this, since the ASYNC_NETWORK_IO wait type is encountered when the Engine has results to send over TDS to the client but there are no available TDS buffers on the connection to send them on. Generally speaking, this means that the client side is not consuming the results efficiently, but based on the ensuing discussion I became intrigued enough to do some testing of whether or not an execution plan would actually impact the ASYNC_NETWORK_IO waits significantly.
To summarize: Focusing on ASYNC_NETWORK_IO waits alone as a tuning metric is a mistake. The faster a query executes, the higher this wait type will likely accumulate, even if the client is consuming results as fast as possible. (Also see Greg’s recent post about focusing on waits alone in general.)
Click through for the things Jonathan tested.
Comments closedI am currently running SQL Server Analysis Services (SSAS) 2019 Enterprise Edition. (This can also be applied to Power BI)
My Fact table has got roughly 950 Million rows stored in
And as mentioned previously it has got over 64 Million distinct users.
The data is queried from SQL Server into SSAS.
Gilbert first checks how close these are and then how much faster the approximate count is.
Comments closedErik Darling turns the seconds into minutes:
I’ve posted quite a bit about how cached plans can be misleading.
I’m gonna switch that up and talk about how an actual plan can be misleading, too.
In plans that include calling a muti-statement table valued function, no operator logs the time spent in the function. I’ve got a User Voice item for it here.
Click through for the demonstration. If that sounds like something you’d like fixed, vote up the User Voice item.
Comments closedDerik Hammer takes us through query tuning a nasty job on Amazon Redshift:
My team built a process to load from a couple of base tables, in our Amazon Redshift enterprise data warehouse, into an other table which would act as a data mart entity. The data was rolled up and it included some derived fields. The SQL query had some complicity [complexity?, ed.] to it.
This process ran daily and was being killed by our operations team after running for 22 hours.
I stepped in to assist with performance tuning and discovered that join choices, such as INNER vs. OUTER joins have a big impact on whether Redshift can use its sort keys or not.
Click through for more details and what Derik ended up doing.
Comments closedErik Darling looks at some internals for us:
Things like Accelerated Database Recovery, Optimize For Sequential Key, and In-Memory Tempdb Metadata are cool, but they’re server tuning. I love’em, but they’re more helpful for tuning an entire workload than a specific query.
The thing with BMOR is that it’s not just one thing. Getting Batch Mode also allows Adaptive Joins and Memory Grant Feedback to kick in.
But they’re all separate heuristics.
Read on to see the extended events around batch mode to help you determine if it’s possible for the optimizer to use it for a given query.
Comments closedBrent Ozar shows why the StartDate + EndDate pattern is not great for filtering:
If all you need to do is look up the memberships for a specific UserId, and you know the UserId, then it’s a piece of cake. You put a nonclustered index on UserId, and call it a day.
But what if you frequently need to pull all of the memberships that were active on a specific date? That’s where performance tuning gets hard: when you don’t know the UserId, and even worse, you can’t predict the date/time you’re looking up, or if it’s always Right Now.
This is where I advocate pivoting to a series of event records, so instead of a start date and end date, you have an event type (started, expired, cancelled, etc.) and a date. There are other alternatives as well, but it’s a good thought exercise.
Comments closedJared Poche investigates a slow record deletion process:
I encountered a curious issue recently, and immediately knew I needed to blog about it. Having already blogged about implicit conversions and how the TOP operator interacts with blocking operators, I found a problem that looked like the combination of the two.
I reviewed a garbage collection process that’s been in place for some time. The procedure populates a temp table with the key values for the table that is central to the GC. We use the temp table to delete from the related tables, then delete from the primary table. However, the query populating our temp table was taking far too long, 84 seconds when I tested it.
Read on to understand why.
Comments closed