Performance Counters to watch

Hardware Counters

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

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.