提问人：Core IP 提问时间：10/4/2023 更新时间：10/5/2023 访问量：75
确定导致 CPU 消耗过高的多个 Azure SQL 查询调用的源
Identifying the Source of Multiple Azure SQL Query Calls Causing High CPU Consumption
问：
我有一个被多次调用的 Azure SQL 查询，它导致 CPU 消耗过高。我怀疑该查询是从我的应用程序中的多个页面或位置调用的，但我无法确定来源。
有没有办法跟踪和识别从应用程序中的哪个页面或位置调用此查询？我想优化它的使用率，并可能减少它造成的 CPU 消耗。
任何可以帮助我追踪这些重复查询调用来源的建议或工具将不胜感激。谢谢！
SELECT
clmns.name AS [Name],
clmns.column_id AS [ID],
clmns.is_nullable AS [Nullable],
clmns.is_computed AS [Computed],
CAST(ISNULL(cik.index_column_id, 0) AS bit) AS [InPrimaryKey],
clmns.is_ansi_padded AS [AnsiPaddingStatus],
CAST(clmns.is_rowguidcol AS bit) AS [RowGuidCol],
CAST(ISNULL(cc.is_persisted, 0) AS bit) AS [IsPersisted],
ISNULL(clmns.collation_name, N'') AS [Collation],
CAST(ISNULL((select TOP 1 1 from sys.foreign_key_columns AS colfk where colfk.parent_column_id = clmns.column_id and colfk.parent_object_id = clmns.object_id), 0) AS bit) AS [IsForeignKey],
clmns.is_identity AS [Identity],
CAST(ISNULL(ic.seed_value,0) AS bigint) AS [IdentitySeed],
CAST(ISNULL(ic.increment_value,0) AS bigint) AS [IdentityIncrement],
(case when clmns.default_object_id = 0 then N'' when d.parent_object_id > 0 then N'' else d.name end) AS [Default],
(case when clmns.default_object_id = 0 then N'' when d.parent_object_id > 0 then N'' else schema_name(d.schema_id) end) AS [DefaultSchema],
ISNULL(dc.Name, N'') AS [DefaultConstraintName],
(case when clmns.rule_object_id = 0 then N'' else r.name end) AS [Rule],
(case when clmns.rule_object_id = 0 then N'' else schema_name(r.schema_id) end) AS [RuleSchema],
CAST(ISNULL(COLUMNPROPERTY(clmns.object_id, clmns.name, N'IsDeterministic'),0) AS bit) AS [IsDeterministic],
CAST(ISNULL(COLUMNPROPERTY(clmns.object_id, clmns.name, N'IsPrecise'),0) AS bit) AS [IsPrecise],
ISNULL(ic.is_not_for_replication, 0) AS [NotForReplication],
CAST(COLUMNPROPERTY(clmns.object_id, clmns.name, N'IsFulltextIndexed') AS bit) AS [IsFullTextIndexed],
CAST(clmns.encryption_type AS int) AS [EncryptionType],
clmns.encryption_algorithm_name AS [EncryptionAlgorithm],
clmns.column_encryption_key_id AS [ColumnEncryptionKeyID],
ceks.name AS [ColumnEncryptionKeyName],
CAST(clmns.is_filestream AS bit) AS [IsFileStream],
CAST(clmns.is_sparse AS bit) AS [IsSparse],
CAST(clmns.is_column_set AS bit) AS [IsColumnSet],
usrt.name AS [DataType],
s1clmns.name AS [DataTypeSchema],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
CAST(clmns.precision AS int) AS [NumericPrecision],
CAST(clmns.scale AS int) AS [NumericScale],
ISNULL(xscclmns.name, N'') AS [XmlSchemaNamespace],
ISNULL(s2clmns.name, N'') AS [XmlSchemaNamespaceSchema],
ISNULL( (case clmns.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
CASE WHEN usrt.is_table_type = 1 THEN N'structured' ELSE N'' END AS [UserType],
clmns.generated_always_type AS [GeneratedAlwaysType],
CAST(clmns.is_hidden AS bit) AS [IsHidden],
CAST(clmns.is_masked AS bit) AS [IsMasked],
ISNULL(clmns.graph_type, 0) AS [GraphType]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id and 1=ik.is_primary_key
LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id and cik.column_id = clmns.column_id and cik.object_id = clmns.object_id and 0 = cik.is_included_column
LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id and cc.column_id = clmns.column_id
LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id
LEFT OUTER JOIN sys.default_constraints as dc ON clmns.default_object_id = dc.object_id
LEFT OUTER JOIN sys.objects AS r ON r.object_id = clmns.rule_object_id
LEFT OUTER JOIN sys.column_encryption_keys AS ceks ON (ceks.column_encryption_key_id = clmns.column_encryption_key_id)
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.schemas AS s1clmns ON s1clmns.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))
LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
WHERE
(clmns.name=@_msparam_0)and((tbl.name=@_msparam_1 and SCHEMA_NAME(tbl.schema_id)=@_msparam_2))
OPTION (FORCE ORDER)
需要找到方法来跟踪和识别从我的应用程序中的哪个页面或位置调用此查询？我想优化它的使用率，并可能减少它造成的 CPU 消耗。
答：
0赞
Alberto Morillo
10/5/2023
#1
您可以开始在代码中使用查询标记。这些标记在 SQL Server Management Studio 和 Azure 门户（查询性能见解）上提供的查询存储报表上可见，并且还将显示在扩展事件捕获的查询中。
var myLocation = new Point(1, 2);
var nearestPeople = (from f in context.People.TagWith("This is my spatial query!")
orderby f.Location.Distance(myLocation) descending
select f).Take(5).ToList();
一个值得尝试的类似但可能更好的选择是 EntityFrameworkQueryableExtensions.TagWithCallSite 方法，该方法使用源文件名和调用方法的行创建标记。示例可以在这里找到。
public static IQueryable<T> TagWithSource<T>(this IQueryable<T> queryable,
string tag = default,
[CallerLineNumber] int lineNumber = 0,
[CallerFilePath] string filePath = "",
[CallerMemberName] string memberName = "",
[CallerArgumentExpression("queryable")]
string argument = "")
{
// argument could be multiple lines with whitespace so let's normalize it down to one line
var trimmedLines = string.Join(
string.Empty,
argument.Split(Environment.NewLine, StringSplitOptions.RemoveEmptyEntries).Select(i => i.Trim())
);
var tagContent = string.IsNullOrWhiteSpace(tag)
? default
: tag + Environment.NewLine;
tagContent += trimmedLines + Environment.NewLine + $" at {memberName} - {filePath}:{lineNumber}";
return queryable.TagWith(tagContent);
}
评论