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,
- Start SQL Server Management Studio.
- Right-click on your database instance and select “Properties”.
- Click on “Memory” table in the “Server Properties” pop-up window.
- 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;
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.
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.
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.
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
Here is a list of information that can be collected to troubleshoot SQL Server issues:
- Database server configuration (number of CPU cores, physical memory, disk space, Windows Server version, SQL Server version, SQL Server memory settings)
- Application server configuration (number of CPU cores, physical memory, disk space, Java core memory settings)
- Database index statistics
- Database fragmentation statistics
- SQL server missing index report
- Database server memory and CPU statistics when server is slow
- Operations that cause the system to be slow
- SQL server slow query report
- Database table statistics
- 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.
To learn more on the topic of requirements management, we’ve compiled a handy list of valuable resources for you!
- Debugging SQL Server Performance - October 12, 2016
- Monitoring Java Applications Running Inside Docker Containers - June 1, 2016