\nThere's a newer version of SQL Server 2008 (Release Candidate 0) available on MSDN and Technet.\n<\/p>\n
\nAnother performance-related feature (actually its additional information) allows you to have better visibility into performance-affecting queries. This information is available as columns query_hash and query_plan_hash in the sys.dm_exec_query_stats and sys.dm_exec_requests DMV. There's a really nice illustrative example in Books Online, so I won't repeat it here. You can use the queries in my last blog post to experiment with this as well.\n<\/p>\n
\nThe queries:\n<\/p>\n
\nSELECT title_id, sum(qty) from sales
\ngroup by title_id
\nhaving sum(qty) = 30\n<\/p>\n
\nand\n<\/p>\n
\nSELECT title_id, sum(qty) from sales
\ngroup by title_id
\nhaving sum(qty) = 40\n<\/p>\n
\nare similar enough that they could be parameterized (although they aren't autoparameterized). Parameterization would save query plan entry storage, rather than allocating a separate plan for each incantation of the similar query. You can also use this feature, along with the additional information in sys.dm_exec_query_stats to produce cumulative statistics (such as avg IOs, elasped time, etc. BOL has a nice query to accumulate similar plans, the relavent part is simply "GROUP BY query_hash".\n<\/p>\n
\nBut because you have similar queries, should you always have a parameterized query? What if "…having sum(qty) = 30" has a completely different plan than "…having sum(qty) = 130"? You can find this information by looking at query_plan_hash. If two plans have similar structure but a different query plan, the query_hash value will be identical but query_plan_hash will be different. That's an indication that parameterization might not be the way to go; the first query to executed will cache it's plan and the other query will use the same plan. This is because of a SQL Server behavior known as parameter sniffing.\n<\/p>\n
\nBecause parameterizing queries is usually one of the first things programmers can do to affect performance, it helps to know when to parameterize. In addition, many folks like to start query tuning by tuning the queries (including similar queries with different parameter values) that are executed most frequently. Query_plan_hash and query_hash gives you visibility into this important information.<\/p>\n","protected":false},"excerpt":{"rendered":"
There's a newer version of SQL Server 2008 (Release Candidate 0) available on MSDN and Technet. Another performance-related feature (actually its additional information) allows you to have better visibility into performance-affecting queries. This information is available as columns query_hash and query_plan_hash in the sys.dm_exec_query_stats and sys.dm_exec_requests DMV. There's a really nice illustrative example in Books […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,29],"tags":[],"class_list":["post-689","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server-2008"],"yoast_head":"\n