A Fine Slice Of SQL Server
Erik Darling continues to be busy:
I haven’t run into a bug with this thing in just about… Well, ever. This proc has been pretty solid.
As I use it though, especially in front of clients, I find myself wanting some additional data points all in one place. With this release, I’ve added a few small things.
Click through for the list of updates.
Comments closedI made some updates to sp_QuickieStore recently, which you can download here.
The main updates to the code are to:
– Add support for SQL Server 2022 views
– Add the ability to search by query hash, plan hash, and SQL handle
But wait, there’s more! Click through to see the full-ish changelog.
Comments closedErik Darling continues a quest. Part 3 involves local variables:
What never seems to get a bad name, despite numerical supremacy in producing terrible results, are local variables.
In this particular scenario, I see developers use them to try to beat “parameter sniffing” to no avail.
A chorus of “it seemed to work at the time”, “I think it made things a little better”, “it worked on my machine”, and all that will ensue.
But we know the truth.
The next part is around stored procedures:
You know and I know and everyone knows that stored procedures are wonderful things that let you tune queries in magickal ways that stupid ORMs and ad hoc queries don’t really allow for.
Sorry about your incessant need to use lesser ways to manifest queries. They just don’t stack up.
But since we’re going to go high brow together, we need to learn how to make sure we don’t do anything to tarnish the image of our beloved stored procedures.
Erik notes that stored procedures are part of the solution but there’s a bit more that we need.
Comments closedKenneth Fisher has a use for temporary stored procedures:
The other day I was asked to create a SQL Audit on several different databases. Completely unexpectedly (sarcasm warning!) the list grew, not once, not twice, but enough times that I’ve lost count, and each time I would copy and paste my code for the new databases and change the database name in each piece. Then on one notable occasion I had to change the code for each of the, at that point 10, copies of the code. Talk about a headache.
Then there was the epiphany.
Comments closedErik Darling answers the long-standing question “Who is active?” with “You is active!”:
– New parameter,
@get_memory_info, that exposes memory grant information, both in two top-level scalar columns and a new XML-basedmemory_infocolumn.– Better handling of the newer CX* parallelism wait types that have been added post-2016
– A top-level implicit_transaction identifier, available in
@get_transaction_info = 1mode– Added
context_infoandoriginal_login_nametoadditional_infocollection– A number of small bug fixes
– Transition code to use spaces rather than tabs
Spaces rather than tabs? SQL should have tabs! But functional programming languages are great and they use spaces! I’m so conflicted!
Comments closedDavid Fowler has a warning about stored procedures which use temp tables created by other processes:
Here’s an interesting issue that recently came up. We were seeing very high compilations and recompilations on a server to the point that it started causing us some very serious issues (admittedly this wasn’t the sole issue but it was certainly a contributing factor, the other factors were also very interesting so I might look at those in another post).
After looking in the plan cache we could see a very high number of single use plans for a particular stored procedure. Now as you probably know, SQL will usually cache an execution plan and use it over and over whenever a particular query runs. SQL’s lazy and it doesn’t want to bother compiling queries unless it really has to.
So what was going on, why wasn’t SQL able to reuse the cached plan?
Read on for the solution, as well as the impact of the problem and ways to work around it.
Comments closedErik Darling gives us some hints on when it might be time to stop using that ORM:
There are, unfortunately, some times when developers refuse to put the ORM down.
I mean, it’s mostly unfortunate for them, because they’ll continue to have performance problems.
Me? I’ll be okay.
The items in this post are issues I’ve run into constantly when working with people who use ORMs, but don’t spend any time looking at the queries they generate.
An important note is that “stored procedure or ORM” is a false choice—most modern ORMs will allow you to generate objects based off of stored procedures, so you can stick with the ORM for the parts it does well but switch to a stored procedure when things get real. Or just use stored procedures across the board and have your ORM act as an auto-mapper for them. That’s an option too.
Comments closedChad Callihan invokes Betteridge’s Law of Headlines:
Generally speaking, it’s best to put things away that aren’t being used. Don’t keep indexes that aren’t getting utilized because they are taking up disk space and still have to be kept up to date with changes. A table is still loaded up with old data that’s not being used but needs kept? Maybe it’s time for options like an archive database or partitioning.
While it’s not on the same level of importance, one related argument I’ve seen and been in is how to handle temp tables in stored procedures. Do you drop them at the end of a stored procedure or do you leave them to be cleaned up by SQL Server? Is one way better for performance than the other? Let’s do some testing and see what we find out.
Read on for the answer.
Comments closedJess Pomfret has another way to search through stored procedure text:
When we’re looking for the command we need within dbatools to fulfil our needs I cannot recommend
Find-DbaCommandhighly enough. This command will search all other commands for the pattern you pass in. Today we know we want to find references in stored procedures so let’s see if there is a command that will help.
Seems like querying sys.sql_modules is a little easier, though if this is a step in a pipeline (such as finding old procedures based on some no-longer-appropriate code snippet and deploying new versions), this can be a good first step.
Greg Dodd wants the usual order:
If you’ve done work with stored procedures, you are probably aware that stored procedures have parameters, and that the parameters can be defaulted when you declare them. I was recently caught out due to some application code that checked when a parameter was specified for a stored procedure, if the value for the parameter was NULL then pass in the keyword DEFAULT. The Code assumed that if I had gone to the effort of specifying the parameter but not the value, that I must want the default value of the Stored Procedure. I had expected it would pass in the SQL NULL keyword.
Read on to see what actually happens and how you can use a default value.
Comments closed