SQL Server 2008 running schema query without identifiable cause

Question

I am analyzing the performance on login to an application and in particular making sure the database response is acceptable. In running just the simple Activity Monitor I can see that there are two expensive schema queries being run, an example is below.

Could someone please point me to why this is being run, in some situations this is taking over 10ms to complete?

SELECT sp.major_id AS id, 
so.name AS ObjectName, 
[theDatabase].sys.schemas.name AS ObjectOwner,  
so.type AS ObjectType, 
sp.minor_id AS smallid,  
sp.name AS PropertyName, 
sp.value AS PropertyValue,
CAST(sp.value AS varbinary(MAX)) AS PropertyValueBinary,
sql_variant_property(sp.value,'BaseType') AS PropertyValueBaseType,
sql_variant_property(sp.value,'Precision') AS PropertyValuePrecision,
sql_variant_property(sp.value,'Scale') AS PropertyValueScale,
sql_variant_property(sp.value,'MaxLength') AS PropertyValueMaxLength,
CASE sp.class WHEN 4 THEN USER_NAME(sp.major_id) END AS UserName,
CASE sp.class WHEN 6 THEN (SELECT TOP 1 name FROM [theDatabase].sys.types sst WHERE sst.user_type_id=sp.major_id) END AS UDTName,
CASE sp.class WHEN 6 THEN (SELECT TOP 1 [theDatabase].sys.schemas.name FROM [theDatabase].sys.schemas INNER JOIN [theDatabase].sys.types ON [theDatabase].sys.types.schema_id=[theDatabase].sys.schemas.schema_id WHERE [theDatabase].sys.types.user_type_id=sp.major_id) END AS UDTOwner,
CASE sp.class
    WHEN  3 THEN (SELECT TOP 1 ss2.name as a FROM [theDatabase].sys.schemas ss2 WITH (NOLOCK) WHERE ss2.schema_id = sp.major_id)
    WHEN  5 THEN (SELECT TOP 1 sa2.name as a FROM [theDatabase].sys.assemblies sa2 WITH (NOLOCK) WHERE sa2.assembly_id = sp.major_id)
    WHEN 10 THEN (SELECT TOP 1 sxsc2.name as a FROM [theDatabase].sys.xml_schema_collections sxsc2 WITH (NOLOCK) WHERE sxsc2.xml_collection_id = sp.major_id) COLLATE database_default
    WHEN 15 THEN (SELECT TOP 1 smt2.name FROM [theDatabase].sys.service_message_types smt2 WITH (NOLOCK) WHERE smt2.message_type_id = sp.major_id)
    WHEN 16 THEN (SELECT TOP 1 ssc2.name FROM [theDatabase].sys.service_contracts ssc2 WITH (NOLOCK) WHERE ssc2.service_contract_id = sp.major_id)
    WHEN 17 THEN (SELECT TOP 1 ss2.name FROM [theDatabase].sys.services ss2 WITH (NOLOCK) WHERE ss2.service_id = sp.major_id)
    WHEN 18 THEN (SELECT TOP 1 srs2.name FROM [theDatabase].sys.remote_service_bindings srs2 WITH (NOLOCK) WHERE srs2.remote_service_binding_id = sp.major_id)
    WHEN 19 THEN (SELECT TOP 1 sr2.name FROM [theDatabase].sys.routes sr2 WITH (NOLOCK) WHERE sr2.route_id = sp.major_id)
    WHEN 20 THEN (SELECT TOP 1 sps2.name FROM [theDatabase].sys.partition_schemes sps2 WITH (NOLOCK) WHERE sps2.data_space_id = sp.major_id)
    WHEN 21 THEN (SELECT TOP 1 spf2.name FROM [theDatabase].sys.partition_functions spf2 WITH (NOLOCK) WHERE spf2.function_id = sp.major_id)
    ELSE CAST(NULL AS sysname)
END AS YukonObjectName,
CASE sp.class 
    WHEN 10 THEN SCHEMA_NAME(( SELECT TOP 1 sx2.schema_id FROM [theDatabase].sys.xml_schema_collections sx2 WITH (NOLOCK) WHERE sx2.xml_collection_id = sp.major_id))
    WHEN 6 THEN SCHEMA_NAME((SELECT TOP 1 st2.schema_id FROM [theDatabase].sys.types st2 WITH (NOLOCK) WHERE st2.user_type_id = sp.major_id))
