FAQs About Operations

Last Updated：2020-07-20

How to access the database via tool or code?

For detailed connection to the database, see Connect to the RDS Instance. To access the database via Cloud network, you need to enable the Cloud network access privilege on the "Detailed Information" page of the RDS instance.

How to conduct regular all-around checkup to the RDS for SQL Server instance's resource status?

You can enable the "Cloud Advisor" service to obtain the test reports on security, availability, performance, and cost of cloud resources regularly. In the report, there are a multitude of RDS-related checkup items, such as RDS-instance disabling, RDS-slow SQL report form/diagnosis, RDS-whitelist access risk, RDS-disk full, RDS-idle instance, RDS-high usage rate, RDS-multi-AZ, etc. To learn about or activate the Cloud Advisor service, go to the Cloud Advisor homepage.

What differences exist in the usage of the RDS for SQL Server instance's character set?

When creating a database, the console supports five types of character sets, as detailed below:

Chinese_PRC_CI_AS
Chinese_PRC_CS_AS
SQL_Latin1_General_CP1_CI_AS
SQL_Latin1_General_CP1_CS_AS
Chinese_PRC_BIN

Of which, "_ci" is not case-sensitive, "_cs" is case-sensitive; "_bin" is sorted by the binary system, and also case-sensitive.

How to view the database's parameter information?

Connect to the RDS, and then execute the following SQL statement for viewing.

select * from sys.configurations

How to view the current database's connection information?

Connect to the RDS, and then execute the following SQL statement for viewing.

select * from sys.dm_exec_connections

How to view the current database's space occupancy?

Connect to the RDS, and then execute the following SQL statement for viewing the database's space occupancy.

use database name; Exec sp_spaceused;

How to view a table's space occupancy?

Connect to the RDS, and then execute the following SQL statement for viewing.

use the database where the table is located; Exec sp_spaceused 'table name';

How to query the maximum transaction execution time in the RDS for SQL Server?

Overview

Transaction in long-time running might result in lock and congestion problems. You should search out and dispose of these transactions to resolve the aforesaid problems.

Cause

In SQLServer，every DML action (SELECT, INSERT, UPDATE, DELETE, MERGE) represents one transaction, no matter whether or not they are executed in "BEGIN TRANSACTION". Transaction in long-time running might result in lock and congestion problems.

Solution

You are advised to search out the transactions in long-time running for troubleshooting by using the following SQL statement.

SELECT  ST.transaction_id AS TransactionID ,
    DB_NAME(DT.database_id) AS DatabaseName ,
    AT.transaction_begin_time AS TransactionStartTime ,
    DATEDIFF(minute, AT.transaction_begin_time, GETDATE()) AS Tran_run_time ,
    CASE AT.transaction_type
      WHEN 1 THEN 'Read/Write Transaction'
      WHEN 2 THEN 'Read-Only Transaction'
      WHEN 3 THEN 'System Transaction'
      WHEN 4 THEN 'Distributed Transaction'
    END AS TransactionType ,
    CASE AT.transaction_state
      WHEN 0 THEN 'Transaction Not Initialized'
      WHEN 1 THEN 'Transaction Initialized & Not Started'
      WHEN 2 THEN 'Active Transaction'
      WHEN 3 THEN 'Transaction Ended'
      WHEN 4 THEN 'Distributed Transaction Initiated Commit Process'
      WHEN 5 THEN 'Transaction in Prepared State & Waiting Resolution'
      WHEN 6 THEN 'Transaction Committed'
      WHEN 7 THEN 'Transaction Rolling Back'
      WHEN 8 THEN 'Transaction Rolled Back'
    END AS TransactionState
FROM    sys.dm_tran_session_transactions AS ST
    INNER JOIN sys.dm_tran_active_transactions AS AT ON ST.transaction_id = AT.transaction_id
    INNER JOIN sys.dm_tran_database_transactions AS DT ON ST.transaction_id = DT.transaction_id
WHERE DATEDIFF(minute, AT.transaction_begin_time, GETDATE())>10 --Find out transactions that run longer than 10 minutes
ORDER BY TransactionStartTime
GO

Notes:

sys.dm_tran_session_transactions: Returns correlation information for current active transactions and sessions.
sys.dm_tran_active_transactions: Returns information of all active transaction on the instance level.
sys.dm_tran_database_transactions: Returns transaction information on the database level.

How to query the deadlock problem via the RDS for SQL Server?

Overview

Deadlock in the database refers to the database system congestion resulting from such causes as contention for resources by two or more processes during the execution.

Cause

Deadlock is a specific condition when two or more processes contend for one resource, and every transaction stops the other transactions from acquiring resources required in their work, thus leading to congestion. Without external force, all transactions cannot be further executed.

Solution

If you encounter the problems resulting from deadlock in use of the RDS for SQL Server, you can query "session_id" in deadlock via the following SQL command. Then, kill corresponding sessions to resolve the deadlock.

SELECT request_session_id sessionid,

    resource_type type,

    resource_database_id dbid,

    OBJECT_NAME(resource_associated_entity_id,

    resource_database_id) objectname,

    request_mode rmode,

    request_status rstatus

FROM sys.dm_tran_locks

WHERE resource_type IN ('DATABASE', 'OBJECT')

Note:

sys.dm_tran_locks: returns lock resources information of current transactions.

RDS for SQL Server's frequent OPS SQL commands.

In use of the RDS for SQL Server, frequent OPS commands for managing server, database, or account are as follows:

