Collecting and Storing Poor Performing SQL Server Queries for Analysis
Problem In an ideal world all of our queries would be optimized before they ever make it to a production SQL Server environment, but this is not always the case. Smaller data sets, different hardware, schema differences, etc. all effect the way our queries perform. This tip will look at a method of automatically collecting and storing poor performing SQL statements so they can be analyzed at a later date. Solution With the new Dynamic Management Views and functions available starting in SQL Server 2005, capturing information regarding the performance of you SQL queries is a pretty straightforward task. The following view and functions give you all the information you need to determine how the SQL in you cache is performing: sys.dm_exec_query_stats sys.dm_exec_sql_text(sql_handle) sys.dm_exec_query_plan(plan_handle) Using the view and functions above we can create a query that will pull out all the SQL queries that are currently in the cache. Along with the...