ELSE 
USER_NAME(CASE sp.class 
    WHEN 3 THEN (SELECT TOP 1 sch2.principal_id FROM [theDatabase].sys.schemas sch2 WITH (NOLOCK) WHERE sch2.schema_id = sp.major_id)
    WHEN 4 THEN (SELECT TOP 1 sp2.owning_principal_id FROM [theDatabase].sys.database_principals sp2 WITH (NOLOCK) WHERE sp2.principal_id = sp.major_id)
    WHEN 5 THEN (SELECT TOP 1 sa2.principal_id FROM [theDatabase].sys.assemblies sa2 WITH (NOLOCK) WHERE sa2.assembly_id = sp.major_id)
    WHEN 15 THEN (SELECT TOP 1 smt2.principal_id FROM [theDatabase].sys.service_message_types smt2 WITH (NOLOCK) WHERE smt2.message_type_id = sp.major_id)
    WHEN 16 THEN (SELECT TOP 1 ssc2.principal_id FROM [theDatabase].sys.service_contracts ssc2 WITH (NOLOCK) WHERE ssc2.service_contract_id = sp.major_id)
    WHEN 17 THEN (SELECT TOP 1 ss2.principal_id FROM [theDatabase].sys.services ss2 WITH (NOLOCK) WHERE ss2.service_id = sp.major_id)
    WHEN 18 THEN (SELECT TOP 1 srs2.principal_id FROM [theDatabase].sys.remote_service_bindings srs2 WITH (NOLOCK) WHERE srs2.remote_service_binding_id = sp.major_id)
    WHEN 19 THEN (SELECT TOP 1 sr2.principal_id FROM [theDatabase].sys.routes sr2 WITH (NOLOCK) WHERE sr2.route_id = sp.major_id)
    WHEN 23 THEN (SELECT TOP 1 sft2.principal_id FROM [theDatabase].sys.fulltext_catalogs sft2 WITH (NOLOCK) WHERE sft2.fulltext_catalog_id = sp.major_id)
    WHEN 24 THEN (SELECT TOP 1 ssk2.principal_id FROM [theDatabase].sys.symmetric_keys ssk2 WITH (NOLOCK) WHERE ssk2.symmetric_key_id = sp.major_id)
    WHEN 26 THEN (SELECT TOP 1 sak2.principal_id FROM [theDatabase].sys.asymmetric_keys sak2 WITH (NOLOCK) WHERE sak2.asymmetric_key_id = sp.major_id)
    WHEN 25 THEN (SELECT TOP 1 sc2.principal_id FROM [theDatabase].sys.certificates sc2 WITH (NOLOCK) WHERE sc2.certificate_id = sp.major_id)
    ELSE CAST(NULL AS sysname)
END)
END AS YukonObjectOwner,
CASE sp.class
    WHEN 2 then spar.name
    ELSE sc.name
END AS FieldName,
si.name AS IndexName,
pso.name AS ParentName,
pu.name AS ParentOwner,
pso.type AS ParentType,
sp.class AS type,
ddlt.name AS DdlTriggerName,
en.name AS EventNotificiationName,
qensq.name AS ENQueueName,
qensqs.name AS ENQueueSchema

FROM [theDatabase].sys.extended_properties AS sp WITH (NOLOCK) 
LEFT JOIN [theDatabase].sys.all_objects  AS so WITH (NOLOCK) ON so.object_id=sp.major_id
LEFT JOIN [theDatabase].sys.schemas  WITH (NOLOCK) ON [theDatabase].sys.schemas.schema_id=so.schema_id
LEFT JOIN [theDatabase].sys.all_columns AS sc WITH (NOLOCK) ON sc.object_id=sp.major_id AND sc.column_id=sp.minor_id
LEFT JOIN [theDatabase].sys.all_parameters AS spar WITH (NOLOCK) ON spar.object_id=sp.major_id AND spar.parameter_id=sp.minor_id
LEFT JOIN [theDatabase].sys.indexes si WITH (NOLOCK) ON si.object_id=sp.major_id AND si.index_id=sp.minor_id
LEFT JOIN [theDatabase].sys.all_objects pso  WITH (NOLOCK) ON so.parent_object_id=pso.object_id
LEFT JOIN [theDatabase].sys.schemas pu  WITH (NOLOCK) ON pso.schema_id=pu.schema_id
LEFT JOIN [theDatabase].sys.triggers  AS ddlt WITH (NOLOCK) ON ddlt.object_id=sp.major_id
LEFT JOIN [theDatabase].sys.event_notifications  AS en WITH (NOLOCK) ON en.object_id=sp.major_id
LEFT JOIN [theDatabase].sys.service_queues  AS qensq WITH (NOLOCK) ON qensq.object_id=en.parent_id
LEFT JOIN [theDatabase].sys.schemas AS qensqs WITH (NOLOCK) ON qensqs.schema_id=qensq.SCHEMA_ID
WHERE sp.major_id = @ObjectID
OR sp.minor_id = @ObjectID

Answer 1

If you catch the query running in Activity Monitor (e.g. it is visible in the Processes tab and the Task State / Command are not empty), you should also see identifiable information in the Login and Host columns (this will at least tell you who is running the query and where from).

Take the value from the Session ID column and plug it into this query (I'll assume it is 57, but change that to match one of the queries you've caught):

DECLARE @spid INT = 57;

SELECT
  s.program_name,
  s.host_name,
  s.login_name,
  c.client_net_address
FROM sys.dm_exec_connections AS c
LEFT OUTER JOIN sys.dm_exec_sessions AS s
ON c.most_recent_session_id = s.session_id;

This should give you more clues about what is doing this, but it seems from a quick search that this query in fact does come from Red Gate's SQL Doc. So, someone on your team must be using that (or the SQL Server instance is exposed to the outside world, and someone not on your team is using it).