Studying the stats that are generated by your SQL server can give you key insights into how it is performing, where there are any issues and what kinds of maintenance and upgrades you need to carry out to make improvements. It’s fascinating that 90% of all data has been created in the last two years. And the majority of this data is stored on servers.
These are just some of the benefits of SQL server performance tuning, but unless you have experience in this area already, you might not know which statistics can have the biggest impact. Here is a quick guide to give you an idea of which stats are most deserving of your attention.
Table of Contents
Wait statistics
When working out what factors are compromising the performance of your SQL server, your first port of call should typically be the wait stats.
There are actually a lot of different types of wait stats to chew through, although at the top level you will encounter just two. Signal waits show that processes are building up as they wait to be able to take advantage of CPU capacity. Resource waits are generated when processes are held back in anticipation of other resources being freed up.
Keeping an eye on all types of wait stats makes sense, and because resource waits are varied it is all the easier to use them to orient yourself in the direction of a specific issue, whether it might be an I/O bottleneck, a memory problem or some other deficiency.
It is worth noting that while wait stats can help you unpick whether or not your SQL server is currently encumbered with a flaw of some kind, they will not necessarily tell you anything about the impact of outside elements on its performance. If you want to sidestep expensive downtime, taking a multifaceted approach to server maintenance will be your best strategy.
Buffer cache usage
Performance of your SQL server will be improved if it is able to access frequently used data from within memory rather than having to pull it from a more sluggish physical hard disk or SSD each time it is required.
You can check in on how the buffer cache is being used in order to see whether the hit ratio is in favour of speed and efficiency, namely using the data stored temporarily in memory rather than on disk. If this is not occurring with sufficient regularity, you can expand the amount of memory which is available for this process in order to address the issue.
There are a number of other useful stats of a similar kind that can be considered alongside buffer cache usage, such as page life expectancy.
Disk capacity levels
The closer your SQL server’s main storage drives get to capacity, the worse their performance will be, which is why keeping an eye on how much space is left and planning ahead to add more room rather than allowing this issue to creep up on you is essential.
Assess disk usage statistics over time, since of course there will be fluctuations in this area, and you can pinpoint rising trends and project these forwards to predict the point at which an upgrade will be necessary. Such forecasting can be a real boon and should also let you be vigilant of hardware failure.
Index analysis
Keeping tabs on the performance of your SQL server’s indexes can be a core part of any optimisation attempts. Once again there are a few statistics to consider in this context, including things like page splits which occur when an index hits its predetermined capacity and divides in two.
Along with SQL query optimisation, such close monitoring of indexes can pay dividends in the long run.
Database Usage Statistics
Last but not least is the database usage statistics. Coming as unexpected among other parameters, database usage statistics tell the server administrator vital information about which database hasn’t been accessed for a long time, so they can be deleted in order to free disk space.
