Today I am going to discuss SQL Server memory issues that your database Instance might face during bulk data load or during any memory intensive operations. You may also face insufficient memory issue when you try to execute numerous queries...
I was getting wait type PREEMPTIVE_OS_WRITEFILEGATHER during database size allocation. Yesterday, I was configuring database size to the value that was suggested by the vendor. We need to set database size to 700GB keeping 150 GB to log files. I...
DBCC SQLPERF is very useful command that we use to get multiple statistics related information in SQL Server. This command provides transaction log space statistics and wait statistics information at instance level and we also use it to reset wait and...
Should we clear or reset Performance Wait Statistics Data present in DMV sys.dm_os_wait_stats in SQL Server? The answer to this question is depends on case to case. Ideally, you should not clear your wait statistics data frequently. Best way is Do Not clear wait...
Normally, ASYNC_IO_COMPLETION Wait type can be seen during backup and restore activities. Whenever you will see this wait type your backup/restore process will be in suspended state most of the time because the process is waiting to get IO resource...
Today I was working on an issue in which backup job was showing in executing state since last 2 days.Initially my observation was either it was hung in middle of its process or it was processing very slow. Later, i...
RESOURCE_SEMAPHORE waits occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts. High waits on RESOURCE_SEMAPHORE usually result...
Wait Types are awesome way to troubleshoot performance related issues. In SQL Server 2005 and later version we can get wait type info from various DMVs. One of the most useful DMV is sys.dm_qs_wait_stats.This DMV gives you the detail report...
