Getting Ready to Monitor SQL Server

I am not going to provide an exhaustive expose into the service-level management tools that ship with the OS or how to use each and every feature. Such an advanced level of analysis would take several hundred pages and is thus beyond the scope of this book. Performance monitoring is also one of the services and support infrastructures that ships with Windows Server 2003 but takes some effort to get to know and master. However, the information that follows will be sufficient to get you started.

Windows Server 2003 monitors or analyzes the bandwidth used by storage, memory, networks, and processing resources. The data analysis is not done on these actual devices. In other words, you do not monitor memory itself, or disk usage itself, such as how often a disk accesses data, but rather how your software components and functionality use these resources. In other words, monitoring does not present a useful service if you just report that 56MB of RAM were used between x time and y time. The investigations need to go further and uncover what processes used the RAM at a certain time and why so much was used.

Let’s say a system runs out of memory. Then would it not be possible that an application is stealing the RAM somewhere? In other words, the application or process or server has a bug that is leaking memory like a ship with a hold below the water line. When we refer to a memory leak, it means that a process has used the memory and has not released it after it is done processing. Software developers should watch their applications on servers and be sure they release all memory they use.

But what if you are losing memory and you do not know which application is responsible? Not too long ago, I found myself running Windows NT mail servers on the Internet supporting high-end mail applications that would simply run out of RAM. After extensive system monitoring, I found out that the leak was in the latest release of the Winsock libraries. At about the same time I discovered the leak, another company in Europe found it too. A patch was soon released by Microsoft. What transpired was that the library functions were not closing the sockets fast enough and the Winsock libraries could not cope with the traffic.

The number of software components, services, and threads of functionality in Windows Server 2003 are so numerous that it is very difficult, if not impossible, to monitor the tens of thousands of instances of storage, memory, network, or processor usage. But the tools you have on the platform are some of the richest in the server operating systems business.

To achieve such detailed and varied analysis, Windows Server 2003 comes equipped with built-in software objects that are associated with these services and applications. They are able to collect data in these critical areas. So when you collect data, you will focus your data collection on these software components. When you perform data collection, the system will collect the data from the targeted object managers in each respective monitoring location or facility.

There are two methods of data collection that are supported. First you can access Registry functions for performance data, which is a legacy method that is still used. You would use function calls in your code to call performance counter DLLs in the operating system. The second is the new way introduced on the Windows 2000 platform that supports collecting data through the Windows Management Instrumentation (WMI).

Under the WMI, the operating system installs a new technology for recovering data. These are known as managed object files (MOFs). These MOFs either correspond to or are associated with resources in a system. The objects that are the subject of performance monitoring are too numerous to list here, but they can be looked up in the OS' Performance Counters Reference. You can find the reference in the Windows Server 2003 operations guides. They also include the operating system’s base services, which include the services that report on RAM, paging file functionality, physical disk usage, and so on. You can also monitor the operating system’s advanced services-Active Directory, Active Server Pages, the FTP service, DNS, WINS, and so on.

But to understand the scope and usage of these objects, it first behooves us to understand some performance data and analysis terms.