hi but we are talking about many stored procedures, how would i know the ones that have the have the parameter sniffing problem? do i have to check each one? i have a query i found which gives me the number of recompilations, writes, reads, can this query help me find the stores procedures which have this issue?

this is the query:

SELECT

substring(text,qs.statement_start_offset/2

,(CASE

WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)

,qs.plan_generation_num as recompiles

,qs.execution_count as execution_count

,qs.total_elapsed_time - qs.total_worker_time as total_wait_time

,qs.total_worker_time as cpu_time

,qs.total_logical_reads as reads

,qs.total_logical_writes as writes

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

LEFT JOIN sys.dm_exec_requests r

ON qs.sql_handle = r.sql_handle

ORDER BY 3 DESC

can i run this query when i am having the performance problem to see which stored procedure/query could be the one with the problems? we are talking about 30-40 transactions per min.