Database Performance / SQL Optimization / Database Administration
Why a Powerful Server Doesn’t Always Ensure Fast Information System Performance
The performance of corporate information systems remains one of the most critical factors for their effectiveness. Companies invest in servers, storage systems, network equipment, and modern database management systems, striving to reduce the execution time of business operations. However, even a significant increase in computing resources does not always resolve issues.
For example, an ERP system that previously processed an order in one second gradually starts taking five or ten seconds for the same operation.
Administrators check the server and find no critical CPU load. The disk subsystem operates without obvious errors, and there is sufficient RAM. The cause may be related to the SQL query structure, an inefficient index, outdated statistics, or transaction blocking.
Another example is a corporate report that previously took a few minutes to generate, but after adding a new data source, it began taking over an hour.
Purchasing a more powerful server might temporarily improve the situation, but it won’t fix incorrectly constructed table joins or the unnecessary reading of millions of rows. Therefore, the optimization of corporate DBMS should begin not with hardware upgrades, but with systematic diagnostics of SQL workload, execution plans, wait events, and actual data processing volumes.
It is particularly important to be able to separate reasons related to software logic from infrastructure limitations.
How a DBMS Executes an SQL Query
SQL is a declarative language: the developer describes the result that needs to be obtained, but does not always define the specific sequence of physical operations.
For example:
SELECT
c.customer_name,
SUM(o.amount) AS total_sales
FROM Customers AS c
JOIN Orders AS o
ON o.customer_id = c.customer_id
WHERE o.order_date >= '2017-01-01'
GROUP BY c.customer_name;
The query describes sales summaries by customer. However, the DBMS must independently determine how to execute the operation.
It can use an index to search for orders by date, scan the table, or choose another access method depending on the data volume and available statistical characteristics.
Nested Loops, Hash Join, or Merge Join can be used for the join.
For aggregation, appropriate grouping and sorting mechanisms are used.
The result depends on the chosen execution plan.
Query Optimizer
The query optimizer uses metadata, statistics, and its own rules for estimating operation costs. It tries to find an efficient plan.
However, its decisions are based on estimates. If the statistics do not reflect the actual distribution of values, the optimizer may incorrectly estimate the number of rows.
For example, the system assumes that after filtering, one hundred records will remain, although in reality there are several million.
As a result, a plan might be chosen that would work well for a small dataset but proves inefficient for the actual workload.
Execution Plan
The execution plan allows to establish the physical operations used by the DBMS. For diagnostics, it is important to compare expected and actual characteristics.
If the optimizer predicted one thousand rows, but the operation actually processed ten million, it is necessary to investigate the cause of such a discrepancy.
Bottleneck Search: CPU, I/O, or Locking
A slow SQL query is not necessarily computationally complex.
Sometimes it spends most of its time waiting for external resources.
CPU-Bound Queries
If a query is CPU-intensive, the cause might be complex computations, large sorts, or inefficient join algorithms. For example, repeatedly transforming text values for millions of records can create significant CPU load.
For diagnostics, it is necessary to analyze CPU time and execution plans.
I/O-Bound Queries
If a query reads significant amounts of data from the disk system, the cause might be inefficient table access or insufficient storage system performance. For example, a report might perform a full scan of a multi-year order table instead of reading only the necessary period.
However, a high number of reads does not always indicate an error: for large analytical queries, a sequential scan might be quite optimal.
Locking and Blocking
In transactional DBMS, operations may wait for locks to be released. For example, a long-running transaction modifies a significant number of inventory records.
Other processes accessing the relevant data may be forced to wait.
In such a case, increasing processor power will not necessarily help.
It is necessary to analyze transaction durations, isolation levels, and the sequence of object access.
Wait Statistics: What the DBMS is Actually Waiting For
One of the important diagnostic tools is wait statistics. It allows identifying resource categories that prevent queries from continuing execution.
For example, SQL Server supports relevant dynamic administrative views.
For analysis, sys.dm_os_wait_stats, sys.dm_exec_requests, and other DMVs can be used.
SELECT TOP (20)
wait_type,
waiting_tasks_count,
wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;
This is a simplified diagnostic example for Microsoft SQL Server. It shows accumulated instance-level waits, not the runtime of a specific query.
Therefore, the results should not be interpreted without additional context.
Some wait types are normal background characteristics of DBMS operation.
To identify a real problem, it is advisable to compare metrics over control intervals and analyze them together with the application workload.
PAGEIOLATCH
Such waits may be related to reading data pages into memory. However, their presence does not automatically mean that disks need to be replaced.
The reason could be an SQL query that reads much more information than necessary.
LCK_M_*
This category is related to lock waits.
It is necessary to determine which transaction is holding the relevant resource and why it is taking so long.
WRITELOG
Such waits can be related to transaction log writes.
The performance of the relevant disk subsystem, transaction intensity, and the nature of their commit are important for analysis.
CXPACKET
In SQL Server 2016, CXPACKET waits can occur during parallel query execution. However, the mere fact of their presence does not mean that parallelism should be disabled.
It is necessary to investigate the plans, the distribution of work between threads, and the server parameters.
It is also important not to port later Query Store features to SQL Server 2016: detailed wait statistics at the Query Store level appear in SQL Server 2017, the general release of which is not yet available in September.
SQL Server 2016 Query Store: Query Performance History
One of the important improvements in Microsoft SQL Server 2016 was the Query Store. It allows storing information about SQL queries, their execution plans, and performance statistics over defined intervals.
This is particularly useful for analyzing regressions.
For example, yesterday the query took one second to execute, and today it takes twenty.
If the execution plan has changed, Query Store helps compare the characteristics of the previous and new plan.
Why Plans Change
A plan can change after statistics updates, index changes, significant table growth, or query recompilation.
Sometimes the optimizer chooses a plan that turns out to be worse specifically for the current data distribution.
Plan Forcing
Query Store allows you to forcibly use a previously captured plan under defined conditions. This can help quickly restore performance after a regression.
However, this mechanism should not replace the analysis of the cause.
If the data structure continues to change, a previously good plan can also become inefficient.
Historical Limitations
In SQL Server 2016, Query Store does not yet have all the capabilities of the next version. Specifically, automatic correction of plan regressions through Automatic Tuning and extended Query Store wait statistics should be considered innovations of SQL Server 2017, which Microsoft is still preparing for general availability.
Therefore, for the production environment in September, it is advisable to focus on SQL Server 2016 and its supported Query Store capabilities.
PostgreSQL 9.6: EXPLAIN ANALYZE and Query Statistics
PostgreSQL remains an important open-source RDBMS for enterprise applications.
As of September 2017, the current stable main branch is PostgreSQL 9.6.
PostgreSQL 10 has not yet been released: its official release will take place on October 5, 2017.
For SQL diagnostics in PostgreSQL, EXPLAIN is used.
EXPLAIN (ANALYZE, BUFFERS)
SELECT
customer_id,
COUNT(*) AS orders_count
FROM orders
WHERE order_date >= DATE '2017-09-01'
GROUP BY customer_id;
ANALYZE allows obtaining actual execution characteristics.
BUFFERS provides information about buffer operations.
This helps determine which operations read a significant amount of data. At the same time, it’s important to remember: EXPLAIN ANALYZE actually executes the query.
For UPDATE, DELETE, or other data modification operations, it should not be run thoughtlessly on a production database.
If necessary, testing should be performed within a controlled transaction or a separate environment.
pg_stat_statements
The pg_stat_statements extension allows accumulating statistics for SQL queries. For example, one can analyze the number of calls, total time, and processing volume.
This is useful for finding queries that create the most aggregate load.
It’s important that one very slow query is not always the main problem.
For example, a query lasting 20 ms, which is executed a hundred thousand times, can consume significantly more resources than one query lasting ten seconds.
Therefore, it is necessary to distinguish between the time of a single execution and the total cost of the load.
Indexes: read acceleration has its own price
One of the most common ways to optimize SQL is by creating indexes. An index allows for more efficient retrieval of necessary records based on certain conditions.
For example, for an orders table, an index on customer and date might be useful:
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);
However, an index should not be created just because the corresponding fields are used in an SQL query.
The order of attributes, selectivity, characteristic conditions, and table size must be considered.
Excessive Indexing
Each additional index requires disk space and updates when records are modified.
If the system has hundreds of redundant indexes, INSERT and UPDATE operations can slow down.
Therefore, optimization must consider the read/write ratio.
Covering Indexes
In certain scenarios, an index may contain attributes necessary for a query. This allows for reducing additional accesses to the main table.
However, increasing the index size also has costs.
Composite Keys
For multi-column indexes, the order of attributes is important. For example, an index (customer_id, order_date) is not necessarily equally effective for a query that filters only by date.
Therefore, solutions need to be verified through the actual execution plan.
Optimizer Statistics and Plan Changes
Modern DBMS use statistical characteristics to estimate the number of rows and the cost of operations. For example, PostgreSQL uses ANALYZE, and Microsoft SQL Server supports corresponding statistics mechanisms and their automatic updates.
However, automatic procedures do not always reflect all the specifics of the workload.
Uneven Distributions
Suppose 80% of orders belong to one region, and the rest are distributed among dozens of others.
If the statistical estimate assumes a uniform distribution, the plan may be inefficient for the largest category.
Parameter Sniffing
In SQL Server, the compilation of a parameterized query can use specific parameter values. A plan that is efficient for one customer is not always suitable for another.
For example, a small customer might have ten orders, while a large one might have several million. If both are processed by the same cached plan, performance can differ significantly.
What to Do
It is necessary to analyze distributions, statistics, and actual execution. Possible solutions include reviewing indexes, modifying SQL, using separate processing branches, or employing appropriate managed recompilation mechanisms.
However, a universal recommendation to force recompilation of all queries can increase CPU load and degrade overall performance.
Transactions and Locking
A separate optimization direction is transaction duration. For example, an application opens a transaction, modifies an order, and then waits for a response from an external service.
While the transaction remains open, the DBMS may hold locks. This creates queues for other operations.
For such scenarios, the application architecture needs to be reviewed. It is not always advisable to perform long-running network requests within a database transaction.
However, separating operations also requires integrity control. For example, if item reservation and order confirmation are performed by different services, rules for handling partial failures must be defined.
This demonstrates that SQL optimization often extends beyond the DBMS itself and requires analysis of the business process.
Practical Scenario: Slow Order Processing in ERP
Consider a hypothetical distribution company that uses SQL Server 2016. Users complain that creating an order at the end of the month takes over ten seconds.
Previously, the operation took less than two seconds.
Step 1. Operation Identification
The IT team determines which SQL queries are executed during order creation.
Step 2. Query Store Analysis
Historical plans and execution time changes are reviewed.
A query is identified that, after a statistics update, started using an inefficient plan.
Step 3. Index Analysis
It is found that the query filters records by customer and date, but the corresponding index does not provide efficient access.
Stage 4. Transaction Verification
Lockings are analyzed separately.
It is established that the mass order update process may conflict with interactive operations.
Stage 5. Correction
The team adjusts the indexing and the execution schedule of the relevant mass process.
Stage 6. Testing
Changes are verified on a characteristic data volume in a test environment.
Stage 7. Implementation
After approval, the changes are transferred to the production system.
Stage 8. Control
The actual order processing time, the number of SQL reads, and the lock status are compared.
In this example, solving the problem did not require increasing memory or processors, but rather correct SQL diagnostics and workload organization.
Data Quality and Performance Optimization
The performance of an enterprise DBMS also depends on the quality of the information model. For example, a large number of duplicate clients increases the volume of directories and complicates joins.
Uncontrolled storage of outdated records can lead to table growth without corresponding business benefit. However, automatic deletion of historical data is not a universal optimization method.
For many enterprises, there are legal requirements for storing documents and financial transactions. Therefore, it is necessary to develop rules for archiving and information lifecycle management.
Data Governance defines those responsible for these decisions. Data Quality ensures the control of accuracy, while the DBA implements the corresponding technical mechanisms.
Intecracy Group Competencies in Corporate DBMS Optimization
Identifying the root causes of low performance requires a combination of information, application, and infrastructure competencies. IQusion IT operates in the fields of system integration, electronic document management, and BPM. Relevant competencies help determine the impact of application operations and business processes on DBMS load.
Softline IT has competencies in server, network infrastructure, and storage systems. These are important for diagnosing processor, disk, and network bottlenecks.
At the same time, changes affecting business logic and data storage must be agreed upon by the responsible departments of the enterprise.
Practical Guidelines for Implementation
Optimization of corporate database performance should be based on actual measurements. This requires analyzing SQL execution plans, statistics, indexes, locks, and resource utilization characteristics.
Microsoft SQL Server 2016 Query Store, PostgreSQL EXPLAIN ANALYZE, pg_stat_statements, and other tools allow for the identification of the causes of inefficient performance. At the same time, it is important to distinguish between SQL architecture problems and physical infrastructure issues.
Not every slow execution is related to disks or insufficient memory. Often, the cause is an incorrect plan, excessive reads, flawed transactional logic, or process conflicts.
The most effective optimization does not start with purchasing a new server, but with understanding what operations the DBMS performs, what resources they consume, and why the actual response time does not meet business needs.
Author