The following excerpt, courtesy of McGraw-Hill Osborne Media, is from Chapter 5 of the book "SQL Server 2000 for experienced DBAs" written by Brian Knight. Click for the complete book excerpt series or purchase the book.
Performance counters to watch
Most of the indicators that provide the information needed are operating system counters, especially those that deal with CPU activity, memory, paging, and the network interface. In SQL Server, you should monitor connections, transactions, and locks.
I would recommend watching the following counters regularly:
- Memory Available Bytes: Shows the available amount of physical memory on the server. An acceptable output for this may vary widely based on how much physical memory is in the machine. If you have 2GB of RAM installed on the machine, it is common to see SQL Server use 1.7GB of RAM. If no other processes are running on your SQL Server, make sure you have at least 80MB available for Windows at any given time. If you see this counter below that amount, I would recommend buying additional RAM immediately.
- Memory Pages/sec: Shows the number of pages that are read from or written to disk. This causes hard page faults, which cause SQL Server to go to page file versus memory. If this counter averages 20, you may want to add additional RAM to stop the paging.
- Network Interface Bytes total/sec: This counter shows the amount of traffic through your network interface in bytes per second. Once you do your baseline (I'll discuss this in a moment), you'll know you have a problem when this number drops or rises a huge amount.
- Paging File % Usage: Similar to the Memory Pages/sec counter, this shows the percentage of the page file that is being utilized. If you see more than 70 percent of your page file being utilized, look into more RAM for your server.
- Physical Disk % Disk Time :This counter shows how active your disk is in percentage form. If this counter sustains an average above 70 percent, you may have contention with your drive or RAM.
- Processor % Processor Time: This is one of the most important counters. It shows how active the processor is in percentage form. While the threshold to be concerned with is 85 percent on average, it may be too late if you wait that long. I generally look at either improving the performance of the queries or adding additional processors when the counter averages above 60 percent.
SQL Server counters
Here are the core counters that I watch for SQL Server:
- SQLServer: Access Methods Full Scans/sec: This shows the DBA how many full table or index scans are occurring per second. If this number is significantly higher than your baseline, the performance of your application may be slow.
- SQLServer: Buffer Manager Buffer Cache Hit Ratio: This shows the ratio of how many pages are going to memory versus disk. I like to see this number as close to 100 percent as possible, but generally 90 percent is very respectable. If you see this number as low, it may mean that SQL Server is not obtaining enough memory from the operating system.
- SQLServer: Database Application Database Transactions/sec: Shows the amount of transactions on a given database or on the entire SQL Server per second. This number is more for your baseline and to help you troubleshoot issues. For example, if you normally show 120 transactions per second as your baseline and you come to work one Monday and see your server at 5,000 transactions per second, you will want to question the activity on your server.
- SQLServer: General Statistics User Connections: Like the transactions per second, this counter is merely used for creating a baseline on a server and in the troubleshooting process. This counter shows the amount of user connections on your SQL Server. If you see this number jump by 500 percent from your baseline, you may be seeing a slowdown in your activity due to a good response from your marketing campaign.
- SQLServer: Latches Average Latch Wait Time (ms): Shows the average time for a latch to wait before the request is met. If you see this number jump high above your baseline, you may have contention for your server's resources.
- SQLServer:Locks Lock Waits/sec: Shows the number of locks per second that could not be satisfied immediately and had to wait for resources.
- SQLServer: Locks Lock Timeouts/sec: This counter shows the number of locks per second that timed out. If you see anything above 0 for this counter, your users will experience problems as their queries are not completing.
- SQLServer: Locks Number of Deadlocks/sec: This counter shows the number of deadlocks on the SQL Server per second. Again, if you see anything above 0, your users and applications will experience problems. Their queries will abort and the applications may fail.
- SQLServer: Memory Manager Total Server Memory: Shows the amount of memory that SQL Server has allocated to it. If this memory is equal to the amount of total physical memory on the machine, you could be experiencing contention since you're not leaving Windows any RAM to perform its normal operations.
- SQLServer: SQL Statistics SQL Re-Compilations/sec: This counter shows the amount of SQL recompiles per second. If this number is high, stored procedure execution plans may not be caching appropriately. Like other counters, this needs to be placed into a baseline and watched to make sure it's not moving radically from that baseline.
- SQLServer: User Settable Query: This counter is a slightly tricky one to implement and is optional. There are up to ten customizable counters that you can implement using the sp_user_counterX stored procedure (where X is a number between 1 and 10).
Data for the SQL Server counters is stored in the sysperfinfo system table in the master database. Only the first 99 databases are stored in the table.
Setting the SQLServer: User Settable Query counter can be useful for tracking very customized tasks. For example, if you have a table that holds items in a queue to be worked on (such as for a call center), you can set this counter when the count in the queue table becomes extremely high. This can trigger given alerts to either e-mail the administrator or self-correct itself. To set this counter, use the sp_user_counter1 stored procedure. You can set this in a SQL Server agent job or by manually executing a query as shown here:
DECLARE @TOTAL_ROWS int
SET @TOTAL_ROWS = (SELECT COUNT(*) FROM CALL_QUEUE)
EXEC sp_user_counter1 @TOTAL_ROWS
This query will set the User Settable 1 counter. You can set up to ten of these by incrementing the sp_user_counterX stored procedure. For example, to set the second counter, simply use the sp_user_counter2 stored procedure.
Be careful how often you update this counter. If you update it too often, you risk slowing down your server. If you want to update it regularly, make sure the query you're using is simple and that it runs fast under high utilized times. It is for that reason that this counter makes a nice supplement to any monitoring regiment but not the primary staple.
Other counters are important for specific types of troubleshooting or monitoring issues. Every DBA has a list of counter preferences. Experiment with System Monitor to come up with your own; the time you invest will pay a huge return.
Click for the next excerpt in this series: System Monitor logging.
Click for the complete book excerpt series.