How many tools (new and enhanced) are available within SQL Server 2008 when you have a need to monitor the performance of queries that are executing/generated?

The initial ones you remember is PROFILER and PERFMON (SYSMON) tools, even further few will say Performance Dashboard reports & Dynamic Management Views. The new tools that are available are Data Collector (Management Data Warehouse), Activity Monitor, Resource Governor & XEvents and enhanced tools are Configuration manager, Graphical Showplan, SQL Profiler, DMVs and Database Tuning Advisor.

 The usual class of PROFILER and PERFMON can be used to assess the system's resource usage by using the Performance Statistics event class to monitor the performance of queries, stored procedures, and triggers that are executing. In this event there are  six event subclasses indicates an event in the lifetime of queries, stored procedures, and triggers within the system. Along with DMVs such as  sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_trigger_stats it will be ideal to reinstate the performance history of your database system for any given execution on the SQL Server instance.To add more fuel to your Performance Data Collection you could make use of  XEvents that will provide extra help with  event notifications to monitor events that occur in the Database Engine. This is helpful to obtain an audit of database activity, gather sample data for a test environment, debug Transact-SQL statements and stored procedures, and gather data for performance analysis tools.

Further on event notifications that can send information to a Service Broker service about many of the same events that are captured by SQL Trace. But unlike traces, event notifications can be used to perform an action inside SQL Server in response to events. Because event notifications execute asynchronously, these actions do not consume any resources defined by the immediate transaction. SQL Server 2008 gets  a number of features one might want to use to collect, analyze, monitor and report performance related data. In this blog post we will not be able to cover all the aspects but I will try to give some of the interesting ones from the good old system monitoring counters that have been added as part of RTM release of SQL Server.

As referred above this version of SQL Server is more loaded for performance collection, monitoring and reporting that can give handful of information for DBA to assess the system. One of them my favourite is Policy Based Management and Management Data Warehouse, if you happen to live in UK then make sure to attend the SQLBits - Cubed: Don't miss the opportunity! community conference. Within the MainAgenda you will see the sessions assigned to SQL Server 2008 (Katmai) and I'm delivering Performance Monitoring Using SQL Server Management Studio - power of Management Data Warehouse  session that covers MDW. Along with the old-timers such as SQL Server profiler, Database Engine Tuning Advisor, SQL Server Management Studio Reports and the SQL Server Management Packs still exist and are available to you.