1. First we need to identify if any particular SPID is getting stuck which is followed by KILL SPID. 2. If your SQL server itself Hanging... When SQL server hangs and become unresponsive we can clear the process ID related to sqlservr.exe by 'End Task' from Task Manager and restart SQL Services. 3. There are chances that due to disk array SQL Server intermittently hangs, so we have to get that replaced. 4. Due to memory allocation SQL server may respond slow, so Target Memory spike shouldn't be consistent, If yes then we need to either allocate more memory or switch to new Server. 5. Firewall - It's sometime observed that due to firewall settings all of sudden changed by itself, so application can stop responding. We need to ensure its as per requirement. 6. Network - PING for RTO can be checked anytime.
Topics
ReplyDeletePage Life Expectancy, DBCC sqlperf(Logspace) High Availability,
Clustered Index, Int, Bigint datatypes
Imp. Waittypes
DISKIO_SUSPEND
HADR_PARTNER_SYNC
HADR_TDS_LISTENER_SYNC
PWAIT_MD_UPGRADE_CONFIG
REQUEST_FOR_DEADLOCK_SEARCH
Most Hangs
ReplyDeleteThere are several types of Hangs in SQL Server.
1. First we need to identify if any particular SPID is getting stuck which is followed by KILL SPID.
2. If your SQL server itself Hanging...
When SQL server hangs and become unresponsive we can clear the process ID related to sqlservr.exe by 'End Task' from Task Manager and restart SQL Services.
3. There are chances that due to disk array SQL Server intermittently hangs, so we have to get that replaced.
4. Due to memory allocation SQL server may respond slow, so Target Memory spike shouldn't be consistent, If yes then we need to either allocate more memory or switch to new Server.
5. Firewall - It's sometime observed that due to firewall settings all of sudden changed by itself, so application can stop responding. We need to ensure its as per requirement.
6. Network - PING for RTO can be checked anytime.
Internal Wait Types
XE_PACKAGE_LOCK_BACKOFF
TEMPORAL_BACKGROUND_PROCEED_CLEANUP
Imp.
HADR_AR_CRITICAL_SECTION_ENTRY
HADR_DATABASE_VERSIONING_STATE
PREEMPTIVE_COM_STRMSEEKANDREAD
PREEMPTIVE_HADR_LEASE_MECHANISM
ASYNC_DISKPOOL_LOCK
FS_GARBAGE_COLLECTOR_SHUTDOWN
Internal Waittypes
PREEMPTIVE_OS_PDH_WMI_INIT
Related old Article Link.
https://amit-sqldbasupport.blogspot.com/2023/04/ms-sql-server-understand-hangs.html