The main language used to communicate with different databases is the Structured Query Language (SQL). A slow SQL application can cost your company more than money and time, but cause customer dissatisfaction and lower sales as well.
There are a myriad of reasons why your SQL application runs slow. Everything from initial coding processes to infrastructure design can keep your application from performing at its best. However, The first step to increasing your SQL speed and reliability is locating and diagnosing the problem in the system.
Find Out Why Your SQL Application Is SlowHere are five common reasons why your SQL applications keep getting slower.
1. CPU Contention
The CPU is the core of the processing system. If it is slow, then the results take longer. The answer is not as easy as “buy a faster CPU.” There are many options like core count, L1, L2, L3 cache sizes and speeds, memory lane count and throughput, NUMA specification and on and on.
What’s more, adding more cores might make the query faster, if the query runs in parallel (single query on multiple CPU cores). But if the query is small, it may run on a single CPU core so adding more cores will not help at all. So should we have a CPU with more cores, or a CPU with fewer cores and a higher clock speed? It depends. In fact, a cheaper strategy is to simply make adjustments so SQL Server has less to process on the CPU!
We can determine of CPU is the #1 constrained resource and if so, we can isolate and analyze the queries which consume the most CPU cycles. This is the highest return, lowest effort place to start. SQL Server caches the SQL query text and execution plans and aggregates the data so we can inspect this to see which queries are using up the CPU from highest CPU consumer to lowest. Once identified, we can do Database Performance Tuning to reduce CPU usage by 20%, 50% or in some cases 80%! This is much cheaper than changing hardware.
2. IO (Storage) Contention
Storage is the slowest component on a SQL Server. Even with SSDs, they are typically slower than memory and CPU. And especially when a Storage Area Network (SAN) is involved. SANs are designed to better utilize disk space, not to improve performance, unless you’re moving up to a very very expensive SAN from old hardware.
If SQL Server reports that it’s waiting on IO much of the time, then we know to tune the IO. We do this by measuring various performance metrics in Performance Monitor (perfmon) and also looking at query execution plans in cache to see which have consumed the most IO (reads or writes – depending on which of these is the constraint). Once identified, we can do Database Performance Tuning to reduce the amount of data pulled off of the storage and cached in memory by SQL Server. We also look at the memory configuration to see if the root problem is really memory and if IO is really just a major symptom and not the root cause.
Finally, we determine if the IO performance meets industry standard latency values for what is expected from the hardware underpinning the system. Is storage misconfigured? Is the wrong RAID level selected (for example, RAID 5 is slower for writes than RAID 10)? Is there a failed drive? If a SAN is involved, and is Multi Path IO (MPIO) configured correctly? Are the network paths concentrating into the same overloaded switch? And so on.
3. Memory Contention
SQL Server caches the databases in what’s called a buffer pool. A buffer pool acts as a storage space for data pages that have been recently written or read to and from disk. A small buffer pool will slow down your SQL application by overwhelming the disk’s subsystem.
We can check the size of the buffer pool by looking at the Performance Monitor (perfmon) counters and other SQL Server metrics. A bigger buffer pool will help speed up your SQL applications. In addition, there are techniques to analyze which parts of indexes or tables are consuming SQL Server’s available buffer pool and we can make adjustments so that only the ‘hot’ (frequently accessed) tables, indexes and parts of tables and indexes are cached – making it perform like SQL Server just got more memory allocated to it.
4. Network Adaptor Constraint
We’ve seen applications that pull too much data from the SQL Server into another application server (Excel exports, SSIS imports or exports, SQL Server Reporting Services (SSRS) or PowerBI reports which pull too much data (columns and also rows) than are needed to render the report.
We’ve also seen huge issues when the network is misconfigured so that user connections and SAN traffic go through the same network card(s) causing huge data queries to actually cause the storage drive letters to disconnect for a few seconds! Sometimes, this even happens at the VM level or even at the SAN level during nighttime SAN backups and other SAN-heavy activities.
The network also becomes a problem with Linked Servers. Linked Servers offer an easy way to connect one SQL Server with another so a query can be run on one server, but it can get or send data to another SQL Server as part of its processing. This is powerful and convenient, however, it is dangerous. If not used carefully, SQL Server, by default will pull ALL rows from a joined table over the linked server connection through the network card, and THEN discard the rows that it determines are irrelevant. This causes a waste of server processing time and network usage, not to mention queries taking way too long to complete, and locks being held open for much longer than they should. This can lead to excessive blocking.
We use Performance Monitor (perfmon) counters to monitor network send/receive throughput to determine if the adaptor is constrained. We also inspect SQL Server’s WaitStats to see if SQL Server is waiting on the network when processing queries.
5. Blocking Problems
Blocking occurs when one query is waiting on another query to finish. Usually SQL Server handles running many queries all at the same time on different CPU cores. However, there are cases when a query needs a lock on a row or a table, and while its processing query 1, a second query needs that same or adjacent data and needs to wait. For example, by default, a reader (SELECT) will block a writer (INSERT, UPDATE or DELETE) and a writer will block another writer and a reader.
Blocking is actually a good feature in SQL Server; it’s a fundamental way of how the server works to ensure data purity. However, excessive blocking causes unnecessary waits and delays.
We can identify when excessive blocking occurs, which queries are involved and which locks are causing the problems on what tables. Once identified, we can do Database Performance Tuning to speed up the queries which are involved in the most blocking to unjam the whole system. Think of an old manual typewriter – one jam up can cause the whole system to seem to freeze up for a while. We can identify the query or queries which are leading the blocking problems and tune it to process faster.
Database Performance Tuning
Database Performance Tuning is a complex science that is a combination of several or all of the following: Adding indexes, changing indexes, dropping indexes, reworking SQL query code, changing SQL Settings like Max Degree of Parallelism (MAXDOP) and the associated threshold, and analyzing the underlying hardware CPU model numbers and CPU classes for expected capability, Virtual Machine (VM) configuration and how the CPUs are presented to the SQL Server guest OS and much more.
Each of these changes requires careful engineering to determine which change is appropriate, how to baseline the existing system performance and query throughput and which metrics to record, making continuous adjustments and measuring the results in an iterative manner until relief is found. We’ll also write up the findings and changes for any change control processes or system change logs, as well as with development team coordination to be sure that future changes by developers do not undo the work we’ve done to improve the system performance.
Find the Right Solutions for Your SQL Problems
Many problems can slow down your SQL applications. Finding out the exact cause of the problem quickly is key to finding a lasting solution. Contact SQLWatchmen today to get professional assistance and find the right solutions for your business.
