June 18, 2024 at 1:51 pm
Hi. we are noticing this following query frequently in our production environment in SQL Sentry. They are definitely not from application. Do you know what it could be and how we can track it down?
SELECT
do.object_name as [object_name],
do.schema_name as [schema_name],
do.referencing_type_desc as [type_desc],
do.referencing_type as [type],
ds.referenced_server as [referenced_server_name],
do.referenced_database as [referenced_database_name],
do.referenced_schema as [referenced_schema_name],
do.referenced_entity as [referenced_entity_name],
ds.referenced_provider as [referenced_provider],
null as [type_code],
null as [definition],
null as [uses_ansi_nulls],
null as [uses_native_compilation],
null as [uses_quoted_identifier],
null as [assembly_file_name],
null as [clr_name],
0 as [permission_set],
1 as [Feature_LinkedServer],
0 as [Feature_TSqlScript],
0 as [Feature_ClrAssemblies]
FROM
(SELECT
OBJECT_NAME(referencing_id) as object_name,
SCHEMA_NAME(referencing_class) as schema_name,
o.type_desc AS referencing_type_desc,
o.type AS referencing_type,
referenced_server_name as referenced_server,
referenced_database_name as referenced_database,
referenced_schema_name as referenced_schema,
referenced_entity_name as referenced_entity
FROM sys.sql_expression_dependencies as d WITH (NOLOCK)
JOIN sys.objects AS o WITH (NOLOCK)
ON d.referencing_id = o.object_id
WHERE d.referenced_database_name is not null AND d.is_ambiguous = 0) as do
FULL OUTER JOIN
(SELECT
OBJECT_NAME(referencing_id) as object_name,
referenced_server_name as referenced_server,
provider as referenced_provider
FROM sys.sql_expression_dependencies as d WITH (NOLOCK)
JOIN sys.servers as s WITH (NOLOCK)
ON s.name = d.referenced_server_name
COLLATE DATABASE_DEFAULT
WHERE d.is_ambiguous = 0) as ds
ON do.object_name = ds.object_name
UNION
SELECT
o.name as [object_name],
SCHEMA_NAME(o.schema_id) as [schema_name],
null as [type_desc],
null as [type],
null as [referenced_server_name],
null as [referenced_database_name],
null as [referenced_schema_name],
null as [referenced_entity_name],
null as [referenced_provider],
o.type as [type_code],
sm.definition as [definition],
sm.uses_ansi_nulls as [uses_ansi_nulls],
sm.uses_native_compilation as [uses_native_compilation],
sm.uses_quoted_identifier as [uses_quoted_identifier],
null as [assembly_file_name],
null as [clr_name],
0 as [permission_set],
0 as [Feature_LinkedServer],
1 as [Feature_TSqlScript],
0 as [Feature_ClrAssemblies]
FROM sys.objects AS o WITH (NOLOCK)
JOIN sys.sql_modules AS sm WITH (NOLOCK)
ON o.object_id = sm.object_id
WHERE o.type IN ('P', 'RF', 'FN', 'IF', 'TF', 'TR', 'V')
AND SCHEMA_NAME(o.schema_id)='dbo'
AND o.is_ms_shipped = 0
AND sm.definition IS NOT NULL
UNION
SELECT
a.name as [object_name],
null as [schema_name],
null as [type_desc],
null as [type],
null as [referenced_server_name],
null as [referenced_database_name],
null as [referenced_schema_name],
null as [referenced_entity_name],
null as [referenced_provider],
null as [type_code],
null as [definition],
null as [uses_ansi_nulls],
null as [uses_native_compilation],
null as [uses_quoted_identifier],
f.name as [assembly_file_name],
clr_name as [clr_name],
a.permission_set as [permission_set],
0 as [Feature_LinkedServer],
0 as [Feature_TSqlScript],
1 as [Feature_ClrAssemblies]
FROM sys.assemblies a WITH (NOLOCK)
JOIN sys.assembly_files f WITH (NOLOCK)
ON a.assembly_id = f.assembly_id
WHERE a.is_user_defined = 1
June 19, 2024 at 11:48 am
I'd start with the login. Which one is running the query? That'll give you a lot of information. It looks like a monitoring query. It might be SentryOne itself. If you're not getting granular information about the query from SentryOne, then set up Extended Events to capture those details. While I love monitoring tools (heck, I help Redgate make and sell one), they're meant to do lots of things really broadly, and sometimes, we need a very narrow focus, which the monitoring tools won't provide. In that case, fall back on the more granular tools we have, Extended Events.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 19, 2024 at 5:13 pm
Thank you. It says the Login is NT Authority\SYSTEM. If it is SQL Sentry, it would show SQLSentry Login? we do have a separate login for SQL Sentry. The application column says "Core Microsoft SQLClient Data Provider". The Host says the SQL Server name.
Also, our SQL Server has a separate database for each of our clients. So, we have around 150 databases in a SQL Server instance. This query shows one for each database. Not all databases show at the same time though.
June 19, 2024 at 5:24 pm
Still sounds like a monitoring solution looking at stuff, especially since it's against every database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 20, 2024 at 1:40 pm
Ok. Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply