Category: Stored Procedures

Startup procedures automatically execute whenever SQL Server is started. Where would you use this? One example is if you had an application that required the use of a global temporary table, you could use a startup procedure to create it. This would allow the table to be immediately accessible to any application that requires it.

Another possibility is that you could “warm up” the cache so that data is already cached when an application needs it. This would help prevent that initial I/O hit the first time the query is called, thus potentially improving initial performance.

Click through for details. I’ve created a couple of these, but they rarely come to mind and that’s one of the big problems: if there is an issue, there’s probably quite a bit of investigative work that would take place before somebody remembers that hey, these things exist.

Comments closed

NULL Parameters

Published 2017-03-16 by Kevin Feasel

Riley Major turns this T-SQL Tuesday into thoughts on procedure parameterization:

But what if the caller wanted the date to be “empty” (i.e. 1900-01-01)? And what if a NULL is passed?

In our environment, we’ve disallowed NULLs from our table fields. We understand that NULL is actually information– it says that the data is unknown– but we believe that for most data fields, there are non-NULL values which just as effectively represent unknown. Typically, 0’s and empty strings (and the “blank” date 1900-01-01) serve that purpose. And those values are more forgiving during coding (they equal things; they don’t make everything else “unknown”), and we accept the risk of paying little attention to which parts of our logic touched “unknown” values.

It’s an interesting look at dealing with optional and default parameters within procedures.

Comments closed

Tuning Indexes For Stored Procedures

Published 2016-11-30 by Kevin Feasel

Kendra Little offers advice on index tuning within stored procedures:

The best way is to run the stored procedure yourself to generate and save an “actual” execution plan, which contains the estimates SQL Server used when it generated the plan as well as actual rowcounts, actual memory granted, etc. It will also contain a green tooltip with a “missing index request” if SQL Server thinks an index would help.

This is an introductory-level post which contains good advice.

Comments closed

View Performance

Published 2016-11-02 by Kevin Feasel

Grant Fritchey looks at view performance vis-a-vis stored procedures:

The difference in the performance including compile time for the procedure alone is 700mc better on average than the view. That’s an 8% difference. It was almost that high for the view that used the procedure at 7%.

If we’re just talking compile time then, there is a significant win if we avoid the view. This is no doubt because of the extra work involved in unpacking the view and going through the simplification process within the optimizer. Plus, the view alone in our query was parameterized by the optimizer in order to assist it’s performance over time (as we saw in the average results without the recompile). All that extra work explains the 8% difference.

Read the whole thing.

Comments closed

Temporary Stored Procedures

Published 2016-10-20 by Kevin Feasel

Jana Sattainathan discusses temporary stored procedures:

The real benefit of these procedures is when they contain lot of logic that you need on a temporary basis but do not want to clutter the existing stored procedure list. You could even have multiple temporary procedures that call each other. I would not go overboard with this. It is just a convenience.

I don’t often see these in use; when I’ve seen them, they’re in environments in which normal stored procedure create rights are locked down and you want to do something as a one-off (like testing an operation against production data). In other words, those sketchy things that we don’t admit to each other that we do…

1 Comment

Replication Raiserror Procedure Change

Published 2016-09-21 by Kevin Feasel

Matt Slocum documents a change to a replication stored procedure in SQL Server 2014 SP2:

I ran into this replication error the other day and I was completely stumped.
Procedure or function sp_MSreplraiserror has too many arguments specified.

We started getting that error message shortly after we had applied SP2 for SQL Server 2014 to a server that is a replication Publisher (source of replicated data).

We dug into the commands that were being replicated and found that there were missing rows in the table on the replication Subscriber (destination for replicated data). Once the rows were populated the errors stopped. However, after digging in a bit more, we found that this error has an explainable source.

Read on for Matt’s solution to the issue.

Comments closed

Naming Procedures

Published 2016-09-02 by Kevin Feasel

Aaron Bretrand shares rules that he uses when naming stored procedures:

