Troubleshooting database performance can be a tricky proposition, not to mention a time-consuming one. The challenge throughout the years has been where to start, and once we’ve found the starting point, it’s a matter of working through a clearly defined process to track down the root cause.

When dealing with users reporting performance issues, a good place to start is to see which sessions are currently running on your database. From there we can see the different sessions, see if there are any locks, and more importantly see if there are any blocking locks that might be affecting performance. Once we’ve analyzed the sessions in the database monitor we can then move over to look at the activity on the database and where the activity is occurring on the database.

Here, we’re looking at a thirty-minute window of the profiling of your database. We can easily identify the performance spikes, and in those areas, there’s a good chance we’ll find the cause. In this case, we’re looking at a query that’s consuming 92% in the CPU resources. From there, we need to better understand the query as well as start to think about alternatives.

Having a tool that can generate those alternatives for us is a significant time-saver. When we find a better-performing alternative, we just may have the answer to our database performance problems.

If you’re struggling with database performance and trying to identify the cause, we invite you to take a look at DBArtisan and DB Optimizer, both products found in IDERA DB PowerStudio, and experience a new way for database administrators to identify and resolve performance issues.