Wednesday, September 30, 2015
Thursday, June 25, 2015
Tuesday, January 27, 2015
Recently I’ve experienced a situation where one of the partition maintenance jobs created a blocking. At this point the database had high t-log usage and backup log was also executing. It was clear some kind of large transaction has occurred during a partition maintenance operation. All inserts into the table which was being partitioning, got blocked too.
Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.
So it is important to select a proper partition key so that when achieving using sliding window concept, it works without impacting users.
Tuesday, January 20, 2015
Wednesday, January 14, 2015
When querying sys.dm_exec_requests dmv, you can see the waiting requests and the resource those requests are waiting for. Sample of such values are shown in Figure 1 below.
Figure 1 – List of wait resources
How do you interpret these values? It has the following format in this case: (It is not always the same format. Depending on the values you see in wait_resource the interpretation would be different)
[database id] : [file id] : [page id]
However still the information is not sufficient because knowing the page id does not give much details related to the issue your troubleshooting. So we need to figure it out the table related to the above page. For that you need to use some undocumented DBCC commands. (Please be careful when running them in production system)
DBCC PAGE (5, 20, 56792898)
DBCC TRACEOFF (3604)
You get an output similar to below;
Figure 2 – Output of DBCC PAGE command
Refer the objectId value. That is the object Id of the page belongs to. After that it is simple to find the object name using OBEJCT_NAME() T-SQL function.
I was analyzing IO stats in one of our SQL Servers and noticed that IO latency (Read / Write) are very high. As a rule of thumb, we know tha...
I wanted build an expression to make a folder name dynamically. (Basically expressions are anyway dynamic in nature) So I simply used the ...
The SQLOS Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on...
When querying sys.dm_exec_requests dmv, you can see the waiting requests and the resource those requests are waiting for. Sample of such val...