要是像查看越来越多的锁,提供意气风发篇轻巧的监督mmsql 运维意况方法

图片 2

SQL
Server本身提供了这么一个DMV来返回这些信息,它就是sys.dm_exec_sessions
。 比如在我的机器上做一下查询:复制代码 代码如下:SELECT * FROM
sys.dm_exec_sessions WHERE host_name IS NOT NULL
如图:我们也可以根据登录名来分组:

 

代码如下复制代码

SELECT
SessionID = s.Session_id,
l.request_session_id spid,
a.blocked,
a.start_time,
a.ecid,
OBJECT_NAME(l.resource_associated_entity_id) tableName,
a.text,
resource_type,
DatabaseName = DB_NAME(resource_database_id),
request_mode,
request_type,
login_time,
host_name,
program_name,
client_interface_name,
login_name,
a.nt_domain,
nt_user_name,
s.status,
last_request_start_time,
last_request_end_time,
s.logical_reads,
s.reads,
request_status,
request_owner_type,
objectid,
dbid,
a.number,
a.encrypted ,
a.blocking_session_id
FROM
sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id =
s.session_id
LEFT JOIN
(
SELECT [Spid] = session_id ,
blocked,
sp.request_id,–请求ID
sp.cmd,
text,
ecid ,
[Database] = DB_NAME(sp.dbid) ,
[User] = nt_username ,
[Status] = r.status ,
[Wait] = wait_type ,
sp.sql_handle,
Program = program_name ,
hostname ,
nt_domain ,
start_time,
objectid,
sp.dbid,
number,
encrypted ,
blocking_session_id
FROM sys.dm_exec_requests r
INNER JOIN sys.sysprocesses sp ON r.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
) a ON s.session_id = a.Spid
WHERE
s.session_id > 50 and l.resource_type = ‘OBJECT’
and start_time < DATEADD( MI,-2,GETDATE()) –执行时间超过2分钟

复制代码 代码如下:SELECT login_name ,COUNT(session_id) AS
session_count FROM sys.dm_exec_sessions GROUP BY login_name;

如果像查看更多的锁,调整where条件即可

dm_os_*:内存、锁定和时间安排

查询结果如图:

 

下面的示例查询显示已重新编译的前 25
个存储过程。plan_generation_num指示该查询已重新编译的次数。

对于这张表的详细字段说明,请参考MSDN教程x

查看被阻塞的语句和它们的等待时间

select cpu_count, hyperthread_ratio, scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb, bpool_visible
* 8 / 1024 as bpool_visible_mb from sys.dm_os_sys_info

图片 1

以下是使用此存储过程的示例。

数据库级别等待的IO

I/O 瓶颈检查闩锁等待统计信息以确定 I/O 瓶颈。运行下面的 DMV 查询以查找
I/O 闩锁等待统计信息。

 

代码如下复制代码 SELECT highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time, q.dbid, q.objectid, q.number,
q.encrypted, q.[text] from (select top 50 qs.plan_handle,
qs.total_worker_time from sys.dm_exec_query_stats qs order by
qs.total_worker_time desc) as highest_cpu_queries cross apply
sys.dm_exec_sql_text(plan_handle) as q order by
highest_cpu_queries.total_worker_time desc

图片 2

select blocking_session_id, wait_duration_ms, session_id from
sys.dm_os_waiting_tasks where blocking_session_id is not null

结果如下:

常规服务器动态管理对象包括:

SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT
    ISNULL(DB_NAME(database_id), ‘ResourceDb’) AS DatabaseName
    , CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))
                                                   AS [Size (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DatabaseName

select database_id, file_id, io_stall, io_pending_ms_ticks,
scheduler_address from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2 where t1.file_handle =
t2.io_handle

结果如图:

SELECT * FROM sys.system_objects WHERE name LIKE ‘dm_%’ ORDER BY name

 

exec sp_block exec sp_block @spid = 7

SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT DB_NAME(database_id) AS [DatabaseName]
  , file_id
  , SUM(CAST(io_stall / 1000.0 AS DECIMAL(20,2))) AS [IO stall
(secs)]
  , SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20,2)))
                                                     AS [IO read
(MB)]
  , SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0  AS
