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...
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...
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...
Here, we are going to talk about disk and performance Impact of online Index rebuild operation. As we know index rebuild is offline operation due to which indexes become inaccessible. Here, I will describe about SQL Server rebuild index online...
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...
This article is part of SQL Server interview questions & answers series. Today, i am covering SQL Server Interview Questions & Answers on Indexes. You can have a look at the last few series of interview questions and answers on different...
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...
This is very interesting topic. How to Avoid Page Split in SQL Server? Let’s start with basics. Page split is a resource intensive operation and causes fragmentation that leads to poor performance in terms of increased I/O operations. We should...
Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly....
SQL Server Indexes are similar to book indexes that we use to search any content in that book. They are very useful in faster data retrieval or data access. We create index on columns of tables or views. An index is...
