【Dynamics365-Finance&Operations实战-Batch性能问题与解决】长时间走行SQL的发现与对应

前提

在工作中经常会有对Batch的性能优化需求，如长时间走行、CPU使用率过高等，此篇用于记载如何发现和解决。

发现

实装Batch后如何发现batch的执行时间过长呢

1.通过Batch的实行履历查看

2.通过LCS监视查看对应时间下的CPU使用率

LCS下的环境检测功能以曲线图方式显示某一时间带下CPU的使用率，当CPU长时间处于100%使用时常常意味着发生了性能问题，此时也需要对应

3.使用sqlserver的几张系统表组合查看sql状态

sqlserver下存在这样几张表：
sys.query_store_runtime_stats
sys.query_store_runtime_stats_interval
sys.query_store_plan
sys.query_store_query
sys.query_store_query_text
经过组合抽出IO或CPU执行时间的特定字段，可以查看在2中对应时间带的执行了哪些sql，进而分析这些sql的执行是否存在问题
使用方式：
sql1 根据batch的执行时间带确定runtime_stats_interval_id:
select * from sys.query_store_runtime_stats s
where
(s.start_time >= '2025-07-01 01:00:00' and s.start_time <= '2025-07-01 12:00:00')
or
(s.end_time >= '2025-07-01 01:00:00' and s.end_time <= '2025-07-01 12:00:00')

sql2 抽出IO消耗最多的前一百位sql：
select top 100
q.query_id
, qt.query_sql_text
, s.last_execution_timne
, i.start_time
, i.end_time
, s.count_executions
, s.average_logical_io_reads + s.avg_logical_io_writes as AvgIO
, (s.avg_logical_io_reads + s.avg_logical_io_writes) * s.count_executions as TotalIO
, (s.avg_logical_io_reads + s.avg_logical_io_writes) * s.count_executions as TotalIO * 8000 as totalIO_bytes
, s.min_logical_io_reads + s.min_logical_io_writes AS MinIO
, s.max_logical_io_reads + s.max_logical_io_writes AS MaxIO
From
sys.query_store_runtime_stats as s
Inner join sys.query_store_runtime_stats_interval as i ON s.runtime_stats_internal_id = i.runtime_stats_internal_id
Inner join sys.query_store_plan as p ON s.palan_id = p.plan_id
Inner join sys.query_store_query as q ON q.query_id = p.query_id
Inner join query_store_query_text as qt ON qt.query_text_id = q.query_text_id
Where
1 = 1
and s.runtime_stats_interval_id = 'xxxxxx'
order by
(s.avg_logical_io_reads + s.avg_logical_io_writes) * s.count_executions Desc
结果包含多条sql，可根据query_id确定执行计划，分析对应sql。

解决

针对一些经典的性能问题的可能原因，有与之对应的解决方案

1.DataAreaID和Partition

D356标准T-SQL会在join和where条件中自动添加对DataAreaID和Partition的引用（实际SQL在上述2抽出的结果中可以确认），但通过subquery抽出的字段以及View、Computed Column不会自动添加，会无法hit到相关index。
因此在subquery或computedColumn返回字段时需要手动添加对dataareaID和partition的条件匹配。

2.index的创建和使用

