Memory is one of the important system resource that SQL Server uses to process their transactions. Sometimes, your server might face memory bottleneck that reduces the performance of running transactions. Here, I will explain top 10 performance counters to identify...
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...
Last week, we found some slowness in one of our database server. When we analyzed the database server, we found poor disk performance where I/O requests taking longer than 15 seconds to complete. Below messages were logged multiple times in error...
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...
