SQL Server: Detect Non-Parameterized Queries

Question

I have a client which has had programming performed by past developers. Their code has recently become suspect, and I'd like to know if they are using parameterized queries. I was hoping I could detect non-parameterized requests through SQL Server, but I haven't found a way to do this. I understand that not all queries need to be parameterized, since a query might be something like

select count(*) from Customers

But if SQL Server could tell me programmatically which queries have any string-constant inputs instead of parameter inputs, that would be great. By the way, logging all SQL queries and removing all lines with the @ symbol is close, but the below query would then be considered legitimate:

select * from Users where Username='user' and Password=@Password

So I really need SQL Server to read into the content of the command and determine if all inputs are parameterized. Thank you.

Answer 1

In fact you're searching for adhoc queries (not stored procedure and not sp_executesql).

You could start by having a look on sys.dm_exec_cached_plans DMV:

SELECT
    CP.usecounts
    , CP.cacheobjtype
    , ST.text as sql_Statement    
    , QP.query_plan
FROM
    sys.dm_exec_cached_plans CP
    CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
    CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
    ST.dbid = DB_ID()
    AND CP.objtype IN ( 'Adhoc')
ORDER BY
    CP.usecounts DESC

Just be aware that Relational Engine could parametrize simple queries (feature called simple parametrization), so you might have same rows to

In case you want to achieve an improvement of performance by not caching plans for adhoc queries, there is a server option called optimize for ad hoc workloads that signals to Relational Engine that first time when a query is executed => to store a lighter version of the plan (plan stub).

Plan cache pollution could be also a reason for degrading database performance. Check this query to detect if it's your case also.

Answer 2

Mihai is pointing you to the current system object, dm_exec_cached_plans. (When I looked up an answer to your question, I found that [syscacheobjects] is actually deprecated.) Therefore, I did upvote his answer.

However, I still think it could save you time, to check for a single quote after the WHERE. Using dm_exec_cached_plans, here's a similar query, using a WHERE like I gave you before:

SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans 
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE [text] LIKE '%WHERE%''%'
----AND [text] NOT LIKE '%sp_executesql%'  ----queries probably ok, with sp_executesql
----WHERE usecounts > 1   ----un-commenting this might also be interesting to check out. 
ORDER BY usecounts DESC;

Or, just adding it to Mihai's query:

SELECT
    CP.usecounts
    , CP.cacheobjtype
    , ST.text as sql_Statement    
    , QP.query_plan
FROM
    sys.dm_exec_cached_plans CP
    CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
    CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
    CP.objtype = 'Adhoc'
    AND ST.dbid = DB_ID()
    AND ST.text LIKE '%WHERE%''%'
ORDER BY
    CP.usecounts DESC

Note the ORDER BY, built on the thought that the commonly occurring queries are causing frequent exposure. But as you probably already realize... if the suspect code is building the query strings itself, then unique values (like row numbers or customer ids or order numbers, etc.) could produce unique queries that, (if they were built without using sp_executesql), could actually be the most important to look at, even though they have usecounts = 1.

As far as the answer to your question goes, ("When do SQL queries get deleted from this table?"): once there are enough queries in the cache to cause "memory pressure," old queries drop out of the cache as new queries come in... and all of it is cleared when SQL restarts. (See http://www.sqlservercentral.com/Forums/Topic1375781-391-1.aspx and http://technet.microsoft.com/en-us/library/ms181055%28v=sql.105%29.aspx)

Hope that helps...