SQL数据库Query Store最大存储大小部署后被还原，如何固定为1000MB？

核心原因 #

你的DevOps部署流程大概率是通过**数据库部署模板（如DACPAC、ARM模板、Terraform配置）**或自动化脚本重新初始化/覆盖了数据库配置，这些模板中Query Store的MAX_STORAGE_SIZE_MB参数保持了默认值100MB，导致手动修改的设置被覆盖。

具体解决步骤 #

1. 修改部署模板中的Query Store配置 #

找到你DevOps流程中使用的数据库部署模板，直接在模板里指定目标存储大小：

• 如果用DACPAC（SQL Server Data Tools项目）：
  在项目的数据库属性中，找到Query Store设置，将Maximum storage size (MB)改为1000；或者直接编辑.sqlproj文件，添加/修改以下配置：

  <PropertyGroup>
    <QueryStoreMaxStorageSizeMB>1000</QueryStoreMaxStorageSizeMB>
  </PropertyGroup>
  

• 如果用ARM模板：
  在数据库资源的properties节点下，添加Query Store的配置：

  "properties": {
    "queryStoreOptions": {
      "maxStorageSizeInMB": 1000,
      "state": "ReadWrite" // 确保Query Store处于读写状态
    }
  }
  

• 如果用Terraform的azurerm_mssql_database资源：
  在query_store_options块中设置存储大小：

  resource "azurerm_mssql_database" "example" {
    // 其他配置...
    query_store_options {
      max_storage_size_mb = 1000
    }
  }
  

2. 在部署脚本中添加固定配置的步骤 #

如果你的部署流程是通过SQL脚本执行，确保在部署完成后（或部署过程中）执行修改Query Store的语句，避免被后续步骤覆盖：

ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1000);
ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE); -- 可选，确保Query Store正常运行

将这段脚本加入到DevOps流水线的部署后步骤，确保它在所有可能重置配置的操作之后执行。

3. 锁定Query Store配置（可选） #

如果需要防止意外修改，可以通过数据库级别的权限控制，限制能修改Query Store配置的角色：

-- 拒绝普通用户修改数据库配置的权限
DENY ALTER ANY DATABASE TO [YourDevOpsUser];

注意：仅在确认DevOps用户不需要修改其他数据库配置时使用，避免影响正常部署流程。

验证方法 #

部署完成后，执行以下语句确认配置是否生效：

SELECT name, value, value_in_use
FROM sys.database_query_store_options
WHERE name = 'max_storage_size_mb';

如果value和value_in_use都显示1000，说明配置已固定。

内容的提问来源于stack exchange，提问作者Aniket Karajgikar