Summary

In this chapter, you initially looked at the various considerations that a DBA has when deciding on a performance monitoring tool. It is also important to understand the various components or layers in a complicated client/server architecture such as SQL Server 2005 because the DBA needs to know which layer to monitor as part of their performance monitoring solution.

We discussed the importance of a baseline and how it forms the basis of a performance monitoring strategy. Few metrics by themselves mean anything. Metrics gathered relative to the baseline are more meaningful in any case.

Importantly, we went through the various performance object counters that a DBA would be interested in for both a performance baseline and performance thresholds. You examined guidelines for what thresholds are typically used by DBAs for monitoring the processor, memory, and I/O subsystems.

You also looked at the SQL Server Profiler trace events that a DBA would typically be interested in when designing a strategy to monitor a database solution.

You examined the different types of alerts supported by SQL Server 2005 next. SQL Server performance condition and event alerts have been available with the product in earlier edi-tions. SQL Server 2005 supports a new type of alert, called WMI event alerts, which are based on the Windows operating system’s WMI layer. This potentially allows much greater functionality because WMI event alerts are not restricted to just SQL Server 2005–based events.

You then moved on to look at the different types of triggers supported by SQL Server 2005. DML triggers are based on data modification operations and typically are used within the database solution for supporting business rules, data integrity, and data flow, although you can also use them to notify the DBA of certain occurrences of events. DDL triggers are a new addition in SQL Server 2005 and represent a great way of auditing and monitoring DDL activity within a SQL Server solution.

Finally, you examined how you can use event notifications to notify DBAs of certain types of events occurring. Importantly, you looked at the differences between triggers and event notifications in how they operate and where they can be used.