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:

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.

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.

ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

---

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

SELECT
     t.name  TableName,
     col.name ColumnName,
     ind.name IndexName
FROM
     sys.indexes ind
INNER JOIN
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
     sys.tables t ON ind.object_id = t.object_id
WHERE
     (ind.is_primary_key = 0
     AND t.is_ms_shipped = 0)
ORDER BY
     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):

SELECT
     dbschemas.Jama Software AS 'Schema',
     dbtables.Jama Software AS 'Table',
     dbindexes.Jama Software AS 'Index',
     indexstats.avg_fragmentation_in_percent,
     indexstats.page_count,
     dbindexes.fill_factor
FROM
     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN
     sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN
     sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN
     sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id
WHERE
     indexstats.database_id = DB_ID()
ORDER BY
     indexstats.avg_fragmentation_in_percent DESC

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

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:

ALTER INDEX REORGANIZE

To rebuild a single index:

ALTER INDEX REBUILD WITH (ONLINE = ON)

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.

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'
PRINT @sql
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

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.

---

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

SELECT
     d.[object_id],
     s = OBJECT_SCHEMA_NAME(d.[object_id]),
     o = OBJECT_NAME(d.[object_id]),
     d.equality_columns,
     d.inequality_columns,
     d.included_columns,
     s.unique_compiles,
     s.user_seeks, s.last_user_seek,
     s.user_scans, s.last_user_scan,
     s.avg_total_user_cost,
     s.avg_user_impact
FROM
     sys.dm_db_missing_index_details AS d
INNER JOIN
     sys.dm_db_missing_index_groups AS g
ON
      d.index_handle = g.index_handle
INNER JOIN
     sys.dm_db_missing_index_group_stats AS s
ON
      g.index_group_handle = s.group_handle
WHERE
     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.

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.

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.

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

SELECT 
        st.text,
        qp.query_plan,
        qs.*
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
WHERE
      qs.max_worker_time > 300
      OR qs.max_elapsed_time > 300

Here is a screen shot of some sample results:

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

Conclusion

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.

---