MS SQL 日常维护管理常用脚本(二)

原创

潇湘隐者 2021-08-22 12:17:39 ©著作权

文章标签 SQLSERVER 常用sql DBA日常维护脚本常用脚本日常维护管理脚本 sql 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者潇湘隐者的原创作品，请联系作者获取转载授权，否则将追究法律责任

监控数据库运行

下面是整理、收集监控数据库运行的一些常用脚本，也是MS SQL 日常维护管理常用脚本(一)的续集，欢迎大家补充、提意见。

查看数据库登录名信息

Code Snippet

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;

查看数据库用户信息

SELECT * FROM sysusers;

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

方法1：用SSMS管理工具查看

方法2：脚本查询

查看用户角色

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'

查看最大工作线程数

Code Snippet

SELECT max_workers_count
FROM sys.dm_os_sys_info

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

SELECT @@SPID

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

Code Snippet

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

查看当前连接的会话信息

--进程号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 ;

查看正在执行的SQL语句

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

方法2： profile去跟踪，比较耗费资源。

方法3：

Code Snippet

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

方法4：

Code Snippet

SELECT m.session_id ,
m.start_time ,
m.command ,
m.wait_type ,
m.cpu_time ,
CAST(s.text AS VARCHAR(1000)) AS sqlText
FROMmaster.sys.dm_exec_requests m WITH ( NOLOCK )
CROSS APPLY fn_get_sql(m.sql_handle) s
SELECT r.session_id,
r.start_time ,
r.command ,
r.wait_type ,
r.cpu_time ,
s.text
FROMsys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

--查看某个会话ID正在执行的SQL

Code Snippet

SELECT m.session_id ,
m.start_time ,
m.command ,
m.wait_type ,
m.cpu_time ,
CAST(s.text AS VARCHAR(1000)) AS sqlText
FROMmaster.sys.dm_exec_requests m WITH ( NOLOCK )
CROSS APPLY fn_get_sql(m.sql_handle) s
WHEREm.session_id = 342
SELECT r.session_id ,
r.start_time ,
r.command ,
r.wait_type ,
r.cpu_time ,
s.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.seesion_id =342

查看SQL SERVER进程执行的语句

Code Snippet

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 ;
SELECTtext
FROM::fn_get_sql(@sql_handle) ;

查找TOP N语句

按平均 CPU 时间返回排名前十个的查询的相关信息。此示例将根据查询的查询哈希对查询进行聚合，以便按照查询的累积资源消耗来分组在逻辑上等效的查询。
--注意：SQL 2005 某些版本，没有sys.dm_exec_query_stats系统动态视图没有query_hash视图。

Code Snippet

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

查看会话阻塞/死锁信息

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

EXEC sp_who active

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

EXEC sp_who2 active

方法3：sp_lock 系统存储过程，报告有关锁的信息，但是不方便定位问题

方法4：sp_who_lock存储过程

Code Snippet

USE master;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE id=OBJECT_ID(N'sp_who_lock')
AND OBJECTPROPERTY(id, 'IsProcedure') =1)
DROP PROCEDURE sp_who_lock;
GO
--==================================================================================================
-- ProcedureName : sp_who_lock
-- Author : 作者不详,出自网络
-- CreateDate : 2013-05-13
-- Description : 查看阻塞和死锁信息
/**************************************************************************************************
Parameters : 参数说明
***************************************************************************************************
无参存储过程
***************************************************************************************************
Modified Date Modified User Version Modified Reason
***************************************************************************************************
2013-06-03 Kerry V01.00.01 调整存储过程格式,代码部分修改以及增加注释信息
***************************************************************************************************/
--=================================================================================================
CREATE PROCEDURE sp_who_lock
AS
BEGIN
DECLARE @spid INT;
DECLARE @block INT;
DECLARE @RowCount INT;
DECLARE @RowIndex INT;
--创建临时表，保持被阻塞或正阻塞其他SQL的SQL语句信息
CREATE TABLE #tmp_lock_who
(
id INT IDENTITY(1, 1) ,
spid SMALLINT ,
block SMALLINT
)
IF @@ERROR<>0 RETURN @@ERROR;
INSERT INTO #tmp_lock_who
(
spid ,
block
)
SELECT 0 ,
blocked
FROM ( SELECT *
FROM sysprocesses
WHERE blocked > 0
) a
WHERE NOT EXISTS( SELECT *
FROM ( SELECT *
FROM sysprocesses
WHERE blocked > 0
) b
WHERE a.blocked = spid )
UNION
SELECT spid ,
blocked
FROM sysprocesses
WHERE blocked > 0;
IF @@ERROR<>0 RETURN @@ERROR;
-- 找到临时表的记录数
SELECT@RowCount = COUNT(1) ,
@RowIndex = 1
FROM#tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR;
IF@RowCount=0
SELECT N'现在没有阻塞和死锁信息' AS MESSAGE;
-- -- 循环开始
WHILE @RowIndex <= @RowCount
BEGIN
-- 取第一条记录
SELECT @spid = spid,
@block = block
FROM #tmp_lock_who
WHERE Id = @RowIndex
IF @spid = 0
SELECT N'引起数据库死锁的是: ' + CAST(@block AS VARCHAR(10))
+ N'进程号,其执行的SQL语法如下';
ELSE
SELECT N'进程号SPID：' + CAST(@spid AS VARCHAR(10)) + N'被进程号SPID：'
+ CAST(@block AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下';
DBCC INPUTBUFFER(@block )
SET @RowIndex = @RowIndex + 1;
END;
DROP TABLE #tmp_lock_who;
RETURN 0;
END

方法5：右键服务器-选择“活动和监视器”，查看进程选项。注意“任务状态”字段。

方法6：右键服务名称-选择报表-标准报表-活动-所有正在阻塞的事务。

小结：总结之后，才发现居然有这么多方法，MGD，系统的整理、梳理知识点是非常有必要的，你能更全面、深入的了解。

查看内存状态

dbcc memorystatus

具体如何分析，请查看官方文档http://support.microsoft.com/kb/907877/zh-cn

查看脚本执行时间

方法1：查看SSMS管理器，查询窗口右下角
方法2:

Code Snippet

DECLARE @exectime DATETIME
SELECT@exectime = GETDATE()
--SQL 语句
PRINT N'SQL执行耗时:' + CONVERT(VARCHAR(10), DATEDIFF(ms, @exectime, GETDATE()))

方法3：

SET STATISTICS TIME ON

--SQL 语句

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

　　dbcc inputbuffer ()

查看那些表缺少索引

下面语句功能强大，执行结果受统计信息的影响

Code Snippet

SELECT sys.objects.name table_name,
mid.statement full_name,
(migs.avg_total_user_cost * migs.avg_user_impact) *(migs.user_seeks + migs.user_scans) AS Impact,
migs.avg_user_impact *(migs.user_seeks + migs.user_scans) Avg_Estimated_Impact,
'CREATE NONCLUSTERED INDEX IDX_' + sys.objects.name + '_N ON '
+ sys.objects.name COLLATE DATABASE_DEFAULT
+ ' ( ' + IsNull(mid.equality_columns, '')
+ CASE WHEN mid.inequality_columns IS NULL
THEN ''
ELSE
CASE WHEN mid.equality_columns IS NULL
THEN ''
ELSE ','
END + mid.inequality_columns
END + ' ) '
+ CASE WHEN mid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE (' + mid.included_columns + ')' END
+ ';' AS CreateIndexStatement,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) *(user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
--ORDER BY [Impact] DESC, [full_name] DESC
ORDER BY [table_name], [Impact] desc