Server

Process	Commands involved
View "sqlserver" service	SC QUERY MSSQLSERVER
Enable "sqlserver" service	SC START MSSQLSERVER
Disable "sqlserver" service	SC STOP MSSQLSERVER

Database

Process	Commands involved
Query user database	SELECT Name FROM Master..SysDatabases where Name NOT IN ('master', 'tempdb', 'msdb', 'model') ORDER BY Name
Create database	CREATE DATABASE dbname COLLATE Chinese_PRC_CS_AS
Delete database	ALTER DATABASE dbname SET PARTNER OFF; ALTER DATABASE dbname SET SINGLE_USER with ROLLBACK IMMEDIATE; DROP DATABASE dbname；
Compress the database log.	alter database dbname set partner off; USE master GO ALTER DATABASE dbname SET RECOVERY SIMPLE WITH NO_WAIT GO ALTER DATABASE dbname SET RECOVERY SIMPLE GO USE dbname GO DBCC SHRINKFILE (N'dbname_Log' , 11, TRUNCATEONLY) GO USE master GO ALTER DATABASE dbname SET RECOVERY FULL WITH NO_WAIT GO ALTER DATABASE dbname SET RECOVERY FULL GO
Query database connections	SELECT * FROM[Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT [DBID] FROM [Master].[dbo].[SYSDATABASES] WHERE NAME='dbname' )
Query database character set	SELECT name, collation_name FROM sys.databases WHERE name = N'dbname'; go

Account

Process	Commands involved
Create a User	if not exists (select name from sys.sql_logins where name='username' ) create login username with password='password',default_database=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; use dbname; if not exists (select name from sysusers where name='username' ) create user username for login username with default_schema=dbo; （readOnly）use master;exec sp_addsrvrolemember 'username','processadmin';use dbname; exec sp_addrolemember 'db_datareader','username'; （readWrite）use master;exec sp_addsrvrolemember 'username','processadmin';exec sp_addsrvrolemember 'username','setupadmin';use dbname; exec sp_addrolemember 'db_owner','username';
Delete User	USE dbname; if exists (select name from sysusers where name='username') DROP USER username; declare @str varchar(500);begin;set @str = '';select @str = @str+'kill '+ltrim(session_id)+';' from sys.dm_exec_sessions where login_name='username';exec(@str);end;if exists (select name from sys.sql_logins where name='username' ) DROP LOGIN username

Process

Commands involved

Create a User

if not exists (select name from sys.sql_logins where name='username' ) create login username with password='password',default_database=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
use dbname; if not exists (select name from sysusers where name='username' ) create user username for login username with default_schema=dbo;
（readOnly）use master;exec sp_addsrvrolemember 'username','processadmin';use dbname; exec sp_addrolemember 'db_datareader','username';
（readWrite）use master;exec sp_addsrvrolemember 'username','processadmin';exec sp_addsrvrolemember 'username','setupadmin';use dbname; exec sp_addrolemember 'db_owner','username';

Delete User

USE dbname; if exists (select name from sysusers where name='username') DROP USER username;
declare @str varchar(500);begin;set @str = '';select @str = @str+'kill '+ltrim(session_id)+';' from sys.dm_exec_sessions where login_name='username';exec(@str);end;if exists (select name from sys.sql_logins where name='username' ) DROP LOGIN username

How to query the statements with TOP5 CPU occupancy via the RDS for SQL Server?

Overview

In use of the RDS for SQL Server, you find via "Resource View" that the instance's CPU usage rate keeps maintaining at a high level, and the response slows down. You can search out these statements for optimization.

Failure Cause

The statement's high occupancy to CPU might result from such causes as high consumption of CPU by normal and complicated statements, too many sleeping connections, and abnormal statements.

Solution

You can search out Top 5 SQL statements regarding CPU occupancy via the following SQL. As for corresponding SQL statements, you can kill them or add an index to optimize them.

SELECT TOP 5

    total_worker_time/execution_count/1000/1000 AS [Avg CPU Time],

    Execution_count,

    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,

    ((CASE qs.statement_end_offset

    WHEN -1 THEN DATALENGTH(st.text)

    ELSE qs.statement_end_offset

    END - qs.statement_start_offset)/2) + 1) AS statement_text, getdate()

FROM sys.dm_exec_query_stats AS qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

    where total_worker_time/execution_count/1000/1000 > 1

    ORDER BY total_worker_time/execution_count DESC;

Notes:

sys.dm_exec_query_stats: returns the performance statistics information of the query plan cached on SQL server.
sys.dm_exec_sql_text: returns the specific SQL statements that are confirmed according to "sql_handle".

FAQs About RDS-for-MySQL

FAQs About RDS-for-PostgreSQL

百度智能云

Relational Database Service

FAQs About Operations

How to access the database via tool or code?

How to conduct regular all-around checkup to the RDS for SQL Server instance's resource status?

What differences exist in the usage of the RDS for SQL Server instance's character set?

How to view the database's parameter information?

How to view the current database's connection information?

How to view the current database's space occupancy?

How to view a table's space occupancy?

How to query the maximum transaction execution time in the RDS for SQL Server?

Overview

Cause

Solution

How to query the deadlock problem via the RDS for SQL Server?

Overview

Cause

Solution

RDS for SQL Server's frequent OPS SQL commands.

How to query the statements with TOP5 CPU occupancy via the RDS for SQL Server?

Overview

Failure Cause

Solution