Monitoring and Controlling Resources Best Practices in SQL Server 2008

06 Nov 2010

SQL

The best practices for monitoring and controlling resources in SQL Server 2008 are:

• Establish a performance baseline over a period of time that reflects the differing utilization of SQL Server 2008 over the business cycle
• Choose an appropriate tool to monitor the system
• Supervise actual performance so that comparisons can be made with the established baseline
• Identify performance bottlenecks and take the appropriate action to rectify the bottlenecks
• Carry out monitoring activities remotely so that the monitoring itself does not affect the performance of the server that is being monitored
• Use SQL Server Data Collectors to centrally collect information from many servers in an organization
• Ensure that the Management Data Warehouse is not a place on a business critical transactional system because it can impede the SQL Server performance
• Use Policy-Based Management to ensure a consistent implementation of all servers running SQL Server across the organization
• Use Resource Governor to control the resource usage of memory and CPU