This is a very common predicament that most SQL developers and DBAs face in their day-to-day database encounters – regardless of the relational database platform being used. “Why is my database slow?” This could be for many reasons, with one of the hard-to-isolate reasons being slow query processing and longer wait times.
Reasons for Slow Database Performance
- Network: There could be network connection issues
- Server: The server workload on which the database is running could be high which makes database processing slower
- Database/Query: There may be redundant query lines, complex or looping syntaxes, query deadlocks, lack of proper indexing, improper partitioning of database tables, etc.
- Storage: Slow storage I/O operations, data striping issues with RAID
While network issues and server workload can be easily measured with typical network monitoring and server monitoring tools, the real complexity arises with the understanding of the following database & query-related questions:
- What query is slow?
- What is the query wait time?
- Why is the query slow?
- What was the time of the day/week of the performance impact?
- What should I do to resolve the issue?
Query response time analysis is the process of answering the above questions by monitoring and analyzing query processing time and wait time, and exploring the query syntax to understand what makes the query complex. We can break down query response time into 2 parts:
- Query processing time – which is the actual processing time for the database run the query. This includes measuring all the steps involved in the query operation, and analyzing which step is causing processing delay.
- Query wait time – which is the time of a database session spent on waiting for availability of resources such as a lock, log file or hundreds of other wait events or wait types
Response Time = Processing Time + Waiting Time
Query waiting time is determined with the help of the wait time metric called wait type or wait event. This indicates the amount of time spent while sessions wait for each database resource.
- In SQL Server, wait types represent the discrete steps in query processing, where a query waits for resources as the instance completes the request. Check out this blog to view the list of common SQL Server wait types.
- In Oracle, queries pass through hundreds of internal database processes called Oracle wait events which help understand the performance of a SQL query operations. Check out this blog to view the list of common Oracle wait events.
A multi-vendor database performance monitoring tool such as SolarWinds Database Performance Analyzer will help you monitor all your database sessions and capture query processing and wait times to be able to pinpoint bottlenecks for slow database response time. You can view detailed query analysis metrics alongside physical server and virtual server workload and performance for correlating database and server issues. There are also out-of-the-box database tuning advisors to help you fix common database issues.