I'm analyzing a 2008 Instance that supports a 3rd party app.
The app will generate the SQL Code then send it to the Database as an ad-hoc query.
I'm using this query (based on a Glenn Berry script):
SELECT qs.creation_time ,qs.last_execution_time ,qs.execution_count ,qs.total_worker_time ,qs.total_physical_reads ,qs.total_logical_writes ,qs.total_logical_reads ,qs.plan_handle ,qt.text ,qt.dbid FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid >= 7 OPTION (RECOMPILE)
My problem is that I'm getting thousands of plans for very similar queries i.e.
SELECT * FROM customers WHERE name = 'bob' SELECT * FROM customers WHERE name = 'bill'
(in reality the queries are very large and up to 3000 characters)
It's making it almost impossible to get the data into a format that's ideal for high level analysis.
Is it possible to quickly compare 2 SQL queries and see if they are pretty much the same query? I'd then pick one of the queries at random and group all the activity against that one query. (I've tried DIFFERENCE but it's very slow)
Does SQL already store a value similar to the MD5 Hash sql_handle that allows it to see if two queries are similar and consequently reuse the same plan? (If such a value exists then I'd group on that)
I don't have this problem with Stored Procedures because the same plan is being reused. It's just all the similar ad-hocs that I want to group together.