Microsoft has been doing a great job at releasing new best practice analyzers. They have now released one for SQL Server 2008 R2. The analyzer will look at things like event logs, permissions, and SQL metadata. The BPA will provide recommendations for any warnings or problems. You can download the BPA by following the link below.
BPA For SQL 2008 R2
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591
Performance Monitor is an essential tool in a server administrators life. This tool allows us to see deep down into the operating system to see what is actually going on. I am going to cover the performance counters I use the most over the next few weeks. This by far does not mean that these are the only counters you should look at. These are just the ones I have found to be very important.
One of the biggest areas we can look at is processor utilization to figure out how our server is performing. Below are the three main counters that we will be looking at today. The definitions are taken strait out of performance monitor.
- %Processor Time – The percentage of elapsed time that the processor spends to execute a non-Idle thread. It is calculated by measuring the percentage of time that the processor spends executing the idle thread and then subtracting that value from 100%. (Each processor has an idle thread that consumes cycles when no other threads are ready to run). This counter is the primary indicator of processor activity, and displays the average percentage of busy time observed during the sample interval. It should be noted that the accounting calculation of whether the processor is idle is performed at an internal sampling interval of the system clock (10ms). On todays fast processors, % Processor Time can therefore underestimate the processor utilization as the processor may be spending a lot of time servicing threads between the system clock sampling interval. Workload based timer applications are one example of applications which are more likely to be measured inaccurately as timers are signaled just after the sample is taken.
- %User Time – The percentage of elapsed time the processor spends in the user mode. User mode is a restricted processing mode designed for applications, environment subsystems, and integral subsystems. The alternative, privileged mode, is designed for operating system components and allows direct access to hardware and all memory. The operating system switches application threads to privileged mode to access operating system services. This counter displays the average busy time as a percentage of the sample time.
- %Privileged Time – The percentage of elapsed time that the process threads spent executing code in privileged mode. When a Windows system service in called, the service will often run in privileged mode to gain access to system-private data. Such data is protected from access by threads executing in user mode. Calls to the system can be explicit or implicit, such as page faults or interrupts. Unlike some early operating systems, Windows uses process boundaries for subsystem protection in addition to the traditional protection of user and privileged modes. Some work done by Windows on behalf of the application might appear in other subsystem processes in addition to the privileged time in the process.
Now that you have the Microsoft definition of these counters lets take a quick look at whats important. The first thing you want to look at is %Processor Time for all instances. If this counter stays below sixty percent the majority of the time the system is pretty healthy. If this counter is consistently between sixty and ninety percent you are going to want to look at the server closer. When this counter consistently stays above ninety percent you should be alarmed and look into what is consuming most of the CPU.
This is where the other two counters come into play. If most of the processor is being consumed in %User Time then you know it is an application like SQL or IIS that are consuming most of the resources. If the most of the process is being consumed by %Privileged Time then you know that your bottleneck is some where else like disk IO, memory, or network. When you are having a %Privileged Time issues it takes a little bit more investigation work to look at all the OS subsystems.
Hopefully you now know what some of the basic processor counters you should be looking at and why. In the next part of the series we will be looking at network interface counters.
With the official release of Windows 7 channel 9 has released another video interview with Mark Russinovich. This interview is basically part two of an interview series. Mark did an interview several months ago that covers some additional talking points. The new video covers a wide range of topics. I guarantee you will have to watch this video a few times to catch all of the information that is presented. If you have not heard the first interview I also highly recommend it. I would also like to thank channel 9 and Mark for making the time to share this great information.
Mark Russinovich: Inside Windows 7 Redux
http://channel9.msdn.com/shows/Going%20Deep/Mark-Russinovich-Inside-Windows-7-Redux/
Mark Russinovich: Inside Windows 7
http://channel9.msdn.com/shows/Going+Deep/Mark-Russinovich-Inside-Windows-7/