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.configurationsNote:[$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]
The following command output is returned.
select * from sys.sysfiles - 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. - Run the following SQL statements to view all uncommitted transactions and their executed statements in an instance.
SELECT DB_NAME(dbid) AS DBNAME,
The following command output is returned.
(SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0 - 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 . - 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