NT Server 4.0’s “Performance Monitor” and Windows Server “Performance” tool include the ability to generate alerts based on all of the various counters that are being monitored. Under NT Server 4.0, Performance Monitor alerts can be written to its own Alert Log window, sent to the operating system’s Event Log, sent as a network message, or it can cause a program to run. Under SQL Server 2000, Performance alerts can be written to a performance data log, sent to the operating system’s Event Log, sent as a network message, or it can cause a program to run.
For example, if you want to be notified when the Processor: % Processor Time counter exceeds 90%, you can create an alert that is fired when the event you specify is encountered. This is very similar to what can be done using SQL Server Performance Condition Alerts.
Using your operating system’s ability to generate performance counter alerts can be a useful proactive management tool for DBAs. If you haven’t explored this tool yet, you should take the time to find out how it can help you better manage your SQL Servers.
*****
While most DBAs are familiar with the Performance Monitor tool included with Windows NT Server, many are not aware of “service” version of this tool, called “monitor.exe”, which is available with the Windows NT 4.0 Resource Kit. This version of the Performance Monitor runs as a service, not as a foreground application. Because it is a service, it can be started and stopped at a command line. This provides the additional benefit of allowing the service to be automatically started and stopped by using the NT Server AT service, or similar scheduling tool. This way, you can schedule performance data to be automatically collected when you want. In addition, this service version of Performance Monitor uses less server resources that the foreground version.
*****
When performance tuning some SQL Server-based applications, it would be handy to be able to measure such things as the number of invoices entered per hour, or the number of checks written per hour, or perhaps the number of times a particular stored procedure has run. You can, and you can monitor these, or almost any type of performance indictor, using Performance Monitor. SQL Server includes a Performance Monitor Object called the User Settable Object, which is a set of ten performance monitor counters that you can customize for your own purpose. In other words, you can create up to ten of your own SQL Server Performance Monitor counters.
Creating your own counters is not hard if you know how to program stored procedures using Transact-SQL. SQL Server includes ten system stored procedures (named sp_user_counter1 through sp_user_counter10), and you can assign any integer value you want to any of the ten available counters. Generally, what you will do is encapsulate one of these special stored procedures in a stored procedure you write that is used to calculate the value you want displayed for the Performance Monitor counter.
One important thing to keep in mind when creating your own SQL Server Performance Monitor counters is that you don’t want the stored procedure you create to track your performance monitor counter to itself be a burden on SQL Server’s performance. Keep your Transact-SQL code in your stored procedure as simple as possible.
*****
As you probably know, each SQL Server connection is associated with a SPID (SQL Server Process ID). Current connection information (including the SPID) can be seen using Enterprise Manager’s Current Activity window. On rare occasions, a user connection can virtually tie up an entire server’s CPUs if something is wrong with the connection. In other cases, a long running query may cause a performance problem. Unfortunately, the Current Activity Window does not always show this is happening with individual user connections, although you know there is something wrong with the server because your CPU cycles are being used up, as can be seen using Performance Monitor and monitoring the System Object: % Total Processor Time counter. So if you know that a user connection is causing problems, but you can’t identify it using the Current Activity window, here’s a method you can use to identify which user connection is causing the problem.
Your first step is to verify that the SQL Server process (sqlservr), and not some other process, is responsible for the excessive CPU use. Remember, the “sqlservr” process is made up of many different treads, each one generally (but not always) representing a specific user connection.
The way to do this is to use the Performance Monitor and go to the Process Object: % Processor Time counter using the Chart View. When you do this, there will be many instances of running processes on your server listed in the Instance list box. What you need to do is to select all of the instances from the Instance list box except the _Total instance (which just sums the total of all the other instances). Once the instances are displayed in the Chart View, you need to change the format of the Chart from Graph to Histogram. This is done from the Options|Chart from the Performance Monitor’s main menu. Now, you should be able to easily see which process is causing the problem. (Note: if “Idle” is the highest rated process, then not much is going one with your server.) If SQL Server is the problem, then the instance with the highest % Processor Time should be “sqlservr”. If this is the case, then proceed with the rest of this tip, otherwise, you need to find out what other process on your server is causing you problems.
Assuming the “sqlservr” process is the guilty party, the next step is to identify which user connection (SPID) is causing the problem. To do this, use Performance Monitor and go to the Thread Object: % Processor Time counter using the Chart View. When you do this, there will be many instances of running threads on your server listed in the Instance list box. What you need to do is to select all of the instances that begin with “sqlservr” from the list box. Once the instances are displayed in the Chart View, you need to change the format of the Chart from Graph to Histogram. This is done from the Options|Chart from the Performance Monitor’s main menu. Now, you should be able to easily see which thread is eating up your CPU resources. While we have now identified which thread (and its Performance Monitor Instance Number) is causing the problem, we still haven’t figured out which user connection is causing the problem. Although we have identified a specific Performance Monitor Instance Number as being the culprit, there is no correlation between a Performance Monitor Instance Number and a SPID in SQL Server. If there was, our job would be much easier. Unfortunately, it will take us two more steps to make this correlation. So here goes.
Our next step is to correlate the Performance Monitor Instance Number we just identified and match it with something called a KPID (Kernel Process ID) or a ID Thread. KPID and ID Thread are exactly the same thing. A KPID or ID Thread is a system-wide identifier that uniquely identifies a thread in NT Server. To do this, use Performance Monitor and go to the Thread Object: ID Thread counter using the Report View. When you do this, there will be many instances of running threads on your server listed in the Instance list box. What you need to do is to select all of the instances that begin with “sqlservr” from the list box, similar to what you did in the previous step. The reason we are using the Report View is because what you need to do now is to find out which Thread ID (KPID) matches the Performance Monitor Instance Number (this is the number identified in the column headings in the report) you identified in the previous step. Now that we know this, we are only one step away from finding our culprit.
Our last step is to correlate the Thread ID (KPID) identified in the last step to the SPID. To so this, run the following query in Query analyzer:
use master
select spid, kpid, hostname, dbid, cmd from master..sysprocesses order by kpid
This query will display a report listing both SPIDs and KPIDs. And this is where you make the last correlation. Just match up the Thread ID (KPID) found in the last step, and then match it to the corresponding SPID, and now you know which SQL Server user connection is causing the problem.
This has been a lot of work, but doing this has helped me to resolve more than one performance-related problem.
*****
Windows 2000’s “Performance” tool includes a feature not previously available in previous versions of the operating system. It allows you to create what is called a “trace log“, which is used to collect information on a series of events relating to performance. Unlike the “counter log” which is used to sample counter data based on intervals we define, the “trace log” actually captures all of the actual events that occur over a defined period of time. This detailed information can be very useful for identifying very specific performance hardware or operating system problems on your server.
Unfortunately, while Microsoft makes it easy to collect performance trace data, they make it hard to analyze, because Windows 2000 does not include a tool to read or analyze the “trace log” data. To do this, you must get a hold of a copy of the Windows 2000 Resource Kit, which includes two rather crude tools (Tracedmp.exe and Reducer.exe) you can use to read the data. Because of this, you probably won’t be making “trace logs” often. But if you have a unusual performance problem on your Windows 2000-based SQL Server, then this tool could come in handy, albeit with a lot of extra work.
*****
Sometimes, for a wide variety of different reasons, the SQL Server Performance Monitor counters will not show up as they should. Often, but not always, this problem can be fixed by following these two steps:
- At the command prompt, type in the following: unlodctr.exe MSSQLServer
- Then type in the following: lodctr.exe <SQL Server path>binnsqlctr.ini
- Reboot the server
If your problem is that the Analysis Services counters can’t be found, then use the following:
- At the command prompt, type in the following:
unlodctr.exe MSSQLServerOLAPService
- Then type in the following: lodctr.exe <SQL Server path>binnmsmdctr.ini
- Reboot the server
If you get an unexpected error message, look it up at msdn.microsoft.com. Note, this tip is for non-clustered SQL Servers only. [2000] Updated 3-19-2004