Microsoft® SQL Server® 2008 Administration with Windows PowerShell™

Chapter 17. Collecting SQL Server Performance and Host Performance Data

The previous two chapters covered PowerShell and T-SQL procedures related to SQL Server inventory and database administration and installation. In this chapter you are going to see the various methods used to collect performance-related data from SQL Server. Performance bottlenecks can arise on three different resources: disk, memory, and CPU. Collecting performance-related data from SQL Server is very important for any troubleshooting. This chapter illustrates how to collect such data related to these resources.

SQL Server 2008 comes with the new feature Performance Data Collection. This chapter covers some functionality of the Data Collector by using Windows PowerShell. This chapter will also help you understand how to execute SQL Server stored procedures on a remote machine and bring the output data from one server to another.

There are two types of performance-related data activities. One is collecting performance-related data from the host; the other is collecting performance-related data related to the SQL Server instance. Both are equally important for troubleshooting any performance-related issues. Performance-related data collection can be scheduled on-host or off-host depending on the company and resource utilization.

SQL Server Performance Data Collection

First we'll collect information related to SQL Server. Data collection based on SQL Server-related performance is usually an on-host job because it ...