Explicitly parameterizing queries is a well-known best-practice for database app developers. There are cases where it can make sense to execute a query unparameterized, but, in general, you should default to explicit parameterization. It can make your queries faster by avoiding unnecessary compilation when the "same" query is run repeatedly with different parameters, and it can reduce competition for limited memory in SQL Server's visible buffer pool. If you don't already know how to parameterize your queries, or if you want more information about the benefits of parameterization, check out:
Below is a handy DMV query that can quickly identify which queries have many different plans cached for the same query. In most cases, this indicates a lack of explicit parameterization of the query. This uses the query plan hash/query plan fingerprint feature that I wrote about in an earlier post. It relies on the fact that two queries that have different inline literal values (e.g. "SELECT...WHERE col1 = 123", vs. "SELECT...WHERE col1 = 456") will get two different query plans, but the plans will have the same query_hash value. You must be running SQL Server 2008 or later to use this.
WITH duplicated_plans AS ( SELECT TOP 20 query_hash, (SELECT TOP 1 [sql_handle] FROM sys.dm_exec_query_stats AS s2 WHERE s2.query_hash = s1.query_hash ORDER BY [sql_handle]) AS sample_sql_handle, (SELECT TOP 1 statement_start_offset FROM sys.dm_exec_query_stats AS s2 WHERE s2.query_hash = s1.query_hash ORDER BY [sql_handle]) AS sample_statement_start_offset, (SELECT TOP 1 statement_end_offset FROM sys.dm_exec_query_stats AS s2 WHERE s2.query_hash = s1.query_hash ORDER BY [sql_handle]) AS sample_statement_end_offset, CAST (pa.value AS INT) AS dbid, COUNT(*) AS plan_count FROM sys.dm_exec_query_stats AS s1 OUTER APPLY sys.dm_exec_plan_attributes (s1.plan_handle) AS pa WHERE pa.attribute = 'dbid' GROUP BY query_hash, pa.value ORDER BY COUNT(*) DESC)SELECT query_hash, plan_count, CONVERT (NVARCHAR(80), REPLACE (REPLACE ( LTRIM ( SUBSTRING ( sql.[text], (sample_statement_start_offset / 2) + 1, CASE WHEN sample_statement_end_offset = -1 THEN DATALENGTH (sql.[text]) ELSE sample_statement_end_offset END - (sample_statement_start_offset / 2) ) ), CHAR(10), ''), CHAR(13), '')) AS qry, OBJECT_NAME (sql.objectid, sql.[dbid]) AS [object_name], DB_NAME (duplicated_plans.[dbid]) AS [database_name]FROM duplicated_plans CROSS APPLY sys.dm_exec_sql_text (duplicated_plans.sample_sql_handle) AS sqlWHERE ISNULL (duplicated_plans.[dbid], 0) != 32767 -- ignore queries from Resource DB AND plan_count > 1;
It took quite a while to complete on a system with ~50,000 ad hoc query plans in the plan cache. :)
But this query is extremely useful for big servers that have many different ad-hoc workloads running on them.
Thanks a million.