Best Practices

Debugging SQL Server Performance

Recently, we have worked on trouble-shooting SQL server performance issues for some of our large customers that run SQL server as their database. In this article, I will summarize what I have learned from the process by describing some steps we can take to trouble-shoot performance issue.

Check SQL Server Configuration

Make sure the database server is configured with enough resources, such as number of CPU cores, and amount of memory.

To check the server configuration, you can open “System Information” view:

Debugging SQL Server Performance

To check SQL Server memory setting,

  1. Start SQL Server Management Studio.
  2. Right-click on your database instance and select “Properties”.
  3. Click on “Memory” table in the “Server Properties” pop-up window.
  4. Check the memory settings.

Debugging SQL Server Performance

Make Sure Snapshot Mode Is On

Make sure snapshot mode is turned on for the database. In order to prevent SQL Server from locking, the snapshot mode flag should be turned on. Run the following query to check if the flag is on:


If the query returns ‘1’, then the snapshot mode is already turned on. Otherwise, run the following query to turn it on.

Check Database Indexes

Check the database indexes to make sure there are no missing indexes. Run the following query to list all all the indexes in the database (credit: tsql – List of all index & index columns in SQL Server DB – Stack Overflow):

Save the query results to a text file with tab delimited columns so they can be imported into a spreadsheet application later.

Avoid Fragmentation

Make sure the database indexes are not fragmented. The indexes in the database help to speed up database query. But when they get fragmented, the queries could run really slow. As part of the database maintenance, the indexes should be re-organized or re-built regularly.

Run the following query to check the fragmentation percentage for all indexes in the database (credit: How to Check Index Fragmentation on Indexes in a Database):

Here is a sample query result for a database with highly fragmented indexes:

Debugging SQL Server Performance

For indexes with page counts greater than 1000, the “avg_framentation_in_percent” should be kept under 10%. It doesn’t matter much for small indexes. As a rule of thumb, indexes with fragmentation percentages between 5% and 30% could be reorganized, and indexes with fragmentation percentages larger than 30% should be rebuilt.

To re-organize a single index:

To rebuild a single index:

You can also reorganize or rebuild an index using SQL Server Management Studio by right-clicking on the index you want to rebuild or reorganize.

If there are many defragmented indexes in the database, you can run the following query to rebuild all of them (credit: SQL SERVER – 2008 -2005 -Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor). Please note, the query can run for a while for a large database. It is recommended to run the query while the database is idle or offline.

Consider setting up maintenance job in SQL Server studio to run database index re-organization regularly. Though rebuilding indexes in SQL Server should probably be done offline or when the system is idle, index reorganization could be done online. Here is a good article on this topic: Rebuild or Reorganize: SQL Server Index Maintenance.

To set up an index reorganization plan in SQL Server, right-click on “Management”, then “Maintenance Plans”, and select “New Maintenance Plan” or “Maintenance Plan Wizard”. Follow the instructions to create a plan.

Debugging SQL Server Performance

Debugging SQL Server Performance

Run Missing Index Report

SQL Server provides a tool that analyzes database activities and recommends additional indexes that may help with query performance. The report could give us some ideas about why certain queries are slow.

To generate the report, run the following query after the database instance has been used for a while (credit: Don’t just blindly create those “missing” indexes!):

Read the article Finding Missing Indexes to understand the output from this query.

Monitor Database Sessions

Use Activity Monitor to monitor database sessions. Microsoft SQL Server Management Studio comes with Activity Monitor, which can be used to monitor the database sessions.

Debugging SQL Server Performance

Look for sessions that are blocked by other sessions for long time. Right-click on the session and select “Details” to show the details of the query that is associated with the process.

Debugging SQL Server Performance

Use Windows Resource Monitor

Windows Resource Monitor can be used to monitor memory and CPU usage of the SQL Server process to make sure there is enough memory in the system and the CPU is not saturated. Please note there is a known issue for SQL Server 2008 where the memory figure shown for the SQL Server process in Resource Monitor is not correct.

Debugging SQL Server Performance

Debugging SQL Server Performance

Identify Slow Queries

If we have determined that the performance is not resource-related, the next step is to identify slow queries that lead to bad performance.

The following query returns the top 100 slow queries that run for more than 300 ms (credit: Long Running Queries):

Here is a screen shot of some sample results:

Debugging SQL Server Performance

Once we have gathered a list of slow queries, we could run them directly on the database and look at the execution plans to understand those queries.

Other tools

Here is a list of other tools that can be used to troubleshoot SQL Server and Java applications:

  • SQL Server Profiler: can be used to identify slow queries. You can start it from SQL Server Management Studio
  • VisualVM: can be used to monitor Java resources usage and make thread dumps
  • Java Mission Control/Flight recording: monitor and record system events
  • JProfiler: identify slow service or database calls (development, not supported by 8.x deployment)
  • New Relic: can monitor database performance and record slow queries

Check Lists

Here is a list of information that can be collected to troubleshoot SQL Server issues:

  1. Database server configuration (number of CPU cores, physical memory, disk space, Windows Server version, SQL Server version, SQL Server memory settings)
  2. Application server configuration (number of CPU cores, physical memory, disk space, Java core memory settings)
  3. Database index statistics
  4. Database fragmentation statistics
  5. SQL server missing index report
  6. Database server memory and CPU statistics when server is slow
  7. Operations that cause the system to be slow
  8. SQL server slow query report
  9. Database table statistics
  10. A couple of thread dumps from Java applications taken during the slow operations if applicable


Debugging SQL server performance issues is not always straight-forward. But with the right tools and patience, we should be able to understand the cause of these issues. Hope the information in this article helps with that.