DECIMAL(20,2)))
                                                  AS [IO written
(MB)]
                                                    ,
SUM(CAST((num_of_bytes_read + num_of_bytes_written)
                / 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [TotalIO
(MB)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id, file_id
ORDER BY [IO stall (secs)] DESC

下面的查询显示缓存计划所占用的 CPU 总使用率。

  • 1,
        ((CASE WHEN BlockedReq.statement_end_offset = -1
          THEN LEN(CONVERT(NVARCHAR(MAX), BlockedSQL.text)) * 2
          ELSE BlockedReq.statement_end_offset
          END – BlockedReq.statement_start_offset)/2) + 1)
                        AS [Blocked Individual Query]
      , Waits.wait_type
    FROM sys.dm_exec_connections AS Blocking                         
    INNER JOIN sys.dm_exec_requests AS Blocked
                ON Blocking.session_id = Blocked.blocking_session_id
    INNER JOIN sys.dm_exec_sessions Sess
                ON Blocking.session_id = sess.session_id 
    INNER JOIN sys.dm_tran_session_transactions st
                ON Blocking.session_id = st.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er
                ON st.session_id = er.session_id
                    AND er.session_id IS NULL
    INNER JOIN sys.dm_os_waiting_tasks AS Waits
                ON Blocked.session_id = Waits.session_id
    CROSS APPLY
    sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)
                                 AS BlockingSQL
    INNER JOIN sys.dm_exec_requests AS BlockedReq                    
                ON Waits.session_id = BlockedReq.session_id
    INNER JOIN sys.dm_exec_sessions AS BlockedSess
                ON Waits.session_id = BlockedSess.session_id
    CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
    ORDER BY WaitInSeconds

select top 10 * from sys.dm_os_wait_stats –where wait_type not in
(‘CLR_SEMAPHORE’,’LAZYWRITER_SLEEP’,’RESOURCE_QUEUE’,’SLEEP_TASK’,’SLEEP_SYSTEMTASK’,’WAITFOR’)
order by wait_time_ms desc

 

代码如下复制代码

查看连接当前数据库的SPID所加的锁

示例查询您可以运行以下查询来获取所有 DMV 和 DMF 名称:

 

下面的查询显示哪个查询占用了最多的 CPU 累计使用率。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
  Waits.wait_duration_ms / 1000 AS WaitInSeconds
  , Blocking.session_id as BlockingSessionId
  , Sess.login_name AS BlockingUser
  , Sess.host_name AS BlockingLocation
  , BlockingSQL.text AS BlockingSQL
  , Blocked.session_id AS BlockedSessionId
  , BlockedSess.login_name AS BlockedUser
  , BlockedSess.host_name AS BlockedLocation
  , BlockedSQL.text AS BlockedSQL
  , DB_NAME(Blocked.database_id) AS DatabaseName
FROM sys.dm_exec_connections AS Blocking                        
INNER JOIN sys.dm_exec_requests AS Blocked
            ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_exec_sessions Sess
            ON Blocking.session_id = sess.session_id 
INNER JOIN sys.dm_tran_session_transactions st
            ON Blocking.session_id = st.session_id
LEFT OUTER JOIN sys.dm_exec_requests er
            ON st.session_id = er.session_id
                AND er.session_id IS NULL
INNER JOIN sys.dm_os_waiting_tasks AS Waits
            ON Blocked.session_id = Waits.session_id
CROSS APPLY
sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)
                                     AS BlockingSQL
INNER JOIN sys.dm_exec_requests AS BlockedReq                   
            ON Waits.session_id = BlockedReq.session_id
INNER JOIN sys.dm_exec_sessions AS BlockedSess
            ON Waits.session_id = BlockedSess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
WHERE Waits.wait_duration_ms > 30000
ORDER BY WaitInSeconds

效率较低的查询计划可能增大 CPU 占用率。

当前数据库中每个表所占缓存的大小和页数

使用此调用可找出 blocking_session_id 所返回的 SQL。例如,如果
blocking_session_id 是 87,则运行此查询可获得相应的 SQL。

相关文章

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图