Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning SQL Server: Tools and Processes
- Performance Tuning SQL Server: Tools Overview
- Creating a Performance Tuning Audit - Defining Components
- Creating a Performance Tuning Audit - Evaluation Part One
- Creating a Performance Tuning Audit - Evaluation Part Two
- Creating a Performance Tuning Audit - Interpretation
- Creating a Performance Tuning Audit - Developing an Action Plan
- Understanding SQL Server Query Plans
- Performance Tuning: Implementing Indexes
- Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 1
- Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 2
- Performance Monitoring Tools: Windows System Monitor
- Performance Monitoring Tools: Logging with System Monitor
- Performance Monitoring Tools: User Defined Counters
- General Transact-SQL (T-SQL) Performance Tuning, Part 1
- General Transact-SQL (T-SQL) Performance Tuning, Part 2
- General Transact-SQL (T-SQL) Performance Tuning, Part 3
- Performance Monitoring Tools: An Introduction to SQL Profiler
- Performance Tuning: Introduction to Indexes
- Performance Monitoring Tools: SQL Server 2000 Index Tuning Wizard
- Performance Monitoring Tools: SQL Server 2005 Database Tuning Advisor
- Performance Monitoring Tools: SQL Server Management Studio Reports
- Performance Monitoring Tools: SQL Server 2008 Activity Monitor
- The SQL Server 2008 Management Data Warehouse and Data Collector
- Performance Monitoring Tools: Evaluating Wait States with PowerShell and Excel
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 1
Last updated Mar 28, 2003.
SQL Server runs on the Microsoft Windows operating system, and only on the Windows operating system. That frees up the developers of SQL Server not to have to worry as much about the “big four” — the main components in a computer: CPU, Memory, Networking, and I/O or storage.
It also means that even as the data professional, you need to know a little something about the operating system. Most SQL Server professionals can not only easily navigate the Windows operating system, but they also know how to set up a Windows system, configure it, and for my purposes today, use the tools within Windows to monitor the performance of the “big four.”
Windows provides many tools that you can use to monitor the system. I’m going to focus on Windows Server 2008 and higher in this tutorial, knowing that you may not necessarily have that operating system available. As of this writing, Windows 2008 Server (and the R2 variant) is be the most widely used in a SQL Server installation, but your test system might be running Windows 7 or some other flavor of Windows. If that’s the case, I recommend that you create a test environment with that version of Windows so that you can more completely mimic your server environment, and so that you have these more advanced monitoring tools to work with.
Before I start, let me mention that I won’t be covering a practical application of these tools in this tutorial. Not to worry: I will be using these tools at length in practical examples, but you need to know the breadth of the tools you have before you can learn how to interpret their results. Rest assured that this is time well-spent. Even a quick exploration of these tools will help you as you explore the performance of your system, whether you’re creating a baseline before a problem, monitoring the system to find issues, or investigating a performance issue after a crisis.
My preferred methodology for performance tuning a Windows or SQL Server system is “outside in.” I look for resource pressure on one of the big four and drill in from there. You can use this method or the “inside out” method of looking for the processes that are waiting on the big four, a method known as “waits and queues” as well. In either case, these Windows Server tools can be useful.
The first place to start in almost any investigation is the Windows Task Manager. To get to it, press CTRL-SHIFT-ESC, or right-click in any “gray” or open area in your menu bar in Windows and select Task Manager.
This tool runs at a very low-level of the “ring buffer” in Windows, which simply means it rides at a high priority in the Operating System — this means it’s usually always available. I always start my investigations with this tool, especially in a crisis.
By default this tool will stay on top of other applications, which at first might be a little irritating if you’re using it to constantly monitor a server. I don’t recommend you use it that way. If you minimize this tool, it actually has a near-real-time graph as a system tray icon that can be quite useful.
There along the “status bar” at the bottom of the tool you’ll see three pieces of information displayed at all times: the number of processes running, the CPU use, and the amount of physical memory use.
The first tab in this tool shows you the Applications that are running on the system. These are full-up programs, which are in turn made up of Processes, which I’ll explain more about in a moment. You’ll see the status of the applications, and you can right-click them to navigate to them when they are locked and so on. By far the most common use of the right-click on this screen is to “End Task”, or kill the application. This should really be a last resort, and in fact doesn’t always work anyway. More often you have to kill the Process associated with an application, and there’s a menu item to go to that process as well, which is more useful. You can also create a “dump file” from this right-click menu, which contains the memory space associated with an application. This is used primarily for Microsoft Support, or support from an application vendor.
The Task Manager doesn’t record any of its data, but there are ways to pipe what it knows out to a file. Mostly you need PowerShell or the SysInternals utilities to do that. It’s more of a “quick look” tool.
The next tab, Processes, shows the more atomic view of what is running on the system:
A few quick “setup” notes are important here. The first is that I normally recommend you check the “Show processes from all users” box at the bottom left of the screen. On a SQL Server system, the services for the database, reporting servers and so on should be running under their own user accounts, so you’ll want to see that information as well.
Another setup task is to move to the “View” menu item and select the columns you would like to see. I normally add the I/O counters and I add the PID as well. The PID is the Process Identifier, which I need for things like the SysInternals and other commands to control a process.
The final setup piece here is to arrange the columns (left-click, hold and drag) and sort them (left-click on the column name) depending on what you want to see and how you want to see it. For instance, I click through each of the big four areas (CPU, Memory, Network and I/O) to see which processes are using which resources the most. This forms the basis for what I want to investigate further.
The next tab is Services.
I don’t use this tab very often, instead I rely on typing NET START at the command line. The graphical tool does offer you a quick way to see what is running and what is not, and there’s even a button that will take you to the Services Control Panel applet.
I do NOT recommend that you stop Windows Services here, especially the SQL Server services. Use the SQL Server Configuration Manager tool in your start menu to work with SQL Server services, as it does more than just control the service. It handles permissions, proper shutdowns and so on far better.
The next tab, Performance, is the one I use most often. This tab quickly shows you the CPU performance as a historical graph, as well as the use of Physical Memory.
Whenever I’m asked to take a look at a system in trouble, I start here and then move to the Processes tab. Again, this is a “look only” process, since you want as much information as you before you make any changes.
Spend a few minutes with this screen, and watch for “heartbeats” (as shown in that graphic) and for spikes and lulls. Knowing this information is vital to tuning your system.
There at the bottom is a button called “Resource Monitor” — ignore that for now. I’ll explain that one further in the next installment in this series.
The next tab is Networking. At first it appears to be little more than a simple display of data moving across the wire, but there is more to it than that.
Using the “View” menu option, you can set a lot more information to show there at the bottom. You can find out if you’re sending more than you’re receiving, the transfer rates, and the type of packets that are moving across your network interface. I use this feature much more now than I did in the past.
The final tab, Users, is less interesting to me than the others.
It feels less “finished” than the other tabs, doesn’t show all my SQL Server users in any way, and doesn’t have a lot of configurable columns. It can be useful for other purposes, but I would like to see things like the number of open files, the duration of connection, memory used, that sort of thing.
In the next installment I’ll cover the rest of the tools you have available, and the ways you can leverage them to monitor your server, and some pointers for how to interpret the results.
InformIT Articles and Sample Chapters
I cover more performance tools and techniques in the Performance Tuning section of the Guide.
Books and eBooks
If you’re new to Windows Server 2008, and you don’t have a great deal of interest in becoming a System Administrator, the “How to” series of books is a great resource to have. Check out Windows Server 2008 How-To.
And the definitive guide for Performance Tuning a Windows Server is here.