Performance Tuning with System Monitor/Perfmon in Sql Server

In this post we will discuss how to detect performance problems in sql server using Syatem monitor or perfmon .Then we will have a brief view of features and working knowledge of perfmon and then finally know the performance charts available.
Detection of performance problems: You can use System Monitor (PerfMon) or SQL Trace (SQL Server Profiler) to detect excessive compiles and recompiles. The SQL Statistics object provides counters to monitor compilation and the type of requests that are sent to an instance of SQL Server. You must monitor the number of query compilations and recompilations in conjunction with the number of batches received to find out if the compiles are contributing to high CPU use. Ideally, the ratio of SQL Recompilations/sec to Batch Requests/sec should be very low unless users are submitting ad hoc queries.
System Monitor (Perfmon) Performance Monitor is a graphical tool supplied as part of the installation of any NT/2000 Server or Workstation that lets you monitor various performance indicators. Hundreds of counters are organized within performance objects. These counters can be monitored on the local machine or over the network and can be set up to monitor any object and counter on multiple systems at once from one session.
performance handles or counters that can be looked at are summarized into following steps:
1) Understand and monitor network request characteristics as they relate to SQL Server and the machine on which SQL Server has been installed. This will mean a complete profile of what is coming into and sent back out over the network from SQL Server.
2) Understand processor utilization. It might be that the processing power is the biggest issue. You need to get a handle on this early.
3) Understand and monitor memory and cache utilization. This is the next detail step into the overall memory usage at the operating system point of view and into the memory that SQL Server is using for such things as data caching, procedure caching, and so on.
4) Understand and monitor disk system utilization. We are often rewarded for a simple disk configuration or data storage approach. We won't know We have a problem unless We look for it. Techniques that are often used include disk stripping, isolation of logs from data, and so on.
Performance Monitor Views: We can switch between
1) chart view,
2) Alert view,
3) Log view
In the next post we will see the problems from parallel query run and steps to fix the bottlenecks of performance problems