Microsoft SQL Server performance issues can manifest in different ways: Database queries that take 30 minutes instead of a few seconds; user complaints flooding in about sluggish applications; and sites collapsing under the strain of heavy traffic. The reasons for such failings include I/O bottlenecks, the growth of an application’s user base, and shortcomings in the initial code.
A good example was the crash of several betting websites an hour before the start of the Grand National 2017. Those sites were robustly designed to handle a huge volume of last-minute bets, but all this traffic was funneled through a backend system that couldn’t deal with the load. One weak link brought everything else down.
Another example is from a client of ours where a SQL Server system had been designed many years ago to process account statements in preparation for the emailing of those statements to every customer. The system functioned very well initially, but its designers failed to envision the scale to which the user base could grow. Many years and a million customers later, it took 10 days of every month for the database to execute the statement run.
Solving the Issues
The common approach to such SQL Server perfomance issues is to add more hardware: More servers to take the load, more or better processors, a lot more memory, an all flash storage array. These are all actions that will boost performance, but if they are not supported by skilled SQL Server performance tuning, they may prove very costly.
Let’s return to the customer statement example. The client considered the addition of more hardware, but that placed undue strain on the budget. Instead, the company asked for a database health check to look into factors that might be contributing to poor performance. This uncovered problems with the initial coding that led to bottlenecks once the user base swelled beyond a certain limit. When these issues were resolved, the SQL Server database execution of the statement run dropped from ten days to ten hours.
As well as flaws in app code, further areas that can cripple database performance include index and data fragmentation, incorrectly structured databases, caching, CPU utilisation, network traffic and disk I/O. It is important to verify that SQL Server and the operating system has been installed and configured according to best practice. So if you are suffering from: slowed SQL Server database performance; applications that have gradually slowed down over time; or hardware upgrades that bought you little or improvement in query times – it may be time for a SQL Server health check. This will show you the issues that need to be addressed immediately as well as areas where improvement is desirable.
Get in touch
If you would like help solving your SQL Server performance issues, or to simply discuss with an expert the best approach to tackle the problem give us a call on 01342 410 223. As a Microsoft Gold Data Platform Partner, we can support all your SQL database needs from planning through development and integration to ongoing support.
