Those of you who use SQL on a regular basis have likely faced various performance challenges while writing queries. Query performance might be slow or poorly written, subqueries may be inefficient, reads from indexes may be few or no, or CPU or memory issues might be causing the problem. How to identify and address multiple SQL performance challenges will be discussed in this article.
Bank of Definition
Before we start, let’s define a few terms:
- Wait States or Queue States: A period of waiting following the execution of queries or the loading of resources. When SQL is executing a query or pulling resources, there is some time in which both a scan of the storage and the data must be performed as well as a calculation or task. CXPACKET wait types are a typical problem associated with high server CPU usage due to poorly-written parallel queries (queries designed to run concurrently). Latch wait types, such as PAGEIOLATCH_EX refer to a wait that occurs when a task is waiting on a latch for an I/O request type of buffer, are another type of wait. As a third common wait type, there is WRITELOG, which lets the SQL session write the contents of a cached log to the storage location of that log.
- SQL offers both locking resources and locking modes. Resources locked by SQL are called lock resources, and lock modes are locks that can be placed on resources so they can be accessed by concurrent transactions and tasks. A lock can be placed on several resources, including a row in a table or a row within an index. Additionally, there are several types of lock modes, including shared locks and exclusive locks. The performance of some locks may suffer, while others may not.
- I/O on the disk and network: Transactions and data moving in and out of the disk, cache, and over the network. The more transactions, the slower the performance. You can, however, decrease the input and output on the physical and logical disks and network by fine-tuning your queries and indexing.
- Locking contention: Typically a term associated with locking. When SQL locks a table, it ensures consistency in read and write operations, but it can cause contention when it is locked. Processes can run into contention, for example, when they are updating the same page simultaneously.
- SQL server CPU usage can be explained by several factors, including SQL processes being ran, poor query execution, system tasks, and excessive compilation and recompilation. It is also possible for the CPU to be strained if there are bad indexes.
A poorly written SQL query
The same query can be formulated in many different ways to get the same results because SQL is declarative. However, these differences in writing queries can negatively impact performance. Rewriting queries can improve performance in two ways:
- Reduce query footprint and make queries faster by rewriting SQL.
- Subqueries can be removed from SQL by rewriting it.
Indexes that are not good
There is a good chance the underlying indexes are lowering overall performance if the number of write operations to a table and its indexes exceeds the number of read operations. In every write to an indexed SQL column, corresponding modifications must also be applied to the indexes of the column. It might make sense to remove or alter the indexes involved if much of the activity is write activity. By reducing the overall output of write activity, this would likely improve performance. Analyze query execution statistics with Dynamic Management Views to identify bad indexes. In order to improve performance, drop the indexes with many writes but few or no reads.
Locking down contention
When multiple processes attempt to perform lock updates concurrently on the same lock resource, locking contention can occur. Additionally, when two or more tasks are unable to resolve a deadlock because each task is holding a lock on a resource the other is attempting to lock, a deadlock can occur.
According to a server perspective, this would be the result of multiple requests from multiple users. Numerous processes can concurrently request access to the same database resources, which increases the likelihood of creating conflicts over time.
To detect contention and deadlocks, follow these tips:
- For detection, use a database information tool. As per Microsoft, the Database Engine offers monitoring tools, such as trace flags and deadlock graph events.
- Each database resource must be accessed in the same order.
- Before beginning a transaction or task, make updates to SQL.
- During tasks and transactions, the data should not be used.
- Code without cursors or limit their use.
- Don’t make large transactions.
Even if the server has plenty of memory installed, SQL servers are usually memory hogs. SQL Server automatically stores data in memory caches in order to improve performance and reduce memory usage. Data will not change once it has been read from the drive unless SQL needs to update the data. SQL Server will cache the entire database if it has enough memory to do so. The SQL language was created to compensate for things like redundant code, NULL values, too many writes, and too few reads, which all result in more memory being consumed and a lull in performance.