Check the common views of an ApsaraDB RDS for SQL Server instance - ApsaraDB RDS

This topic describes how to check and query the common system views of an ApsaraDB RDS for SQL Server instance.

Note

Take note of the following items:

Before you perform high-risk operations, such as modifying the configurations or data of Alibaba Cloud instances, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
Before you modify the configurations or data of an instance, such as an Elastic Compute Service (ECS) instance or an RDS instance, we recommend that you create snapshots or enable backup for the instance. For example, you can enable log backup for an RDS instance.
If you granted permissions on sensitive information or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity. Sensitive information includes usernames and passwords.

Procedure

Connect to the RDS instance from a client. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.
Execute the following SQL statements in sequence to view system parameter settings:
```
USE [$DB_Name];
SELECT * FROM sys.configurations;
```
Note
[$DB_Name] specifies the database name. For more information about the parameters, see sys.configurations.
The following figure shows a sample output.
Execute the following SQL statements in sequence to view the file-related information about the database:
```
USE [$DB_Name];
SELECT * FROM sys.sysfiles;
```
The following figure shows a sample output.
Execute the following SQL statement to view the size of the database file:
```
SELECT name, CONVERT(float,size) * (8192.0/1024.0)/1024 AS Size_MB,* from [$DB_Name].dbo.sysfiles;
```
The following figure shows a sample output.
Execute the following SQL statement to view the I/O statistics of the database file:
```
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID('[$DB_Name]'), [$File_ID]);
```
Note
You must set the [$File_ID] parameter to the value of the fileid parameter that is obtained from Step 4.
The following figure shows a sample output.
Execute the following SQL statement to view all uncommitted transactions on the RDS instance and the statements that are executed in the transactions:
```
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 figure shows a sample output.
Execute the following SQL statement to view the data and index fragments:
```
DBCC SHOWCONTIG;
```
The following figure shows a sample output. The output displays the data and index fragments of specific tables or views. For more information about the parameters, see DBCC SHOWCONTIG.
Execute 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 figure shows a sample output.

Execute the following SQL statement to view 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 figure shows a sample output.