The first step in diagnosing SQL Server Slowness is to determine the physical bottleneck with the most contention. Contention in one of the following areas does not always mean that subsystem is performing poorly. It could just as well be improper utilization due to poor tuning. Nevertheless, identifying the bottleneck is always [...]
Archive of posts filed under the Troubleshooting category.
Performance Counters
Object: – Memory
Counter: – Available Mbytes
Preferred Value: – > 20MB
Description: -
Reference: – KB 889654
Object: – Memory
Counter: – Free System Page Table Entries
Preferred Value: – > 7000
Description: – Free System Page Table Entries is the number of page table entries not currently in use by the system. If < 7000, consider removing /3GB.
Reference: – KB 311901
Object: [...]
Blocking Processes (lead blocker)
SELECT
spid
,status
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(char(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
Turn On Deadlock Trace Flag
DBCC TRACEON (1204, -1)
Fix – Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
This error message is due to the server setting of Remote Connection Query Timeout. The default is 600 seconds, or 10 minutes.
EXEC SP_CONFIGURE ‘remote query timeout’, 1800
reconfigure
EXEC sp_configure
EXEC SP_CONFIGURE ’show advanced options’, 1
reconfigure
EXEC sp_configure
EXEC [...]