存储过程

本文介绍RDS SQL Server(2012及以上版本)支持的存储过程。

使用说明

本文所述命令适用于在SSMS工具中执行,命令中包含了GO作为批命令分隔符。如果您计划在DMS中执行存储过程命令,请勿在命令中添加GO关键字,否则会报错。

关于SQL Server存储过程的更多介绍,请参见RDS SQL Server使用介绍

更新数据库统计信息

T-SQL命令

sp_rds_update_db_stats

描述

用于灵活高效地更新数据库统计信息,支持采样率、并行度、超时时间及阈值百分比多维度配置。

使用方法

-- 如下为包含多个参数的综合示例
-- 更新test_db数据库统计信息,设置采样率为50%,并行度为4,超时时间为7200秒,修改阈值为3
EXEC sp_rds_update_db_stats        
    @db_name = 'test_db',          -- 数据库名(必填)
    @sample_percent = 50,          -- 采样率(可选)
    @max_dop = 4,                  -- 并行度(可选,该参数不支持2012及以下版本)
    @timeout_seconds = 7200,       -- 超时时间(可选)
    @modification_threshold = 3;   -- 修改阈值(可选)
说明

仅传入@db_name参数,或者SQL Server数据库版本为2008时,默认将执行系统sp_updatestats,详情请参见微软官方教程

参数项

是否必填

描述

@db_name

指定需要更新统计信息的目标数据库名称。使用示例如下:

-- 仅设置数据库名,遵循微软sp_updatestats统计信息更新逻辑
EXEC sp_rds_update_db_stats @db_name = 'test_db';

@sample_percent

控制统计信息采样的百分比,类型为 float,取值范围为 [0, 100]

未指定该参数时使用系统默认采样率,详情请参见微软官方教程。使用示例如下:

-- 设置采样率为30%
EXEC sp_rds_update_db_stats 
    @db_name = 'test_db',
    @sample_percent = 30;

@max_dop

设置并行度(Degree of Parallelism),类型为 int,默认值为 0(表示使用系统默认设置),最大不超过RDS实例规格核数该参数不支持2012及以下版本。示例如下:

-- 设置最大并行度为4
EXEC sp_rds_update_db_stats 
    @db_name = 'test_db',
    @max_dop = 4;

@timeout_seconds

设置统计信息更新操作的超时时间,单位为秒(s),默认值为 3600 秒(即 1 小时)。示例如下:

-- 超时时间设置为7200秒(2小时)
EXEC sp_rds_update_db_stats 
 @db_name = 'test_db',
 @timeout_seconds = 7200;

@modification_threshold

设置统计信息更新的修改阈值百分比,类型为 int,默认值为 0

  • 默认策略(当值为 0 时):采用阿里云推荐的最优实践策略:

    • 表行数 < 500:30%

    • 500 ≤ 行数 ≤ 1,000,000:20%

    • 行数 > 1,000,000:5%

  • 手动传参:需要预先计算设定值,设定值范围为 [0, 100]。计算公式为设定值 = 修改行数 * 100 / 总行数。若期望触发统计信息更新,计算结果必须小于等于设定值。

    -- 表有10,000行数据时,若期望在修改100行时触发统计信息更新,设置阈值为1即可
    EXEC sp_rds_update_db_stats 
        @db_name = 'test_db',
        @modification_threshold = 1;

实例内复制数据库

T-SQL命令

sp_rds_copy_database

支持的实例

  • 高可用系列

  • 基础系列

描述

在实例内复制一个数据库。

说明
  • 实例剩余空间不得小于当前数据库大小的1.3倍。

  • 不支持专属集群MyBase SQL Server实例使用。

使用方法

USE db
GO
EXEC sp_rds_copy_database 'db','db_copy'
GO
  • 第一个参数是被拷贝的数据库。

  • 第二个参数是拷贝的目标数据库。

设置数据库在线

T-SQL命令

sp_rds_set_db_online

支持的实例

  • 高可用系列

  • 基础系列

描述

您将数据库设置为OFFLINE后,不能直接通过ALTER DATABASE设置为ONLINE,请使用本存储过程设置为ONLINE。

使用方法

USE master
GO
EXEC sp_rds_set_db_online 'db'
GO

参数是指定要被设置ONLINE的数据库。

数据库全局授权

T-SQL命令

sp_rds_set_all_db_privileges

支持的实例

  • 高可用系列

  • 基础系列

描述

为一个用户授予所有或多个用户数据库的权限。

说明

授权时,当前用户对被授权数据库的权限必须大于或等于被授予的权限。

使用方法

