A Fine Slice Of SQL Server
I’m excited about this feature. I’m not being negative, here. I just want you, dear reader, to have reasonable expectations about it.
This isn’t a post about it making a query slower, but I do have some demos of that happening. I want to show you an example of it not kicking in when it probably should. I’m going to use an Extended Events session that I first read about on Dmitry Pilugin’s blog here. It’ll look something like this.
Read on for a demonstration of the point.
Comments closedJared Poche continues an investigation into the TOP operator:
I’ve explained what a blocking operator is and provided a few examples, but maybe this doesn’t seem important. It’s affecting the TOP operator, sure, but don’t people just use this to look at the TOP 1000 rows of their tables in SSMS?
The TOP operator is useful for many operations, especially in a large environment. Large operation can timeout or fail for a variety of reasons, consuming resources without providing the results you need. A small, batch-sized operation is more likely to succeed and tends to perform more consistently. Many maintenance operations make sense to run with a TOP operator, so we should make sure those operations aren’t stymied by blocking operators.
Read on for several examples.
Comments closedJared Poche takes a look at the TOP operator and learns a bit along the way:
Sort is a blocking operator. Don’t feel bad if you haven’t heard of the term; I’ve been working with SQL Server for 15 years, and I’m sure I never heard the term until the incomparable Grant Fritchley mentioned it while he was lecturing at my place of employment.
So sorts and several other types of operators (eager spools, remote query\scan\etc, hash match joins, and more) will block the normal flow and gather all their results before passing any rows on. The hash match join only blocks while building its hash table from the first input, before probing the second.
Read the whole thing. Jared is just getting started with blogging, too, so go pay his blog a visit.
Comments closedErik Darling finds an eager spool even when there is a good index to use:
But he did write about Eager Index Spools recently, and the post ended with the following statement:
Eager index spools are often a sign that a useful permanent index is missing from the database schema.
I’d like to show you a case where you may see an Eager Index Spool even when you have the index being spooled.
Click through for Erik’s demonstration.
Comments closedPaul White gives us a lesson on eager spools:
Index spools do not tell the optimizer they support output ordered by the spool’s index keys. If sorted output from the spool is required, you may see an unnecessary Sort operator. Eager index spools should often be replaced by a permanent index anyway, so this is a minor concern much of the time.
There are five optimizer rules that can generate an Eager Index Spool option (known internally as an index on-the-fly). We will look at three of these in detail to understand where eager index spools come from.
Read on for a detailed discussion of eager spools.
Comments closedA 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 closed