Feel frustrated by performance? SQL performance refers to the ability of your database to quickly and efficiently perform queries. Performance tuning isn’t just one thing, but a series of practices used to improve performance. But to know which tuning tactic is best, you need to know what’s wrong and where to look.
Here are the most common areas to investigate when you’re facing performance issues, and what to do if they’re the culprit.
1.Wait statistics
SQL Server constantly plots statistics about which resources it is waiting on which correlates to the reasons why queries are not completing quickly. These are stored in a central location as aggregated statistics for the entire server. Studying these wait statistics will shed light on where queries are getting stuck (e.g. not enough memory, CPU etc.) and allow you to focus tuning efforts specifically where your system is experiencing bottlenecks.
2.Expensive queries
Like wait statistics, SQL Server also collects query execution statistics. Analysing these statistics can show you which queries are the most “expensive” on your server. The common metrics to measure by are: execution count, CPU usage, reads, and writes. These statistics are also centrally aggregated but watch do take care. A server restart will zero out these statistics and your search will have to start all over again.
3.Query Store
If you are on SQL Server 2016 or above, you are able to use the feature called Query Store. This collects the query execution metrics already mentioned and persists them on a per database level. This allows you to identify the expensive queries even after server restarts, because the metrics are saved inside your database. There are a set of built-in reports inside SQL Server Management Studio that allow you to investigate the Query Store and identify the expensive queries and perform query tuning in a more focused way. The query store will then allow you to see if your tuning work has been successful by showing the change in metrics for a given query execution history.
4. Server configuration
As the saying goes, ‘An ounce of prevention is worth more than a pound of cure.’ Ensuring your servers and databases are configured optimally can prevent performance problems from occurring in the first place. Identifying industry best practices and implementing them (with necessary changes according to your environment) can be key to making sure your system can operate at peak performance levels.
5. Application and/or infrastructure design
We regularly see systems where application and infrastructure design decisions made years ago have begun to bite us once a system has grown over time. These issues are usually not noticed in a development environment because such environments rarely have representative data quality and size that compares to the production system. “It works on my machine” is a sentence that is often mentioned in these cases and is a blocker for many performance and scaling situations.
Working with representative data is essential to being able to create applications that are ready to deal with production workloads. Similarly, these data sets will require better design decisions from the beginning to avoid performance issues during development. Catching these issues earlier is much cheaper to fix than once the application is already in production.
Need more help with your performance issues?
Knowing where to look might be half the battle, but fixing things can require skills you might not have in-house.
That’s where Data Masterminds come in.
We work with companies like yours to make sure your system is healthy, secure, and scalable. Book a call with us today.