如果一个index长下面这个样子：
CREATE NON_CLUSTERED INDEX [TEST_INDEX] ON [DBO.TESTTABLE]
{
[PARTITION] ASC,
[DATAAREAID] ASC,
[FIELD1] ASC,
[FIELD2] ASC
}
INCLUDE ([FIELD3]) WITH (STATUSTUCS_NORECOMPUTE = OFF, ONLINE = OFF, FILEFACTOR = 100, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
且PARTITION和DATAAREAID是主要过滤项目
则：

PARTITION	DATAAREAID	FIELD1	FIELD2	FIELD3	执行计划类型	检索到的key	结论
√	√	√	√	√	INDEX SEEK	PARTITION, DATAAREAID, FIELD1, FIELD2	检索条件不影响index的hit
√	√	√	√	x	INDEX SEEK	PARTITION, DATAAREAID, FIELD1, FIELD2	include字段不包含在select字段中不影响index的hit
√	x	√	√	√	INDEX SEEK	PARTITION, FIELD1, FIELD2	大幅影响性能
√	x	√	√	x	INDEX SEEK	PARTITION, FIELD1, FIELD2	大幅影响性能
√	√	x	√	x	INDEX SEEK	PARTITION, DATAAREAID, FIELD2	略微影响性能
√	√	√	x	x	INDEX SEEK	PARTITION, DATAAREAID, FIELD1	略微影响性能
x	√	√	√	√	INDEX SCAN	-	index是前头匹配原则，select缺少index的第一字段会导致无法hit

3.当需要在view中添加有条件分歧的computedColumn时的实装

假设有一个名为field的computed字段需要按照某一前置字段的值判断：
public static str getField()
str field1 = strfmt('select flag_field1 from table1 where 条件1')
str filel2 = strfmt('select flag_field2 from table1 where 条件2')
str 前提判断变量 = SysComputedColumn::returnField(viewStr(testView(关联view的逻辑名)), identifierStr(table2(关联表的逻辑名)), fieldStr(table2(关联表逻辑名), preJudgeFlag(字段逻辑名)))
str 最终返回字段 = strfmt('case when (%1 = '1'') then ( %2 ) else ( %3 ) end', 前提判断变量, field1 , filel2 )
return 最终返回字段

在关联View中关于该字段的SQL表现：
select
CAST(
(
CASE
when preJudgeFlag = '1'
then (
select flag_field1 from table1 where 条件1
)
else (
select flag_field2 from table1 where 条件2
)
end
) AS NVARCHAR(255)
) AS field
from ....

4.DataAreaID不存在和存在的表相互join时如何优化

针对2.index的创建和使用中可以知道，当本该出现在index前部的DataAreaID由于某些表中没有enable SaveDataPerCompany时该index不会被hit到，所以需要在有DataAreaID的表中额外添加没有DataAreaID的index，或将既存的DataAreaID在前面的index修改，让DataAreaID到后面

5.loop query过多的问题

当使用subquery时当无法确定唯一key且需要从同一张表中抽出多条字段时，比起多次的写where条件一样的subquery，可以一次将多个字段同时取得用特殊字符分开后合并为同一个字段，之后在代码中分割处理。
当可以确定唯一key且需要从同一张表中抽出多条字段时，可以使用outer join select主表的方式减少对DB的access数。

当数据量足够少时，可以将全部数据取出后map化。

6.不同时间或环境下执行计划不一致的问题

sqlserver会根据数据量、where条件和index自动调整执行计划，但有时我们并不需要他进行优化。
比如有几张表CustTable（1000件），CustInvoice（100万件），CustInvoiceHeader（1万件）
当我们实装为：
select accountNum from custTable
outer join invoiceNumber from custInvoiceHeader where custInvoiceHeader.accountNum == custTable.accountNum
outer join invoiceNumberDetailID from custInvoice where custInvoice.invoiceNumber == custInvoiceHeader.invoiceNumber
and custInvoice.activeDate > 'yyyy-mm-dd HH:mm:ss'
就可能会被sqlserver解析成优先从数据量最大的custInvoice表先开始抽出。
为保证数据的取得顺序为：CustTable -> CustInvoiceHeader -> CustInvoice，可以通过下面方式实装：
select forceselectorder accountNum from custTable
outer join invoiceNumber from custInvoiceHeader where custInvoiceHeader.accountNum == custTable.accountNum
outer join invoiceNumberDetailID from custInvoice where custInvoice.invoiceNumber == custInvoiceHeader.invoiceNumber
and custInvoice.activeDate > 'yyyy-mm-dd HH:mm:ss'

7.ttsbegin和ttscommit

对于没有CUD的操作来说，无需tts。

8.对于无需改换特定company的查询，使用crosscompany替代changecompany以减少DB访问次数。