Thursday, June 14, 2012

Troubleshooting database slowness

Check task manager make sure SQL Server is not consuming more than 50% RAM available. If CPU usage is high, check sysprocesses table in master database for the number of connections that are using SQL Server - make sure you check the ones with abnormally high CPU time and spid > 50 and to exclude all system connections.

This query will show which processes have been holding the connections for more than 3 hours, as well as blocking queries:

SELECT   spid,
sqltext.text as [SQL], 
blocked As [Process Blocked],
         loginame as [User], 
         hostname as [Hostname], 
         program_name as [Application], 
         cpu as [CPU], 
         physical_io as [Physical IO], 
         memusage as [Memory Usage], 
         open_tran as [Open Transactions],
         last_batch as [Time Last Ran],
         DB_NAME(sysprocesses.dbid) As [Database Name]
FROM     sysprocesses  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE    spid > 50 
AND Datediff(hh,last_batch,Getdate()) > 3 
ORDER BY last_batch 

Next, check sys.dm_exec_query_stats for the top 10 longest running queries:

t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC

Also examine SQL Server logs to see if anything unusual has occurred.

No comments:

Post a Comment