Tags: Improving the performance
A leading provider of business management solutions currently hosts many of their workloads using SQL Server databases on Azure. They faced a number of SQL Server database performance issues and needed an efficient solution to resolve them. Having analyzed the customer’s system, we delivered instructions with detailed descriptions of all discovered issues and approaches to fix them. With our instructions, our customer achieved more than three times better overall query performance.
Customer problem
Our customer has been supporting .Net application connected to SQL Server database hosted on virtual machines in Azure cloud. Despite being applicable for most cases, default SQL Server configuration can not guarantee the maximal performance. Our customer wanted to take full advantage of SQL Server and Azure capabilities. So they needed strategies on how to deal with problematic code instances that reduced the overall performance.
After analyzing the customer’s system, we reviewed different metrics to deliver a deployment road map along with optimized SQL Server configurations.
Let’s overview the major discovered issues and offered strategies on how to deal with them.
Blocking
Database blocking occurs as a consequence of locking various objects in a database (usually a row of data) until the associated transactions are completed. Locking is a necessary part of any successful database transaction. To ensure the data integrity every transaction must pass the ACID test. Its isolation part is addressed to locking. The locking of objects stops all other processes from being able to change these objects. If we make a request against some locked object, that request will not be executed until the lock is removed. A certain amount of blocking is normal. However, substantial blocking leads to a great amount of waiting connections, hurting overall database performance.
We identified a number of blocked processes occurring in the customer’s database with a certain frequency. Because of that, we observed slow performance during each operational day and system hangs for dozens of seconds.
As the most effective solution for lowering the amount of blocking, we recommended reducing the locks for SQL operations using batching. A batch is a set of SQL statements submitted together and executed as a group, one after another. When you send several SQL statements to the database at once, you reduce the amount of communication overhead, thereby improving SQL Server database performance.
Moreover, partitioning of large objects can also help reduce the negative effect of blocking.
Missing and inefficient indexes
An index is a data structure (usually a B-tree) that stores column values from one table. This allows dramatically improved database performance because B-trees carry on data operations like insertions and deletions in logarithmic time.
Eventually, we prepared recommendations for keeping track of the index states. What’s more, we provided instructions on how to use SQL Server Database Engine Tuning Advisor to identify indexing issues and choose the most appropriate indexes for each user workload. We also recommended using SQL Server Dynamic Management Views to identify missing indexes and add them to improve the SQL Server database performance. Besides, we showed how to use dynamic management views to monitor the level of maintenance on the index caused by insert, update, or delete operations. Having the index usage statistics, we can consider dropping the indexes that incur maintenance overhead or with rare usage.
Problematic queries
We discovered a number of queries that utilized a SPOOL operator. In most cases, they were recursive queries built using Common Table Expressions (CTE). We modified the database schema by implementing closure tables to mitigate this problem. Closure tables store every possible relationship, greatly reducing the need for recursion. This approach appears to be much faster than recursion with CTEs. However, its downside is the explosion of data that we must store. The amount of data should not be a determinant since partitioning, cost of storage, and appropriate indexes will eliminate this downside.
Other problematic queries we observed were queries with implicit conversions. The most common cause of implicit conversion is a mismatch between the data types of columns that we process. To deal with that first we should analyze the overhead caused by conversion. If the overhead is significant, we should rewrite the queries in order to unify the data types.
Recommendations for the future
Apart from resolving the issues slowing down SQL Server database performance, we provided our customer with best practices on how to avoid problems in the future.
We specified the best configuration of the temporary database used for sorts, joins, hashes, and many other operations given the current data set. This database should be adequate enough for the activity that takes place on the server. So, the database size, as well as database placement, is essential to performance.
As an example of best practice, SQL Server should proactively alert the DBA and other key team members when an issue arises. We can achieve this by assigning SQL Server Agent alerts to operators, so that Database Mail will receive notifications about SQL Server job failures.
In addition, we provided SQL Server configuration best practices such as logging service tuning and In-Memory implementation for key tables.
Improving SQL Server database performance
Our solution allowed our customer:
- gain deep vision into their system with detailed metrics on all problematic objects;
- eliminate all the issues by themselves using our comprehensive recommendations;
- gain knowledge of SQL Server configurations to avoid problems in future;
- dramatically increase SQL Server database performance.
