Your SQL servers are running slow. What next?

Could your business survive without its data? In today’s highly digitised world, the answer is probably, no, it couldn’t.  

Yet, slow queries are one of the most common problems in a SQL Server environment. Your IT or support teams may describe this as a ‘slow response rate’ on your website or applications. This is a particularly onerous problem with 30% of site visitors expecting a page to load in one second or less, while 18% expect a page to load instantaneously. 

The situation could even snowball, causing users to get a timeout error message. With a single hour of downtime costing most businesses around $300,000, can you afford to ignore the signs of a slowing SQL Server? 

Your SQL Server environment is the backbone of your business. You need to make sure it is running at an optimal level. Otherwise, your SQL Servers cannot pull and push data as quickly and as accurately as possible – and your business’s bottom line will suffer as a result. 

To avoid these issues, you need to conduct a robust SQL Server performance analysis to identify the reasons behind your slowing systems. 

What causes a slow SQL Server? 

There are a range of reasons. It often boils down to one of these issues: missing indexes, badly designed databases, inadequate index design, a lack of I/O storage, small buffer pools, slow network and incompatible technologies. 

However, to identify the root cause of such performance issues, your database administrator (DBA) may need to look in a variety of places as they carry out a SQL Server performance analysis to identify your performance issues: 

1. Settings

First, check your hardware and installation settings – you may need to change these to optimise your servers. Also, check for any software-related errors and problematic queries.  

Memory is one of the leading hardware-based causes of poor SQL Server performance – and you need to monitor your memory usage regularly to identify any potential issues. 

As a general rule, the percentage of memory available in your SQL Server environment should be more than 20%. If the percentage of available memory drops below 20%, then your users could experience performance issues because there is insufficient memory allocation. 

Storage input/output is one of the slowest SQL Server system resources, compared to other hardware resources. 

You should monitor your storage system for performance bottlenecks. If it has any, investigate whether you can optimise the configuration and design of your storage system. This could help you improve your performance and achieve scalability. 

There are a variety of reasons for CPU performance bottlenecks. You may need to optimise your query plan, improve your database design or design application, improve your SQL Server configuration or boost your hardware resources, for example. Your DBA may be able to advise you on the best option. 

Indexes are vital to improve your SQL Server performance and must be reviewed regularly. 

If you have too many indexes or frequently update your tables, you could incur additional overheads because your SQL Server needs to perform extra work to keep these indexes up to date.  

Check your clustered and non-clustered indexes or, more precisely, their fragmentation.  

Unmaintained indexes are also one of the main causes for performance problems. So, check that your index maintenance plans are carried out regularly.  

What antivirus and antispyware are running on your SQL environment? Hopefully, none.  

Running antivirus on your production SQL Servers can cause problems in production and make some files unavailable when you need them. They could also overload your CPU. 

If there is a specific business case for running antivirus and antispyware on your SQL Server environment, try to manage it and your servers to minimise interference. 

Where is your SQL Server installation stored? You should make sure you have separate, dedicated physical drives for your data and log files to optimise your performance. 

If they are stored on the same physical disk, this can slow your response rates because the writes on the disk occur at the same time. Also, make sure there is plenty of space to store your log files. 

Who has access to what in your SQL Server environment? If an unexperienced individual makes changes to your SQL Servers, they could cause its performance to slow.  Worst-case scenario, they could bring down your entire online operation. 

You should assess who has permissions to manage your SQL Servers. You don’t want everyone snooping around and accessing your corporate data, for obvious reasons.  

Authentication is another area you may want to simplify. You could minimise access to your system admin account (SA) and, where possible, use the Windows Authentication tool to connect to your SQL Servers and web applications. 

In conclusion 

If you are still experiencing problems, your DBA may need to carry out further analysis. However, you may not be able to diagnose and fix all of the reasons behind your slowing SQL Servers. 

This is because diagnosing performance issues is a complex undertaking and one that requires a certain level of expertise. Fixing any identified issues is an even more specialist task - and one that could have repercussions across your business if you get it wrong.  

Our expert team is here to help you get it right. We can conduct a thorough and cost-effective SQL Server performance analysis to fix your SQL Servers as quickly as possible. Get in touch here to book this in with our expert team.