CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jason Roell
Jason Roell

Reputation: 6819

How Can I Troubleshoot Performance Issues in SQL Server

I have a relatively large database (millions of records per table) that has many tables and indexes that have been set up by a previous developer and I am trying to find a good way to troubleshoot some of our performance issues.

Our application makes many database calls throughout its life-cycle and I am trying to find a good way to diagnose some of the slower queries, or some instances where we are missing indexes OR have indexes on tables that we write to more that we read from.

What are some quick queries that will provide me with some insight into what I am looking for?

Upvotes: 2

Views: 1027

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

You should use SQL Server Profiler to track what querys are arriving to your server.

Then use Database Engine Tuning Advisor to test each query.

Upvotes: 3

Jason Roell
Jason Roell

Reputation: 6819

Finding these can be a real pain if you are not familiar with SQL Server’s system tables that are tracking all this information for you. However, hopefully by exploring some of the queries that I have listed below (for the most common problems), you will find some insight into your particular issue.

What it does: Lists the top statements by average input/output usage for the current database

SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.[text],qs.statement_start_offset/2, 
               (CASE 
                              WHEN qs.statement_end_offset = -1 
                THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
                              ELSE qs.statement_end_offset 
                END - qs.statement_start_offset)/2) AS [Query Text]         
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.[dbid] = DB_ID()
ORDER BY [Avg IO] DESC OPTION (RECOMPILE);

Notes for how to use: Helps you find the most expensive statements for I/O by SP

What it does: Possible Bad NC Indexes (writes > reads)

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

Notes for how to use: — Look for indexes with high numbers of writes and zero or very low numbers of reads — Consider your complete workload — Investigate further before dropping an index

What it does: Missing Indexes current database by Index Advantage

SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], 
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID() -- Remove this to see for entire instance
ORDER BY index_advantage DESC OPTION (RECOMPILE);

Notes on how to use: — Look at last user seek time, number of user seeks to help determine source and importance — SQL Server is overly eager to add included columns, so beware — Do not just blindly add indexes that show up from this query!!!

What it does: Get fragmentation info for all indexes above a certain size in the current database — Note: This could take some time on a very large database

SELECT DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name], 
i.name AS [Index Name], ps.index_id, index_type_desc,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats( NULL,NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i 
ON ps.[object_id] = i.[object_id] 
AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
--AND page_count > 500
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);

Notes on how to use: — Helps determine whether you have fragmentation in your relational indexes — and how effective your index maintenance strategy is

These should get you started!

I wrote a longer blog post about this topic here for those interested.

Upvotes: 5

Related Questions