A Fine Slice Of SQL Server
Josephine Bush tries out some stored procedures:
Erik Darling, founder of Darling Data, has created these fantastic stored procedures to query SQL Server more efficiently to get health, log, or performance information. I will go through them here regarding using them in Azure SQL database since I don’t have any SQL Servers I manage anymore.
Read on to see which ones you can use in Azure SQL DB and which require SQL Server.
Comments closedErik Darling shares some updates:
I’ve been doing a lot of work on all of my free SQL Server troubleshooting stored procedures lately.
If you haven’t used them, or haven’t even heard of them, now’s a good time to talk about what they are, what they do, and some of the newer features and functionality.
Read on to see what’s new. If you haven’t used any of Erik’s procedures, I highly recommend them.
Comments closedErik Darling wraps up a series on stored procedures. First, cursors and loops:
You will, for better or worse, run into occasions in your database career that necessitate the use of loops and cursors.
While I do spend a goodly amount of time reworking this sort of code to not use loops and cursors, there are plenty of reasonable uses for them.
I do think we push the “don’t use cursors or loops” thing a little too hard in the SQL Server world, but I also think that a majority of cases in which you’re doing something in a loop, you should be doing it in code outside of SQL Server.
Erik then wraps things up for real:
The general idea of the series was to teach developers about the types of things I always seem to be fixing and adjusting, so that I can hopefully fix really interesting problems in the future.
Of course, that all depends on folks finding these and reading them. If that were the general sway of the world, I’d probably never had been in business in the first place.
Click through for a listing of all of the posts in the series.
Comments closedErik Darling isn’t content with simple searches:
Like having a built-in type to make dynamic SQL more easily managed, it would also be nice to have some mechanism to manage dynamic searches.
Of course, what I mean by dynamic searches is when you have a variety of parameters that users can potentially search on, with none or few of them being required.
Erik provides two techniques and contrasts the two, so check it out.
Comments closedErik Darling goes back to the well on a popular and important topic:
One of the most fun parts of my job is telling people that parameter sniffing is a good thing. They’re often shocked, because the phrase has such ominous connotations to developers.
The parameters! They get sniffed! Performance! Something! Stuff!
Parameter sniffing does not mean the sky is falling. Like I’ve said before, if it were such a bad idea, it would have been abandoned.
Read on for more thoughts on the topic.
Comments closedI probably overuse temp tables. I’m the first one to admit it. But often when I choose to use one, it’s a choice between:
- Spending a long time trying to business understand logic for a company I don’t work for
- Tinkering with query syntax and using really weird tricks and hints to get different plan shapes
- Waiting a long time to create or tweak indexes on huge tables (usually on “dev” hardware)
Erik provides a good example of where a temp table fits well, contrasting it to a common table expression that fares poorly. My bias tends to come in the opposite direction from Erik’s: I’m susceptible to playing query golf and getting everything I need back in one optimized call. But part of craftsmanship is knowing how and when to use each tool.
Comments closedErik Darling offers some advice:
Wrapper stored procedures are useful for things like:
- Transforming declared local variables into parameters
- Preventing code from compiling when it isn’t used
- Generating different query plans to deal with parameter sniffing
The upside of using this over dynamic SQL is that you have a convenient object name attached to the code.
Read on for the downside to this, as well as a pair of videos on the topic.
Comments closedErik Darling does not approve:
Like many other things we’ve discussed thus far, local variables are a convenience to you that have behavior many people are still shocked by.
You, my dear and constant reader, may not be shocked, but the nice people who pay me money to fix things seem quite astounded by what happens when you invoke local variables.
So I find myself in a difficult position: do I dredge up more red meat for the millions of die-hard SQL Server performance nuts who come here for the strange and outlandish, or produce evergreen content for people who pay my substantial bar tabs.
You have at least a 50% chance to guess what Erik does next.
Comments closedThere are two forms of conditional logic that I often have to fix in stored procedures:
- Branching to run different queries at different times
- Complicated join and where clause logic
The problems with both are similar in terms of performance. You see, when smart people tell you that SQL is a declarative language, and not a procedural language, they’re usually trying to get you to stop using cursors.
And that’s not always wrong or bad advice, trust me. But it also applies here.
Read on for exceptions to the rule and how you can make your life a bit easier if you do have this in place.
Comments closedErik Darling plays the roles of both Goofus and Gallant here:
All sorts of bad things happen when you do this. You can’t index for this in any meaningful way, and comparing non-string data types (numbers, dates, etc.) with a double wildcard string means implicit conversion hell.
You don’t want to do this. Ever.
Unless you want to hire me.
Click through for good advice on the proper use of data types and input parameters.
Comments closed