By: Ben Snaidero
Overview
When monitoring general database health there isn't really an easy way to categorize the counters as we did when looking at general server health, but the following set of counters will give you good indication of the general health of your SQL Server instance.
[SQLServer:Access Methods] "Full scans/sec"
The number of unrestricted full table or index scans per second. This value should also be very close to 0. While full scans, especially index scans, are not completely unavoidable and are not always bad, if this number is high you might want to look into any tables that may be missing an index or look for poorly written queries.
[SQLServer:Access Methods] "Forwarded Records/sec"
The number of rows per second fetched from a heap through forwarded record pointers. Having many forwarded record pointers means that there were many updates to rows that no longer fit on the original page. In almost all instances, this number should be very close to 0. If your table has a clustered index and you have the fill factor set correctly you should very rarely encounter any forwarded records. If for some reason you do have a heap table that has a lot of forwarded record pointers the only options for fixing them are to rebuild the heap (must be SQL Server 2008+) or add a clustered index to the table and set the fill factor correctly.
[SQLServer:Access Methods] "Page Splits / Sec"
The number of page splits per second. Determining what is a good number of page splits depends on many factors. How many writes you have, fill factor settings, etc. In my experience, you encounter a lot of page splits when you have a workload with a lot of inserts/updates and having indexes with a fill factor that is set too high. When fixing this by setting the fill factor lower be careful not to set it too low since this will cause the data to become very spread out and use more disk space than necessary.
[SQLServer:Buffer Manager] "Buffer Cache hit ratio"
This ratio is a measure of the percentage of pages that were found in memory (SQL buffer pool) without having to be read from disk. After server startup this value will be very low as all data will have to be read from disk but over time it should level off so it should not really be measured until the server has had some time to warm up. Generally the higher the better and in order to get a better hit ratio your only real option is to increase the amount of memory available to SQL Server. Some editions of SQL Server starting with SQL Server 2014 have the buffer pool extension feature which can use SSDs to increase the size of the SQL Server buffer pool.
[SQLServer:Buffer Manager] "Page life expectancy"
The number of seconds that a page should be able to stay in memory without being referenced. As with a lot of performance counters, good values for this counter depend a lot on your workload but most best practices say this should be at least 300 seconds. Keep in mind that this measure is an instantaneous measure and not a rolling average so if you have a time of day where a lot of big report queries are run then you could expect this value to be lower given the amount of data that will have to be read.
[SQLServer:Buffer Manager] "Checkpoint Pages / Sec"
This counter shows the number of dirty pages that are moved from the SQL buffer pool to disk during a checkpoint. The number of pages that you would expect to be moved per second depends a lot on your system and its usage. If this counter is higher than normal you can use indirect checkpoints to reduce the number of pages flushed per second.
[SQLServer:Memory Manager] "Memory Grants Pending"
This is defined as the total number of SQL Server processes that are waiting for workspace memory to be granted. If you are not experiencing any memory pressure then this value should almost always be zero. Any value that is consistently above zero could be an indication that you are experiencing memory pressure and you should consider allocating more server memory to SQL Server.
[SQLServer:Memory Manager] "Total Server Memory (KB)" & "Target Server Memory (KB)"
"Total Server Memory (KB)" specifies the amount of memory the server has committed using the memory manager while "Target Server Memory (KB)" indicates the amount of memory that SQL Server can potentially consume. If after some amount of time the "Total Server Memory (KB)" is consistently lower that "Target Server Memory (KB)" then you could potentially move this SQL Server instance to a server with less memory since it doesn't actually need all the memory you have allocated to it. What you want to look out for is when these two values are equal and you have a low "Buffer Cache Hit Ratio" or the "Memory Grants Pending" is consistently above zero. In these cases you could be experiencing memory pressure.
[SQLServer:Locks] "Average Wait Time (ms)" & "Lock Waits / Sec"
"Average Wait Time (ms)" is the average amount of time waited in milliseconds for each lock request that resulted in a wait. "Lock Waits / Sec" is the number of lock requests per second that required the lock requestor to wait. "Average Wait Time (ms)" should be very low, under 50ms and "Lock Waits / Sec" should ideally be very close to 0. If either value is over these thresholds you should start looking into your queries as usually this means there are poorly written queries or tables missing indexes that result in more locking/blocking than is necessary.
[SQLServer:SQL Statistics] "Batch Requests/Sec"
This counter tells us the number of T-SQL commands that are being received by the server per second. There are no good or bad values for this counter, it is more a measure of how busy the SQL Server instance is. As we've done with other counters if you have a regular baseline value for this counter you can refer back to it to see if your server is more or less busy than normal.
[SQLServer:General Statistics] "User Connections"
This measures the number of current connections to SQL Server. As with the "Batch Requests/Sec" counter this counter is simply a good indicator for how busy your SQL Server instance is, more users usually leads to more queries which leads to more resource usage.
[SQLServer:SQL Statistics] "SQL Compilations/Sec" & "SQL Re-Compilations/Sec"
"SQL Compilations/Sec" measures the number of times per second a SQL statement was compiled. After a SQL Server instance has been running for some amount of time this value should stabilize (it will be high after any restart as the plan cache is rebuilt). "SQL-Recompilations/Sec" is the number of times per second a SQL statement had to be recompiled. These occur after a large number of inserts/updates/deletes causes the statistics to be updated or after a schema change. This value should usually be very low.
