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

How to do it

Once the essential tools are installed, go through the following steps to implement
performance baseline practices to complete the task:

1. Open the Activity Monitor.

2. Obtain a list of all tables on the user database to review the permissions. Let us

call it as a schema reconciliation report to verify the information about integrity
constraints, indexes, and columns.

3. Review all the database maintenance scheduled jobs history to determine success

or failure.

4. Open the SSMS tool, right-click on server instance, and open, Activity Monitor to

obtain an instantaneous window view of Processes, Resource waits, Data File I/O,
and Recent Expensive Queries.

As best practice it is ideal to close down the
Activity Monitor window within the SSMS tool
when it is not required.

5. Within the Activity Monitor window look at the Overview window that displays the

graphs on % Processor Time (%), Waiting Tasks, Database I/O (MB/sec), and
Batch Requests/Sec information that will have a default value of ten seconds
Refresh Interval.

6. To obtain a trend of resource usage, it is ideal to increase the refresh interval to 30

seconds or higher by right-clicking on any of the graphs in the Overview tab.

Using the Performance Monitor (Perfmon) tool, we will
create a custom Data Collector set containing performance
counters based on selective counters activity.

7. Download the SQL Server PERFMON Counters poster document from http://www.

quest.com/backstage/images/promotions/SQLServer-Perfmonance-
Poster.pdf from Quest Software, which shows the essential list of counters
for baseline.