What do you do to check if your SQL server is meeting its performance expectations? Are you one of those database administrators who wait until there are complaints from users for them to see what could be wrong?
Well, if you are, then you do not know that things can go wrong and bring your entire company down. One of the best practices when it comes to database management is making sure that you constantly monitor its performance.
You can do this by keeping a list of SQL Server counters that play a vital role in monitoring your SQL Server. Counters provide you with a way of measuring performance in real-time and over time.
Here is a guide to some of the most common SQL Server counters, what they do, and how you can use them;
SQL Server: Buffer Manager: Page life expectancy
The page life expectancy counter is used to measure the amount of time a page spends in the buffer cache. This measure is given in seconds. If a page stays for a long time in the memory, then it means that the server does not have to resolve queries by reading the same page from the disk.
You need to run this counter over time for you to get the baseline for your SQL server. If you find it below 300 seconds, which is five minutes, then you need to get additional memory for your database environment.
This is because your applications might be extremely slow, something that might drive away your customers. This can hurt small businesses, especially startups, which should be doing everything they can to optimize their digital operations.
SQL Server: Buffer Manager: Buffer cache hit ratio
The buffer cache hit ratio counter is used to show the number of times an SQL Server finds a data page in the buffer cache whenever there is a query that has requested the data page.
You need to make sure that you get the highest number possible when you run this counter. This is because getting a high number means that your server can provide queries with data from its memory without having to read from the disk.
The baseline for this number is about one hundred. If you get 100, then this means that the server gets every page it needs in memory. If you have a low number, then you need to get additional memory for your server.
Validate your concepts for performance during the design stage with AI-generated attention analytics
SQL Server: SQL Statistics: Batch Requests/Sec
This is one of the most common SQL Server performance counters and is used to show the total number of batches received by the server each second. If you want to measure the activity your SQL Server processes over time, then this is the SQL counter you need to use.
If your server is processing a high number of batches every second, then it means that it is busy. Unfortunately, we do not have a universally accepted number that can be used to indicate that a server is very busy.
This is because if you compare today’s servers with those that were used a few years ago, you will notice a lot of differences especially when it comes to the power that they come with. Modern machines can process a larger number of batches compared to older ones.
That notwithstanding, reviewing this counter over time can give you a baseline number that you can use for your server.
SQL Server: SQL Statistics: SQL Compilations/Sec
If you would like to know how many times your server compiles an execution plan every second, then this is the counter to use. This is vital because of the number of resources used when the server is compiling an execution plan.
You can compare compilations per second with the number of batch requests you get every second if you want to find out if any complications are hurting the performance of your database environment.
This can be achieved by dividing the total number of batch requests by the number of compiles you get every second. This gives you a ratio that you can use to identify how many batches your server executes in every compilation.
SQL Server: General Statistics: User Connections
The user connections counter is used to determine the total number of users connected to the server at the point of taking the sample. Just like some of the counters in this list, this counter does not have a baseline number.
It depends on how busy your server is. Watching this counter over time can give you a rough idea of the number of users that connect to your server. After that, get the low and high usage statistics during optimal usage.
If your counter ever exceeds the low or high marks recorded, then you might have a bottleneck causing problems to your database environment. Database bottlenecks make it impossible for servers to handle their normal loads.
Other common SQL Server performance counters include SQLServer: SQL Statistics: Batch Requests/Sec, SQLServer: Locks: Lock Waits/Sec: _Total, SQLServer: General Statistic: Processes Block, SQLServer: Access Methods: Page Splits / Sec, and SQLServer: Buffer Manager: Checkpoint Pages/Sec.
Depending on your requirements, use these counters to monitor the performance of your SQL server to make sure that it meets your expectations as well as those of your users.
