What Are the Effects of Slow Queries?
Slow queries can have serious negative impacts on your business. The impacts will vary depending on your architecture, industry, or application, but the bottom line is to not ignore them. Let's discuss whom and what they will impact.Slow Queries Affect Users
Quick response time is the key to making end users happy. In many cases, what users end up seeing on a webpage comes straight from the database with minimal hops in between. That means application response time is primarily just database response time and query latency. Having slow queries means your users are more likely to see spinners or loading screens instead of the information they've requested. For more on application response times and user satisfaction, check out the Apdex metric.Slow Queries Affect Other Parts of a System
For most web architectures, like the three-tier architecture, the database is often at the bottom of the stack. It's the foundation. When there's high latency at the foundation of a system, it bubbles up to higher levels and affects other components of a system.
Database latency escalates to APIs and applications, and eventually end users.
Slow Queries Can Lead to Inefficient Use of Resources
Slow queries can mean your database does more work than it needs to, which means it's using more resources than it needs to. When limited resources like CPU or I/O run out, everything can start to slow down. Inefficient use of resources is also a problem when you're not using the resources you have. For example, if your database has a locking problem, you could have slow queries without saturating any of your resources.Why Are Queries Slow?
Now that we have an idea of the consequences of slow queries, let's think about why queries are slow in the first place. Let's focus on the three biggest reasons:- Queries may have a lot of work to do
- Queries may not have resources to do work quickly
- Queries may be waiting
Queries May Have a Lot of Work to Do
Query execution involves doing work like reading data from storage, performing computation, and organizing data together in different ways. There are lots of factors involved! For example, if your data set can fit comfortably in memory, then reading data from storage will never be a significant part of the equation. Other factors like grouping, filtering, joining tables together, and sorting introduce their own complexities in terms of how much work is done. In general, the more data you have, the more work queries generally have to do to give you the results you want. A full table scan means a query has to read all of the rows in a table. If there are billions of rows, this will take a while no matter what. The “Use The Index, Luke” database performance guide has a great section on data volume and query performance.Queries May Not Have Resources to Do Work Quickly
Even if your query doesn’t have to do a lot of work, its execution isn’t isolated from the rest of the work being done in a system. If your resources like disk I/O, network throughput, or CPU are at capacity, then your queries can take more time to run.Queries May Be Waiting
Maybe your queries do relatively little work, aren’t starved for resources, and are still slow because they’re waiting. Often, queries are waiting on other queries because locks at the data level (row locks) or higher level activity like flushing data out to disk or performing maintenance on a global cache.How to Find and Improve Slow Queries
The query profiler in Database Performance Monitor (DPM) can show you top queries consuming a large portion of your database’s execution time without using indexes. In many cases, adding an index reduces the amount of data the server needs to examine to run the query and improves execution speed.
DPM also performs SQL analysis to let you know if a query could use improvement based on best practices.
DPM also captures explain plans automatically, so you can immediately see the execution plan of a slow query, and see how it changes over time or across hosts.
The charts page has plenty of organized key system performance metrics to let you know when a system resource is hitting its limits. Note that you can set a threshold alert on any metric, so you can receive notifications before a disaster happens.
