A Fine Slice Of SQL Server
Erin Stellato explains how Query Store works when you have encrypted stored procedures:
There are two issues here:
– Why are only some plans showing up in Query Store for a query that’s part of an encrypted stored procedure?
– Can I force a plan for a query that’s part of an encrypted stored procedure?When in doubt, test.
Read on for the results.
Comments closedBert Wagner takes on the idea that stored procedures are faster (or slower) than ad hoc SQL:
A few months ago I was presenting for a user group when someone asked the following question:
Does a query embedded in a stored procedure execute faster than that same query submitted to SQL Server as a stand alone statement?
The room was pretty evenly split on the answer: some thought the stored procedures will always perform faster while others thought it wouldn’t really matter.
Bert explains the answer. For me, performance isn’t even on the radar for how I try to convince people to use procedures. Instead, explain it to developers as an interface: developers program against a contract, where they can send in a specified set of inputs (procedure parameters), get back a specified output (the shape of the result set), and not have to care about the fine details. Programming to interfaces is extremely common in business development, and this is just one more interface.
Comments closedThis is a stored procedure that I have found useful in a number of circumstances. It came into being because there are times that I need to run a job via T-SQL and wait for it to complete before continuing the script. The problem is that
sp_start_jobdoes just what it says: it starts the job and then immediately continues. There is no option to wait for it to finish.I needed some way to pause script execution pending the completion (hopefully successfully) of the job.
One important note: this procedure using the undocumented
xp_sqlagent_enum_jobssystem procedure. While this has been around for ages, it is unsupported. I get why may bother some, but this procedure is the only way that I know of to reliably determine the current run status of a job.
Read on to learn more about the procedure and grab a copy.
Comments closedThomas Rushton shows how to filter any stored procedure’s result set and uses sp_who as an example:
sp_who– useful – up to a point. Particularly when the server is busy, and you’re looking for something specific (eg to see if certain processes are out of a database before running an update)If the server is busy – don’t you wish there was a way to run something like
sp_who WHEREdbname = 'foo'Yeah. Unfortunately, it doesn’t work like that.
That doesn’t work, but Thomas shows you what does.
Comments closedKenneth Fisher takes us through procedure parameters:
If you are executing a stored procedure with a bunch of parameters it can be a bit of a pain if you have to pass a value in for each of them. Fortunately, it’s pretty easy to make some parameters required and others optional. You simply give them a default value.
Kenneth also points out that functions don’t behave this way, and shows how to handle parameters where you don’t want to accept NULL under any circumstances. This is useful when NULL is just a placeholder for “I don’t really want to use this parameter” but the application doesn’t know how to avoid sending the parameter in the first place.
Comments closedKenneth Fisher wants to see how security for temporary stored procedures works:
With normal stored procedures there is something called ownership chaining. Without going into a lot of detail about what it means, let’s say that you run a stored procedure. SQL is going to check the permissions to see if the stored procedure can update that table right? Well, who’s permissions? Yours? Well, yes, if you have permissions you are fine. But you won’t always. If you don’t then SQL is going to check the owner of the stored procedure (dbo?) and see if they also own that table. If so then we’re golden, perform the update. That might seem scary but it’s pretty normal.
What was scary (at least to me) is the question “How is that handled for a temp stored procedure?”
Read on for the results of Kenneth’s tests.
Comments closedBert Wagner shows how you can perform testing with temporary stored procedures:
A while back I learned that it’s possible to create temporary stored procedures in SQL Server.
I never put that knowledge into practice however because I struggled to think of a good use case for when a temporary stored procedure would be preferable to a permanent stored procedure.
Not long ago I encountered a scenario where using a temporary stored procedure was the perfect solution to my problem.
Those scenarios are rare but Bert did hit one of them.
Comments closedErik Darling explains that the IF block in a stored procedure won’t help you with performance:
Making plan choices with IF branches like this plain doesn’t work.
The optimizer compiles a plan for both branches based on the initial compile value.
What you end up with is a stored proc that doesn’t do what it’s supposed to do, and parameter sniffing times two.
Read on to see an example of this. If you really, really want to use an IF block, you could separate the components out into individual stored procedures and call those stored procedures independently.
Comments closedErin Stellato shows us how we can find the slowest query within a stored procedure:
Figuring out exactly what causes slow performance for a stored procedure can sometimes feel like trying to unravel a ball of Clark Griswold’s Christmas lights. It’s not uncommon to see procedures with hundreds, even thousands of lines of code. You may have been told which stored procedure runs slow by a user or manager, or you might have found it by looking in SQL Server DMVs. Either way, once you have detected the offending procedure, where do you start?
If you’re running SQL Server 2016, one option is Query Store. Query Store captures individual queries, but it also captures the object_id, so you can find all the queries that are associated with an object to determine which ones are problematic.
This is quite useful when you have to tune a procedure you’ve never seen before, and as you go to open that procedure, the vertical scroll bar keeps getting smaller and smaller.
Comments closedDavid Fowler shows you how to create a signed certificate which allows for cross-database access:
Recently Manish Kumar asked an interesting question, what do you do if your proc accesses multiple or even all the databases on the server?
So, instead of giving him a fuzzy answer in reply, I thought I’d write up exactly how you can deal with that sort of situation.
We’ve got two options and we’ll have a look at both of them (I’m not going to go into details about how signing procs works, please see the post mentioned earlier for a basic overview, here I’m going to look specifically at procs that access multiple databases).
Click through to see both solutions.
Comments closed