ALTER PARTITION FUNCTION Causes Blocking?

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. 

MSDN states:

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.

ALTER PARTITION FUNCTION (Transact-SQL)

So it is important to select a proper partition key so that when achieving using sliding window concept, it works without impacting users.

Cheers.

Registry Check Pointing in a Windows Cluster to Bring SQL Server Online

Check this out!

Registry Check Pointing in a Windows Cluster to Bring SQL Server Online

How To Find Object Name For “wait_resource”

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 TRACEON (3604)
GO
DBCC PAGE (5, 20, 56792898)
GO
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.

Cheers.