A Fine Slice Of SQL Server
Milos Radivojevic notes a peculiarity with Query Store:
Be very careful with forcing plans against queries referencing table variables and table valued parameters. The query_id attribute for such queries is very instable and forcing plans even as a workaround could have very short life time. You should use different approach to fix such issues.
Read the whole thing to understand why this is important.
Comments closedIsolation level settings for the entire database exist only as a default. All connections created will inherit the database’s isolation level. Furthermore, a database only has two possible isolation levels:
– read committed
– read committed snapshot (also known as “RCSI”)There are four additional isolation levels (read uncommitted, repeatable read, serializable, snapshot) which can only be selected at the connection or statement level. In SQL Server and Azure SQL Database Managed Instance, the default database isolation level is read committed. In Azure SQL Database, the default is read committed snapshot.
Read the whole thing.
Comments closedTo hold onto Shared locks, you’d need to use an isolation level escalation hint, like REPEATABLE READ.
I could do that here if I were a lazy cheater.
Instead, I’m going to show you a more common and interesting scenario.
This leaves the classes of non-lazy cheater and a lazy non-cheater (because non-lazy non-cheater sounds batty). Regardless of your answer, great post by Erik.
Comments closedAll performance spools are lazy. The spool’s worktable is gradually populated, a row at a time, as rows stream through the spool. (Eager spools, by contrast, consume all input from their child operator before returning any rows to their parent).
Performance spools always appear on the inner side (the lower input in graphical execution plans) of a nested loops join or apply operator. The general idea is to cache and replay results, saving repeated executions of inner-side operators wherever possible.
When a spool is able to replay cached results, this is known as a rewind. When the spool has to execute its child operators to obtain correct data, a rebind occurs.
You may find it helpful to think of a spool rebind as a cache miss, and a rewind as a cache hit.
Read the whole thing. I am admittedly the type of person Paul mentions at the end (I reflexively hiss when I have a query performing poorly and see a spool).
Comments closedAaron Bertrand walks us through a case where filtered indexes become unhelpful:
Again, focusing on the areas highlighted in orange: the statement has a parameter @0 (previously it had @1) but, more importantly, the clustered index is scanned now instead of the filtered index. This has impacts throughout the plan, including how many rows are both estimated to be read and actually read in order to return those 11 rows. You can see a much higher I/O cost (about 22X), the predicate is now listed explicitly in the tooltip, and you can see warnings about residual I/O (which just means a lot more rows were read than necessary). The root operator still has the warning about the unmatched index, so at least the plan gives you some clue that a filtered index exists that might be useful if you change the parameterization setting for the database (or add OPTION (RECOMPILE) to the statement):
There are still ways to make filtered indexes work with forced parameterization, such as index hints, but Aaron does a great job explaining why something which seems like it should just work doesn’t always.
Comments closedLukas Eder takes on the myth that COUNT(*) differs from COUNT(1):
Now that we know the theory behind these
COUNTexpressions, what’s the difference betweenCOUNT(*)andCOUNT(1). There is none, effectively. The1expression inCOUNT(1)evaluates a constant expression for each row in the group, and it can be proven that this constant expression will never evaluate toNULL, so effectively, we’re runningCOUNT(*), counting ALL the rows in the group again.There should be no difference, and parsers / optimisers should be able to recognise this and not do the extra work of checking every expression evaluation for
NULL-ness.
Interestingly, in one of the four major RDBMS platforms (not including DB2), there is a performance difference of about 10%.
Comments closedErin Stellato shows how you can mine the Query Store tables to learn more about your workload:
The query text and plan are stored in two separate tables, sys.query_store_query_text and sys.query_store_plan, respectively. The text is stored as a nvarchar(max) data type, and the plan is stored as varbinary(max). This means that the data can be mined, looking for patterns and explicit use of objects. Want to know what queries use an index? Look for it in the plans. Want to know what queries have a RECOMPILE hint on them? Look for it in the query text. Anything you want to find with regard to query text or plans is there, you just have to know how to query the data.
It’s hard to tune queries if you don’t know what’s running.
Comments closedGrant Fritchey shows off an improvement in SQL Server 2019:
I’ve always felt responsible for making such a big deal about the differences between estimated and actual plans. I implied in the first edition of the execution plans book (get the new, vastly improved, 3rd edition in digital form for free here, or you can pay for the print version) that these things were so radically different that the estimated plan was useless. This is false. All plans are estimated plans. However, actual plans have some added runtime metrics. It’s not that we’re going to get a completely different execution plan when we look at an actual plan, it’s just going to have those very valuable runtime metrics. The problem with getting those metrics is, you have to execute the query. However, this is no longer true in SQL Server 2019 (CTP 2.4 and greater) thanks to sys.dm_exec_query_plan_stats
Click through for an example, as well as what you need to do to enable this.
Comments closedErik Darling reminds you to check out the properties of execution plan elements:
I read a lot of posts about query plans, and I rarely see people bring up the properties tab.
And I get it. The F4 button is right next to the F5 button. If you hit the wrong one, you might ruin everything.
But hear me out, dear reader. I care about you. I want your query plan reading experience to be better.
Erik provides sound advice here.
Comments closedJeffry Schwartz takes us through an odd case:
Recently, a customer requested that we tune a query that took 13 seconds to return 11 rows. SQL Server 2017 suggested an index to improve performance, so we added it in a development environment. The improvement made the query run 647 seconds, almost 50 TIMES longer than the original! This naturally caused much consternation, so we decided to determine what and why it happened as well as how we could still achieve the original objective, i.e., make the query run faster. This article discusses what caused the original performance problem in addition to the new one that was caused by the introduction of an index, and illustrates how we were able to make the query run significantly faster than it did originally. We will cover reading query plans, examining the specific details of query plan operators, the effects of index statistics on missing index recommendations, using query plan XML to enable simpler query plan comparison, and the effects of using functions in where clauses.
Click through to understand how this could be the case.
Comments closed