如何排查Azure SQL弹性池查询首次执行慢的性能问题

数据老陈

2026-10-5

问题描述

我在配置了弹性池的Azure SQL数据库上开发视图，此前有Oracle相关开发经验，在Azure上遇到了无法解释和排查的异常问题。

核心异常

同一三表关联查询在Oracle上每次执行耗时均不到1秒，但在Azure上首次执行需要40-50秒，立即重新执行则性能与Oracle持平。查询关联模型表MODEL_TABLE、商品项表ITEM_TABLE和属性表ATTR_TABLE，按指定PRODUCT_ID过滤数据，SQL语句如下：

select pa.*
  from ITEM_TABLE ppi, ATTR_TABLE pa, MODEL_TABLE pmc
 where ppi.ITEM_ID = pa.ID
   AND pa.ID_TYPE = 'ITEM'
   AND ppi.MODEL_ID = pmc.MODEL_ID
   AND pmc.PRODUCT_ID = '8675309eeeiine'

ANSI标准写法：

select pa.*
  from ITEM_TABLE ppi
       INNER JOIN ATTR_TABLE pa ON (ppi.ITEM_ID = pa.ID AND pa.ID_TYPE = 'ITEM')
       INNER JOIN MODEL_TABLE pmc ON (ppi.MODEL_ID = pmc.MODEL_ID) 
 where pmc.PRODUCT_ID = '8675309eeeiine'

现有排查基础

Oracle和Azure两侧的表结构、数据完全一致
已确保两侧配置了相同的索引，还额外在Azure上新增了多组索引尝试优化性能
已导出两侧执行计划，Oracle执行计划如下：

Plan hash value: 4203126522
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                                | 67991 |    13M|       | 48475   (1)| 00:00:02 |
|   1 |  SORT ORDER BY            |                                | 67991 |    13M|    16M| 48475   (1)| 00:00:02 |
|*  2 |   HASH JOIN               |                                | 67991 |    13M|       | 45474   (1)| 00:00:02 |
|*  3 |    HASH JOIN              |                                |  2790 | 78120 |       |   697   (2)| 00:00:01 |
|*  4 |     VIEW                  | index$_join$_004               |   289 |  4046 |       |    60   (2)| 00:00:01 |
|*  5 |      HASH JOIN            |                                |       |       |       |            |          |
|*  6 |       INDEX RANGE SCAN    | grp_MOD_PROD_ID               |   289 |  4046 |       |     1   (0)| 00:00:01 |
|   7 |       INDEX FAST FULL SCAN| grp_MOD_MODEL_CONCEPT_ID      |   289 |  4046 |       |    73   (0)| 00:00:01 |
|   8 |     VIEW                  | index$_join$_001               | 77844 |  1064K|       |   637   (1)| 00:00:01 |
|*  9 |      HASH JOIN            |                                |       |       |       |            |          |
|  10 |       INDEX FAST FULL SCAN| grp_PROD_ITM_MODEL_CONCEPT_ID | 77844 |  1064K|       |   283   (1)| 00:00:01 |
|  11 |       INDEX FAST FULL SCAN| grp_PROD_ITM_PROD_ITM_ID      | 77844 |  1064K|       |   242   (1)| 00:00:01 |
|* 12 |    TABLE ACCESS FULL      | grp_ATTRIBUTES                |  4435K|   736M|       | 44736   (1)| 00:00:02 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("PPI"."PRODUCT_ITEM_ID"="PA"."OWNER_ID")
   3 - access("PPI"."MODEL_CONCEPT_ID"="PMC"."MODEL_CONCEPT_ID")
   4 - filter("PMC"."PRODUCT_ID"='123456')
   5 - access(ROWID=ROWID)
   6 - access("PMC"."PRODUCT_ID"='123456')
   9 - access(ROWID=ROWID)
  12 - filter("PA"."OWNER_TYPE"='PRODUCT_ITEM')

Azure侧已配置的索引如下：

