SQL Server Monitoring

SQL server provides a number of tools and views that allow DBAs to effectively monitor the server. This section provides an introduction to SQL Server monitoring tools. SQL Server allows DBAs to access a new set of SQL views called Catalogue views to obtain system catalog information. These read-only views display metadata and definitions. Many catalogue views are available. The views follow a naming convention and may be queried through a lookup of an associated System function.

System Catalogue Views

The SQL Server Profiler uses a trace file to capture the load on a server at specific points and replay the situation. A DBA may use the profiler to analyze heavy workloads that affect performance. The profiler may be used to diagnose performance problems, identify slow running queries, identify the reason behind slow execution, and capture sets of commands that lead to system failure, etc. SQL Server Profiler trace files may be saved and exported to other formats.

Profiler Options

A SQL Server 2005 database alert may be scheduled to respond to error flags thrown by the database when errors occur. The error flag normally contains information about error severity. This information is relayed to the DBA through the alert. For example, an alert may be created and signaled once an executing query crosses a certain threshold in terms of time taken for execution. Alerts may be designed and deployed using SQL Server Agent or T-SQL commands.

SQL Server 2005 provides several new Dynamic Management Views (DMVs) that allow DBAs to monitor instances from the server level down to the database level in Real Time. DMVs are found in each database's System folder and have the prefix dm_<view_name>. Execution of user modules and connections may be monitored via DMVs that start with the prefix dm_exec_. Execution schedule and lock related information may be obtained through dm_os_, transaction related information may be procured via DMVs prefixed with dm_trans_. Disk input and output operations may be monitored through DMVs with prefix dm_io_.

The Database tuning advisor recommends streamlined database designs that optimize data manipulation and retrieval efficiency. Specifically, the advisor provides Time-bound tuning recommendation, multi-database tuning, partition tuning, scalability suggestions, and event tuning. Results of DTA analyses may serve as output to T-SQL scripts, text reports, or XML reports. The DTA component runs outside the SQL Server processes on its own DTAShell.exe executable.

Database Tuning Advisor

The SQL Server Management Studio includes a current activity window that displays information about current users, locks, processes, locked objects and so on at the database level. An event notification is a database object similar to a trigger and may be designed to execute in response to specific DDL statements and so on. Unlike a trigger, an event notification is not synchronously executed along with the triggering event; it may even be sent to wait on a message queue for processing at some later time. Event notification may be used to monitor changes to database structure and so on in complex scenarios.