监控数据库运行
查看数据库登录名信息
- 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'
查看最大工作线程数
- SELECT max_workers_count
- FROM sys.dm_os_sys_info
查看当前用户进程的会话ID
SELECT @@SPID
查询当前会话使用哪种协议
- SELECT net_transport
- FROM sys.dm_exec_connections
- WHERE session_id = @@SPID;
查看当前连接的会话信息
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:
- 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:
- 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
- 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进程执行的语句
- 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视图。
- 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存储过程
- 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:
- 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 ()
查看那些表缺少索引
下面语句功能强大,执行结果受统计信息的影响
- 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
查看应该被移除的索引
查看那些多余的、应该被移除的索引
SQL 1:
- SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
- INDEXNAME = I.NAME,
- I.INDEX_ID
- FROM SYS.INDEXES I
- JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
- WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
- AND I.INDEX_ID NOT IN(
- SELECT S.INDEX_ID
- FROM SYS.DM_DB_INDEX_USAGE_STATS S
- WHERE S.OBJECT_ID = I.OBJECT_ID
- AND I.INDEX_ID = S.INDEX_ID
- AND DATABASE_ID = DB_ID())
- ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC
SQL 2: 分析下面语句,移除那些没有必要的索引
- SELECT DB_NAME(database_id) AS N'DataBaseName' ,
- OBJECT_NAME(U.object_id) AS N'Table_Name' ,
- I.name AS N'Index_Name' ,
- user_seeks AS N'用户索引查找次数',
- user_scans AS N'用户索引扫描次数',
- last_user_seek AS N'最后查找时间' ,
- last_user_scan AS N'最后扫描时间' ,
- rows AS N'表中的行数'
- FROM sys.dm_db_index_usage_stats AS U
- INNER JOIN sys.indexes I ON U.index_id= I.index_idAND U.object_id= I.object_id
- INNER JOIN sysindexesT ON I.object_id = T.id
- WHERE database_id= DB_ID('DbName')
- AND OBJECT_NAME(U.object_id)='TableName'
- ORDER BY user_seeks, user_scans, object_name(U.object_id);