SQL Server Consulting, Education, and Training
Thanks for watching!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
This May, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning.
All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of great content.
Get your tickets here for this event, taking place Friday, May 10th 2024 at the Microsoft Offices in Burlington.
Here’s what I’ll be presenting:
Session Abstract:
Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.
I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.
I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.
Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.
Get your tickets here for this event!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Darling Data was born too late for MySpace, Friendster, Facebook, and Make Out Club (MOC).
While I patiently wait for 4chan to make business accounts available, I figured I’d try out something new.
So, you can find bite (byte?) sized chunks of my videos on TikTok here: Darling Data
I’ve also hired my wife (clearly a nepo baby) as a social media intern to slice, dice, edit, caption, #hashtag, and add complimentary gifs to the clips.
If I’m not careful, she’ll be better at SQL Server than me by the time this goes to international media outlets. I would have hired her to write this press release, but she went to bed.
Since I’m clearly not good at these things, please do the TikTok version of liking and subscribing over there, fellow kids.
This post was brought to you by the Senior Executive Management Team at Darling Data, a subsidiary of Beer Gut Magazine, Inc.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
This May, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning.
All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of great content.
Get your tickets here for this event, taking place Friday, May 10th 2024 at the Microsoft Offices in Burlington.
Here’s what I’ll be presenting:
Session Abstract:
Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.
I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.
I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.
Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.
Get your tickets here for this event!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I was quite publicly mystified by the coupling of Azure Data Studio into the SQL Server Management Studio installer. They’re different tools for different people.
This isn’t a tirade against Azure Data Studio, nor is it a victory lap since apparently the feedback item I posted was part of the decision to remove it.
This is purely informational, since the announcement that it’s not in there anymore is nearly as quiet as the announcement that it was being included back in SQL Server Management Studio 18.7, and only slightly louder than the availability of a command line switch to skip installing it.
Back when I initially complained about the inability to skip installing Azure Data Studio, there was a lot of talk about how SQL Server Management Studio would increasingly rely on it for new functionality. I suppose that was either untrue, or the roadmap changed significantly.
Quite cynically, I thought it was a cheap way to increase the install base of a new product, but who knows? I assume Microsoft has better telemetry about usage than binaries just existing. Again, who knows?
A further miffance was that you could download and install Azure Data Studio independently, but not SQL Server Management Studio.
If you read through Erin Stellato’s post about the SQL Server Management Studio roadmap, where version 20 was fully released:
For SSMS 20 we have removed Azure Data Studio from the installation. In addition to being a highly voted item (Make ADS An Optional Install Alongside SSMS · Community (azure.com)), this aligns with the future extension work for SSMS 21 and we decided to make the change in the current release.
So, hooray! Dreams do come true, etc. When I was but a young boy, I dreamed that someday my desires would be aligned with future extension work for SSMS.
I’d also like to say here that being the public face of SQL Server Management Studio makes Erin Stellato about the bravest soul I know. It’s a tool that millions of people rely on, and nearly everyone actively complains about. Part of my RSS feed is not only posts from Microsoft about data platform related stuff, but also the comment feed. She has saintly patience in her responses to the comments on these things. Comments on the internet maintain their position and title as the worst things on earth. Erin deserves infinite credit for doing the job that she does as well as she does it.
If you’re keen on moving to SQL Server Management Studio 20, you can download it here.
If you’re still stuck using older versions because of a new bug or an old feature, you can skip installing Azure Data Studio by running the installer via the command prompt:
SSMS-Setup-ENU.exe /Passive DoNotInstallAzureDataStudio=1
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I have never once seen anyone use these. The most glaring issue with them is that unlike a lot of other directives in SQL, these ones just don’t do a good job of telling you what they do, and their behavior is sort of weird.
Unlike EXISTS and NOT EXISTS, which state their case very plainly, as do UNION and UNION ALL, figuring these out is not the most straightforward thing. Especially since INTERSECT has operator precedence rules that many other directives do not.
So, cool, if you know you want a unique set of rows from somewhere, these are good places to start.
What’s better, is that they handle NULL values without a lot of overly-protective syntax with ISNULL, COALESCE, or expansive and confusing OR logic.
The tricky part is spotting when you should use these things, and how to write a query that makes the most of them.
And in what order.
Often the best way to get a feel for how things work is to run simple queries and test the results vs. your expectations, whatever they may be.
I like these queries, because the UserId column in the Comments table is not only NULLable, but contains actual NULLs. Wild, right?
SELECT
c.*
FROM dbo.Comments AS c
WHERE c.UserId IS NULL
AND c.Score > 2
INTERSECT
SELECT
c.*
FROM dbo.Comments AS c
WHERE c.UserId IS NULL
AND c.Score > 3
ORDER BY
c.Score;
Running this will return results where a Comment’s Score is greater than 3 only, because that’s the starting point for where both query results begin to match results across all the columns.
Note that the UserId column being NULL doesn’t pose any problems here, and doesn’t require any special handling. Like I said. And will keep saying. Please remember what I say, I beg of you.
Moving on to EXCEPT:
SELECT
c.*
FROM dbo.Comments AS c
WHERE c.UserId IS NULL
AND c.Score > 2
EXCEPT
SELECT
c.*
FROM dbo.Comments AS c
WHERE c.UserId IS NULL
AND c.Score > 3
ORDER BY
c.Score;
This will only return results from the “first” query (often referred to as the left, or outer query) with a Score of 3, because that’s the only data that exists in it that isn’t also in the “second” (or right, or inner) query.
Both queries will find many of the same rows after Score hits 2 or 3, but those get filtered out to show only the difference(s) between the two.
In case it wasn’t obvious, it’s a bit like using NOT EXISTS, in that rows are only checked, and not projected from the second/right/inner query, looking for Scores greater than 3.
Again, NULLs in the UserId column are handled just fine. No ISNULL/COALESCE/OR gymnastics required.
I’m really trying to drive this home, here.
SQL Server 2022 introduced some “modern” ANSI syntax. By modern, I mean that IS DISTINCT FROM was introduced to the standard in 1999, and IS NOT DISTINCT FROM was introduced in 2003.
While no database platform adheres strictly or urgently to ANSI standards, waiting 20 years for an implementation in SQL Server is kind of really-extra-super-duper son-of-a-gun boy-howdy dag-nabbit-buster alright-bucko hold-your-horses listen-here-pal levels of irritating.
Think of all the useless, deprecated, and retired things we’ve gotten in the past 20 years instead of basic functionality. It’s infinitely miffing.
Anyway, I like these additions quite a lot. In many ways, these are extensions of INTERSECT and EXCEPT, because the workarounds involved for them involved those very directives. Sort of like Microsoft finally adding GREATEST and LEAST, after decades of developers wondering just what the hell to do instead, I hope they didn’t show up too late to keep SQL Server from being bullied by developers who are used to other platforms.
We can finally start to replace mutton-headed, ill-performing syntax like this:
SELECT
c = COUNT_BIG(*)
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON u.Id = p.LastEditorUserId
OR (p.LastEditorUserId IS NULL);
With stuff that doesn’t suck, like this:
SELECT
c = COUNT_BIG(*)
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON u.Id IS NOT DISTINCT FROM p.LastEditorUserId;
The query plan timings tell enough of a story here:
But not everyone is able to use the latest and greatest (or least and greatest, ha ha ha) syntax. And the newest syntax isn’t always better for performance, without additional tweaks.
And that’s okay with me. I do performance tuning for a living, and my job is to know all the available options and test them.
Like here. Like now.
Let’s compare these two queries. It’ll be fun, and if you don’t think it’s fun, that’s why you’ll pay me. Hopefully.
SELECT
c = COUNT_BIG(*)
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON u.Id IS NOT DISTINCT FROM p.LastEditorUserId;
SELECT
c = COUNT_BIG(*)
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON u.Id = p.LastEditorUserId
WHERE EXISTS
(
SELECT p.LastEditorUserId FROM dbo.Posts AS p
INTERSECT
SELECT u.Id FROM dbo.Users AS u
);
Here’s the supporting index that I have for these queries:
CREATE INDEX
LastEditorUserId
ON dbo.Posts
(LastEditorUserId)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
It’s good enough. That’s what counts, I guess. Showing up.
At first glance, many queries may appear to be quite astoundingly better. SQL Server has many tricks up its sleeves in newer versions, assuming that you’re ready to embrace higher compatibility levels, and pay through the nose for Enterprise Edition.
This is a great example. Looking at the final query timing, you might think that the new IS [NOT] DISTINCT FROM syntax is a real dumb dumb head.
But unless you’re invested in examining these types of things, you’ll miss subtle query plan difference, which is why you’ll pay me, hopefully,
The second query receives the blessing of Batch Mode On Row Store, while the first does not. If we use the a helper object to get them both functioning on even terms, performance is quite close:
SELECT
c = COUNT_BIG(*)
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON u.Id IS NOT DISTINCT FROM p.LastEditorUserId
LEFT JOIN dbo.columnstore_helper AS ch
ON 1 = 0;
In this case, the slightly tweaked query just slightly edges out the older version of writing the query.
There are many ways to write a query, and examine the performance characteristics. As SQL Server adds more options, syntax, capabilities, and considerations, testing and judging them all (especially with various indexing strategies) becomes quite an endeavor.
I don’t blame developers for being unaware or, or unable to test a variety of different rewrites and scenarios. The level of understanding that it takes to tune many queries extends quite beyond common knowledge or sense.
The aim of these posts is to give developers a wider array of techniques, and a better understanding of what works and why, while exposing them to newer options available as upgrade cycles march bravely into the future.
Keeping up with SQL Server isn’t exactly a full time job. Things are changed and added from release to release, which are years apart.
But quite often I find companies full of people struggling to understand basic concepts, troubleshooting, and remediations that are nearly as old as patches for Y2K bugs.
My rates are reasonable, etc.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.