1

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.

7
  • 2
    The likely cause is blocking. An uncommitted DDL transaction will block the function.
    – Dan Guzman
    Apr 24, 2018 at 11:18
  • Thank you for the hint. It is really strange because no one was doing any DDL transaction.
    – TN.
    Apr 24, 2018 at 11:23
  • @TN. How do you know that? Apr 24, 2018 at 15:11
  • @AaronBertrand Access to the db is restricted. (It is semi-production database.)
    – TN.
    Apr 26, 2018 at 12:22
  • That doesn't mean DDL can't be happening. Apr 26, 2018 at 13:08

1 Answer 1

5

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:

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.