A Fine Slice Of SQL Server
A common dilemma is when you have two date columns, and you need to judge the gap between them for something.
For instance, say you have a table of orders and you want to figure out how long on average it takes an ordered item to ship, or a shipped item to be delivered.
You’re not left with many good ways to write the query to take advantage of indexes.
Read on to see what Erik means, and one way you can speed it up a bit.
Comments closedArun Sirpal shows how we can get execution plans from Azure SQL Database:
Yes you can still get execution plans for Azure SQL Database, you cannot get this from the Azure Portal so it is good to know for your tuning days. It is based on the same code you have probably run many times before. Lets get the plan handle and work through an example.
Click through for the example.
Comments closedMilos 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 closed