How to get resource usage of an executing query in SQL Server without using SYSMON/PERFMON utility?

Perfmon/Sysmon utility is a handy utility for Database Administrators to invoke for data to be collected on system resources usage. The logging function of this tool is useful for logging details that can be used for further analysis from the trace.

The SQL Server Develoment team has made a major contribution to combining this facility with SQL Server Management Studio (SSMS) in version 2005. There is a set of pre-made reports in Management Studio that can help DBAs to gather information in many common areas of investigation. For instance, the reports for CPU, Memory, Disk session, queries etc are extremely useful when combined with the reporting tool. To accomplish this task follow the below steps:

1. Open Management Studio
2. Highlight a server instance or database in Object Explorer
3. Open the Summary Page (F7)
4. Click on the Reports button

In SSMS there is a feature to generate multile reports using Object Explorer node fashion. The user can highlight a SQL server instance to select different sets of reports.

In order format the trace as a manageable report the DBA must configure the SQL Server Reporting Services envionrment by joining the SSMS with .RDL file.

Tip:Please be aware that the Reporting Services engine control will be slow compared to other reporting when dealing a large amount of trace capture.