Press "Enter" to skip to content

Category: Stored Procedures

Reducing Child Procedure Temp Table Plan Cache Pollution

Joe Obbish resolves a specific type of plan cache issue involving shared temp tables:

The problem can be observed with a simple repro script. The child stored procedure performs a series of updates on a temp table. The parent procedure creates the temp table, calls the child stored procedure, and returns data from the temp table.

Click through to see an example of the issue, as well as one technique to mitigate the problem.

Comments closed

In Praise of Stored Procedures

Jay Robinson defends the highly defensible:

The point I’m trying to make is that the database code is treated as part of the solution. It is in the repos just like the C# code. It is deployed just like the C# code. I even include unit testing, just like the C# code.

I also keep all T-SQL in the database. There is no T-SQL code inside the C# code. Stored procedures are used for all functionality, even basic CRUD functions. In fact, probably 80–90% of the stored procedures in my systems are essentially CRUD functions. The only operation that doesn’t go through stored procedures is bulk insertion, and I use SqlBulkCopy for that.

Jay makes what I often consider the most important point from the standpoint of developers: that stored procedures act as an interface.

Comments closed

Parameterizing Stored Procedures from Power Query

Soheil Bakhshi calls a stored procedure:

From time to time, Excel users require to get the data from a SQL Server stored procedure. The stored procedures usually accept some input parameters and return the results. But how can we dynamically pass values to the stored procedures from cells in Excel to SQL Server?

Read on for two approaches to the problem. Like Soheil, I think the second approach is much smoother, in part because it isn’t 30-something steps long.

Comments closed

The Importance of Dev Environment Security

Kenneth Fisher shares an opinion:

I was asked a rather interesting question during an interview recently. It went something like this:

Your resume says you’ve implemented security standards. What do you do if a development team comes to you and says “We have to have a SQL Server Id in production (or other restricted permission) because that’s the way we tested it and we are going into production now.”?

And in general my response is “You give it to them. It’s too late to stop them at that point.”

Kenneth goes on to elaborate.

I prefer a compromise solution, which requires more than 2 environments. Dev is a sandbox. Developers don’t know what they need and generally need more space to work than the final product itself will need. A wide-open dev environment is something I don’t mind. In some organizations, a local dev environment may be sufficient for this task—that is, SQL Server (or whatever) installed on the developer’s personal machine or brought in via Docker container.

However, there must be at least one pre-production environment in which developers have prod-level rights. Ideally, it would not be the last step of integration prior to prod but instead something like a QA environment which is part of a CI/CD process.

I should also point out that module signing helps with this problem: stored procedures may contain rights that devs shouldn’t have but are necessary to the way an application functions. Access to execute those stored procedures is something the DBA team can control and put some limits around, so in Kenneth’s example, truncation could be handled via stored procedure. That procedure handles the truncation operation and opens the door to further checks, such as a list (or table) of tables which the app is allowed to truncate, logging of operations, etc.

Comments closed

sp_PressureDetector Updates

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 closed

IF Branching, Local Variables, and Stored Procedures

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

Database Scripting via Temporary Stored Procedure

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

Version 12 of sp_WhoIsActive

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

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