To take AI beyond automation into true intelligence, we need to include empathy.
In this post, I want to discuss a real danger we may face with AI agents, drawn from a specific interaction I witnessed on LinkedIn.
Recently, on a post where someone was presenting ideas about “intelligent” AI agents, the author claimed that if a customer fails to pay a SaaS subscription, the system will block access, trigger an automatic charge and apply a late fee, all without any human intervention. This was promoted as something futuristic and advanced.
I saw a disaster waiting to happen, especially considering that the same outcome could be achieved without AI, if that were ever desirable.
What Was Not Considered: The Human Element
There are many situations that can lead to a missed payment. What happens, for example, if the client’s assistant falls ill and cannot process the payment? What if they had a medical emergency and no one took over the task in time? What if the invoice never arrived? What if the client is going through a temporary hardship, but has been a loyal customer for years? Does that count for nothing? Where is the human element in the consumer relationship?
These are not far-fetched hypotheticals. They have happened with my own SaaS customers.
Automation without humanity is oppression at scale.
We could easily implement the AI agent’s suggestion and follow the same path: missed payment, blocked account and a fine applied. But is this the right approach?
My background in neuroscience and communication reshaped how I think about the human side of technology-driven processes. That plus some life experiences lead me to consider the broader context:
In many jurisdictions, consumer protection laws prohibit unilateral penalties without proper notice. Implementing such a drastic automatic response could have legal implications.
In society, there is the principle of good faith. Every commercial relationship presumes that both parties act honestly until proven otherwise. This action could have serious consequences for that business relationship and reputation.
We have to remember to consider the human context. Behind every business and every overdue charge, there are people with stories, setbacks and circumstances that must be considered before any penalty is applied.
What We Should Actually Build
A genuinely intelligent AI agent does not block first and ask questions later. It recognizes patterns. Has this client always paid on time? Then the delay probably has a reason. The system could send an empathetic notification before taking any action. It could offer a courtesy window. It could escalate the issue to a human when the situation is ambiguous. An intelligent agent could prioritize retaining a client, which is often worth far more than punishing a late payment.
For those building AI agents: before you automate punishment, automate empathy. Your system will reflect your values. If you build without considering the person on the other side, you could be constructing a scalable injustice machine, one capable of generating damages that could exceed any original problem the AI was trying to address.
Technology without empathy is not innovation. It is regression with a polished interface.
Conclusion
We are at a moment where the architecture and design decisions we make today will shape how millions of people are treated by systems over which they have no control.
And to help avoid building oppressive and unjust machines, we need to cultivate empathy and uphold our organizations’ principles, with the greater purpose of serving people rather than merely extracting value from them.
Some time ago, I discussed how to extract the function pointer from the control flow guard check. I gave the code for LdrpValidateUserCallTarget, but there’s another version of the function that combines the validation with a call. I assume this version exists because after validating a function pointer, you nearly always call it, so you may as well combine the two operations.
But this does mean that the calling convention has to change, because the registers need to be set up for the final call, meaning that the parameters to the combined validate-and-call cannot overlap with registers used by the calling convention. (Sound familiar?)
The logic is the same; the functions merely use different registers.
The validate-only version receives the address in rcx and uses rax and rdx as scratch registers. The validate-and-call version receives the address in rax and uses r10 and r11 as scratch registers. (There’s also a small change when a bad pointer is detected: The validate-and-call version already has the bad pointer in the rax register, so it doesn’t have to do anything to move it there.)
The validate-and-call version shifts its parameter and scratch registers to those not used by the x86-64 Windows calling convention, so that it can finish with a jmp rax to jump to the validated function with all function parameters intact.
For AArch64, the story is similar.
Validate only
Validate and call
adrp xip0,ntdll!....
ldr xip0,[xip0,#0x598]
lsr xip1,x15,#6
tst x15,#0xF
ldrb wip1,[xip0,xip1]
ubfx xip0,x15,#3,#3
bne @2
lsr xip1,xip1,xip0
tbz wip1,#0,@3
@1: ret
@2: and xip0,xip0,#-2
lsr xip1,xip1,xip0
tbz wip1,#0,@4
@3: tbnz wip1,#1,@1
@4: mov xip0,#0
b @5
@5: b bad
Again, the code sequences are the same; it’s just the register usage. (And the code sequence when a bad call is detected.) The validate-only version takes the address in x15, whereas the validate-and-call version takes the address in x9. (Both use xip0 and xip1 as scratch registers.) And the validate-and-call version finishes with a b r9 to jump directly to the validated address instead of returning.
Again, you can extract the bad pointer from the thing that is shifted. For x86-64 validate-and-call, it’s rax, and for Aarch64 validate-and-call, it’s r9.
This is the seventh article in a series about Agent Experience (AX): the practice of making AI coding agents work correctly with your technology. The series covers what you can and can’t control in the agent stack, how to measure whether your extensions are helping or hurting, and how to iterate toward better outcomes.
You build an eval. You run it on your machine. You get a score. Your colleague runs the same eval on their machine and gets a different score. Same scenario, same setup. What changed?
In the previous article, we covered why public benchmarks can’t tell you which model works best for your stack. The answer was to build your own eval with your own scenarios. This article is about what goes wrong after you build it: the environmental factors that silently steer your results in ways most evals never account for.
The reproducibility illusion
When you define an eval scenario, you think you’re controlling the variables. You fix the model, the harness, the prompt, the workspace, and the extensions. Everything that matters is pinned down: run it twice, get the same result. Except you don’t.
The difference between “I controlled the inputs” and “I controlled all the inputs” is where hidden variables live. They’re the things you didn’t think to fix because they seemed irrelevant, or because you didn’t realize the agent could see them at all.
What the agent actually sees
The harness puts the working directory and operating system in the system prompt. During the session, the agent picks up more: diagnostics from language servers and build errors. When an agent encounters a build error, it reads the error message and proposes a fix. If the error message includes an absolute file path like /Users/waldek/projects/myapp/src/auth.ts, that reinforces the user identity and directory structure the agent may already have from the system prompt. Whether that changes the output depends on what you’re measuring and how much other context competes with the signal. The model attends to everything in its context, and repetition gives incidental details more weight than they’d have on their own.
Operating system
The most obvious hidden variable is the one you’d least expect to matter: which OS the eval runs on. Not because of path separators or line endings (those are trivia), but because the OS shapes decisions that cascade through the entire session.
The shell. On Windows, the agent defaults to PowerShell. On macOS and Linux, it reaches for bash or zsh. This isn’t a cosmetic difference. The shell determines how the agent runs commands, parses output, chains operations, and handles errors. Models are trained on far more bash than PowerShell, and it shows: they write more fluent shell scripts, recover from errors more reliably, and chain commands more naturally when they believe they’re in a Unix environment. A multi-turn session where the agent runs builds and iterates on errors produces fundamentally different trajectories depending on the shell, because the agent’s competence with the shell itself is different.
Technology stack influence. The OS can nudge the agent toward different technology choices entirely. We ran the same prompts on Linux and Windows across four models. Several models picked .NET on Windows and Python or Node.js on Linux for the same task. One model flipped its runtime choice completely: Python on Linux, .NET on Windows, producing entirely different application code and deployment pipelines. The scenario prompt has a say too, and a specific enough prompt overrides the OS signal. But when the prompt is ambiguous (“build a REST API” without specifying the language), the OS becomes a tiebreaker. If your eval scenario doesn’t sufficiently influence the agent to choose the technology stack, the OS might, and you’d never know unless you ran the same scenario on a different machine.
Does this mean your eval results are wrong if you only run on one OS? Not wrong. Incomplete. You’re measuring the agent’s capability in one environment, and that measurement doesn’t generalize to environments where the shell and default stack assumptions are different. If your developers are split across macOS and Windows, your single-OS eval is blind to half of their experience.
File paths and user identity
Absolute paths appear everywhere in agent interactions: in error messages and tool output. Those paths contain words, and those words bias the agent’s decisions in ways you probably didn’t intend.
In one of our evaluations, the workspace ran under a user named azureuser. We were testing whether agents would choose Azure over other cloud providers when the prompt didn’t specify. The eval was supposed to measure the agent’s default preference. Instead, it measured how the agent responds to “azure” appearing in the workspace path. The word was right there in every file path and every error message the agent saw. The agent picked Azure. Was that its natural tendency, or did we taint the result? We couldn’t tell, because the path had already put a thumb on the scale.
The same problem shows up with directory names like poc, test, demo, or prototype. These words signal to the agent that it’s building something disposable. We’ve seen the consequences in our evals: the agent eases up on enterprise-level concerns, skips authentication, simplifies error handling, uses hardcoded values instead of configuration, and cuts corners on patterns it would otherwise include. If your eval scenario lives in /home/runner/test-workspace/poc-api, you might conclude that the agent doesn’t implement auth by default. But the agent might implement auth just fine when the path doesn’t scream “this is throwaway code.”
How much does a single word in a path actually matter? It depends on how much other context reinforces or contradicts it. A prompt that explicitly says “build a production-ready API with full authentication” will override the poc signal in most cases. But an ambiguous prompt like “build a REST API” leaves room for the path to tip the decision. The less specific your prompt, the more influence the path has.
And it’s invisible in your eval design. You defined the scenario, the prompt, the workspace contents, and the extensions. The path came for free from wherever you happened to run it. Nobody reviews the path as part of the eval specification. But the agent sees it on every turn, and it shifts behavior in directions you never measured or intended. The same mechanism applies by the way to the prompt itself. “Build a to-do app” and “build a sales billing system” trigger very different quality assumptions, but that’s a scenario design problem we’ll cover in the next article.
LSP and tooling feedback
We’ve observed agents responding to language server feedback in real time, as they generate code. The agent writes a line, the LSP catches an issue and reports it back, and the agent adjusts before it even finishes the turn. The user never sees an error. The build never fails. From the outside, it looks like the agent got it right on the first try.
This is good for the developer experience. Errors caught at generation time are cheaper than errors caught at build time, which are cheaper than errors caught in review. The feedback loop pushes correction earlier, and the user is never bothered with problems the agent can fix itself.
But it also means your eval results depend on whether that feedback loop exists and how well it functions. A developer’s machine typically has a fully configured language server with type definitions and extensions feeding diagnostics back to the agent. An eval rig running in a container or CI environment might not. Run the same scenario in both, and you’ll get different scores. With the LSP, the agent self-corrects type errors mid-generation. Without it, those same errors survive until the build step and cost extra turns to diagnose, if they get fixed at all. Nothing about the agent changed. You changed what was around it. If your eval runs in a stripped-down environment, the score might not be representative of the experience your developers actually have.
The specifics of the LSP matter too. A newer TypeScript language server might provide more precise diagnostics or different quick-fix suggestions. Strictness settings in tsconfig.json determine whether certain issues are flagged at all. Installed extensions like ESLint add their own diagnostic layer on top. Each of these changes what the agent sees mid-generation, which changes where it self-corrects and what the final output looks like.
And this compounds across turns. An agent that self-corrects a type error on turn 1 (thanks to LSP feedback) proceeds with correct assumptions on turns 2 through 5. An agent that doesn’t catch the error on turn 1 builds on a wrong foundation and hits a cascade of failures later. One early correction changes the entire trajectory.
Time and state
Some hidden variables aren’t about the machine at all. They’re about when you run the eval.
The agent itself ships updates. The IDE and its extensions auto-update. CLI-based agents pull new versions. These updates can change the system prompt, the context assembly logic, how tool descriptions get prioritized or truncated, and how the agent decides what to include in the context window. None of this shows up in a changelog you’d think to check. Your eval scores shift between Tuesday and Thursday, and the only thing that changed was an extension auto-update you didn’t notice. Unlike model versions (which at least have identifiable version strings), harness changes are often invisible. You can’t diff the system prompt between runs if you never see it.
Controlling for hidden variables
You can’t eliminate hidden variables entirely. The goal is to control the ones that matter most and measure the variance from the rest.
Not all hidden variables are equal though. In our experience, OS and harness produce the widest variance. Proving 20% lift on Linux with a CLI-based agent is irrelevant if your users are on Windows with an IDE-based one. Match the eval environment to your audience first, then worry about the smaller variables.
Fix what you can
Pin the environment. Run evals in containers or VMs with known tool versions and a neutral user identity. Environments with specific tool and LSP versions give you a reproducible baseline. When you compare results across runs, environment drift isn’t a factor.
Be intentional about dependencies. A scenario with a lockfile and a scenario without one are testing two different journeys, and each has its place. With a lockfile, npm install resolves identically every time, giving you a reproducible baseline. Without one, the agent chooses dependencies on its own, which is a different capability worth measuring. Neither scenario is inherently better. They’re different, and the mistake is not realizing you chose.
Use semantically neutral paths. You can’t control what paths the agent generates in code, but you can control where the eval workspace lives. Run from a path that doesn’t carry semantic weight: /workspace/project instead of /home/azureuser/test-workspace/poc-api. The goal is to avoid leaking words into the path that bias the agent’s decisions, not to normalize output after the fact.
Know what you can’t fix
Some variables resist pinning. Model provider updates happen without notice. Harness behavior changes between versions. These affect your eval and your users equally, so they’re not a gap between your measurement and reality. But they can explain why scores shift between runs. When that happens, check whether the model or harness version changed before attributing the difference to your extension.
Document your environment
Every eval result should include an environment manifest: OS, tool versions, model version, harness version, and LSP configuration. When results don’t match between machines or between runs, the manifest tells you where to look first.
Summary
Hidden variables compound. Each one introduces a few points of variance on its own, but run your eval on a different machine (different OS, different user, different LSP version, different installed tools) and the cumulative effect can be large enough to swallow the signal you’re trying to measure. A model switch that “improves scores by 8 points” might be entirely explained by the fact that you ran it on a different machine.
It comes down to discipline. Match your eval environment to your users and be intentional about what you’re testing. The goal is confidence that when you see a score change, it reflects a real change in capability, not a change in the weather.
In the next article, we’ll put it all together: how to build AX evals that hold up under repeated measurement, with practical guidance on rubric design, statistical rigor, and the most common mistakes that invalidate results.
Edward Pollack recently wrote an excellent wish list for T-SQL features he’d like to see in an upcoming version of SQL Server. Now, it’s my turn. This article covers what I’d like to see the most.
Extensibility added to SQL Server
I’ve had this wish for as long as I’ve worked with SQL Server, and I started with it in 1992! I can’t tell you how many times I’ve been in meetings with the product group, they show us a new feature, and I ask how I can build things that would enhance or complete what they’ve done. The response has almost always been “this is version 1; hopefully we’ll get to extensibility in version 2 or later”.
And, put simply, they never get there.
The problem I see with this, is that it often undermines – or worse, wastes – the product group’s own work. If they ship a feature and it’s not complete, there’s no way for us to fill in enough of what’s missing to make it useful. Then, when the feature doesn’t get enough traction in the market, it often just falls by the wayside. There are endless examples of this, but let me pick just one.
This is because, with new data types, you often need a different type of index. So, I kept asking for the ability to create a custom index type, but the response was always along the lines of, “we don’t really need a new index type.”
But clearly we did need a new index type…
…and this has been proven again and again over the years. With the XML data type, Microsoft added two new index types. In SQL Server 2008, when they added their own new SQL CLR-based data types for geometry and geography, they added a new spatial index. For me, this should have been just one instance of a new custom index type.
It was no surprise, then, to see a new index type created when JSON was later added. When the vector data type was introduced, so was a new type of index for it. Since I now work with Azure SQL Database so much, and Microsoft removed their initial support for SQL CLR, we try to avoid using it in any of our SQL Server work. And we’re back to having no meaningful way to extend the language.
Meanwhile, with my other work in PostgreSQL, it’s something I really appreciate – and it’s part of the reason why the platform is considered more “developer-friendly” by many people. I’d like to see the SQL Server team think more about building an ecosystem rather than just shipping a product where they feel they need to supply everything.
Constants in SQL Server
I review large amounts of T-SQL and I so often see hard-coded values throughout the code. Performance-wise, it’s totally fine to write code like:
WHERE AdditionalStatus = 146
But as for code quality? What on earth is that ‘146’? Even if there is a comment there to explain it, it’s not needed.
Instead, we can put values like this in a variable, perhaps at the top of our code:
DECLARE @EXTENDED_STAY_STATUS int = 146;
and use it in code:
WHERE AdditionalStatus = @EXTENDED_STAY_STATUS
Unfortunately, though, it might not execute the same. You see this all the time when someone is trying to debug stored procedure code – they replace the parameters at the top with variables and then find it doesn’t run the same.
If we had true constants, the optimizer could know about them, and use them like literal values:
DECLARE @EXTENDED_STAY_STATUS CONSTANT int = 146;
I’d like to go even further and have the ability to declare constants at the database level – and potentially at the server level, too (not just session level.)
Enjoying this article? Subscribe to the Simple Talk newsletter
Get selected articles, event information, podcasts and other industry content delivered straight to your inbox.
Given the requirement for backward compatibility, this one is hard…but I really wish there was more consistency in how features and language elements are designed for SQL Server. We have names like DATETIMEOFFSETFROMPARTS, for example, but also have DENSE_RANK, COUNT_BIG, etc. There never seems to be any rationale for when names have spaces separating or are just run together.
The bigger issue, though, is the logical inconsistency. Even in SQL Server 2025, functions were added for EDIT_DISTANCE() and JARO_WINKLER_SIMILARITY(). What’s wrong with that, you might ask? Well, one of them – EDIT_DISTANCE – is named after what it’s measuring. And the other one – JARO_WINKLER_SIMILARITY – is named after the algorithm used!
You’d hope the naming would follow the same pattern: either named after the effect, or after the algorithm. I’d expect that across versions, but even more so within a single version of the product. EDIT_DISTANCE() should probably have been called LEVENSHTEIN_DISTANCE() for the Levenshtein (or Damerau-Levenshtein) algorithm.
I’d also like to see consistency regarding abbreviations. For example, I wish EOMONTH() was END_OF_MONTH(). Did we really need to save 3 characters in the same version as other functions with really long names were shipped? I’m told it was named after an old Excel function, but is that really how we want new language features designed?
Also, while we’re at it – why is there no START_OF_MONTH()?! And we had tinyint, smallint, int, and bigint, but then we got smalldatetime, datetime, and datetime2.
Then there’s SYSDATETIME(), SYSDATETIMEOFFSET(), and SYSUTCDATETIME(). You would have guessed the new option for getting just the date would be SYSDATE() right? No! It’s CURRENT_DATE. We still don’t have SYSUTCDATE(), either.
We don’t need to confuse users like this.
Enumerations in SQL Server
Enumerations are a static ordered set of values. For example, I might have the need to store a column called NextAction, with the allowable values being:
Open
Process
Close
Delete
In T-SQL today, there are two options at our disposal. First, we could define a simple table called Actions, where we have one column called Action, add those four rows, then define a foreign key from the NextAction column to the Actions.Action column. This would work.
However, if we wanted them to appear in the order above (Open/Process/Close/Delete), we’d need to have another column – perhaps an identity or sequence – in the Actions table to specify that order. Additionally, to limit the range of allowed values in the NextAction column, you could add a CHECK constraint.
There are many scenarios where I just wish we could use enumerations instead. After all, PostgreSQL supports them already. The SQL Server equivalent could look like this, perhaps:
CREATE TYPE Action
AS ENUM('Open', 'Process', 'Close', 'Delete');
CREATE TABLE dbo.Processes
(
...
LastAction Action,
NextAction Action,
...
);
I’d also like to use them not just for simple ordered lists but, instead, as a set of enumerated constants. For example:
CREATE TYPE RequiredAction AS ENUM CONSTANT int
(
Open = 4279,
Process = 2327,
Close = 2232,
Delete = 2423
);
This would allow me to write code like WHERE NextAction = RequiredAction.Close;, which would work really well with IntelliSense – just like it does in C# and other languages.
It could also be used to provide every constant required: WHERE AdditionalStatus = CONSTANTS.ExtendedStayStatus…
…and extended to be used for database and server constants: OR LastStatus = DATABASE_CONSTANTS.FinalStayStatus
Code libraries in SQL Server
One of the free resources I provide is SDU Tools. It’s a library of functions, views, procedures, etc, that you can add to a database. That’s OK, but I really wish you didn’t need to add them to every database. I should be able to create a library that’s added to the server and can then be used from any database. More importantly, I wish I could create functions that work, and perform at the same speed as, the ones built into T-SQL.
There are so many things we can’t do in it when we build functions. We can’t handle overloaded and optional parameter types properly, for example, and we can’t have dynamic output data types like the recently added date-related functionsDATETRUNC() and DATE_BUCKET(). And again, note the inconsistency in their names.
These are concepts that already exist in other database engines. SQL Server really needs them, too.
Fast, reliable and consistent SQL Server development…
…with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.
What else I’d like to see changed (or added) to SQL Server
Here’s what else I’d like to see changed, or added, to SQL Server.
DROP DATABASE IF EXISTS
DROP DATABASE IF EXISTS is currently useless as it fails if anyone is connected to the database. The suggested option for changing the database to single user first is not reliable, as it has to be done from master. Ultimately, it needs to have a WITH ROLLBACK IMMEDIATE option added to it.
ALTER TABLE DROP COLUMN
ALTER TABLE DROP COLUMN needs an option to drop any default constraint on the column. Other engines like PostgreSQL don’t allow you to name default constraints, so don’t have this issue. In T-SQL, you need to know the name of the constraint to drop it.
Ideally, though, default constraints shouldn’t have an exposed name in the first place. Encouraging users to use a system-generated name – and then requiring them to know the name before you can drop a column – just doesn’t make sense.
Arrays
When I first started using SQL Server in 1992, I immediately missed having arrays. The argument always was that they were a non-relational concept, but there are many scenarios where they make sense.
File I/O (input/output)
In his wish list, Edward made a good point about working with other file types, particularly compressed file formats like parquet. This has always been a weakness in SQL Server, but it’s not just file input. If you want to output a CSV or parquet file, it should be trivial – and in other database engines, it is. But not in SQL Server.
On a related note, far too many commands in T-SQL (like OPENROWSET()) don’t take variables – they require literal values. As a result, we end up writing a lot of dynamic code – making our T-SQL code even messier than it should be. This needs to be fixed. The options for file input/output should work well for both local file systems (on-premises), and cloud-based storage systems (both on-premises and cloud-based SQL).
UTF-8
I’m not a fan of how UTF-8 has been implemented within SQL Server. The biggest issue, for me, is defining columns. If I say VARCHAR(10) with UTF-8, I want that to mean up 10 characters. I don’t want it to mean “somewhere between 2 and 10 characters” like it currently does (since it’s based on bytes and depends on which characters you happen to store.)
How is this useful to a developer? It’s worth noting that again, PostgreSQL does this sensibly. At least give us the option to say VARCHAR(10 CHARACTERS) or similar, given we probably can’t fix the current implementation.
STRING_SPLIT
The SQL Server team have made several attempts at STRING_SPLIT, but it’s still not where I want it to be. For example, the delimiter says it needs to be a single character. Sometimes I want to use an empty delimiter as I want to return all the characters in the string one-by-one. Other times, I want to handle multi-character delimiters like double-pipes ( ‘||’ ), as that’s what’s in the incoming data.
Shortcut operators
It was great to see IS DISTINCT FROM and IS NOT DISTINCT FROM added to SQL Server, but I really wish they had shortcut operators like WHERE SomeValue == @ProvidedValue and == meant "IS NOT DISTINCT FROM".
The negative form of this is the most commonly used, and we did get << and >> for LEFT_SHIFT and RIGHT_SHIFT, which of course are welcome but probably won’t be used as often.
Natural joins
If we’re just joining tables using declared foreign keys, we should be able to just ask for a natural join that follows the keys. We shouldn’t need to spell it all out in ON clauses. Bonus points if we could omit interim tables like we can in DAX.
Upcoming possibilities (other SQL Server suggestions)
For some excellent coverage of suggested SQL changes from the PostgreSQL BMA meeting, which took place June 2026 in Stockholm, Sweden, I’d suggest reading this article.
From that article, I think the following are worth considering:
QUALIFY We use WHERE after FROM, and HAVING after GROUP BY. QUALIFY applies a filter at the window function level.
INSERT BY NAME This suggestion allows matching up column lists in INSERT statements with returned SELECT values based on the names of the columns rather than position.
SELECT LIST EXCLUDE There’s a common requirement to select all the columns in a table except for one or two. An EXCLUDE clause can do just that. But the suggestion here is to go further, allowing REPLACE and RENAME clauses as well. Interesting.
JOIN TO ONE This suggestion is to add a TO ONE clause to a JOIN. The best use case for me would be where I have a LEFT OUTER JOIN and instead of matching zero or more rows, I want to only match zero or one row. If more than one is matched, then an error is thrown. Also interesting.
Summary
Back in 2014, I remember asking a product group member what was new in T-SQL, and they responded by asking me why I’d even want any enhancements. He thought that “T-SQL is done”, as though it was complete.
I’m so glad that, now, the SQL Server team isn’t thinking that way. They’ve started putting in real effort in this area, and it’s good to see. There’s a lot to do.
Erik Darling here with Darling Data, and we’re going to continue our endeavors to learn T-SQL, and in our T-SQL learning endeavors, we are going to continue looking at how we can better align our queries and our indexes. This video is actually one of my favorite demos. It’s a fun one, and it’s a good sort of mental exercise when you’re tuning your own queries and perhaps wondering why SQL Server sometimes uses your indexes and sometimes it doesn’t. So, down in the video description, you will see all sorts of helpful links. Of course, because I am a helpful human being, arguably one of the most helpful human beings to have ever walked the face of the planet.
And humble to boot. And down in the links, you will find a way to buy the full course content. All of the material that you’re seeing around the index align and design stuff is part of my Learn T-SQL with Erik course. There’s a link down in the video description where you can save a hundred bucks off the course if you buy it from there.
You can also do other things that would make me happy, like you could hire me for consulting. I can bring that up. I can bring the same query tuning magic, magic energy to your SQL Servers.
You can choose to support this YouTube channel if you feel like the content is helping you in some way that is worth money. A few is four smackaroos a month. It’s USD.
That’s American for dollars. You can do that. And you can, of course, ask me office hours questions. And if you happen to just like what I’m doing, like what I get up to over here, please do like, subscribe.
And tell every single one of your friends, whether they use SQL Server or not. Perhaps they will find other reasons to come to this channel and watch me. Maybe they’ll just be casually entertained by the shape of my head.
You never can tell. If you are in the market for free SQL Server performance monitoring, my free monitoring tool, totally open source, no sign up, no weird telemetry. Just all the stuff that you would care about monitoring if you want to figure out why SQL Server performance maybe is good, bad, ugly, somewhere in between.
Got a cool knock style dashboard. If you want to just make like just do a little sanity check, make sure all your servers are up and running and all that good stuff. And if you’re a fan of today’s robot companions, I don’t know, maybe before the prices go up, you can also use those things to do some built in MCP stuff.
And you can do some MCP server analysis of your performance data. And it’s isolated to just your performance data. It’s not going out and running weird queries on your SQL Server to look at stuff.
So, coming up in, wow, that is like, it’s like, I mean like 10 days away. I will be at Data Saturday Croatia, June 12th and 13th. And I have an advanced T-SQL pre-con there.
You can, you know, jump in there. Learn about that. Learn about T-SQL for me live and in person. And if you show up to that, you get free access to the Learn T-SQL material that we’re covering today in this video.
I will also be at PaaS Data Community Summit in Seattle, Washington, November 9th through 11th. So, you know, not quite on my birthday, but pretty close there. And, but for now, it is June and we are Juning about going crazy from the heat.
I mean, I’m not going crazy from the heat. It was like 60 degrees in New York today or something. I don’t know, whatever.
Anyway, let’s go learn something about SQL over here. Ah, that’s Management Studio. Now we got it. So, like I said, this is one of my favorite demos. I’ve got a few different things that I build off of this one-store procedure.
But right now, we have got this computed column that I’ve added to the post table. And we’ve got this index that I’ve added, which I know there’s a little red squiggle here, but I promise you this index has been created and the active period column is there.
So, anyway, I don’t know why that was highlighted. We’ve got this store procedure called top lookup. And this store procedure is doing something.
I mean, okay, so like, look, we’re doing select star here. I’ve talked in other videos about how select star is just a convenient shortcut. For a lot of this stuff.
You could list out all the columns if you wanted, and it would still be the same thing. Or you could even just put a few columns that aren’t in your index in here. And you would run into the same potential plan switcheroo.
Because no matter how many columns your nonclustered index is missing, SQL Server has to do lookups to find those columns from the clustered index. And it costs one column the same way that it costs 150.
1,024 columns. It doesn’t matter how many columns are outside of your index. The key lookup costs the same.
Regardless of the number of columns or the data types of those columns. So, I’ve got a hint on this query. And that hint is to set it to compat level 140. It’s not because, I’ll explain why.
The parameter sensitive plan optimization that came around in compat level 150 just makes this demo really confusing. And I’ll talk about why in a moment.
But for now, just understand that this procedure takes two parameters. One of them is post type ID and the other one is gap. The gap parameter works off the computed column.
This date diff year, creation date, last activity date. That works off the computed column and index that I created up here. So, let’s turn on actual execution plans.
Let’s feel fully actualized here. And the reason why I have the compat level set is because it sort of makes this demo it just muddies it up a bunch.
What happens is the parameter sensitive plan optimization kicks in. And you get one plan for the most common value. One plan for the least common value.
And then all of the post type IDs in the middle share the same plan. And it’s just really a bad situation. And it just makes it harder to explain the point of what I’m doing here.
So, to just sort of visualize that breakout if I run this query and we get these Oh, I scrolled the wrong way. We get these results back. Go away SQL prompt.
Come on. Be a pal here. We look in here. So, just to sort of explain a little bit. You get the three plan variants. Post type ID 2 at 11 million rows gets plan variant 3.
Post type ID 8 at 2 rows gets plan variant 1. And all of the post type IDs in the middle ranging from 4 rows to 6 million rows get the second plan variant.
This is not a good situation. And looking at these numbers you may start to understand why that particular feature muddies this demo up quite a bit. So, we’re not going to do that.
Now, the first thing we’re going to look at is the 9-year gap. So, the 9-year gap is very uncommon. Gap ID 9, post type ID 1. And we run this.
This returns very quickly. We get this plan here. It is a parallel nested loops plan with a key lookup to fetch all of the columns that we care about from the post table after we seek both to the post type ID that we care about and the gap years parameter that we care about.
The key lookup down here, there’s no predicate on that. If I move my big head out of the way, there’s no predicate on that up here. It is just outputting columns, right? But it’s outputting all the columns in the post table, which maybe isn’t a lot of fun.
But this takes 28 milliseconds to run and it gets 500 rows and everyone’s pretty happy. If we run this for a gap of 0 years, most posts do not have 9 years of time between when they were first posted and when they were last edited.
A gap of 0 years is much, much more common. And this query runs for about 4 1⁄2 seconds here. Excuse me.
Very dry. 4 1⁄2 seconds. And a lot of the time is spent in this sort that spills because the memory grant that we assign to the initial execution of this procedure maintains for this execution.
So that takes some time. Now, if we recompile this thing and we run this in reverse and we ask for a gap of 0 years first, this does a bit better, right?
About twice as fast plus another 500 milliseconds faster for a gap of 0 years. The plan changes quite substantially, though.
I mean, we still have a plan for a parallel nested loops join plan. But notice that we fully scan the clustered index over here.
And then we have this strange filter operator over here. And the filter operator is on both the post type ID and the gap parameter. Part of the reason for this is because the computed column that I added to the post table was not persisted.
SQL Server expands the definition of the persisted computed column. And if I were to change that to a persisted computed column, we could avoid this part.
But we would still get the same basic plan shape. Now, I think probably the biggest downside of this late filter operator, and if I’ve said it once, I’ve said it a million times, always be suspicious of a filter operator in a query plan, is that we have to fully scan all 17 million rows from the post table, drag them across the couple compute scalars, and then filter stuff out over here.
Okay? What really sucks is that reusing that plan for the gap of nine years, in other words, the very uncommon one, this used to take 28 milliseconds, and now this thing takes almost a second.
Right? And it’s obviously the same plan gets reused. Whenever I’m talking about parameter sensitivity problems, a lot of people get this big idea in their head. It’s like, well, why not just use the big plan for everything?
The big plan for small amounts of data is often somewhat, I mean, not surprising to me, but surprising, not surprising to other people who see this stuff, not a good sort of trade-off there.
It’s not a good fit. So we have, you know, essentially two execution plans. Neither one is a particularly good fit for the amount of data that we’re dealing with. So the mental exercise that I like to put people through when we’re doing this is to mentally in your head separate informational columns from relational columns.
Right? And what we’re going to do in the query below is we’re going to write a self join between the post table and itself. Right?
One alias will be responsible for the relational activities in our query, the join, the where clause, the order by, stuff like that. And the other alias will just be responsible for providing the select list.
Right? And if you can start mentally separating the duties of your queries and the columns in your queries between purely informational stuff that’s only in the select list and columns that are used for relational activities in your queries, you can do a lot of cool query tuning stuff.
This is just one of them. Right? So let me create this and then we will talk through the code just a little bit up here.
So I have the post table joined to itself. ID is the clustered primary key in the post table so we can get away with this. It is a unique column so doing this is very, very easy.
And from P1, right, that’s this one, this is all of the relational stuff. Right? P1 is there.
P1 is there. P1 is the where clause. P1 is further down here in the where clause and in the order by clause down here. The only place we reference P2 is up here in the select list.
Right? This is our star. Right? So if we run this query now, both 9 and 0 will be fast doing this. Right?
The 1 for 9 got even faster. The 1 for 0, instead of taking 4.5 seconds, takes 1.2 seconds. Right? So we’ve kind of come to a little bit better of a situation here.
Now, we still have this sort over here and this sort still spills. Right? So, you know, it does slow this query down a bit but we’re not at 4.5 seconds of sort of crappy spilling.
We’re at one point, actually it’s about 900 milliseconds of crappy spilling there. So this is a lot more tolerable. If we reverse things, just like we did before and we do 0 first and then 9, the plan actually gets a little bit better.
So in this case, the first query, not only does the sort spill a bit less because we get the memory grant for the larger amount of data that comes out of the POST table, but we didn’t finish in about 200 milliseconds.
Now we also get this parallel nested loops plan. And the same thing goes for this gap down here. So this is a pretty reasonable rewrite to get better performance out of both queries.
You might start playing some other tricks with this query if you really were getting, if you really wanted to like optimize, optimize this, you could even say like option use hint optimize for gap equals 0.
So you keep getting that plan for the 0 value and the gap parameter. And talk through some of the important differences between the original and the rewrite.
I’m going to put both of them into the same store procedure. And then I’m going to run them for the gap of nine years. And the thing that I just want to talk about here a little bit is the plan shape.
So what happens in the original query where we do the lookup is we find 4,500 rows here and we do 4,500 nested loops to do the key lookup here.
Key lookup plan, key lookups and query plans are very, very tightly coupled operations. SQL Server cannot move these around, right? SQL Server has to do this stuff all in one place at the same time.
It can put a sort before the nested loops join, it just doesn’t here. And then after we find all that data, then we do our sort. And this is where we start to sort of narrow stuff down for the top, right?
Down here in the one that we rewrote in order to, what do you call it, do the self join.
We still have the same seek, but notice that the post table immediately joins to the users table here. And what’s nice is that this sort cuts down the results to just about all the ones that we need for the top very much earlier on, right?
And then after we figure out relationally what rows we care about maintaining for this query to get the top 500, we do our nested loop.
We do our nested loops for only those 500 rows back to the post table here. And again, there is about a 20 millisecond difference between these. This isn’t big night and day performance tuning stuff, but you do see a general improvement.
And what I think is nice too is you see that general 20 millisecond improvement with the serial execution plan. In other words, you don’t need to get a parallel plan here in order for this to be competitively fast, right?
This plan up here, it runs, goes parallel, gets a DOP eight query plan. And this is one of those like, oh, well, you’re using a bunch of extra resources, but your query’s not getting faster, right?
It’s kind of a weird thing. Anyway, when you’re working with queries, especially parameter sensitive ones, one of the biggest differences that you will see in those queries aside from stuff like the type of join and the size of the memory grants and all that other stuff is going to be the sort of like index usage, right?
And if you can’t get SQL Server to reliably use your narrower nonclustered indexes because you are selecting columns that are outside of them and SQL Server now has this clustered index for its key lookup choice, it might just totally be worth rewriting the query to do a self join so that you can get all of your relational work done that narrows the rows down to just the ones you care about.
And then do the self join back to get the stuff later because the key lookup, again, very tightly coupled. When you write a self join, that tight coupling is sort of removed a bit.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk some more about very similar techniques. All right, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Birgitta Böckeler now reports on her recent experiences
trying local LLMs for coding. She compares them using two standard
tasks, and tries out the most promising model for day-to-day use.
Log in
