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
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
