Diagnosing performance issues in the database

Often it’s the database that is suspected when performance is surprisingly slow.

After dealing with the situation a few times and talking to a couple of DBAs, I’ve established a few approaches to diagnosing where performance issues may lie.

Scenario

Imagine that you’re working on an application that communicates to a SQL Server database with standard CRUD actions.
When a tester is testing a process that you have developed (let’s say it’s selecting out a subset of record data), they have noticed that the transaction is causing a time-out.

Approach

1. Replicate
Ensure that you can replicate the same performance issue as the tester. Ensure that you perform the action exactly the same way. This may reveal that the tester is using the code in a way that you didn’t anticipate, causing performance to suffer because you had not thought to optimize it.

2. Isolate
If you an replicate the problem, the next logical step is to workout where the actual problem lies – Is it the application, or is it the database?

3. Resolve
Once you’ve identified the source of the problem – application or database – you can then try a few typical solutions to resolve the issue.

Here are a few ideas for an issue related to the database

  • Check that the the specific columns being used in the where clause in your select has an appropriate index.
  • Check that the number of records in the table you are trying to query has not increased in a significant number. This could mean that older records need to be archived, or the query needs to be modified.
  • Check the health of the database server. If the network has slowed down, or the server is having performance problems, it is going to affect your query.

In regards to an application performance problem, it could be a large variety of things depending on the nature of the application but you can try the following:

  • Review the code that performs the particular function. Have you tested every outcome? Is there a place that resources could get hung?
  • Revisit the unit tests for the particular function that is having performance issues. Is it correctly testing the functionality?

Hopefully that will give anyone with an issue some inspiration to resolve it.

Subscribe

0 comments