Watch this week's video on YouTube

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.

In short, the answer is that the query optimizer will treat a query defined in a stored procedure exactly the same as a query submitted on its own.

Let's talk about why.

Start with a Plan

While submitting an "EXEC <stored procedure>" statement to SQL Server may require fewer packets of network traffic than submitting the several hundred (thousands?) lines that make up the query embedded in the procedure itself, that is where the efficiencies of a stored procedure end*.

*NOTE: There are certain SQL Server performance features, like temporary object caching, natively compiled stored procedures for optimized tables, etc… that will improve the performance of a stored procedure over an ad hoc query. However in my experience, most people aren't utilizing these types of features so it's a moot point.

After receiving the query, SQL Server's query optimizer looks at these two submitted queries exactly the same. It will check to see if a cached plan already exists for either query (and if one does, it will use that), otherwise it will send both queries through the optimization process to find a suitable execution plan. If the standalone query and the query defined in the stored procedure are exactly the same, and all other conditions on the server are exactly the same at the time of execution, SQL Server will generate the same plans for both queries.

To prove this point, let's look at the following query's plan as well as the plan for a stored procedure containing that same query:

CREATE OR ALTER PROCEDURE dbo.USP_GetUpVotes
    @UserId int
AS
SELECT  
    COUNT(*) AS UpVotes 
FROM 
    dbo.Posts p
    INNER JOIN Votes v
        ON v.PostId = p.Id 
WHERE 
    p.OwnerUserId = @UserId
    and VoteTypeId = 2
ORDER BY UpVotes DESC


EXEC dbo.USP_GetUpVotes 23

DECLARE @UserId int = 23

SELECT 
    COUNT(*) AS UpVotes 
FROM 
    dbo.Posts p
    INNER JOIN Votes v
        ON v.PostId = p.Id 
WHERE 
    p.OwnerUserId = @UserId
    and VoteTypeId = 2
ORDER BY UpVotes DESC

As you can see, the optimizer generates identical plans for both the standalone query and the stored procedure. In the eyes of SQL Server, both of these queries will be executed in exactly the same way.

But I Swear My Stored Procedures Run Faster!

I think that a lot of the confusion for thinking that stored procedures execute faster comes from caching.

As I wrote about a little while back, SQL Server is very particular about needing every little detail about a query to be exactly the same in order for it to reuse its cached plan. This includes things like white space and case sensitivity.

It is much less likely that a query inside of a stored procedure will change compared to a query that is embedded in code. Because of this, it's probably more likely that your stored procedure plans are being ran from cached plans while your individually submitted query texts may not be utilizing the cache. Because of this, the stored procedure may in fact be executing faster because it was able to reuse a cached plan. But this is not a fair comparison - if both plans would pull from the cache, or if both plans had to generate new execution plans, they would both have the same execution performance.

So does it matter if I use stored procedures or not?

So while in the majority of cases a standalone query will perform just as quickly as that same query embedded in a store procedure I still think it's better to use stored procedures when possible.

First, embedding your query inside of a stored procedure increases the likelihood that SQL Server will reuse that query's cached execution plan as explained above.

Secondly, using stored procedures is cleaner for organization, storing all of your database logic in one location: the database itself.

Finally, and most importantly, using stored procedures gives your DBA better insight into your queries. Storing a query inside of a stored procedure means your DBA can easily access and analyze it, offering suggestions and advice on how to fix it in case it is performing poorly. If your queries are all embedded in your apps instead, it makes it harder for the DBA to see those queries, reducing the likelihood that they will be able to help you fix your performance issues in a timely manner.

Have you ever wanted to find something that was referenced in the body of a SQL query?

Maybe you need to know what queries you will have to modify for an upcoming table rename.  Or maybe you want to see how many queries on your server are running [SELECT *]{.lang:default .highlight:0 .decode:true .crayon-inline}

Below are two templates you can use to search across the text of SQL queries on your server.

Watch this week's video on YouTube

