Pls note That I get theses information from by google-ing
Performance Audit Checklist
Counter Name | Average | Minimum | Maximum |
Memory: Pages/sec | |||
Memory: Available Bytes | |||
Process: Private Bytes | |||
Processor: % Processor Time | |||
System: Processor Queue Length | |||
SQL Server Buffer: Buffer Cache Hit Ratio | |||
SQL Server General: User Connections |
Memory: Pages/sec
This counter measures the number of pages per second that are paged out of RAM to disk, or paged into RAM from disk. The more paging that occurs, the more I/O overhead your server experiences, which in turn can decrease the performance of SQL Server. Your goal is to try to keep paging to a minimum, not to eliminate it.
Assuming that SQL Server is the only major application running on your server, then this figure should ideally average between zero and 20. You will most likely see spikes much greater than 20, which is normal. They key here is keeping the average pages per second less than 20.
If your server is averaging more than 20 pages per second, one of the more likely causes of this is a memory bottleneck due to a lack of needed RAM. Generally speaking, the more RAM a server has, the less paging it has to perform.
In most cases, on a physical server dedicated to SQL Server with an adequate amount of RAM, paging will average less than 20. An adequate amount of RAM for SQL Server is a server that has a Buffer Hit Cache Ratio (described in more detail later) of 99% and higher. If you have a SQL Server that has a Buffer Hit Cache Ratio of 99% or higher for a period of 24 hours, but you are getting an average paging level of over 20 during this same time period, this may indicate that you are running other applications on the physical server other than SQL Server. If this is the case, you should ideally remove those applications, allowing SQL Server to be the only major application on the physical server.
If your SQL Server is not running any other applications, and paging exceeds 20 on average for a 24 hour period, this may mean that you have changed the SQL Server memory settings. SQL Server should be configured so that it is set to the “Dynamically configure SQL Server memory” option, and the “Maximum Memory” setting should be set to the highest level. For optimum performance, SQL Server should be allowed to take
as much RAM as it wants for its own use without having to compete for RAM with other applications.
Memory: Available Bytes
Another way to check to see if your SQL Server has enough physical RAM is to check the Memory Object: Available Bytes counter. This value should be greater than 5MB. If not, then your SQL Server needs more physical RAM. On a server dedicated to SQL Server, SQL Server attempts to maintain from 4-10MB of free physical memory. The remaining physical RAM is used by the operating system and SQL Server. When the amount of available bytes is near 5MB, or lower, most likely SQL Server is experiencing a performance hit due to lack of memory. When this happens, you either need to increase the amount of physical RAM in the server, reduce the load on the server, or change your SQL Server’s memory configuration settings appropriately.
Process: Private Bytes
Private Bytes is the current size, in bytes, of memory that this process has allocated that cannot be shared with other processes.
Processor: % Processor Time
The Processor Object: % Processor Time counter, is available for each CPU (instance), and measures the utilization of each individual CPU. This same counter is also available for all of the CPUs (total). This is the key counter to watch for CPU utilization. If the % Total Processor Time (total) counter exceeds 80% for continuous periods (over 10 minutes or so during your 24 hour monitoring period), then you may have a CPU bottleneck. If these busy periods are only occur occasionally, and you think you can live with them, that’s OK. But if they occur often, you may want to consider reducing the load on the server, getting faster CPUs, getting more CPUs, or getting CPUs that have a larger on-board L2 cache.
System: Processor Queue Length
Along with the Processor: % Processor Time counter, you will also want to monitor the Processor Queue Length counter. If it exceeds 2 per CPU for continuous periods (over 10 minutes or so during your 24 hour monitoring period), then you probably have a CPU bottleneck. For example, if you have 4 CPUs in your server, the Processor Queue Length should not exceed a total of 8 for the entire server.
If the Processor Queue Length regularly exceeds the recommended maximum, but the CPU utilization is not correspondingly as high (which is typical), then consider reducing the SQL Server “max worker threads” configuration setting. It is possible the reason that the Processor Queue Length is high is because there are an excess number of worker threads waiting to take their turn. By reducing the number of “maximum worker threads”, what you are doing is forcing thread pooling to kick in (if it hasn’t already), or to take greater advantage of thread pooling.
SQL Server Buffer: Buffer Cache Hit Ratio
This SQL Server Buffer: Buffer Cache Hit Ratio counter indicates how often SQL Server goes to the buffer, not the hard disk, to get data. In OLTP applications, this ratio should exceed 90%, and ideally be over 99%. If your buffer cache hit ratio is lower than 90%, you need to go out and buy more RAM today. If the ratio is between 90% and 99%, then you should seriously consider purchasing more RAM, as the closer you get to 99%, the faster your SQL Server will perform. In some cases, if your database is very large, you may not be able to get close to 99%, even if you put the maximum amount of RAM in your server. All you can do is add as much as you can, and then live with the consequences.
SQL Server General: User Connections
Since the number of users using SQL Server affects its performance, you may want to keep an eye on the SQL Server General Statistics Object: User Connections counter. This shows the number of user connections, not the number of users, that currently are connected to SQL Server.
If this counter exceeds 255, then you may want to boost the SQL Server configuration setting, “Maximum Worker Threads” to a figure higher than the default setting of 255. If the number of connections exceeds the number of available worker threads, then SQL Server will begin to share worker threads, which can hurt performance. The setting for “Maximum Worker Threads” should be higher than the maximum number of user connections your server ever reaches.
===========
For IIS live monitor:
The next time you see the high CPU utilization open a command prompt and run the following command:
iisapp
(If it asks you to register cscript please do so and then run the command again....)
This will show you the process ID for each application pool (w3wp.exe) running and which process is serving which application pool.
Next, look at task manager and see which process ID is the spiking the CPU. OWA could slow down even if it is the other application pool using a lot of CPU - if one process sucks down the cycles all the other processes suffer.
Once we know which application pool is causing the issue we can move forward.
Additionally, if you need to kill an application pool for whatever reason it is generally best to recycle the application pool from the IIS management console. If you don't want to go this route it is less overhead on the system to kill the offending w3wp.exe rather than restart IIS.
As far as a patch for the issue - there is no single specific cause of a w3wp.exe process using a lot of CPU, it depends entirely on what code is loaded in the worker process. This being the case there may be an issue you are encountering that there is a patch for, but unless you know what the cause of the problem is there is no way to know what fix you need.
==================================
To see if you have memory pressure, have a look at the Perfmon counter SQL Server: Buffer Manager, Page Life Expectancy. If that is averaging less than 300 seconds, or dipping towards zero at any point, then you have memory pressure. The pages/sec counter will be high if you are suffering memory pressure. You will probably need to tune queries (indexes!), or increase memory, or both.
No comments:
Post a Comment