I’ve talked about this in my live sessions, but this is an extreme case that really happened – a team took over a week to fix a bug in a stored procedure, and the delay was caused solely by poor naming standards. What happened was that the application was calling dbo.Customer_Update, but the team was hunting for the bug in a different procedure, dbo.Update_Customer. While there was no formal convention in place, the real problem was inconsistency – a consultant charged with writing a different application didn’t check for an existing procedure, she just looked for dbo.Update_Customer in the list; when she didn’t find it, she wrote her own. The bug itself wasn’t crucial, but that lost time can never be recovered.

I’ll repeat again that the convention you choose is largely irrelevant, as long as it makes sense to you and your team, and you all agree on it – and abide by it. But I am asked frequently for advice on naming conventions, and for things like tables, I’m not going to get into religious arguments about plural vs. singular, the dreaded tblprefix, or going to great lengths to avoid vowels. But I think I have a pretty sensible standard for stored procedures, and I am always happy to share my biases even though I know not everyone will agree with them. Again, I touched on this in my earlier post, but sometimes these things bear repeating and a little elaboration.

I agree with this: pick a standard and stick to it.

Comments closed

Fork Bombs

Published 2016-08-24 by Kevin Feasel

Brent Ozar creates a fork bomb in SQL Server:

I’ve always found fork bombs funny because of their elegant simplicity, so I figured, why not build one in SQL Server?

In order to do it, I needed a way to spawn a self-replicating asynchronous process, so I built:

A stored procedure
That creates an Agent job
That runs the stored procedure

I didn’t think it was possible. I certainly didn’t think it would take a half-dozen lines of code.

Comments closed

Stored Procedure Last Run Times

Published 2016-06-29 by Kevin Feasel

Richie Lee has a script to see when stored procedures were last executed:

Quick script to get the last time a stored procedure was executed in the database. The reason for the seemingly over-engineered script is that different query plans can be generated, meaning that stored procedures can appear more than once in the list.

The query doesn’t quite work as-is, but making qs.execution_count into an aggregation and removing it from the GROUP BY would work. I’d probably rewrite it to look a bit more like:

WITH querystats AS
(
	SELECT
		OBJECT_NAME(qt.objectid) AS ProcedureName,
		SUM(qs.execution_count) OVER (PARTITION BY OBJECT_NAME(qt.objectid)) AS ExecutionCount,
		qs.creation_time AS CreationTime,
		ROW_NUMBER() OVER (PARTITION BY OBJECT_NAME(qt.objectid) ORDER BY creation_time DESC) AS rownum
	FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
	WHERE
		qt.[dbid] = DB_ID()
		AND OBJECT_NAME(qt.objectid) LIKE '%%'
)
SELECT
	qs.ProcedureName,
	qs.ExecutionCount,
	qs.ExecutionCount
FROM querystats qs
WHERE
	qs.rownum = 1;

Comments closed

Business Logic

Published 2016-05-06 by Kevin Feasel

Ed Elliott hits a classic architectural argument—whether business logic should be in stored procedures;

Stackoverflow is a specific use case and they decided to use .Net so they have a specific set of problems to deal with in terms of performance. They deploy (as I understand it) 10 times a day so if they need to change a query then they can quickly and easily – how quickly can you modify code and get it to production to fix a problem causing downtime on your mission critical app written in powerbuilder 20 years ago? (I jest but you get the point)

I like Ed’s back-and-forth arguing, as there are legitimate cases for both sides and the best answer almost always is somewhere in between for line of business apps. I have three points that I tend to mention whenever this discussion comes up.

First, a lot of “business logic” is actually data logic. Check constraints, foreign key constraints, unique key constraints, and even primary key constraints (for non-surrogate primary keys) are business rules, but they’re business rules around how the data is shaped and it’s a lot better to use your database system to maintain those rules.

Second, validation rules should be everywhere. The fancy Javascript library should do validation, the server-side business logic should do validation, and the database should do validation. You don’t know what’s going to skip one or more of these layers, and your database is the final gatekeeper preventing bad data from sneaking into your system.

Third, at the margin, go where your maintenance developers are most comfortable. If they’re really good with C# but not good with SQL, the marginal business logic (the stuff you could really go either way on) should stay in the app tier; if your maintainers have really strong SQL skills but are lagging on the .NET side, I’d stick the marginal logic in stored procedures.

Comments closed