SQL Server – System Functions and Schema Locks

Background

Early today, I was loading data and wanted to monitor how things are going but unfortunately ran into Schema Block issues ( LCK_M_SCH_S ).

sp_whoIsActive

Here is what I received upon running sp_whoIsActive

sp_whoIsActive

Explanation:

  1. Session 52
    • Insert into dbo.PaymentAccount
  2. Session 67
    • Ran a query to script out Foreign Keys
    • But, ended up blocked by Session 52
    • The wait is LCK_M_SCH_S

 

Diagnostic

Thought the problem was trying to access Object Catalog Views such as sys.foreign_keys.

Dirty Read

Original Code


set transaction isolation level read committed;
go

select top 5

		tblFK.parent_object_id

from   sys.foreign_keys tblFK

 

Revised Code


set transaction isolation level read uncommitted;
go

select top 5

		tblFK.parent_object_id

from   sys.foreign_keys tblFK

Explanation

  1. Fixed by changing the transaction isolation level to read-uncommitted

 

Functions

Functions also have problems.

Scaler Function – Object_Namme

Original Code


--Blocked 

set transaction isolation level read uncommitted;
go

select 

		parentObject
			= object_name(parent_object_id)

from   sys.foreign_keys with (nolock)

 

Revised Code



set transaction isolation level read uncommitted;
go

select top 500

	parentObject
	/*
	 object_name(tblFK.parent_object_id)
	*/
	= QuoteName(tblFKS.name)
		+ '.'
		+ QuoteName(tblFKP.name)

from   sys.foreign_keys tblFK with (nolock)

inner join sys.objects tblFKP   with (nolock)

	on tblFK.parent_object_id = tblFKP.object_id
			
inner join sys.schemas tblFKS   with (nolock)

	on tblFKP.schema_id = tblFKS.schema_id


Explanation

  1. Fixed by replacing scaler function object_name ( and object_schema_name ) with references to actual referenced tables such as sys.object and sys.schemas

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s