Do table variables and variables respect transactions? If I set the value of a variable during a transaction, and I roll it back, what happens?
[crayon-6af2b6d2b2593880725367/]
"I hate views," the DBA said. "They kill performance. And nested views are even worse."
Wrong. I'll prove it.
A perfect storm of unusual problems caused a client to ask, "What if we just used NVARCHAR(4000) as the default datatype parameter for any query, regardless of what datatype the table has in it - like numbers or dates?"
It actually works pretty well in most situations, believe it or not.
If you update a row without actually changing its contents, does it still hurt?
Paul White wrote in detail about the impact of non-updating updates, proving that SQL Server works hard to avoid doing extra work where it can. That's a great post, and you should read it.
When you're doing DUI operations against tables with millions of rows, you have to be really careful about SQL Server's estimates. Ideally, you want your delete/update/insert (what - what'd you think I meant?) queries to be as simple as possible - even a simple join can cause SQL Server to do wildly, wildly incorrect estimates, which affects memory grants, parallelism, wide vs narrow plans, and more.
If your query doesn't have an ORDER BY clause,
you can't reliably predict the order of your results over time.
Sure, it's going to look predictable at first, but down the road, as things change - the indexes, the table, the server's configuration, the size of your data - you can end up with some ugly surprises.
I know this is gonna sound crazy, but let's say you had a table where people stored all kinds of things in one column: dates, integers, file names, sale prices, file names, you name it. And let's say your application frequently ran a query looking for dates in that column, like this: [crayon-6af2b6d2c2816907387831/] Even if…
Say we have a stored procedure that queries the Stack Overflow Users table to find people in a given location. Here's what the table looks like:
And here's what my starting stored procedure looks like:
[crayon-6af2b6d2c2a66264603037/]
It works:
Say that you've got a table with a datetime column in it, like the LastAccessDate column in the Stack Overflow Users table:
And let's say you wanna find the users who last accessed the site on September 9, 2018 - regardless of the time. Here are a few ways to do it:
[crayon-6af2b6d2c36b5010001441/]
Here are their actual execution plans. I'm using SQL Server 2019, with 2019 compatibility level, and the 50GB StackOverflow2013 database.
The SQL Server documentation has a pretty cool list of query hints:
Yeah, I surf in dark mode. I'm a dark kinda guy. But wait - what's that colored box? ENHANCE!
Let's get one thing out of the way first: this isn't about the relative good or evil of triggers. I kinda like triggers, in fact - they're good for enforcing business logic in a hurry when you can't redo code across an entire application. Sure, in a perfect world, we'd always have the code do whatever the code needs to do - but there are times when you can't, and triggers come in handy then.
I get this question a lot, so let's set up an example with the Stack Overflow database. My query's goal is to:
Find the top 5 Locations
List the users who live in those top 5 Locations, alphabetized by their DisplayName
The performance of scalar functions sucks hard. Let's see it in action using the Stack Overflow database - any size will work. I'll set things up first in case you want to follow along: [crayon-6af2b6d2c57a3727807534/] I'm purposely setting my Cost Threshold for Parallelism to be low here because I want to demonstrate what happens when…
I don't have a problem with triggers. They get the job done when you need to implement business logic in a hurry, and you're not allowed to change the application. As long as you keep the number of statements to a minimum (say, 2-3), and don't try to do something really slow like fire up a cursor, triggers can be an efficient way to solve hard problems quickly.
This week, I've been writing about how SQL Server 2019's bringing a few new features to mitigate parameter sniffing, but they're more complex than they appear at first glance: adaptive memory grants, air_quote_actual plans, and adaptive joins. Today, let's talk about another common cause of wildly varying durations for a single query: user-defined functions.
A client asked a great architecture question that deserved its own blog post:
Should we use more "stored procedures" for select, insert, update, delete or should that just be sent as queries from the application?
If you ask a junior database administrator where to put something, she'll probably say "in the database" because that's the thing she has control over. That's fair - DBAs love having control over stuff. However, let's step back a little.
When I do performance tuning for clients, I really pride myself on making as few changes as possible in order to make a tremendous difference. I consider it a failure when I have to tell someone to rewrite a bunch of queries from scratch. However, there are some cases where I just can't work around…
When we think about building dynamic SQL, we usually think about a stored procedure like this that takes input parameters, builds a string, and then executes that string. Here's a simple example: [crayon-6af2b6d2c74e0428596227/] The more parameters you have, the crazier the code gets, and the harder it becomes to produce really fast plans across all…
Say I've got a function in my WHERE clause:
[crayon-6af2b6d2c82fd247886314/]
If I have an index on DisplayName, will SQL Server use it? Sure:
Even though SQL Server can't seek to "Brent Ozar," it will still scan that entire index, along the way trimming the leading & trailing spaces from every user's DisplayName.
Say we have a stored procedure that queries the Stack Overflow database. We have two separate parameters, @DisplayName and @Location, so folks can search for people by name, location, or both.
For performance reasons, we decide to build dynamic SQL:
[crayon-6af2b6d2c8c5c960513073/]
When we run it, it works, and we can see the query on the Messages tab in SSMS:
