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.
Hardware Counters
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).
NOTE
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.
CAUTION
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.
This was first published in July 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation