A Fine Slice Of SQL Server
Erik Darling notes that adding an ORDER BY clause to a query can change the underlying query plan:
Sometimes I think it’s interesting how adding a seemingly useless or harmless thing to a query can change the query plan.
Here’s a quick example using an Order By on an indexed column.
Your mileage may vary on whether that’s a good thing.
Leave a CommentWe can see that the MaxLength for the string columns is set at -1, meaning they are being passed over TDS to SQL Server as LOBs (Large Objects) or essentially as MAX datatyped columns, and this can impact performance in a negative manner. If we change the .NET DataTable definition to be strongly-typed to the schema definition of the user-defined table type as follows and look at the MaxLength of the same column using a debug break:
This can be important, especially if you make a lot of calls or use fairly large TVP sizes.
Leave a CommentErik Darling doesn’t want to mess with your cursors (that much):
Read the code. Understand the requirements.
I tune queries all day long. The number of times someone has said THIS CURSOR IS A REAL BIG PROBLEM and been right is pretty small.
Often, there was a tweak to the cursor options, or a tweak to the query the cursor was calling (or the indexes available to it) that made things run in a more immediate fashion. I want to tune queries, not wrestle with logic that no one understands. Old code is full of that.
I’ll grant the premise (and add my own case where a cursor was necessary to solve the problem), though I did work at one company where the entire product logic was driven by nested cursors 5 or 6 levels deep. Those were really big problems. I think you’ll find the problem most frequently in shops with a heavy dose of Oracle, as Oracle cursors do perform well.
Leave a CommentGrant Fritchey reminds us to look at the first operation when viewing an execution plan:
The first time you see a new execution plan that you’re examining to fix a performance problem, something broken, whatever, you should always start by looking at the first operator.
First Operator
The first operator is easily discerned (with an exception). It’s the very first thing you see in a graphical execution plan, at the top, on the left. It says SELECT in this case:
It’s easy to overlook, but Grant gives some good reasons not to do so.
Leave a CommentChris Webb takes us through the Diagnose Step button in Power Query:
As you might have guessed, it’s closely related to the Query Diagnostics functionality that was introduced back in October. Whereas the existing Query Diagnostics functionality allows you to see what happens inside the Power Query when a query is executed, this new feature does something similar but allows you to run a query up to a specific step. This is useful in scenarios where you want to reduce the diagnostics data you are collecting to a subset of the steps in the query without having to comment out a lot of M code.
It looks pretty useful.
Comments closedTaiob Ali points out something added to SQL Server 2017 (and later 2016 and 2014):
Microsoft SQL Server Management Studio (SSMS) version 17.5 added new showplan attributes UdfCpuTime and UdfElapsedTime to QueryTimeStats. These two attributes will measure the time and CPU spent on user-defined functions within a query execution hence helping to discover the impact of UDF execution within full query execution. This feature was first added in SQL Server 2017 CU3 and was backported to SQL Server 2016 SP2. Finding the execution time and CPU for UDF was always a challenge for Data professionals because the number of times a function will execute will vary.
This was a blind spot for a very long time.
Comments closedThe plan is created under CL 140, but the estimation number of rows for the table variable is not 1 but the actual one. Even if you would specify the FORCE_LEGACY_CARDINALITY_ESTIMATION hint, the query will be deferred compiled and behavior of table variable would be the same.
Table variable deferred compilation respects settings at the database scope and ignores all hints except one.
Click through to learn what that one hint is.
Comments closedI’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 closed