Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 308

SP: StmtCompleted
SQL:BatchCompleted
SQL:StmtCompleted

RPC:Completed

26. It is ideal to limit the data to a minimum and monitor the events such as Stored
procedures: RPC Completed and TSQL: BatchCompleted.
27. Further, to perform a collective collection of PERFMON and PROFILER data, use
the SQLNexus tool from the http://sqlnexus.codeplex.com/ site. Before
installing the SQLNexus tool, go through the Getting Started link of the URL.

To monitor and manage the data platform using PowerShell, we
can obtain related cmdlets from http://psx.codeplex.com /.

28. Open Dynamic Management Views (DMV).
29. Finally, the following list of DMVs are useful to obtain the snapshot of system
resources usage:

sys.dm_os_sys_info
sys.dm_os_wait_stats
sys.dm_os_performance_counters
sys.dm_os_memory_clerks
sys.dm_exec_procedure_stats
sys.dm_os_waiting_tasks
sys.dm_exec_sql_text
sys.dm_exec_requests

30. Once these tools are used to set up, perform the application activities that are
considered as day-to-day and important for business as usual tasks.

This completes the steps to implement a performance baseline on an existing SQL
Server instance.

How it works...

When troubleshooting a slow performance task and setting up a performance baseline for tasks,
Performance Monitor and SQL Server native tools come in handy. The Integrated Resource
View screen from Activity Monitor gives the required snapshot of system resources usage.
Further, using PERFMON tool shows the condition of the CPU, disk, network, and memory of the
local server in real time. Also, for each of the resource types, the detail section also shows a list
of processes running. This can help you identify the process causing the bottleneck.