I have 2 tables that are partitioned (they do NOT share the same partition scheme and function). There are 2 processes running (simplified for better understanding):
slide out partition 3 from table1
drop indexes from table1_stage
load data into table1_stage
create indexes on table1_stage
slide table1_stage into partition 3 of table1
slide out partition 3 from table2
drop indexes from table2_stage
load data into table2_stage
create indexes on table2_stage
slide table2_stage into partition 3 of table2
So all seems nice. However, when we run them in paralel, whatever runs first blocks the other! Why the heck is that? They are not touching same tables, not same indexes, not same partitioning schemes, not same partitioning functions!*&%*%!! (me being upset)
Waiting type is LCK_M_S, and resource description is:
keylock hobtid=281474978938880 dbid=9 id=lock3d049e00 mode=X associatedObjectId=281474978938880
associatedObjectId=281474978938880 DOES NOT exist in database with id=9.
Thanks in advance for your help!
This value should be an allocation unit id so search for that to get the object name.
Is there any thing in common between the 2 tables: i.e. FK TR? or a procedure that accesses both tables?
Also see this link explaining about schema locks: http://msdn.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx
Schema modification (Sch-M) locks are used when a table data definition language (DDL) operation (such as adding a column or dropping a table) is being performed.
Schema stability (Sch-S) locks are used when compiling queries. Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive (X) locks on a table. However, DDL operations cannot be performed on the table.
SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT object_name(i.object_id) AS table_name ,i.name AS index_name ,i.index_id ,i.type_desc as index_type ,partition_id ,partition_number AS pnum ,[rows] ,allocation_unit_id AS au_id ,a.type_desc AS page_type_desc ,total_pages AS pages FROM sys.indexes i INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id -- INNER JOIN sys.system_internals_allocation_units a ON p.partition_id = a.container_id -- undocumented DMV WHERE allocation_unit_id = 281474978938880 --AND i.object_id=object_id('dbo.MD_Caplet_CollarStrangle');
-- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
First of all, thanks for your reply.
Things that are in common are database, filegroup (PRIMARY), and stored procedures that do all these steps of processes 1/2. For example, same stored procedure to slide out partition, same stored procedure to drop staging table indexes, etc.. Of course, to achieve it, they had to be based on dynamic sql (not sure if it is important for this issue). Everything else, partition functions/schemes, are different.
When I ran your sql, I got this:
table_name index_name index_id index_type partition_id pnum rows au_id page_type_desc pages
sysschobjs clst 1 CLUSTERED 281474978938880 1 1287 281474978938880 IN_ROW_DATA 33
So blocking on metadata??? But what? Also, when I ran the same 2 processes this morning, NO BLOCKING!!! Scary (I don't dare to go into production with this). Any idea?
Are you sure there is no foreign key relationship between the two tables? If so, is casade delete or insert enabled? To verify the relationship for inserts and deletes, it may be necessary to SELECT from the other to make sure we are enforcing the relationship.
I would also suggest capturing the event with Profiler and include the execution plan. This may help with determining why they have a common resource that the other is waiting on.
You would definitely want to test with load before going to production.
Hope this helps.
Rob Beene, MSFT