sp_rds_set_all_db_privileges 'user','db_owner','db1,db2...'
  • 第一个参数是被授权的用户。

  • 第二个参数是授予该用户的数据库角色。

  • 第三个参数是数据库,可以指定一个或者多个,用逗号分隔,也可以不指定该参数(不指定表示全部用户数据库)。

删除数据库

T-SQL命令

sp_rds_drop_database

支持的实例

高可用系列

说明
  • 基础系列实例暂不支持本存储过程,请使用DROP DATABASE db

  • 请在非目标库的命令窗口下使用高权限账号执行该命令,并确保该账号拥有目标数据库的操作权限。更多操作,请参见修改账号权限

描述

删除实例中的数据库。删除时会将关联的对象移除掉,高可用系列会自动将镜像移除,并且KILL在该数据库上的连接。

使用方法

USE db
GO
EXEC sp_rds_drop_database 'db'
GO

参数是要被删除的数据库。

设置更改跟踪

T-SQL命令

sp_rds_change_tracking

支持的实例

高可用系列

描述

设置数据库的更改跟踪标记。

使用方法

USE db
GO
EXEC sp_rds_change_tracking 'db',1
GO
  • 第一个参数是数据库名称。

  • 第二个参数表示是否启用。

    • 1:启用。

    • 0:禁用。

开启数据库变更捕获

T-SQL命令

sp_rds_cdc_enable_db

支持的实例

高可用系列、集群系列

描述

启用数据库的数据变更捕获。

使用方法

USE db
GO
-- 启用数据库级别的变更捕获(CDC)
EXEC sp_rds_cdc_enable_db
GO
-- 启动指定表的变更捕获(CDC)
EXEC sys.sp_cdc_enable_table
    @source_schema = '<模式名称>',
    @source_name = '<表名称>',
    @role_name = '<CDC角色名称>'

关闭数据库变更捕获

T-SQL命令

sp_rds_cdc_disable_db

支持的实例

高可用系列、集群系列

描述

关闭数据库的数据变更捕获。

使用方法

USE db
GO
-- 关闭数据库级别的变更捕获(CDC)
EXEC sp_rds_cdc_disable_db
GO
-- 关闭指定表的变更捕获(CDC)
EXEC sys.sp_cdc_disable_table
    @source_schema = '<模式名称>',
    @source_name = '<表名称>',
    @capture_instance = '<CDC捕获实例名称>'
    
-- 获取特定表的CDC捕获实例名称的方法
SELECT capture_instance
FROM cdc.change_tables
WHERE source_schema = '<模式名称>'
    AND source_name = '<表名称>'

配置实例参数

T-SQL命令

sp_rds_configure

支持的实例

  • 高可用系列

  • 基础系列

描述

设置实例参数。若有主备实例,会自动同步。目前支持的参数如下,更多参数使用详情,请参见微软官方文档

参数项

描述

使用示例

fill factor (%)

指定索引页的填充因子百分比。

EXEC sp_rds_configure 'fill factor (%)', 90;

max worker threads

指定并行执行查询和处理请求的工作线程的最大数量。

EXEC sp_rds_configure 'max worker threads', 100;

cost threshold for parallelism

指定并行的开销阈值。

EXEC sp_rds_configure 'cost threshold for parallelism', 30;

max degree of parallelism

指定查询的最大并行度。

EXEC sp_rds_configure 'max degree of parallelism', 4;

min server memory (MB)

指定SQL Server实例使用的最小内存量。

EXEC sp_rds_configure 'min server memory (MB)', 1024;

max server memory (MB)

指定SQL Server实例使用的最大内存量。

EXEC sp_rds_configure 'max server memory (MB)', 4096;

blocked process threshold (s)

指定被阻塞进程的阈值。

EXEC sp_rds_configure 'blocked process threshold (s)', 20;

nested triggers

指定是否启用嵌套触发器。取值如下:

  • 0:禁用。

  • 1:启用。

说明

202305月前创建的实例不支持该参数。

EXEC sp_rds_configure 'nested triggers', 1;

Ad Hoc Distributed Queries

指定是否启用即席分布式查询。取值如下:

  • 0:禁用。

  • 1:启用。

说明

202305月前创建的实例不支持该参数。

EXEC sp_rds_configure 'Ad Hoc Distributed Queries', 1;

clr enabled

指定是否启用CLR (Common Language Runtime)。取值如下:

  • 0:禁用。

  • 1:启用。

说明

202305月前创建的实例不支持该参数。

EXEC sp_rds_configure 'clr enabled', 1;

default full-text language

指定默认的全文搜索语言。常用取值如下:

  • 0:默认语言。默认语言由操作系统的区域设置决定。

  • 1033:英语。

  • 2052:简体中文。

点击展开查看全部取值

取值

语言名称(英文)

中文释义

0

Neutral

中立语言

1025

Arabic

阿拉伯语