1. Searching Stored Procedures, Functions, and Views

If the queries you are interested in are part of a stored procedure, function, or view, then you have to look no further than the [sys.sql_modules]{.lang:default .highlight:0 .decode:true .crayon-inline} view.

This view stores the query text of every module in your database, along with a number of other properties.

You can use something like the following as a template for searching through the query texts of these database objects:

USE [<database name>];
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    o.type_desc AS ObjectType,
    DB_NAME(o.parent_object_id) AS DatabaseName,
    s.name as SchemaName,
    o.name as ObjectName,
    r.Definition
FROM
    sys.sql_modules r
    INNER JOIN sys.objects o
        ON r.object_id = o.object_id
    INNER JOIN sys.schemas s
        ON o.schema_id = s.schema_id
WHERE
    -- put your search keyword here
    r.Definition LIKE '%SELECT%'

For example, I recently built a query for searching stored procedures and functions that might contain SQL injection vulnerabilities.

Using the starting template above, I added some filtering in the WHERE clause to limit my search to queries that follow common coding patterns that are vulnerable to SQL injection:

USE [<database name>];
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    o.type_desc AS ObjectType,
    DB_NAME(o.parent_object_id) AS DatabaseName,
    s.name as SchemaName,
    o.name as ObjectName,
    r.Definition
FROM
    sys.sql_modules r
    INNER JOIN sys.objects o
        ON r.object_id = o.object_id
    INNER JOIN sys.schemas s
        ON o.schema_id = s.schema_id
WHERE
    -- Remove white space from query texts
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        r.Definition,CHAR(0),''),CHAR(9),''),CHAR(10),''),CHAR(11),''),
        CHAR(12),''),CHAR(13),''),CHAR(14),''),CHAR(160),''),' ','')
    LIKE '%+@%'
    AND 
    ( -- Only if executes a dynamic string
        r.Definition LIKE '%EXEC(%'
        OR r.Definition LIKE '%EXECUTE%'
        OR r.Definition LIKE '%sp_executesql%'
    )

2. Searching Ad-Hoc SQL Queries

Searching across ad-hoc queries is a little tougher.  Unless you are proactively logging the query texts with extended events or some other tool, there is no way to definitively search every ad-hoc query text.

However, SQL Server does create (or reuse) an execution plan for each query that executes.  Most of those plans are then added to the execution plan cache.

Execution plans are eventually removed from the cache for various reasons, but while they exist we can easily search their contents, including searching through that plan's query text.

As a starting point, you can use the following code to retrieve SQL query texts that are currently stored in the plan cache:

USE [<database name>];
GO

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)') AS [Query],
   query_plan AS [QueryPlan]
FROM 
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
WHERE
    -- put your search keywords here
    stmt.value('(@StatementText)[1]', 'varchar(max)') LIKE '%SELECT%'

Although the template above searches for the query texts in our execution plans, you can also use it to search for other query plan elements, such as elements that indicate if you have non-sargable query.

I used this technique recently to search for ad-hoc queries that might be vulnerable to SQL injection.  I modified the template above to search the input parameter values instead of the query texts, flagging any values that look like they might have some injection code in them:

USE [<database name>];
GO

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)') AS [Query],
   query_plan AS [QueryPlan],
   stmt.value('(.//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(1000)') AS [ParameterValue] 
FROM 
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
WHERE
    -- if single quotes exist in a parameter
    stmt.value('(.//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(1000)') like '%''%'
    OR stmt.value('(.//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(1000)') like '%sys.objects%'
    OR stmt.value('(.//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(1000)') like '%[0-9]=[0-9]%'

So while using this technique won't allow you to search across 100% of ad-hoc queries, it should be able to search the ones that run most frequently and appear in your plan cache.

Intrigued by how I'm searching query texts for SQL injection vulnerabilities? Attend my online webcast on Tuesday November 14, 2017 at 1PM Eastern at the PASS Security Virtual Group to learn about these queries and how protect yourself from SQL injection.