A Fine Slice Of SQL Server
Grant Fritchey searches for included columns:
A common issue in performance is the lookup, key or RID, but the question frequently asked is, which columns are looked up? You need to know in order to try to address the issue. The answer is easy enough to find. Let’s take a quick look.
Read on for the answer, which you can find in SQL Server Management Studio. Or by shredding a bunch of XML if that’s the kind of thing you’re into.
Comments closedItzik Ben-Gan has some solutions to show:
This month, I’m going to start exploring the submitted solutions, roughly, going from the worse performing to the best performing ones. Why even bother with the bad performing ones? Because you can still learn a lot from them; for example, by identifying anti-patterns. Indeed, the first attempt at solving this challenge for many people, including myself and Peter, is based on an interval intersection concept. It so happens that the classic predicate-based technique for identifying interval intersection has poor performance since there’s no good indexing scheme to support it. This article is dedicated to this poor performing approach. Despite the poor performance, working on the solution is an interesting exercise. It requires practicing the skill of modeling the problem in a way that lends itself to set-based treatment. It is also interesting to identify the reason for the bad performance, making it easier to avoid the anti-pattern in the future. Keep in mind, this solution is just the starting point.
Click through for a solution which is straightforward but slow.
Comments closedNigel Foulkes-Nock ran into a problem:
In many cases, this works well, but recently I’ve seen examples where it becomes troublesome, specifically when trying to process higher data volumes of data.
The same code can behave perfectly on a small dataset, but then cause issues on a larger database built in exactly the same format. This results in Queries changing from taking a few seconds to struggling to complete.
Read on to see why, as well as one solution that Nigel details.
Comments closedJoe Obbish goes beyond the obvious reason:
Upon reading the title, you may be thinking that of course parallel scans will be slow in the cloud. Cloud storage storage simply isn’t very fast. I would argue that there’s a bit more to it.
Click through for a deep dive with some advice on what you might do to fix a specific (but common) scenario.
Comments closedItzik Ben-Gan posted an interesting T-SQL challenge on SQL performance dot com. I’m writing up my solution in my own blog post because I have a lot to say and getting code formatting right can be tricky in blog post comments. For reference, my test machine is using SQL Server 2019 CU14 with an i7-9700K CPU @ 3.60 GHz processor. The baseline cursor solution completes in 8465 ms on my machine.
Click through for a bit of formal logic and a lot of tuning.
Comments closedEitan Blumin explains what hypothetical indexes are and why they might be useful:
Using Hypothetical Indexes, you can generate an estimated execution plan for a given query, that would essentially assume the existence of a “hypothetical” index as if it actually exists as a real index. Compare that estimated execution plan to its counterpart without the hypothetical index, and you’ll be able to determine whether creating this index for real is worth the time and effort.
Hypothetical Indexes are actually nothing new in SQL Server. It existed since SQL Server version 2005. However, its use is still not widespread to this day. Most likely because it’s not very easy to use and the relevant commands are undocumented.
Click through to see how to use them and an important warning if you try it in production.
Comments closedErik Darling has a recommendation:
When working with ORMs, care has to be taken to strongly type your parameters to match the data type, length, precision, and scale of the columns those parameters will be compared to. Time and time again, I see the same patterns with string parameters:
– They’re unnecessarily typed as Unicode/nvarchar
– They’re not defined with an appropriate length
– They’re used as catch-all parameters for temporal types (dates, etc.)
Spoiler: these aren’t benefits.
Comments closedJoe Obbish skips past the commercials:
If you’re like me, you started your database journey by defining cursors with the default options. This went on until a senior developer or DBA kindly pointed out that you can get better performance by using the FAST_FORWARD option. Or maybe you were a real go-getter and found Aaron Bertrand’s performance benchmarking blog post on different cursor options. I admit that for many years I didn’t care to know why FAST_FORWARD sometimes made my queries faster. It had “FAST” in the name and that was good enough for me.
Recently I saw a production issue where using the right cursor options led to a 1000X performance improvement. I decided that ten years of ignorance was enough and finally did some research on different cursor options. This post contains a reproduction and discussion of the production issue.
I thought everybody knew how this works: the database streams the data tape from the supply pully to the play shaft by using a sprocket to rotate the gear in the cassette at a fixed speed. The FAST_FORWARD cursor option engages the fast forward idler in the VCR database and causes rotation to occur more rapidly than normal.
When you put NOLOCK in your query, SQL Server will:
– Read rows twice
– Skip rows altogether
– Show you changes that never actually got committed
– Let your query fail with an error
This is not a bug. This is by design.
There are reasons why you might want to use NOLOCK, but start with no and you’ll be in better shape.
Also, remember that NOLOCK really means “No, lock!”
Chad Callihan makes me want to change the title to “Getting Sarge a Date”:
We’ll use the StackOverflow2013 database for this example. Let’s say we want to return the users created in 2013. One way to return this data is to use the YEAR() function to pull out the desired year for our query:
For the reference, check out Chad’s prior post. My expectation is that about 90% of people in the US who are aware of the term pronounce it “Sarge-able” instead of “Sar-guhble” and therefore immediately think of Sergeants.
1 Comment