1026

Bulgarian

保加利亚语

1027

Catalan

加泰罗尼亚语

1028

Traditional Chinese

繁体中文

1029

Czech

捷克语

1030

Danish

丹麦语

1031

German

德语

1032

Greek

希腊语

1033

English

英语

1036

French

法语

1037

Hebrew

希伯来语

1039

Icelandic

冰岛语

1040

Italian

意大利语

1041

Japanese

日语

1042

Korean

韩语

1043

Dutch

荷兰语

1044

Bokmål

挪威语(博克莫尔)

1045

Polish

波兰语

1046

Brazilian

巴西葡萄牙语

1048

Romanian

罗马尼亚语

1049

Russian

俄语

1050

Croatian

克罗地亚语

1051

Slovak

斯洛伐克语

1053

Swedish

瑞典语

1054

Thai

泰语

1055

Turkish

土耳其语

1056

Urdu

乌尔都语

1057

Indonesian

印度尼西亚语

1058

Ukrainian

乌克兰语

1060

Slovenian

斯洛文尼亚语

1062

Latvian

拉脱维亚语

1063

Lithuanian

立陶宛语

1066

Vietnamese

越南语

1081

Hindi

印地语

1086

Malay - Malaysia

马来语(马来西亚)

1093

Bengali (India)

孟加拉语(印度)

1094

Punjabi

旁遮普语

1095

Gujarati

古吉拉特语

1097

Tamil

泰米尔语

1098

Telugu

泰卢固语

1099

Kannada

卡纳达语

1100

Malayalam

马拉雅拉姆语

1102

Marathi

马拉地语

2052

Simplified Chinese

简体中文

2057

British English

英式英语

2070

Portuguese

葡萄牙语

2074

Serbian (Latin)

塞尔维亚语(拉丁文)

3076

Chinese (Hong Kong SAR, PRC)

中文(香港特别行政区)

3082

Spanish

西班牙语

3098

Serbian (Cyrillic)

塞尔维亚语(西里尔文)

4100

Chinese (Singapore)

中文(新加坡)

5124

Chinese (Macao SAR)

中文(澳门特别行政区)

说明

202305月前创建的实例不支持该参数。

EXEC sp_rds_configure 'default full-text language', 2052;

default language

指定默认的语言。常用取值如下:

  • 0:英语(美国)。

  • 30:简体中文。

点击展开查看全部取值

取值

语言名称(英文)

中文释义

0

English

英语(美国)

1

German

德语

2

French

法语

3

Japanese

日语

4

Danish

丹麦语

5

Spanish

西班牙语

6

Italian

意大利语

7

Dutch

荷兰语

8

Norwegian

挪威语

9

Portuguese

葡萄牙语

10

Finnish

芬兰语

11

Swedish

瑞典语

12

Czech

捷克语

13

Hungarian

匈牙利语

14

Polish

波兰语

15

Romanian

罗马尼亚语

16

Croatian

克罗地亚语

17

Slovak

斯洛伐克语

18

Slovenian

斯洛文尼亚语

19

Greek

希腊语

20

Bulgarian

保加利亚语

21

Russian

俄语

22

Turkish

土耳其语

23

British English

英语(英国)

24

Estonian

爱沙尼亚语

25

Latvian

拉脱维亚语

26

Lithuanian

立陶宛语

27

Brazilian Portuguese

巴西葡萄牙语

28

Traditional Chinese

繁体中文

29

Korean

韩语

30

Simplified Chinese

简体中文

31

Arabic

阿拉伯语

32

Thai

泰语

33

Norwegian (Bokmål)

挪威语(博克莫尔)

说明

202305月前创建的实例不支持该参数。

EXEC sp_rds_configure 'default language', 30;

max text repl size (B)

指定复制过程中的文本最大大小。

说明

202305月前创建的实例不支持该参数。

设置最大文本复制大小为100 MB:

EXEC sp_rds_configure 'max text repl size (B)', 104857600;

optimize for ad hoc workloads

指定是否启用针对即席工作负荷进行优化的动态管理视图。取值如下:

  • 0:禁用。

  • 1:启用。

说明

202305月前创建的实例不支持该参数。

EXEC sp_rds_configure 'optimize for ad hoc workloads', 1;

query governor cost limit

指定查询的最大运行时间(秒)。设置为0表示没有时间限制。

说明

202305月前创建的实例不支持该参数。

EXEC sp_rds_configure 'query governor cost limit', 10;

recovery interval (min)

指定恢复间隔。

说明

202305月前创建的实例不支持该参数。

EXEC sp_rds_configure 'recovery interval (min)', 60;

remote login timeout (s)

指定远程登录的超时时间。

说明

202305月前创建的实例不支持该参数。