------------------------------------------------------------------------------------------------------------------
--grpAttributes
------------------------------------------------------------------------------------------------------------------
ALTER TABLE [x].[grpAttributes] ADD  CONSTRAINT [PK_grpAttributes] PRIMARY KEY CLUSTERED 
(
    [AttributeId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_ATTR_ATTRNAM_LKP] ON [x].[grpAttributes]
(
    [AttributeName] ASC,
    [LookupValue] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_ATTR_ATTRNAM_VAL_DATE] ON [x].[grpAttributes]
(
    [AttributeName] ASC,
    [ValueType] ASC,
    [DateValue] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_ATTR_ATTRNAM_VAL_NUM] ON [x].[grpAttributes]
(
    [AttributeName] ASC,
    [ValueType] ASC,
    [NumberValue] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_ATTR_OWN_ID_OWN_TYPE] ON [x].[grpAttributes]
(
    [OwnerId] ASC,
    [OwnerType] ASC
)
INCLUDE([ParentAttributeId],[AttributeName],[IsApplicable],[OccurrenceIndex],[Locale],[ValueType],[TextValue],[NumberValue],[DateValue],[LookupRowId],[LookupValue],[Sha1],[SortValue],[PiUsageContext],[DiUsageContext],[LocaleWeb]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_ATTR_OWN_ID_TYPE_ATTRNAM] ON [x].[grpAttributes]
(
    [OwnerType] ASC,
    [OwnerId] ASC,
    [AttributeName] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_ATTR_OWNER_ID_PI_USE_CONTEXT] ON [x].[grpAttributes]
(
    [OwnerId] ASC,
    [PiUsageContext] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_ATTR_OWNER_TYPE] ON [x].[grpAttributes]
(
    [OwnerType] ASC
)
INCLUDE([OwnerId],[AttributeName],[OccurrenceIndex],[Locale],[TextValue],[NumberValue],[DateValue],[SortValue]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_ATTR_PARENT_ATTR_ID] ON [x].[grpAttributes]
(
    [ParentAttributeId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_TEMP_grp_ATTR_ATTRNAM_LKP] ON [x].[grpAttributes]
(
    [AttributeName] ASC,
    [LookupValue] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_TEMP_grp_ATTR_OWNER_ID_PI_USE_CONTEXT] ON [x].[grpAttributes]
(
    [OwnerId] ASC,
    [PiUsageContext] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

------------------------------------------------------------------------------------------------------------------
--grpProductitems
------------------------------------------------------------------------------------------------------------------

ALTER TABLE [x].[grpProductItems] ADD  CONSTRAINT [PK_grpProductItems] PRIMARY KEY CLUSTERED 
(
    [ProductItemId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [ix_items_prod_type_base_value] ON [x].[grpProductItems]
(
    [ProductType] ASC,
    [BaseValue] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_PROD_ITEMS_FOD_SALES_CLS_BASE_VAL] ON [x].[grpProductItems]
(
    [FirstOrderableDate] ASC,
    [SalesClass] ASC,
    [BaseValue] ASC
)
INCLUDE([PartNumber],[ModelConceptId]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_grp_PROD_ITM_INV_ITM_ID] ON [x].[grpProductItems]
(
    [InventoryItemId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_PROD_ITM_MODEL_CONCEPT_ID] ON [x].[grpProductItems]
(
    [ModelConceptId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_PROD_ITM_PART_NUMBER] ON [x].[grpProductItems]
(
    [PartNumber] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

------------------------------------------------------------------------------------------------------------------
--grpModelConcepts
------------------------------------------------------------------------------------------------------------------
ALTER TABLE [x].[grpModelConcepts] ADD  CONSTRAINT [PK_grpModelConcepts] PRIMARY KEY CLUSTERED 
(
    [ModelConceptId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_MOD_NAME] ON [x].[grpModelConcepts]
(
    [Name] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_grp_MOD_PROD_ID] ON [x].[grpModelConcepts]
(
    [ProductId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

进度更新

2021年9月7日更新

grpAttributes表共有560万行数据，创建仅含80万行的预过滤版本并配置相同索引后，查询首次执行耗时降至9秒，二次执行速度更快，可见数据规模是影响性能的因素之一。还尝试创建了带索引的视图（Azure SQL无物化视图，视图索引是最接近的替代方案），该视图基于全量grpAttributes表过滤掉大量冗余数据，直接查询该视图时耗时降至2秒，但性能不稳定，当把视图嵌入更复杂的查询时，SQL优化器偶尔不会走视图索引。
待确认问题：

Azure SQL在特定数据规模下出现性能下降是正常现象吗？
如何了解相关性能阈值，该问题是否与数据库配置有关？

2021年9月9日更新

已对接所使用Azure DB的平台负责人，对方称问题属于Microsoft Azure DB产品自身问题，与平台侧无关，因此仍需自行定位这个简单三表关联查询性能极差的根因。目前已确认两侧索引、数据、结构、执行计划均一致，但Oracle性能远优于Azure SQL。

解决方案

根因定位

你遇到的首次执行慢、二次执行快的现象，核心是冷缓冲池+索引结构不合理，和Query Store无关，属于SQL Server（Azure SQL内核）和Oracle存储引擎的固有设计差异：

缓存策略差异：Oracle默认会将大表全表扫描数据存放在SGA冷区，不会被快速淘汰；而Azure SQL的缓冲池LRU淘汰策略会优先清理大表冷数据，你的560万行grpAttributes表首次查询时需要从磁盘全量加载符合条件的数据，刚好对应你观测到的40-50秒耗时，二次查询时数据已经在内存中所以性能达标。
索引顺序错误：你现有grpAttributes表上的IX_grp_ATTR_OWN_ID_OWN_TYPE索引前导列是OwnerId、次列是OwnerType，而你的查询过滤条件是先匹配OwnerType='ITEM'再关联OwnerId，这种前缀顺序会导致索引只能扫描所有OwnerId条目才能过滤OwnerType，无法直接命中过滤条件，属于半失效状态。

优化方案

调整覆盖索引：重建grpAttributes表的查询专用索引，前导列匹配过滤+关联顺序，INCLUDE所有需要返回的列避免回表，创建语句如下：

CREATE NONCLUSTERED INDEX [IX_grp_ATTR_OWN_TYPE_OWN_ID_COVER] ON [x].[grpAttributes]
(
    [OwnerType] ASC,
    [OwnerId] ASC
)
INCLUDE([ParentAttributeId],[AttributeName],[IsApplicable],[OccurrenceIndex],[Locale],[ValueType],[TextValue],[NumberValue],[DateValue],[LookupRowId],[LookupValue],[Sha1],[SortValue],[PiUsageContext],[DiUsageContext],[LocaleWeb]) 
WITH (DROP_EXISTING = OFF, ONLINE = ON) ON [PRIMARY]

建完后首次查询会直接走这个覆盖索引，不需要加载全表数据，耗时直接降到和Oracle持平的水平。
2. 索引视图强制生效：如果要在复杂查询中使用你已经建好的索引视图，直接加WITH (NOEXPAND)提示强制优化器不展开视图，语法参考：

SELECT * FROM 你的索引视图名 WITH (NOEXPAND) WHERE 过滤条件

弹性池资源校验：如果调整索引后首次查询仍有IO等待过高的问题，查Azure SQL监控的IOPS/吞吐量指标，看首次

如何排查Azure SQL弹性池查询首次执行慢的性能问题

核心异常 #

现有排查基础 #

进度更新 #

2021年9月7日更新 #

2021年9月9日更新 #

根因定位 #

优化方案 #