Performance Monitoring

To monitor performance of queries, SQL Server has Extended Events. This is a lightweight monitoring system. The SQL Server auditing system is based on Extended Events. You can use Extended Events either through the New Session Wizard GUI, or through the New Session UI GUI.
Another option for monitoring is SQL Trace. This captures events in the database internally. It is an old technique and is officially deprecated. You create the traces through system stored procedure calls. You can also create them via the SQL Server Problem UI. The events are filtered and put into queues.
There are some commands you can issue so that queries automatically generate output to help with monitoring:
  • SET STATISTICS IO ON
  • SET STATISTICS TIME ON
  • SET SHOWPLAN_TEXT / SET SHOWPLAN_XML
  • SET STATISTICS_PROFILE / SET STATISTICS_XML
IO causes general stats to be shown. TIME displays CPU and elapsed time. SHOWPLAN gives you estimated plans in either Text or XML format. PROFILE gives you actual plans used in text format; XML gives you the same in XML format.

In the database, there are Dynamic Management Objects (DMOs). These are a bunch of views and functions to help conduct monitoring. They are part of the sys schema. As of SQL Server 2012, there are about 130 of these DMOs. They are grouped in categories like operating system, execution, and indexes. One important example is sys.dm_os_sys_info.