MS SQL 日常维护管理常用脚本(二)
SQL 管理 常用 脚本 维护 日常 MS
2023-09-14 08:58:21 时间
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
--进程号1--50是SQL Server系统内部用的
( 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
--================================================================================================== -- 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;
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; DROP TABLE #tmp_lock_who; RETURN 0;
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
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
基于jsp+mysql+Spring的SpringBoot招聘网站项目(完整源码+sql)主要实现了管理员登录,简历管理,问答管理,职位管理,用户管理,职位申请进度更新,查看简历 运行环境: java jdk 1.8, IDE环境: IDEA tomcat环境: Tomcat 7.x,8.x,9.x版本均可 主要功能说明: 管理员登录,简历管理,问答管理,职位管理,用户管理,职位申请进度更新,查看简历等功能。
PolarDB-X 1.0-用户指南-SQL审计与分析-高级管理 PolarDB-X SQL审计与分析支持高级管理,您可以通过高级管理跳转到日志服务控制台,修改SQL日志的存储时间、对SQL日志进实时订阅与消费、数据投递和对接其他可视化等高级操作。 开启SQL日志审计后,在当前页面右上角单击高级管理可以跳转到日志服务控制台,修改日志存储时间、配置日志消费等高级操作。 由于相同地区PolarDB-X数据库的SQL审计日志存储在同一Logstore中,如果修改了Logstore的审计日志存储时间,该修改会对该地域下所有PolarDB-X数据库生效。即仅支持修改同一地域所有数据库的日志保存时间,不支持修改某一数据库的日志保存时间。
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
--进程号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:
( 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
查找TOP N语句
按平均 CPU 时间返回排名前十个的查询的相关信息。此示例将根据查询的查询哈希对查询进行聚合,以便按照查询的累积资源消耗来分组在逻辑上等效的查询。
--注意:SQL 2005 某些版本,没有sys.dm_exec_query_stats系统动态视图没有query_hash视图。
方法1:查看那个引起阻塞,查看blk不为0的记录,如果存在阻塞进程,则是该阻塞进程的会话 ID。否则该列为零。
EXEC sp_who active
方法2:查看那个引起阻塞,查看字段BlkBy,这个能够得到比sp_who更多的信息。
EXEC sp_who2 active
方法3:sp_lock 系统存储过程,报告有关锁的信息,但是不方便定位问题
方法4:sp_who_lock存储过程
--================================================================================================== -- 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;
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; DROP TABLE #tmp_lock_who; RETURN 0;
方法5:右键服务器-选择“活动和监视器”,查看进程选项。注意“任务状态”字段。
方法6:右键服务名称-选择报表-标准报表-活动-所有正在阻塞的事务。
小结:总结之后,才发现居然有这么多方法,MGD,系统的整理、梳理知识点是非常有必要的,你能更全面、深入的了解。
查看内存状态
dbcc memorystatus
具体如何分析,请查看官方文档http://support.microsoft.com/kb/907877/zh-cn
查看脚本执行时间
方法1: 查看SSMS管理器,查询窗口右下角
方法2:
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
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
基于jsp+mysql+Spring的SpringBoot招聘网站项目(完整源码+sql)主要实现了管理员登录,简历管理,问答管理,职位管理,用户管理,职位申请进度更新,查看简历 运行环境: java jdk 1.8, IDE环境: IDEA tomcat环境: Tomcat 7.x,8.x,9.x版本均可 主要功能说明: 管理员登录,简历管理,问答管理,职位管理,用户管理,职位申请进度更新,查看简历等功能。
PolarDB-X 1.0-用户指南-SQL审计与分析-高级管理 PolarDB-X SQL审计与分析支持高级管理,您可以通过高级管理跳转到日志服务控制台,修改SQL日志的存储时间、对SQL日志进实时订阅与消费、数据投递和对接其他可视化等高级操作。 开启SQL日志审计后,在当前页面右上角单击高级管理可以跳转到日志服务控制台,修改日志存储时间、配置日志消费等高级操作。 由于相同地区PolarDB-X数据库的SQL审计日志存储在同一Logstore中,如果修改了Logstore的审计日志存储时间,该修改会对该地域下所有PolarDB-X数据库生效。即仅支持修改同一地域所有数据库的日志保存时间,不支持修改某一数据库的日志保存时间。
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
相关文章
- Oracle必会的基础SQL-管理维护
- SQL Server修改数据库对象所有者(Owner)浅析
- SQL Tune Report–sqltrpt.sql
- ORA-06502:PL/SQL :numberic or value error: character string buffer too small
- java.sql.SQLException: The SQL statement must not be null or empty.这个错误
- sql 语句系列(字符串之父与子之间)[八百章之第十二章]
- 设置SQL*Plus 的AUTOTRACE
- SQL Server 字符串处理函数
- 【学习总结】SQL的学习-2-sql操作
- mysql 下 计算 两点 经纬度 之间的距离 含具体sql语句
- SQL User-Agent注入详解
- ROW_NUMBER (Transact-SQL)
- SQL SERVER服务器链接连接(即sql server的跨库连接)
- Sql Server中sql语句自动换行
- SQL SERVER服务器链接连接(即sql server的跨库连接)
- SQL SERVER服务器链接连接(即sql server的跨库连接)
- DBMS/Database:数据库管理的简介、安装(注意事项等)、学习路线(基于SQLSever深入理解SQL命令语句综合篇《初级→中级→高级》/几十项代码案例集合)之详细攻略
- 怎样能写出性能优良的SQL语句 从sql语句提高数据库的性能
- SQL基础【一、SQL简介】
- SQL基础【十八、事物】(sql事物慎用,还是写业务逻辑代码好一些,入伙涉及到更换数据啥的很麻烦!)
- 【sql优化】(大表小技巧)有时候 2 小时的 SQL 操作,可能只要 1 分钟
- SQL Server 权限管理
- 【SQL开发实战技巧】系列(二十八):数仓报表场景☞人员分布问题以及不同组(分区)同时聚集如何实现
- 【SQL开发实战技巧】系列(十八):数据仓库中时间类型操作(进阶)INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
- Delphi中SQL批量插入记录
- sql server数据库数据查询成功
- SQL Server 管理套件(SSMS)