Arthur Daniels shows us a good reason for using better data sizes than just VARCHAR(MAX) everywhere:
That’s a lot of desired memory, 1,493,120 KB aka 1.4 GB, but there was only
25 MB used in the sort. So why was SQL Server so far off the right estimate? It’s the data types we picked.
That’s a lot of memory for a fairly simple query returning 300,000 rows, each containing a string and an int.
Bert Wagner continues his series on visualizing physical join operators:
Hash Match joins are the dependable workhorses of physical join operators.
While Nested Loops joins will fail if the data is too large to fit into memory, and Merge Joins require that the input data are sorted, a Hash Match will join any two data inputs you throw at it (as long as the join has an equality predicate and you have enough space in tempdb).
Bert has some great animated GIFs too.
Hugo Kornelis covers the Adaptive Join operator:
The Adaptive Join operator was added in SQL Server 2017 as an alternative to the other join operators: Nested Loops (ideal for joining a small data stream with a cheap input), Hash Match (most effective for joining large unsorted sets) and Merge Join (ideal for joining data streams that are sorted by the join key). It is intended to be used when there is no efficient way to fulfill the order requirement of the Merge Join, and the optimizer cannot reliably predict which of the remaining algorithms (Hash Match or Nested Loops) would perform best.
Because it has to be able to join the data using either the Nested Loops or the Hash Match algorithm, Adaptive Join suffers from the combined restrictions of these operators. As such, Adaptive Join supports only four logical join operations: inner join, left outer join (but not the probed version), left semi join, and left anti semi join; it requires at least one equality-based join predicate, it uses lots of memory, and it is semi-blocking.
Read on for a detailed look at this operator.
Hasan Savran explains how we can tune queries in CosmosDB:
This is most common question in my talks about Cosmos DB from DBAs. Cosmos DB is a managed database, this does not mean that you cannot tune up your queries. But the way you tune up the queries is nothing like SQL Server.
First you need to be sure that you configured your Cosmos DB containers right. What do I mean with that? You should pick the right partition key before you start to tune up any of your queries. Tuning up your queries is not going to help you in long run if you selected a wrong partition key when you created Cosmos DB containers. Throughput value is another value you need to worry about, the good news about the throughput is, you can change it if you need to. You cannot change your partition key!
It’s a whole different world over there.
Hugo Kornelis describes a new operator:
The Bitmap operator is used to build a bitmap that, based on a hash, represents which values may be present in a data flow. Due to the chance of hash collisions in the hash function used, the Bitmap process can produce false positives but not false negatives – so a match based on a bitmap is not guaranteed to be a match to the actual data, but a non-match based on a bitmap is guaranteed to not be a match in the actual data.
The generated bitmap is typically used in other operators to remove rows for which there is no match in the bitmap, and hence guaranteed no match in the original set of data processed by the Bitmap operator. The use of Bitmap operators is most common in execution plans for star join queries in large data warehouses. An example can be seen here.
Click through for details on how it works and plenty of good information on it.
Arthur Daniels shows us what happens when there are multiple missing indexes in an execution plan:
This is missing index request #1, and by default, this is the only missing index we’ll see by looking at the graphical execution plan. There’s actually a missing index request #2, which we can find in the XML (I know, it’s a little ugly to read. Bear with me).
I am of two minds on this. It probably should be easier to see multiple index candidates, but there’s already so much risk of people just copy-pastaing missing index recommendations that adding more seems like a bad idea.
Arthur Daniels recommends you review any warning signs in execution plans:
Some things in life we ignore. For example, the “check engine” light. That’s just there as a suggestion, right?
But when you’re performance tuning, you can’t afford to ignore the warning signs. I can’t count the number of times that I’ve found the issue with a query by looking at the warnings.
The example Arthur uses involves implicit conversion, but there are several important warnings SSMS bubbles up.
Erin Stellato shows us how we can find the slowest query within a stored procedure:
Figuring out exactly what causes slow performance for a stored procedure can sometimes feel like trying to unravel a ball of Clark Griswold’s Christmas lights. It’s not uncommon to see procedures with hundreds, even thousands of lines of code. You may have been told which stored procedure runs slow by a user or manager, or you might have found it by looking in SQL Server DMVs. Either way, once you have detected the offending procedure, where do you start?
If you’re running SQL Server 2016, one option is Query Store. Query Store captures individual queries, but it also captures the object_id, so you can find all the queries that are associated with an object to determine which ones are problematic.
This is quite useful when you have to tune a procedure you’ve never seen before, and as you go to open that procedure, the vertical scroll bar keeps getting smaller and smaller.
Bert Wagner walks us through nested loop joins:
Nested loops joins work like this: SQL Server takes the first value from our first table (our “outer” table – by default SQL Server decides for us which table of the two this will be), and compares it to every value in our second “inner” table to see if they match.
Once every inner value has been checked, SQL Server moves to the next value in the outer table and the process repeats until every value from our outer table has been compared to every value in our inner table.
This description is a worst case example of the performance of a nested loop join.
Read the whole thing. Understanding physical join operators is a key to figuring out if your data retrieval is as fast as it should be.
There are a whole bunch of limitations in creating indexed views. One of them is that you can’t base the query on DISTINCT.
Fair enough, but you can do GROUP BY.
And what’s pretty cool is that the optimizer can match a query written to find distinct values to an indexed view with a group by.
Click through for the best example ever.
Kevin Feasel
2019-01-10
Data Types, Query Tuning
No Comment