june 12, 2026
Why a database runs slowly: main causes and how to identify them
Database (DB) performance is the foundation upon which the operations of a modern business are built. In the era of digitalisation, any delay in data processing instantly translates into financial losses. DB performance degradation is an «eternal» topic, as it is the data storage layer where the performance of most applications hits a bottleneck. When a database «slows down», everything suffers: from the loyalty of external clients to the operational efficiency of internal employees.
In this article, we will examine a systemic approach to troubleshooting, based on the experience of experts from the Lasmart company, and look at how to transform a one-off complex audit into a regular automated process.
The cost of a «slow» database: symptoms and consequences
Before proceeding with the «treatment», it is necessary to record the symptoms. Slow DB performance manifests itself not only in user complaints but also in specific technical metrics:
- Increased latency: web interface pages take longer to load, reports take excruciatingly long to generate, and system responsiveness drops.
- Timeout errors: the database stops responding within the allocated time interval, leading to broken sessions and lost transactions in the backend.
- Instability and crashes: the database performs intermittently — periods of normal operation alternate with sudden «freezes».
- Anomalous resource consumption: consistently high central processing unit (CPU) utilisation at 90% and above, without any apparent reason such as an increase in incoming traffic volumes.
The business consequences of this degradation are critical: a drop in revenue due to the inability to place an order, deterioration of the customer experience, and direct downtime for highly paid specialists, such as analysts, who are forced to wait 30 minutes for the simplest queries to complete instead of working.
Systemic audit: four levels of diagnostics
To resolve issues effectively, Lasmart experts suggest dividing the audit into four interconnected levels. This allows for the systematic elimination of bottlenecks — from the «hardware» right down to specific lines of code.
Level 1. Hardware and infrastructure
A common misconception is that purchasing a more powerful server will solve any problem. However, without configuring the software layer, this often leads merely to pointless spending on expensive licences, which in the corporate segment (e.g., MS SQL Server Enterprise) are tied to the number of CPU cores.
- Central processing unit (CPU): the load on the CPU increases either due to a natural growth in business processes or, more frequently, due to inefficient query execution plans. Before adding cores, it is essential to ensure that the current ones are not being wasted on sub-optimal data sorting in memory.
- Random-access memory (RAM): a lack of memory is signaled by a low cache hit ratio or a queue for memory allocation (the Memory Grants Pending metric). If the database lacks RAM, it begins to actively use the disk, which is orders of magnitude slower.
- Disk subsystem: the use of SSDs is standard today. Yet even with them, load balancing is critical: situations where 2 out of 10 disks are 80% overloaded while the rest sit idle lead to delays across the entire system.
- Isolation and virtualisation: a database is an extremely resource-hungry entity. It should not compete for resources with other applications on the same physical server. In virtual environments, «overcommit» (resource oversubscription) should be avoided, where more virtual cores are allocated than physically exist on the host.
Level 2. DBMS configuration
Often, a database is configured with average, conservative parameters «out of the box». Fine-tuning the configuration can provide a performance boost without any investment in hardware.
- Parallelism settings (MaxDop): this parameter determines how many cores a single query can use. For analytical workloads (OLAP), it is recommended to set a value between 1/4 and 1/2 of the available cores. Setting the value to 1 (completely disabling parallelism) is often merely a way to mask the problems of inefficient code.
- Cost Threshold for Parallelism: this parameter defines the «cost» of a query above which the server will begin to parallelise it. The default value of 5 is far too low in modern realities, forcing the server to split even tiny queries and waste resources on thread management. It is recommended to start from 50.
- Transaction parameters: enabling the Read Committed Snapshot Isolation (RCSI) mode allows read queries to proceed without waiting for write transactions to complete. This is critical for systems experiencing simultaneous heavy data insertion and report generation.
- TempDB: this is the server’s «scratchpad». If the TempDB system database is misconfigured (too few files or located on a slow disk), it will become a bottleneck for the entire DBMS.
Level 3. Data modelling and indexes
This is the level where opportunities for exponential acceleration are most frequently «buried».
- Indexes as a tool and a burden: indexes speed up searches but slow down data insertion and updates. During audits, Lasmart often identifies hundreds of unused or duplicate indexes that do nothing but waste space and resources.
- Fragmentation: during intensive write operations, data within indexes becomes «shuffled». If fragmentation exceeds 30%, a rebuild is required; otherwise, the server will have to read far more data pages than necessary.
- Partitioning: for tables with volumes of 50–100 million rows and above, partitioning becomes mandatory. Splitting data (e.g., by month) allows the server to scan only the required «partition», and old data can be deleted instantly using the Truncate Partition command instead of a heavy Delete operation.
- Statistics: this is data about data distribution, based on which the server builds a query execution plan. Outdated statistics are the most common cause of sudden slowdowns, when the server abruptly decides to use a slow scanning method instead of a fast index seek.
Level 4. SQL query optimisation
Even on a powerful server with flawless indexes, bad code can «bring down» the system. The main anti-patterns include:
- Redundancy (SELECT* ): reading all columns instead of only the required ones increases the load on the network, memory, and disk cache.
- Non-SARGable predicates: using functions inside a WHERE clause (e.g., WHERE YEAR(date) = 2023) makes it impossible to use an index on the date field. The server is forced to scan the entire table.
- Abuse of DISTINCT: it is often used to «treat» symptoms — when a query produces duplicates due to incorrect JOINs. However, the DISTINCT operation is extremely resource-intensive as it requires a full sorting of the result set.
- Correlated subqueries: queries that execute once for every single row of the main query can turn execution into an endless process on large volumes of data.
From a one-off audit to automation with Lasmart
A traditional manual audit is an expensive and labour-intensive procedure. An expert spends weeks collecting logs, analysing execution plans, and checking hundreds of parameters. By the time such an audit is complete, the situation within the database may have already changed. The Lasmart company has developed the «Database Monitoring» solution, which shifts this expertise into an automated mode:
- Continuous metrics collection: the system automatically analyses DBMS configurations, infrastructure status, and modelling parameters.
- Intelligent diagnostics: the product identifies critical queries consuming maximum CPU or RAM resources, detects unused indexes, and flags misconfigured parallelism settings.
- Automated recommendations: instead of dry figures, the user receives a «patient card». Problems are highlighted using a traffic-light system: red zones require immediate intervention, yellow zones demand attention, and green zones confirm that the settings are correct.
- Maintenance management: the module automates index defragmentation and statistics updates, selecting the optimal time with the lowest load on the system.
Automation results
Deploying a specialised monitoring and audit solution allows you to:
- Reduce labour costs for conducting audits by 80%.
- Accelerate routine procedures (such as updating data warehouses) by 1.5 to 2 times.
- Decrease the number of incidents related to database crashes or slowdowns by 75%.
- Free up to 50% of hardware capacity by eliminating inefficient processes.
Conclusion
A slow database is almost always the consequence of accumulated errors at various levels: from virtualisation settings to the order of columns in an index. A systemic approach allows you not merely to «put out fires» but to build a reliable data architecture.
Automating the audit process with tools similar to Lasmart’s developments transforms the struggle for performance from a heroic feat into a routine, controlled, and predictable process, ensuring business stability and a comfortable working environment for employees.
