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.