none
locking semantics on sys.transmission_queue

    Question

  • Are lock hints propagated to the underlying tables of system catolog views? I ask because I often query sys.transmission_queue with nolock, and I wanted to know if this was honoured through out the underlying tables.

    Secondly, is sys.transmission_queue indexed at all, providing a way to prevent table-scanning?

    Thanks

    Wednesday, February 15, 2006 6:00 PM

Answers

  • Is my understanding the lock hints should be honored for sys.transmission_queue.

    The catalog views will reveal the the structure information for sys.transmission_table. sys.transmission_table is a view over sysxmitqueue (as 'show query plan' will quickly reveal). sysxmitqueue has object_id 68 (as revealed by sys.all_objects) and sys.all_columns and sys.index_columns will reveal the table structure and cluster keys:

    select * from sys.all_columns where object_id = 68
    select * from sys.index_columns where object_id = 68

    Note that of course Microsoft reserves the right to chenge this structure at any moment, w/o notice etc etc.

    Whether a query will do a table scan or an index seek is entirely decided by the query optimizer. In general, the expected access pattern for sys.transmission_queue is to search for messages belonging to a given conversation_handle.

    HTH,
    ~ Remus

    Wednesday, February 15, 2006 11:31 PM