Tools

In this section, I'll touch on a few of the tools we'll use throughout the book to explore SQL Server. These tools come from a variety of sources, and you certainly don't need all of them to work through the book. I mention them in various places in order to point out their usefulness and to give you some tips on how to leverage them to solve a particular problem or examine a particular piece of data. That said, you can work through most of the examples in the book with little more than the tools that come with Windows and Microsoft's freely downloadable WinDbg debugger.

TList

This tool comes on the Windows Support Tools CD. You can use it to list the running processes and to list the modules loaded within each process. A variety of other process-specific information can be returned as well.

Pviewer

Pviewer also comes with the Windows Support Tools. It allows you to view information about running processes and threads. You can also kill processes and change process priority classes. One really handy aspect of it is that you can use it to view processes on your local machine as well as those across a network on a remote machine.

Pview

This tool comes with the Platform SDK and is essentially the same tool as Pviewer. It offers the same functionality and displays the same type of information. Of course, the features it sports vary based on the release of the SDK and how recent the build you have is, but it is basically the same tool as Pviewer.

Perfmon

Windows' Performance Monitor is probably the single most valuable tool included with the product for looking under the hood to see what's happening within the OS. Perfmon (or Sysmon, as it's now known) provides the ability to monitor several key statistics about running processes and threads, memory and CPU utilization, disk use, and a bevy of other interesting objects and diagnostics.

You use Perfmon by adding counters to a data collection, then allowing the tool to sample them over time as the system runs. You can view these counters as lines on a chart, as literal values, or as bars on a histogram. You can save logs created by Perfmon as binary files, as delimited text files, and as SQL Server tables. Throughout the book, I'll mention Perfmon counters that are useful in exploring a particular technology or subsystem within Windows or SQL Server.

WinDbg

As I mentioned in the Introduction, we'll frequently use Microsoft's freely downloadable standalone debugger, WinDbg, to peer under the SQL Server hood. We won't actually be using WinDbg for the normal purpose debuggers are typically used for: debugging apps. Instead, WinDbg will serve as a type of “X-ray machine” for SQL Server, a tool that lets us see what's happening behind the scenes.

A version of WinDbg comes with Windows, and you can also download it from the Microsoft public Web site (as of this writing, you can find it at http://www.microsoft.com/ddk/debugging/default.asp). For working through the examples in this book, I suggest you get WinDbg from the Microsoft Web site so that you can be sure to have the latest version.

Probably the single most important thing to remember when using WinDbg or any symbolic debugger is that, in order to successfully debug much of anything, you must have debugging symbols and the debugger's symbol path must be set so that it can find them. Debugging symbols are generated by your compiler/linker product. For Microsoft products, the standard symbol file format is the Program Database (PDB) format and is automatically produced for debug builds in Visual C++ and for any executable in VB whose Create Symbolic Debug Info project property is set. Typically, if you have debug symbols, you will find a PDB file corresponding to your EXE or DLL name in the same folder with the executable.

An exception to this is SQL Server. SQL Server's symbol files are located in the exe and dll subfolders under the main SQL Server Binn folder. (The symbols for sqlservr.exe, the main SQL Server executable, are in the exe folder; the symbols for the key DLLs it uses are in the dll folder.) These symbols are retail debug symbols—symbol files that have been stripped of many things essential to real-world debugging such as parameter types, local variables, and the like. These symbols aren't suitable for true debugging, but they're just right for our purposes. Retail symbols work fine for looking under the hood of an application and exploring a running process.

As I've mentioned, in order to successfully debug anything with WinDbg, you'll need to correctly set the symbol path. You can set the symbol path in WinDbg by pressing Ctrl+S or by choosing File | Symbol File Path from the menu system.

The retail symbols for much of Windows (and for many other products) are available over the Internet via Microsoft's symbols server. You don't have to download these—you simply point the debugger at the symbols server and it takes care of downloading (and caching) symbols files as it needs them. As of this writing, you can use http://msdl.microsoft.com/download/symbols in your symbol path to reference Microsoft's publicly available symbol server over the Internet. Microsoft has a great Web page explaining exactly how this works and how to use it at http://www.microsoft.com/ddk/debugging/symbols.asp. Read this page and set your symbol path accordingly in WinDbg. Currently, my WinDbg symbol path is set to SRV*c: empsymbols*http://msdl.microsoft.com/download/symbols.

When debugging a component or program to which you have source code, it's also important to set the WinDbg source path correctly. You can do this by pressing Ctrl+P or by selecting File | Source File Path in the WinDbg menu system. Setting the source path allows WinDbg to find the source code to your app while debugging so that you can step through it, set breakpoints, and so on.