As a DBA of some years experience, I have been used to using Performance Monitor to check the health of the database server on either a periodic basis for trend analysis, or on a second-to-second basis for load tuning.
As stated in the many other tips and articles on the monitoring of servers, I/O is one of the key things to monitor. I regularly check both disk and network I/O to see if bottlenecks are occurring on these subsystems.
One problem is knowing if it is indeed SQL Server which is causing the I/O at the time. While the SQL Server should be a dedicated machine, there are occasionally other activities that occur on the SQL Server’s disk subsystem. An extreme example that occurred to me was a system administrator using the SQL Server’s disk subsystem to store temporary files before being moved them off somewhere else.
The Performance Monitor counter that I use is:
Object — Process
Counter — IO Data Operations / sec
Instance — sqlservr
Adjust the scale in Performance Monitor as necessary (I use 0.1), and the relationship between SQL Server I/O and what has been registered on your system becomes obvious. When there is a high degree of correlation between Physical Disk > disk transfers / sec and the counter above, then you can start looking at what is happening within SQL Server to see what can be improved. Other areas to look for are correlations between this counter and with network traffic (either bytes / sec or packets / sec. I have found the Network > Output Queue Length counter to be very unreliable on an SMP machine, consistently reporting a base value of 4294966272 on my 8-way box!).
In summary, there is no substitute for knowing your own environment, and this is just one small part of helping in this task. [6.5, 7.0, 2000] Added 4-30-2002
The above tip was contributed by Andrew Preston. Preston has been involved in the IT industry in Australia for approximately 17 years. In that time, he has worked as Systems Administrator on S/36, AS/400, Netware 2.x, 3.x, and 4.x, Wang and NT/2000 systems. Paralleling this has been DBA and DB querying expertise on DB2, Focus and SQL Server. During the last 6 years, he has worked as a consultant in the Electronic Document and Records Management field, responsible for implementing and tuning a large number of customer systems in Australia, the US and the UK based on SQL Server, Sybase and Oracle. He is currently contracting as a DBA with a Canberra-based organization. His email address is agpreston@bigpond.com.
*****
Another way to think about SQL Server performance is from the perspective of queues. As you probably know, a queue is essentially a waiting area where some function needs to be performed, but for whatever reason, it cannot be perform now and must wait in the queue until it is its turn.
From a SQL Server performance perspective, queues are bad things because they mean that some function has to wait. Ideally, we don’t want SQL Server to have to wait, we want it to perform its function immediately. A queue essentially means that performance is not as good as it should be.
So one way to look at performance tuning SQL Server is to eliminate any queues that are found on SQL Server. By definition, if we can eliminate all queues, SQL Server will be performing as fast as it possibly can.
SQL Server essentially has three key queues that can be monitored. There are queues for the CPU, the I/O subsystem, and the network. Below are some of the key Performance Monitor counters that you can use to monitor these key SQL Server queues.
- System: Processor Queue Length
- Physical Disk: Avg. Disk Queue Length
- Network Interface: Output Queue Length
Ideally, the values for these queues should be less than 2 on average over time. If not, then you are experiencing a resource-related bottleneck and need identify and resolve that problem. See more information on these specific counters on this website.
*****
Windows 2000 and Windows 2003 include a service called the Performance Logs and Alerts service. It is used by System Monitor when creating counter logs, trace logs, and alerts. By default, this service is set to “manual,” and is turned on automatically when you use System Monitor to configure counter logs, trace logs and alerts. Also by default, this service runs under the local system account.
As you may know, if you want a service on one computer to access another computer, the account used for the service must run under a domain user’s account, not a local system account. What this means is that if you want to use System Monitor on one computer to track counter logs, trace logs, or alerts on another server, that you must change the service account used by the Performance Logs and Alerts service to an appropriate domain account that has local admin rights on the computer you want to remotely monitor.
You can do this from the Services screen, where you must change the service account to a domain account. In most cases, the best account to use for the Performance Logs and Alerts service is the same domain account you use for your SQL Server service accounts.
*****
Like many DBAs, you probably often monitor your SQL Servers from your desktop using System Monitor. If you have recently upgraded from Windows 2000 Workstation to Windows XP Workstation, you may run into a problem using System Monitor to remotely monitor your SQL Server if you store log files in the .blg (binary performance log) file format. The problem is that when XP was introduced, they changed the format for .blg files.
For example, if you use Windows XP to capture a counter log trace using System Monitor from a Windows 2000-based SQL Server, and then try to open up the file to view it from a non-XP computer, you won’t be able to open it because of the file format differences. Your options are to open the file up with your XP-based desktop, or to use the relog.exe utility to convert the .blg file to the Windows 2000 .tsv or .csv format.
]]>