Press "Enter" to skip to content

Category: Stored Procedures

Version 12 of sp_WhoIsActive

Published 2021-11-12 by Kevin Feasel

Erik 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-based memory_info column.

– 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 = 1 mode

– Added context_info and original_login_name to additional_info collection

– 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 closed

External Temp Tables and Plan Reuse

Published 2021-10-20 by Kevin Feasel

David 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 closed

The Limitations of ORMs

Published 2021-09-27 by Kevin Feasel

Erik 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 closed

What to Do with Temp Tables in Stored Procedures

Published 2021-06-01 by Kevin Feasel

Chad 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 closed

Searching through Stored Procedures using dbatools

Published 2021-05-26 by Kevin Feasel

Jess 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-DbaCommand highly 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.

Comments closed

Passing Defaults to Stored Procedures

Published 2021-05-26 by Kevin Feasel

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

Finding Text in a Stored Procedure

Published 2021-05-17 by Kevin Feasel

Chad Callihan has a way to search the text of stored procedures:

Story time. Let’s say a database server is receiving a new release that include a change to a stored procedure. All of the databases are supposed to get the changes but one way or another there are problems with the release and it has to be stopped part of the way through. Maybe some changes got rolled back but others weren’t rolled back. We don’t have accurate logging of what databases have been updated but we want to know if a stored procedure is on the old version or received the new version.

I’ve used this technique quite often. The only downside is that if you have a lot of procedures and don’t specify the object ID, search can get a bit slow.

Comments closed

sp_QuickieStore in Action

Published 2021-04-28 by Kevin Feasel

Erik Darling has a new stored procedure. First up, an introduction:

If you’ve ever tried to write a query to find stuff in Query Store and quickly gotten frustrated, you’re not alone.

Whomever designed that schema hates you. It’s probably the same person who designed Extended Events.

I know, there’s a GUI. The stuff you can find in there is definitely useful, but I often find myself wishing it did a little bit more. Asking for additional features often feels like porridge-begging, and even if something new did get added eventually, it wouldn’t help you in the moment.

With all that in mind, I wrote sp_QuickieStore to try to take some of that pain away. As of this post, it’s V1. There will likely be changes and fixes and all that good stuff, but right now it’s good enough for an introduction.

Erik then shows off the results:

Under the more concise mode, you get one set of results back that combines metrics from query_store_runtime_stats, along with query text, query plan, and context settings. Some previews below. There’s a lot more in the actual results, but the screen caps would get very repetitive.

This looks really interesting, so go check it out.

Comments closed

Temp Tables and Nested Stored Procedures

Published 2021-03-29 by Kevin Feasel

John Morehouse takes us through a catch in creating temp tables inside nested stored procedures:

SQL Server offers a lot of flexibility when working with objects like stored procedures and temporary tables.  One of the capabilities is to allow for stored procedures to call other stored procedures.  This is called “nesting” one stored procedure within the context of the calling stored procedure.  In addition, you can instantiate a temporary table from within the parent procedure that can be utilized within the context of the child procedure.

But wait! I’m not done yet!

You can also instantiate a temporary table with the same name as the parent temporary table within the child procedure.  But there is a catch!

I’ve done the former (and more frequently, had to support when somebody else did the former), but I don’t think I’ve done the latter. Mostly because it seems like it’s an invitation for problems.

Comments closed

Stored Procedure Return Values and Entity Framework Core

Published 2020-11-03 by Kevin Feasel

Erik Ejlskov Jensen shows us how to retrieve the return value from a stored procedure using Entity Framework Core:

SQL Server stored procedures can return data in three different ways: Via result sets, OUTPUT parameters and RETURN values – see the docs here.

I have previously blogged about getting result sets with FromSqlRaw here and here.

I have blogged about using OUTPUT parameters with FromSqlRaw here.

In this post, let’s have a look at using RETURN values.

Click through for the process.

Comments closed