Transcript lesson27
Monitoring and Optimizing SQL Server Lesson 27 Skills Matrix Performance Monitor • SQL Server cannot function properly if it does not have available system resources, such as ample memory, adequate processor power, fast disks, and a reliable network subsystem. • Performance Monitor comes with Windows and is located in the Control Panel under Administrative Tools. Performance Monitor • Once you understand how Performance Monitor works, you can begin to setup a monitoring process. • The counters that you can monitor are grouped into objects. • An object is a part of the system, such as the processor or the physical memory. • A counter displays the statistical information about how much that object is being used. Performance Monitor Common Counters Common Counters SQL Server Performance Counters SQL Server Performance Counters SQL Profiler • Profiler allows you to monitor and record what is happening inside the database engine. • This is accomplished by performing a trace, which is a record of data that has been captured about events. • Traces are stored in a table, a trace file, or both, and they can be either shared (viewable by everyone) or private (viewable only by the owner). SQL Profiler Database Engine Tuning Advisor • If even one SQL Server database were out of tune, it could slow down the entire system. • If any of this is true, your databases need tuning. To do that, you need to use the Database Engine Tuning Advisor. • Before you can run the Database Engine Tuning Advisor, you need to create a workload. – You do this by running and saving a trace in Profiler (usually by creating a trace with the Tuning template). Database Engine Tuning Advisor Summary • This lesson stressed the importance of monitoring and optimizing. • Monitoring allows you to find potential problems before your users find them; without monitoring, you have no way of knowing how well your system is performing. Summary • You can use Performance Monitor to monitor both Windows and SQL Server. • Some of the more important counters to watch are Physical Disk:Average Disk Queue (which should be less than 2) and SQLServer:Buffer Manager: Buffer Cache Hit Ratio (which should be as high as possible). Summary • You can use Profiler to monitor queries after they have been placed in general use; it is also useful for monitoring security and user activity. • Once you have used Profiler to log information about query use to a trace file, you can run the Database Engine Tuning Advisor to optimize your indexes. • The Database Engine Tuning Advisor provides new features that enable both novice and experienced database administrators to tune databases for better query performance. Summary for Certification Examination • Know SQL Server counters, and understand how to use them. • Know the counters available in SQL Server and the acceptable values for each of them. • There are too many to memorize but review Tables 27-1 and 27-2 to find the most important counters and values. Summary for Certification Examination • Familiarize yourself with Profiler. • Profiler displays what is happening in the database engine by performing a trace, which is a record of data that has been captured about events that are logically grouped into event classes. • There are a large number of events, most of which can be used in optimization. • Understand what the Database Engine Tuning Advisor does and when to use it.