SQL Server-Dynamic Mgmt Views...

While working on Sql Server development projects, one of the tasks that is constantly performed is performance tuning of the queries. There are lot of tools/techniques available in sql server to perform this task. Since 2005 with the arrival of dynamic management views, the tracking of the queries that are being run on a sql server database has become a little more easier. One could use the DMV's to find out the totalcpu,totalduration and totalreads consumed by a query. The DMV's that can be used for the above purpose are:

sys.dm_exec_sql_text
sys.dm_exec_query_plan
sys.dm_exec_query_stats

In order to get the actual text and the query plan for a particular row present in sys.dm_exec_query_stats, one has to use the CROSS APPLY operator and pass a parameter to the sys.dm_exec_sql_text, sys.dm_exec_query_plan views. For example it would be something like:

select query_plan,
substring(stext.text, (stat.statement_start_offset/2)+1
, ((case stat.statement_end_offset when -1 then datalength(stext.text)
else stat.statement_end_offset
end - stat.statement_start_offset)/2) + 1) as txt
from sys.dm_exec_query_stats as stat
cross apply sys.dm_exec_sql_text(stat.sql_handle) as stext
cross apply sys.dm_exec_query_plan (stat.plan_handle) as qplan

In above code segment, the sql_handle value is passed as a parameter dm_exec_sql_text to get the actual sql text, and the plan_handle is passed to get the actual query plan. The query plan is displayed with a hyperlink, clicking on the link will show the execution plan of the query.