What is Bottleneck in SQL Server
The term bottleneck means the neck of a bottle that reduces the flow from the bottle. Similarly, SQL Server bottleneck means a reduction in the performance of SQL Server. This situation usually occurs when any shared resources like SQL database are concurrently accessed by too many people. Though bottlenecks are inevitable in every system, they should be addressed to save users from loss of time and effort.
Common Symptoms of SQL
Server Bottlenecks
-
SQL
Server hogging the processor - Longer execution times on queries
- ZExcessive
I/O
- Application log showing out-of-memory messages
- Extreme activity on the disks
- Long wait times per
I/O
Type of SQL bottleneck
There are three main types of SQL
Server bottlenecks: CPU
, I/O
, and memory
- CPU Bottleneck
This is the most common type of database bottleneck and also the easiest to find. In this case, the CPU
will be highly utilized by the SQL
Server all the time but have low overall throughput.
The obvious reason for CPU
bottlenecks is insufficient hardware resources. However, CPU
utilization can usually be reduced by configuration changes and query tuning.
Queries that can cause CPU
bottlenecks:
- Write query statements with a large number of records.
- Read query without index
- Read query that gets many large fields (text field, metadata field etc...)
- Query that has count, sum, aggregate function,...
- Transform query: Query transforms fields, creates temp fields from existing fields
- Query that has too many join statements
- I/O Bottleneck
I/O
issues can be caused by slow hardware use, bad storage solution design, and configuration. Besides hardware components, such as disk types, disk array type, and RAID
configuration that affect I/O
performance, unnecessary requests made by a database also affect I/O
traffic. Frequent index scans, inefficient queries, and out-of-date statistics can also cause I/O
workload and bottlenecks.
As the cloud becomes more and more popular, when setting up the server we need to pay attention to the disk performance, especially the disk I/O
.
You can refer to the link below for information related to disk performance from GCP
and AWS
.
- Memory Bottleneck
Memory bottlenecks are usually a result of insufficient memory resources or SQL
Server activities eating up available memory. The symptoms to look out for include longer query execution times, excessive I/O
, out-of-memory messages in the application log, and frequent system crashes.
Let's take a look at the memory's role in SQL
server:
- Cache index
- Memory for calculation, sort,...
- Contains records of each query's results
- Buffer data before writing to disk
- Buffer for connections to the DB
In InnoDB:
The InnoDB buffer pool is a memory area that holds cached InnoDB data for tables, indexes, and other auxiliary buffers
Typically, a recommended innodb_buffer_pool_size value is 50 to 75 percent of system memory
If you have a 16GB ram server + innodb_buffer_pool_size = 50%
then you will have 8GB to store the cache data. But when your cache data size exceeds 8GB, the SQL
server will write data to disk (and read this data from disk too) which may cause memory bottleneck and I/O
bottleneck.
How to deal with database bottlenecks
There are many approaches to dealing with database bottlenecks. However, the most important thing is always monitoring the system and having a mindset to optimize your database structure and queries.
- Monitoring CPU, disk I/O and memory
- By monitoring
CPU
, diskI/O
and memory we can find out the bad performing part then find the solution such as reviewing and tuning queries, adding tuning config, or adding memory. - Understand how
SQL
Server uses memory -> choose the right memory for your system. You can calculate the maximumMySQL
memory usage based on configuration settings used in your my.cnf file here.
- Well structure database design
- Poor database design can cause database bottleneck. If the database is not normalized, it will lead to redundant data or too many join statements.
- Choose the appropriate data type to save memory (for example
DATETIME
orTIMESTAMP
,INT
orUNSIGNED INT
...)
- Optimize queries and Proper Index Management
- Add appropriate index, reduce redundant index.
- Optimize queries:
- Select the required fields only to save memory, bandwidth, and
I/O
. - Restrict the use of the join statements, select distinct
- Create joins with
INNER JOIN
(notWHERE
) - Use
LIMIT
to sample query results ...etc
- Select the required fields only to save memory, bandwidth, and
Summary
Database bottleneck is a common problem, especially for systems with a large number of users.
It's very hard (or impossible) to configure a SQL
Server database that has zero bottlenecks, but you can take steps to proactively avoid bottlenecks and minimize their impact on performance before users even know there's a problem.
Reference