Andrew Fraser DBA

9 Monitoring Tools

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)

SQL Server is largely self tuning. Because it is designed for only one operating system, it is tightly integrated into Windows Server memory management. The SQL Server Buffer Cache, for example is determined dynamically and automatically, which tends to leave little in the way of performance tuning and configuration for the DBA. The exception is when multiple instances are installed and run on the same server. Each instance must then have its memory parameters manually controlled by the DBA. This is set by right clicking on the instance name in SQL Server Management Studio, choosing “Properties”.

Indexing and optimal coding still has to be performed manually, although SQL Server includes good tools to assist with this. SQL Server uses a cost based optimizer, so statistics have to be refreshed for optimal explain plans.

The DBA may also need to be involved in resolving locking problems.

There are seven main monitoring tools in SQL Server:

9.1 Windows Event Viewer in “Start” > “Admin Tools” > “Event Viewer”

An expanded equivalent of the unix /var/log/messages file.

9.2 Windows/SQL Server Performance Monitor in ““Start” > “Admin Tools” > “Performance Monitor”

This shows a huge range of common statistics such as Buffer Cache Hit Ratio, writes per second, etc. A brief definition of each statistic is included.

9.3 SQL Server log files are viewed under Management > SQL Server Logs. By default they are recycled and only five old logs kept, it is good practice to increase that. To do so, right click on “SQL Server Logs” and choose “Configure”.

The SQL Server Agent has its own logs under “SQL Server Agent” > “Error Logs”, and the history of its jobs and steps is also retained against each job.

9.4 Activity Monitor in “SQL Server Management Studio” right-click on instance.

This lists current processes (like Oracle Top Sessions), and database locks (like Oracle lock manager).

9.5 SQL Server Profiler in “SQL Server Management Studio” > “Tools”

Similar to Oracle trace and AWR.

9.6 Database Engine Tuning Advisor in “SQL Server Management Studio” > “Tools”

Recommends indexes and indexed views (= materialized views).

9.7 SQL Query Window in “SQL Server Management Studio” > “New Query” button

This displays the explain plan and statistics for SQL Statements. It is also useful as a window for actually executing statements.

Lab 9 – Use each of these 7 tools

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)