SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability50
A trace event is created to flush the statistics. The event and performance data can use the SQL Server trace framework or the SQL Server Profiler.
For sys.dm_exec_query_stats, a query is identified by using the sql_handle argument. This handle obtains the text of the query. The execution plan of the query is obtained using plan_handle. The result is an XML type query plan.
Examples of queries leveraging this view include the following.
The following query lists the performance figures of all queries in statement cache.
select * from sys.dm_exec_query_stats
The following example shows the Transact-SQL statement text where the specific handle was read from the result set of the first query.
select * from sys.dm_exec_sql_text(sql_handle)
This query shows the query plan.
select query_plan from sys.dm_exec_query_plan(plan_handle)
The results from this DMV contain columns with performance metrics for each query including:
Physical reads and writes.
Logical reads and writes.
Time spent in common language runtime (CLR) for all statements that refer to a CLR function.
Number of executions.
Worker time, showing the time spent in processing.
Elapsed end-to-end time from the point when the request came into SQL Server to the time when the result is returned, including statement execution and wait times.
Average per executions calculated from the columns displayed.
Monitoring index usage
SAP production systems that have been active for many years have received periodic SAP release upgrades and custom changes. In some cases, when SAP release upgrades are made, custom-programmed functionality can be discontinued, leaving related database structures such as database indexes in the database. In this case, the sys.dm_db_index_usage_stats DMV is used to collect and track the usage of each index and to assist in determining which indexes are actually in use.
Microsoft Corporation © 2005