EXEC sp_rds_configure 'remote login timeout (s)', 30;

remote query timeout (s)

指定远程查询的超时时间。

说明

202305月前创建的实例不支持该参数。

EXEC sp_rds_configure 'remote query timeout (s)', 60;

query wait (s)

指定查询在等待资源时的等待时间。

说明

202305月前创建的实例不支持该参数。

EXEC sp_rds_configure 'query wait (s)', 5;

min memory per query (KB)

指定每次查询占用的最小内存量。

说明

202305月前创建的实例不支持该参数。

EXEC sp_rds_configure 'min memory per query (KB)', 1024;

in-doubt xact resolution

指定系统如何处理不确定的分布式事务。取值如下:

  • 0(非集群版实例默认值):禁用自动解决。系统不会自动处理不确定的分布式事务,需要手动处理。

  • 1:假设自动提交。若系统对不确定的事务没有足够的信息来解决,将默认自动提交这些事务。

  • 2(集群版实例默认值):假设自动回滚。若系统遇到不确定的事务,将默认回滚这些事务。

EXEC sp_configure 'in-doubt xact resolution', 2;

使用方法

EXEC sp_rds_configure '<参数项>',<参数取值>
  • 第一个参数是要设置的实例配置参数项。

  • 第二个参数是该实例参数的值。

增加链接服务器Linked Server

T-SQL命令

sp_rds_add_linked_server

支持的实例

  • 实例系列:集群系列、高可用系列(不支持基础系列

  • 实例规格:通用型、独享型(不支持共享型

  • 计费方式:包年包月、按量付费(不支持Serverless实例

描述

增加实例的链接服务器。支持分布式事务,实例的链接服务器会在实例主备自动创建,HA切换后不需要再配置。更多详情,请参见自动或手动主备切换

使用方法

DECLARE
@linked_server_name sysname = N'yangzhao_slb', --Linked Server的名称
@data_source sysname = N'****.sqlserver.rds.aliyuncs.com,3888', --目标端SQL Server的IP和端口号,格式为IP,Port
@user_name sysname = N'ay15' , --目标端SQL Server的登录用户名
@password nvarchar(128) = N'******', --目标端SQL Server登录名对应的密码
@source_user_name sysname = N'test', --当前SQL Server实例上执行创建链接服务器的登录用户名
@source_password nvarchar(128) = N'******', --当前SQL Server实例上执行创建链接服务器的登录用户名对应的密码
--链接服务器的一些设置项,通过XML格式传递。本文示例设置项为数据访问、RPC及RPC out的权限
@link_server_options xml
= N'
      <rds_linked_server>
        <config option="data access">true</config>
        <config option="rpc">true</config>
        <config option="rpc out">true</config>
      </rds_linked_server>
'

EXEC sp_rds_add_linked_server
@linked_server_name,
@data_source,
@user_name,
@password,
@source_user_name,
@source_password,
@link_server_options

设置跟踪标志

T-SQL命令

sp_rds_dbcc_trace

支持的实例

  • 高可用系列

  • 基础系列

描述

设置实例的跟踪标记。目前只支持部分跟踪标记,若有主备实例,会自动同步。

使用方法

EXEC sp_rds_dbcc_trace '1222',1/0
  • 第一个参数是跟踪标记。

  • 第二个参数表示打开或关闭。

    • 1:打开。

    • 0:关闭。

更改数据库的名称

T-SQL命令

sp_rds_modify_db_name

支持的实例

  • 高可用系列

  • 集群系列

  • 基础系列

描述

更改数据库的名称。高可用系列和集群系列实例在更改名称后会自动重建主备关系,重建过程中会进行备份和还原,当数据库空间比较大时,需要注意当前实例的剩余可用空间。

使用方法

USE db
GO
EXEC sp_rds_modify_db_name 'db','new_db'
GO
  • 第一个参数是原数据库的名称。

  • 第二个参数是新数据库的名称。

Server级别角色授予

T-SQL命令

sp_rds_set_server_role

支持的实例

基础系列

描述

针对Login用户授予Server级别角色,角色范围包括setupadminprocessadmin,如需创建其他权限以及了解更多账号权限,请参见创建SA权限账号账号权限列表

使用方法

EXEC sp_rds_set_server_role @login_name='test_login',@server_role='setupadmin'
  • 第一个参数是账号名。

  • 第二个参数是角色名。角色支持setupadminprocessadmin

常见问题

Q:为什么使用普通权限的账号执行EXEC sp_rds_drop_database 'dbtest';命令后出现Cannot use KILL to kill your own process.报错?

A:请在非目标库的命令窗口下使用高权限账号执行该命令,并确保该账号拥有目标数据库的操作权限。更多操作,请参见修改账号权限