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

[See an index of all bad habits / best practices posts]

For a long time, I was a big proponent of all the metadata helper functions. These are the little one-liners, like OBJECT_ID(), that let you derive properties of an object, usually based on another property. While very handy in many cases, they can be problematic in others - especially if you are trying to use NOLOCK against the catalog views / DMVs. Let's say you want to be able to see tables that have been created, even if the transaction hasn't yet been committed:

BEGIN TRANSACTION;
CREATE TABLE dbo.foo(id INT);

In another window, run this, which works fine:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT name, [object_id] FROM sys.objects WHERE name = N'foo';

However, try this instead:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT OBJECT_ID(N'dbo.foo'); -- blocked

You can see why; in the window where the table was created, run the following query:

SELECT request_mode
  FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.foo');

You'll see that the session_id that started the transaction has a Sch-M lock on the table. And while the read uncommitted query against sys.objects does not require any locking (even if you wrap it in a transaction), the call that goes through OBJECT_ID() actually requests a Sch-S lock that immediately goes into the WAIT state. And hence the block that will persist until the original transaction is either committed or rolled back. Which reminds me: don't forget to roll back that transaction you started above.

Adam Machanic filed a Connect item for this issue way back in 2009, but it was closed as "Won't Fix":

• Connect #432497 : Metadata functions should follow same isolation semantics as metadata queries

So, something to keep in mind, if you are writing diagnostic queries of any kind, and you want to use read uncommitted to minimize both the impact of your queries on, and the impact on them by, other activity on the system. It is highly unlikely these types of queries will actually cause problems, unless you're performing very aggressive queries against things like sys.dm_db_index_physical_stats(), but you can certainly be a victim in a lot of scenarios.

Another issue that may come up is the behavior when permissions block a user from accessing the metadata. Consider a case where a user executes a stored procedure that in turn calls another procedure. Sometimes you may want to have home-grown auditing set up, without worrying about giving every single user explicit access to the chain of things that auditing needs to access. However it may not work out as you expect:

USE [master];
GO
CREATE LOGIN peon WITH PASSWORD = N'peon', CHECK_POLICY = OFF;
GO
USE some_database;
GO
CREATE USER peon FOR LOGIN peon;
GO
CREATE PROCEDURE dbo.NestedProcedure
AS
BEGIN
  SET NOCOUNT ON;
 
  SELECT helper = OBJECT_SCHEMA_NAME(@@PROCID);
 
  SELECT [catalog] = s.name
    FROM sys.schemas AS s
    INNER JOIN sys.objects AS o
    ON s.[schema_id] = o.[schema_id]
    WHERE o.[object_id] = @@PROCID;
END
GO
CREATE PROCEDURE dbo.WrapperProcedure
AS
BEGIN
  SET NOCOUNT ON;
  EXEC dbo.NestedProcedure;
END
GO
GRANT EXECUTE ON dbo.WrapperProcedure TO peon;
GO
EXECUTE AS USER = N'peon';
EXEC dbo.WrapperProcedure;
REVERT;

In this case, the call to OBJECT_NAME() yields NULL, while the call to sys.objects yields an empty result set:

Results from nested procedure

In both cases the name has been shielded from the user (yay security!), but it is important to understand the difference in functionality, depending on what you are doing with the output, how many joins are involved with the queries, etc. A workaround in this case would be to pass the @@PROCID to a utility stored procedure that executes as a higher-privileged user, and perform the name resolution there - certainly a simpler solution than granting blanket metadata permissions to all users.

Another case that came up recently from Kris Gruttemeyer is when investigating data from DMVs which cross database boundaries. He had a query like this:

SELECT [SP Name] = OBJECT_NAME(t.objectid),
   [Number of Executions] = SUM(s.execution_Count)
FROM sys.dm_exec_procedure_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
WHERE OBJECT_NAME(t.objectid) = N'MySPName'
GROUP BY OBJECT_NAME(t.objectid);

This returned stored procedures that he was sure weren't being called, and validated via trace. The reason? He was running the stats query from the right databases, but had procedures in other databases that *happened* to have been assigned the same object_id. While many people assume object_id for user objects will be unique across an instance, on my system, I was able to reproduce this scenario quite easily:

Same object_id, different names

Then I executed only the stored procedure in db1:

EXEC db1.dbo.db1_foo;
GO 100

Now, I imagined that I was actually looking for executions of the other procedure, db2.dbo.db2_bar. When I ran his query above, and just changed the filter on the procedure name:

USE db2;
GO
SELECT [SP Name] = OBJECT_NAME(t.objectid),
   [Number of Executions] = SUM(s.execution_Count)
FROM sys.dm_exec_procedure_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
WHERE OBJECT_NAME(t.objectid) = N'db2_bar'
GROUP BY OBJECT_NAME(t.objectid);

One would expect an empty set, but you actually get evidence of the executions of the other stored procedure, in the other database, that just happens to have the same object_id:

This is not the result you were looking for

Joining to the catalog views on objectid/object_id would actually still cause the same problem, as it would still yield the local object name, but the local procedure might actually have never been executed:

SELECT [SP Name] = p.name,
   [Number of Executions] = SUM(s.execution_Count)
FROM sys.dm_exec_procedure_stats AS s  
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
INNER JOIN db2.sys.procedures AS p
   ON p.[object_id] = t.objectid
WHERE p.name = N'db2_bar'
GROUP BY p.name;

Results are still showing the executions from that other procedure that "borrowed" our object_id:

Still not the result you were looking for

The proper way to do this, IMHO, would be to use the catalog views instead of the helper functions *and* add the following clause to filter to the database we care about:

AND t.[dbid] = DB_ID(N'db2')

You could just say DB_ID() but, by specifying the database name explicitly, you no longer have to care about the current database context. I've also been known to be a stickler about schema prefix. So the query becomes:

SELECT [SP Name] = p.name,
   [Number of Executions] = SUM(ps.execution_count)
FROM sys.dm_exec_procedure_stats AS ps  
CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) AS t
INNER JOIN db2.sys.procedures AS p
   ON p.[object_id] = t.objectid
INNER JOIN db2.sys.schemas AS s
   ON p.[schema_id] = s.[schema_id]
WHERE p.name = N'db2_bar'
  AND s.name = N'dbo'
  AND t.[dbid] = DB_ID(N'db2')
GROUP BY p.name;