We receive SQL Server error 802 when buffer pool of SQL Server instance is full and cannot grow further. Here, i will discuss about this error 802 insufficient memory available in the buffer pool in this article along with its...
Lock Pages in Memory is a Windows policy that assures the SQL Server or any other application to keep data in physical memory and not page out by windows if it is enabled for that application. This is very useful setting...
We should always monitor system resource utilization of our database servers and make sure that these utilizations should not exceed the benchmark ranges of our environment. Today, I will explain about monitoring top perfmon counters for CPU usage. If your...
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...
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...
SQL Server Performance Tuning is an important subject and it is essential to implement various measures at regular intervals of time to optimize the scalability and performance of the Server. There are multiple areas which we analyze to understand a...
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...
Disk I/O problems are some of the most difficult problems to diagnose and to debug. Here I am going to discuss about perfmon disk counters that will help us in diagnosing the disk related issues. I will segregate the values...
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...
I am working on a project where we are migrating Oracle databases to SQL Server for SAP applications. We are using SAP migration tools to migrate these databases and we are not using SSMA for Oracle in this migration. These...
SQL Server Auto Shrink is database property that allow database engine to automatically shrink database files if its value set to ON/True. Shrinking a database is not a good practice because it is very expensive operation in terms of I/O,...
We use SQL Server database Autogrowth setting to automate database file growth. This property is very helpful if you use it carefully with proper planning. But there can be a negative performance impact on your database if you don’t give attention...
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...
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...
