In this article I will give you the information you need to help solve specific bottlenecks, and to resolve future bottlenecking issues. The best tool to help identify hardware bottlenecks is Performance Monitor. Using Performance Monitor, we can identify issues in each of a set of performance objects.
I would like to say that the subject of SQL server performance tuning is big and complex, so my focus in this article will be specifically on SQL Server hardware tuning and performance monitoring.
The Art of Performance Monitoring
Performance monitoring is an art, in that it is a combination of talent, experience, knowledge, and sometimes just plain luck. How do you know if you can do it? You have to try, try, and try again. Keep at it, read up on it. Keep Performance Monitor continually open against your production server and watch what is happening throughout each day.
Here are some good guidelines to get you started.
1. Make sure that you are running your typical processes (SQL Server) and typical queries and stored procedures when monitoring.
2. Do not just do real-time monitoring of your servers. Capture long running logs. In Windows NT, install the Datalog/Monitor service from the NT Resource Kit; this functionality is available out of the box in Windows 2000.
3. Always have disk counters turned on by running from a command prompt the command DISKPERF -Y, and then rebooting. Even in a production environment, the overhead is minimal; the last thing you want to do in the middle of a crisis where logical and physical disk counters are needed is to have to add them and reboot.
4. For routine, daily, desktop monitoring, set up the chart window with an interval of 18 seconds. In both the Windows NT PerMon and the Windows 2000 MMC SysMon, this will give your chart a Window of 30 minutes. For me, this has proven to be the ideal interval for both seeing the past and minimizing the impact on the server.
5. Utilize SQL Profiler for individual queries and processes in coordination with PerMon or SysMon to get a good picture of the impact of individual queries.
6. Know the terminology of performance monitoring. Objects are lists of individual statistics that are available for monitoring. An example is the Processor object. A counter is a single statistic that falls under the heading of an object. An example is the percent processor time counter under the Processor object. An instance is further breakdown of a counter statistic into duplicate components. Not all counters have separate instances. The percent processor Time counter has instances for each processor, and a _Total instance as a summery of all processor activity.
7. Know your tools. While you may know how to set up a chart in PerMon, learn how to set up a log with Datalog or Performance Log. Other tools to be familiar with are DBCC MEMUSAGE, Task Manager, and SQL Enterprise Manager Current Activity.
8. Do not be afraid to experiment. The BackOffice Resource Kit has tools in it for creating test data (DataSim), creating test databases (DBGen), and simulating loading from multiple clients (SqlLS).