ms sql数据库服务管理器(SQL日常维护管理脚本--第二部分)
ms sql数据库服务管理器(SQL日常维护管理脚本--第二部分)SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID; 6、查看当前连接的会话信息SELECT @@SPID 5、查询当前会话使用哪种协议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 A
概述前面已经介绍了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方面的内容,感兴趣的朋友可以关注一下~