If you want to identify non parameterized queries that are hogging your SQL Server plan cache then you are at the right place.
sys.dm_exec_query_stats DMV becomes very handy when we are looking out for statistical information related to queries. Upon executing the above DMV, we will see a lot of columns in the output, but for now, we should concentrate on query_hash & sql_handle columns. Queries having the same query_hash but different sql_handle are effectively the same queries that are stored as different objects.
In simple terms, queries having the same query_hash and different sql_handle values denotes that, the optimizer is not resuing the plans.
query_hash gives us the information related to logically identical queries and sql_handle helps us to get the query text.
If we JOIN sys.dm_exec_query_stats and sys.dm_exec_sql_text and then do some aggregations on top of few columns, then we will be able to extract meaningful information and at the same time can find out queries that are not able to reuse the plans.
Here is the query that helps us identify whether optimizer is reusing the plans or not:
SELECT eqs.query_hash AS QueryHash ,COUNT(DISTINCT sql_handle) AS CountOfSQLHandles ,SUM(execution_count) AS NoOfExecutions ,SUM(eqs.total_logical_reads) AS TotalLogicalReads ,SUM(eqs.total_worker_time) AS TotalCPUTime ,SUM(eqs.total_elapsed_time) AS TotalDuration ,MAX(est.