`select OBJECT_ID(N'X')` takes "forever"

Question

I have encountered strange state of a SQL Server. In one particular database in this SQL Server select OBJECT_ID(N'X') takes "forever". How to diagnose what is going on?

EDIT: Problem disappeared. However, I am still curious what was going on.

Answer 1

OBJECT_ID() (and several other metadata functions) have to observe locks taken against objects, and wait for them to be released. I generally recommend against their use because, unless you actually want to wait for the lock to release (which is totally valid, but uncommon), you end up getting blocked. This is what I suspect happened here, though there is no way to prove it now.

I blogged about this here:

• Bad habits : Using (certain) metadata "helper" functions

The workaround is, instead of using the functions, pull the values from the catalog views. It's more code, but it will obey isolation semantics, unlike the functions, which won't. So instead of:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT OBJECT_ID(N'dbo.foo'); 
-- actually won't obey isolation level

You say:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- will obey isolation level
SELECT o.[object_id] 
  FROM sys.objects AS o
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE s.name = N'dbo'
  AND o.name = N'foo';

Again, it's more code, but you've found the price of convenience here. It's a shame schema name isn't exposed as a computed column; I wrote a tip about creating your own views to get around this and other complexities here:

• Making SQL Server metadata queries easier with these new Views