概述

前面已经介绍了MS SQL日常维护管理脚本第一部分，今天继续第二块的内容，主要是整理、收集监控数据库运行的一些常用脚本。

监控数据库运行

1、查看数据库登录名信息

SELECT name AS LoginName ,
 dbname AS DefaultDB ,
 createdate AS CreateDate,
 updatedate AS UpdateDate,
 language AS Language ,
 CASE WHEN isntname = 1 THEN 'NT USER'
 ELSE 'SQL USER' END AS UserType
FROM syslogins;

2、查看数据库用户信息

SELECT * FROM sysusers;

3、查看用户拥有的服务器角色

查看用户角色

SELECT name ,
 CASE WHEN sysadmin = 1 THEN 'yes' ELSE '' END AS IsSysadmin ,
 CASE WHEN dbcreator = 1 THEN 'yes' ELSE '' END AS IsDbCreate ,
 CASE WHEN securityadmin= 1 THEN 'yes' ELSE '' END AS IsSecurityadmin ,
 CASE WHEN bulkadmin = 1 THEN 'yes' ELSE '' END AS IsBulkadmin ,
 CASE WHEN diskadmin = 1 THEN 'yes' ELSE '' END AS IsDiskadmin ,
 CASE WHEN processadmin = 1 THEN 'yes' ELSE '' END AS IsProcessadmin ,
 CASE WHEN serveradmin = 1 THEN 'yes' ELSE '' END AS IsServeradmin ,
 CASE WHEN setupadmin = 1 THEN 'yes' ELSE '' END AS IsSetupadmin 
FROM syslogins
--WHERE NAME='loginname'

4、查看当前用户进程的会话ID

SELECT @@SPID

5、查询当前会话使用哪种协议

SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

6、查看当前连接的会话信息

--进程号1--50是SQL Server系统内部用的

SELECT * FROM sys.dm_exec_sessions WHERE session_id >=51

--查看某台机器的连接会话信息

SELECT * FROM sys.dm_exec_sessions WHERE session_id >=51 AND host_name='PO130018801'

--查看某个登录名的连接会话信息

SELECT * FROM sys.dm_exec_sessions WHERE session_id >=51 AND login_name='username'

--查看活动的连接会话信息

SELECT * FROM sys.dm_exec_sessions WITH(NOWAIT) WHERE session_id >=51 AND status ='running'

--查找连接到服务器的用户并返回每个用户的会话数

SELECT login_name ,
 COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name ;

7、查看正在执行的SQL语句

方法1： 选择数据库实例，单击右键，选择”活动监视器“，监控/查看正在执行的SQL

方法2：

SELECT[Spid] = session_Id ,
 ecid ,
 [Database] = DB_NAME(sp.dbid) ,
 [User] = nt_username ,
 [Status] = er.status ,
 [Wait] = wait_type ,
 [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2,
 ( CASE WHEN er.statement_end_offset = -1
 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
 * 2
 ELSE er.statement_end_offset
 END - er.statement_start_offset ) / 2) ,
 [Parent Query] = qt.text ,
 Program = program_name ,
 Hostname ,
 nt_domain ,
 start_time
 FROMsys.dm_exec_requests er
 INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
 CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
 WHEREsession_Id >= 51

8、查看SQL SERVER进程执行的语句

USE master
DECLARE @spid INT ;
DECLARE @sql_handle BINARY(20) ;
SET @spid = 56
SELECT @sql_handle = sql_handle FROMsysprocesses AS A WITH ( NOLOCK ) WHEREspid = @spid ;
SELECT text FROM::fn_get_sql(@sql_handle) ;

9、查找TOP N语句

按平均 CPU 时间返回排名前十个的查询的相关信息。此示例将根据查询的查询哈希对查询进行聚合，以便按照查询的累积资源消耗来分组在逻辑上等效的查询。

--注意：SQL 2005 某些版本，没有sys.dm_exec_query_stats系统动态视图没有query_hash视图。

USE DBNAME;
GO
SELECT TOP 10 query_stats.query_hash AS "Query Hash",
 SUM(query_stats.total_worker_time) /
 SUM(query_stats.execution_count) AS "Avg CPU Time",
 MIN(query_stats.statement_text) AS "Statement Text"
FROM
 (SELECT QS.*,
 SUBSTRING(ST.text,(QS.statement_start_offset/2) + 1,
 ((CASE statement_end_offset
 WHEN -1 THEN DATALENGTH(st.text)
 ELSE QS.statement_end_offset END
 - QS.statement_start_offset)/2) + 1) AS statement_text
 FROM sys.dm_exec_query_stats AS QS
 CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO

10、查看会话阻塞/死锁信息

方法1：查看那个引起阻塞，查看blk不为0的记录，如果存在阻塞进程，则是该阻塞进程的会话 ID。否则该列为零。

EXEC sp_who active

方法2：查看那个引起阻塞，查看字段BlkBy，这个能够得到比sp_who更多的信息。

EXEC sp_who2 active

11、查看内存状态

dbcc memorystatus

12、查看进程正在执行的SQL语句

dbcc inputbuffer ()

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~