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.



No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |