Database administrators don’t often get to be heroes, but if there is a time when they’re hailed as such, it’s when they can solve a problem like a slow-running query or error message for a user. It’s not that DBAs pull out their magic wands or even that they know everything about how the database works. Instead, database troubleshooting involves looking for clues through a series of questions. Like taking apart an electronic to see how it works, DBAs often figuratively “take apart” a database function or component to understand how to fix it.
Here are six questions DBAs ask end-users, application administrators and themselves when determining the root cause of a problem and subsequently fixing it.
1. Can you identify the component that’s causing the problem?
As you’re digging into the code, can you identify the script that’s causing the error or slow down? Sometimes there is a global problem, but often the issue is contained to one particular area of an application. The shipping module, or the document tracking module, or the label printing module may be slow while other areas of the application are performing normally.
The problem for the DBA then becomes identifying which executing commands relate to the particularly troublesome module. In highly active systems, it may be difficult to identify the specific piece of a complicated module that’s causing the hangup. Performance monitoring tools can come in handy here, but you may need to resort to using a trace to identify which piece of code is problematic. If you must use a trace, configure it to be as narrow as possible — gather data from just one user, for example — because a trace can come with significant impact on server performance if not used properly.
2. Is this a new feature or function, or has it recently changed?
Before digging too deeply, though, ask if the problematic process was recently updated. When we get a call about a function running slowly or timing out, our first question is always, “Is this new?” If a user ran this exact process last week without an issue, that tells us that the feature or function is probably not the problem.
However, if it’s a new query or feature in a system, or an existing one that received a recent update, it’s a good idea to have developers look at the code or examine the query for errors. If it’s a recently revised feature or function, developers can sometimes quickly provide you with a copy of the updated code so you can determine if it’s the problem. If it is, you may want to roll back to the previous version of it until you can figure out how to remediate the issue in the code.
3. What resources is the process using?
Processes require resources like disk utilization, memory, CPU, network bandwidth and more to run. Use dynamic management views like sys.dm_os_waiting_tasks to see what kind of resources are experiencing the most contention, or you can use the Query Store to identify resource consumption for the top recently executed queries. These options can help identify specific long-running processes that may be causing your issues.
4. Are any of the resources the process is using at their limit?
Once you’ve identified the resources, determine if any of those resources are at their limit — CPU, disk utilization, network bandwidth, locks, etc. The type of resource at its limit can give clues for troubleshooting. High CPU and disk utilization can be caused by improperly indexed queries or inefficient queries. High network bandwidth may mean that a query is trying to return too much data.
5. How can you resolve resource issues, if that’s where the problem lies?
If you’re at the limit of certain resources, you have a few options. If your CPU is maxed, you may want to lower the allowed number of processors that any one query can use (MAXDOP) or increase the threshold which determines when a query is allowed to use more than one processor (Cost Threshold for Parallelism). If your disks are struggling, conduct a review of recommended indexes (sys.dm_db_missing_index_xxx dynamic management views will be helpful here) to see if there are recommendations which can reduce overall required utilization (indexes also help with CPU, too, so it can be a double play). If you’ve exhausted your options, you may have to purchase more hardware, which can be very expensive.
6. What information can you gather to help avoid problems like this in the future?
When solving any problem, it’s important to collect information that you may be able to use in the future. For example, recognizing bad patterns in queries will allow you to make recommendations for the next time before they get deployed. Designing a process — or purchasing one — to track configuration options and performance historically instead of just at the time of a problem will help you identify exactly when things started to go bad and ask what may have changed or be abnormal about that time. Performing a root cause analysis and documenting it can help guide co-workers the next time something similar occurs.
Contact SQL Tailor Consulting Today
SQL Tailor Consulting can help get to the bottom of your SQL Server database issues. We have decades of experience enjoying “hero status” as problem-solvers and enjoy looking for clues by asking the right questions. Give us a call at (248) 919-8086 for a free consultation.
