Overview

This article describes the system views and related query statements that are commonly used in daily use and maintenance of RDS SQL Server.

 

Detail

Alibaba Cloud reminds you that:

  • Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

If you use a client to connect to an instance, see connect to an instance.

 

Query

  • Run the following SQL statements in sequence to view the system parameter configuration:
    use [$DB_Name]
    select * from sys.configurations
    Note:[$DB_Name] is the database name.
    The following command output is returned.
    系统参数配置
    Note: For more information about the parameters, see sys.configurations.

  • Run the following SQL statements in sequence to view the files in the database:
    use [$DB_Name]
    select * from sys.sysfiles
    The following command output is returned.
    数据库文件信息
  • Run the following SQL statement to check the database file size:
    select name, convert(float,size) * (8192.0/1024.0)/1024 AS Size_MB,* from [$DB_Name].dbo.sysfiles
    The following command output is returned.
    数据库文件大小
  • Run the following SQL statement to view the I/O statistics of database files:
    select * from sys.dm_io_virtual_file_stats(DB_ID('[$DB_Name]'),[$File_ID])
    Note:[$File_ID] is the file ID you obtained in the previous step.
    The following command output is returned.
    I/O统计信息
  • Run the following SQL statements to view all uncommitted transactions and their executed statements in an instance.
    SELECT DB_NAME(dbid) AS DBNAME, 
    (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0
    The following command output is returned.
    事务
  • Run the following SQL statements to view data and index fragments:
    DBCC SHOWCONTIG
    A similar output is displayed. The data of the specified table or view and the index fragmentation status are displayed. For more information, see DBCCSHOWCONTIG .
    DBCC SHOWCONTIG
  • Run the following SQL statement to view the index fragments in the database:
    select * from sys.dm_db_index_physical_stats(DB_ID(N'[$DB_Name]'),NULL,NULL,NULL,DEFAULT)
    The following command output is returned.
    查看数据库中的索引碎片
  • Run the following SQL statement to view the recently executed statements:
    SELECT     p.spid, p.status, p.hostname, p.loginame, p.cpu, r.start_time, r.command,     p.program_name, text FROM     sys.dm_exec_requests AS r,     master.dbo.sysprocesses AS p     CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) WHERE     p.status NOT IN ('sleeping', 'background') AND r.session_id = p.spid
    The following command output is returned.
    查看近期执行语句

 

Application scope

  • ApsaraDB RDS for SQL Server