Tag Archive for: SQL Server

Debugging SQL Server Performance

SQL inscription against laptop and code background. Learn sql programming language, computer courses, training.

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:

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= '<database_name>'


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


RELATED: Defining and Implementing Requirements Baselines

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):

     t.name  TableName,
     col.name ColumnName,
     ind.name IndexName
     sys.indexes ind
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
     sys.tables t ON ind.object_id = t.object_id
     (ind.is_primary_key = 0
     AND t.is_ms_shipped = 0)
     t.name, col.name, ind.name

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):

     dbschemas.Jama Software AS 'Schema',
     dbtables.Jama Software AS 'Table',
     dbindexes.Jama Software AS 'Index',
     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
     sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
     sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
     sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id
     indexstats.database_id = DB_ID()
     indexstats.avg_fragmentation_in_percent DESC

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.

SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'
PRINT @sql
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
CLOSE TableCursor

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

RELATED: Release Management Options in Jama Connect

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!):

     s = OBJECT_SCHEMA_NAME(d.[object_id]),
     o = OBJECT_NAME(d.[object_id]),
     s.user_seeks, s.last_user_seek,
     s.user_scans, s.last_user_scan,
     sys.dm_db_missing_index_details AS d
     sys.dm_db_missing_index_groups AS g
      d.index_handle = g.index_handle
     sys.dm_db_missing_index_group_stats AS s
      g.index_group_handle = s.group_handle
     d.database_id = DB_ID() AND OBJECTPROPERTY(d.[object_id], 'IsMsShipped') = 0

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):

FROM    (
    SELECT  TOP 50 *
    FROM    sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
      qs.max_worker_time > 300
      OR qs.max_elapsed_time > 300

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.

Product Development Challenges
Help us personalize your content experience!

Download this whitepaper to learn how to reduce the risk of failing to comply with regulations, and how a single source of truth enables collaboration across distributed teams, increasing insight and minimizing the introduction of risk:
Safeguarding Regulated Products Amidst Growing Complexity: A Frost & Sullivan Executive Brief

Click to download this informative whitepaper and learn how to up-level your risk management practices:
Conquering the 5 Biggest Challenges of Requirements

Click to download this informative whitepaper and learn how to enable testing earlier in the process to reduce risk:
Verify, Validate, Trace, and Test

Download this whitepaper to learn how to reduce the risk of failing to comply with regulations, and how a single source of truth enables collaboration across distributed teams, increasing insight and minimizing the introduction of risk:
Safeguarding Regulated Products Amidst Growing Complexity: A Frost & Sullivan Executive Brief

Click to watch this informative webinar and learn how to establish effective review cycles across distributed stakeholders:
How to Streamline Reviews and Collaborate with Remote Teams, Customers, and Suppliers

Click to dowload this info-packed ebook to improve collaboration and alignment across key stakeholders:
Guide to Optimizing Engineering Team Collaboration

Dowload this ebook to learn the importance of tracing requirements without the headaches and risks of a traceability matrix in Excel and set your organization up for future success:
The Jama Software Guide to Requirements Traceability

In this webinar we address how to solve some of the key challenges teams face when integrating hardware and software requirements, risks, and tests, with a document based or legacy tool approach:
Managing Product Development Complexities Across Hardware and Software Teams

Dowload this whitepaper to learn how to manage projects more effectively and efficiently using collaboration, traceability, test coverage, and change management:
Successful Product Delivery

Dowload this eBook to learn the business value of better requirements, the four fundamentals of requirements management, and finding the right level of detail in requirements:
Best Practices Guide to Requirements and Requirements Management

Dowload this eBook to gain insights to help you thoughtfully consider potential requirements and test management solutions. Plus, get tips on how to get the buy-in you need to undertake the kind of change necessary to succeed with complex product development:
Selecting the Right Requirements Management Tool: A Buyer’s Guide