SQL Server 2008 and 2008 R2 (SSQA.net)

It is a common criteria that using Dynamic Management Views (DMVs) you can easily obtain the system & database state on a SQL instance.

It may not be easy for a starter DBA to ensure what kind of DMV is used to obtain information on Recent Expensive Queries, if so for you Activity Monitor is the tool to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server. 

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

Use Activity Monitor to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server.

The Activity Monitor page has the following sections:

• Overview - Shows graphical displays of the percent of processor time, number of waiting tasks, database I/O (megabytes per second), and batch requests (number per second).
• Active User Tasks - Shows information for active user connections to SQL Server Database Engine.
• Resource Waits - Shows wait state information.
• Data File I/O - Shows I/O information for database data and log files.
• Recent Expensive Queries - Shows information about the most expensive queries.
  

SQL Server 2008 SSMS tool helps to view the Activity Monitor in SQL Server 2005 and SQL Server 2008 having that user VIEW SERVER STATE permission. For SQL 2000 version instances, a user must have SELECT permission to the sysprocesses and syslocks tables in the master database. Permission to view these tables is granted by default to the public database role.

Coming to subject line topic (above list) Recent Expensive Queries pane helps to see which of active queries based on the data in the dm_exec_requests DMV, see the underlying information is also obtained using DMVs. This pane results are sorted by CPU (see below image):

This shows up all the queries including the queries executed to open the Management Studio to view database properties etc., as you see on the picture I have looked at top 100 rows from SalesOrder table on Adventure Works database. Here is the trick to obtain the relevant expensive query Execution Plan simply right click on the query line, you’ll see a new command called Show Execution Plan.

Click on the show plan launches the usual SHOW PLAN graphical image that you get from a query editor from query plan cache. To go back to basics, QL Server has a pool of memory that is used to store both execution plans and data buffers. The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. The part of the memory pool that is used to store execution plans is referred to as the procedure cache.

Again the above information is obtain using sys.dm_exec_cached_plans DMV, the handle of the plan (which is binary), and the number of times the plan has been used. To go further on the details it uses sys.dm_exec_requests DMV that gives you the currently running processes, wait information, all the session settings for the processes, etc. with the handle for all the plans which are currently being run. 

Now the actual part of solution to obtain Missing Index Details information, the usual behaviour of UIi represents the green text that indicates a missing index (see BOL for information on Execution Plans). So now if you want to create index simply right click in the execution plan and select the “Missing Index Details…” command.

By clicking on the option will present you the new query editor to execute the creation of new index, it sounds simple to take help of SSMS for such important piece of option for performance and by not juggling the PROFILER and Database Tuning Advisor. Whenever there is a query performance issue the very first option I go with this above procedure to keep up the show and then look into details which buys time to solve the actual problem.