阿里云数据库SQL Server运维须知

如何通过工具或者代码访问数据库?

详细的连接数据库操作请参见连接阿里云数据库 实例。如果通过公网访问需要在阿里云数据库 实例“详细信息”页面开通公网访问权限。

如何对阿里云数据库 SQL Server 实例的资源状况进行定期的全面检查?

您可开通“云顾问 Cloud Advisor”服务,定期获得云上资源在安全、可用性、性能、成本方面的检查报告。报告中包含若干阿里云数据库相关检查项目,如:阿里云数据库-实例停用、阿里云数据库-慢SQL报表/诊断、阿里云数据库-白名单访问风险、阿里云数据库-磁盘打满、阿里云数据库-闲置实例、阿里云数据库-高使用率、阿里云数据库-多可用区等。了解或开通云顾问服务,请前往云顾问主页。

阿里云数据库 SQL Server 实例的字符集使用有哪些区别?

控制台创建数据库时支持五种字符集,分别为:

  • Chinese_PRC_CI_AS
  • Chinese_PRC_CS_AS
  • SQL_Latin1_General_CP1_CI_AS
  • SQL_Latin1_General_CP1_CS_AS
  • Chinese_PRC_BIN

其中_ci的不分区大小写, _cs区分大写; _bin是按二进制排序,也区分大小写。

如何查看数据库的参数信息?

连接阿里云数据库 后执行如下 SQL 语句可查看

select * from sys.configurations

如何查看当前数据库的连接信息?

连接阿里云数据库 后执行如下 SQL 语句可查看:

select * from sys.dm_exec_connections

如何查看当前数据库的空间占用?

连接阿里云数据库 后执行如下 SQL 语句可查看数据库的空间占用:

use 数据库名; Exec sp_spaceused;

如何查看一个表的空间占用?

连接阿里云数据库 后执行如下 SQL 语句可查看:

use 表所在的数据库; Exec sp_spaceused '表名';

阿里云数据库 SQL Server 如何查询最大事务执行时间?

事务执行时间过长问题概述

长时间运行的事务可能会导致锁和拥堵的行为,查找出这些事务并处理可解决以上问题。

事务执行时间过长问题原因

在SQLServer中,每个DML操作都是一个事务,而无论他们是否在BEGIN TRANSACTION中执行。而长时间运行的事务可能会导致锁和拥堵的行为。

事务执行时间过长解决方案

用户可以通过如下SQL语句查找出长时间运行的事务,用于排除相应的问题。

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_typeWHEN 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_stateWHEN 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 TransactionStateFROM 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_idWHERE DATEDIFF(minute, AT.transaction_begin_time, GETDATE())>10 --找出运行时间大于10分钟的事务ORDER BY TransactionStartTimeGO

说明:1、sys.dm_tran_session_transactions:返回当前活动事务和会话的相关信息。2、sys.dm_tran_active_transactions:返回实例级别上,所有正在活动的事务信息。3、sys.dm_tran_database_transactions:返回数据库级别上的事务信息。

阿里云数据库 SQL Server 如何查询死锁问题?

死锁问题概述

数据库中的死锁是指两个或两个以上的进程在执行过程中,由于竞争资源等原因造成的数据库系统阻塞现象。

死锁问题原因

死锁是一种特殊情况,只有两个或者多个进程竞争同一组资源时才会出现,每个事务都阻止其他事务获得完成其工作所需的资源,从而造成的一种阻塞的现象,若无外力作用,它们都将无法推进下去。

死锁问题解决方案

用户如果在使用阿里云数据库 SQL Server 的时候遇到死锁导致的问题,可以通过以下的SQL命令查询出出现死锁的session_id。进而通过kill相应session解除死锁。

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 rstatusFROM sys.dm_tran_locksWHERE resource_type IN ('DATABASE', 'OBJECT')

说明:

sys.dm_tran_locks:返回当前事务的锁资源信息。

阿里云数据库 SQL Server 常见运维SQL命令

当用户在使用阿里云数据库 SQL Server 时候,整理服务器,数据库或者帐号等常见操作命令,如下:

服务器:

流程涉及命令查看sqlserver服务SC QUERY MSSQLSERVER启动sqlserver服务SC START MSSQLSERVER停止sqlserver服务SC STOP MSSQLSERVER

数据库:

流程涉及命令查询用户数据库SELECT Name FROM Master..SysDatabases where Name NOT IN ('master', 'tempdb', 'msdb', 'model') ORDER BY Name创建数据库CREATE DATABASE dbname COLLATE Chinese_PRC_CS_AS删除数据库ALTER DATABASE dbname SET PARTNER OFF;
ALTER DATABASE dbname SET SINGLE_USER with ROLLBACK IMMEDIATE;
DROP DATABASE dbname;压缩数据库日志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
D阿里云服务器 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查询数据库连接SELECT * FROM[Master].[dbo].[SYSPROCESSES] WHERE [DBID]
IN
(
SELECT
[DBID]
FROM
[Master].[dbo].[SYSDATABASES]
WHERE
NAME='dbname'
)查询数据库字符集SELECT name, collation_name FROM sys.databases WHERE name = N'dbname';
go

帐号:

流程涉及命令创建用户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 login username with default_schema=dbo;
use master;exec sp_addsrvrolemember 'username','processadmin';use dbname; exec sp_addrolemember 'db_datareader','username';
use master;exec sp_addsrvrolemember 'username','processadmin';exec sp_addsrvrolemember 'username','setupadmin';use dbname; exec sp_addrolemember 'db_owner','username';删除用户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
阿里云数据库 SQL Server 如何查询CPU占用TOP5的语句?

问题概述

当用户在使用阿里云数据库 SQL Server 时候,在资源视图查看到实例的CPU使用率持续处于高位,并且响应减慢。用户找到这些语句后可以进行相应优化。

问题原因

语句CPU占用较高可能是由于正常的复杂语句对CPU消耗大,也可能是睡眠连接过多、异常语句原因导致的。

问题解决方案

用户可以通过如下的SQL查询到CPU耗时TOP 5的SQL语句,对于相应的SQL语句,用户可以kill或者添加索引来优化相应的sql。

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;

说明:1、 sys.dm_exec_query_stats:返回在SQL服务器缓存的查询计划的性能统计信息。2、 sys.dm_exec_sql_text:返回根据sql_handle确定具体SQL语句。