Monitoring SQL Server Performance
SQL Server provides several tools for monitoring events in SQL Server and for tuning the physical database design. These tools are described in the topic Tools for Performance Monitoring and Tuning (http://go.microsoft.com/fwlink/?LinkId=146357) in the SQL Server books online. Information about specific tools used for SQL Server performance monitoring and tuning is provided below.
Because BizTalk Server is such a database-intensive process, it is often helpful to take a peek at what is going on in SQL Server when troubleshooting BizTalk Server performance issues. The remainder of this topic describes performance monitoring tools available for reviewing both real-time and archived data on SQL Server 2008 R2 and SQL Server 2008 SP1.
SQL Server 2008 R2 Activity Monitor provides information about SQL Server processes and how these processes affect the current instance of SQL Server. For more information about SQL Server 2008 R2 Activity Monitor, see Activity Monitor (http://go.microsoft.com/fwlink/?LinkId=146355) in the SQL Server books online. For information about how to open Activity Monitor from SQL Server Management Studio, see How to: Open Activity Monitor (SQL Server Management Studio (http://go.microsoft.com/fwlink/?LinkId=135094) in the SQL Server books online.
SQL Server 2008 R2 provides a data collector that you can use to obtain and save data that is gathered from several sources. The data collector enables you to use data collection containers, which enable you to determine the scope and frequency of data collection on a computer that is running SQL Server 2008 R2. For more information about implementing SQL Server 2008 R2 data collection, see Data Collection (http://go.microsoft.com/fwlink/?LinkId=146356) in the SQL Server books online.