VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > MongoDB >
  • SQL语句大全之监视SQL Server

监视SQL Server

1、监视工具

sp_who、sp_who2、sp_lock

2、性能监视器

三个方面:
(1)处理器使用率;
(2)磁盘活动;
(3)内存使用率;

3、查看锁定信息

SELECT l.resource_type, l.resource_associated_entity_id
,OBJECT_NAME(sp.OBJECT_ID) AS ObjectName
,l.request_status, l.request_mode,request_session_id
,l.resource_description
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions sp
 ON sp.hobt_id = l.resource_associated_entity_id
WHERE l.resource_database_id = DB_ID()

4、数据库中的索引使用情况

--------------------------------------------------------------------------------
IF OBJECT_ID('dbo.IndexUsageStats') IS NULL
CREATE TABLE dbo.IndexUsageStats
(
 IndexName sysname NULL
,ObjectName sysname NOT NULL
,user_seeks bigint NOT NULL
,user_scans bigint NOT NULL
,user_lookups bigint NOT NULL
,user_updates bigint NOT NULL
,last_user_seek datetime NULL
,last_user_scan datetime NULL
,last_user_lookup datetime NULL
,last_user_update datetime NULL
,StatusDate datetime NOT NULL
,DatabaseName sysname NOT NULL
)
 
GO
----Below query will give you index USED per table in a database.----
INSERT INTO dbo.IndexUsageStats
(
 IndexName
,ObjectName
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
,StatusDate
,DatabaseName
)
SELECT
 si.name AS IndexName
,so.name AS ObjectName
,diu.user_seeks
,diu.user_scans
,diu.user_lookups
,diu.user_updates
,diu.last_user_seek
,diu.last_user_scan
,diu.last_user_lookup
,diu.last_user_update
,GETDATE() AS StatusDate
,sd.name AS DatabaseName
FROM sys.dm_db_index_usage_stats  diu
JOIN sys.indexes si
  ON diu.object_id = si.object_id
 AND diu.index_id = si.index_id
JOIN sys.all_objects so
  ON so.object_id = si.object_id
JOIN sys.databases sd
  ON sd.database_id = diu.database_id
WHERE is_ms_shipped <> 1
  AND diu.database_id = DB_ID()
 
IF OBJECT_ID('dbo.NotUsedIndexes') IS NULL
CREATE TABLE dbo.NotUsedIndexes
(
 IndexName sysname NULL
,ObjectName sysname NOT NULL
,StatusDate datetime NOT NULL
,DatabaseName sysname NOT NULL
)
 
----Below query will give you index which are NOT used per table in a database.----
INSERT dbo.NotUsedIndexes
(
 IndexName
,ObjectName
,StatusDate
,DatabaseName
)
SELECT
 si.name AS IndexName
,so.name AS ObjectName
,GETDATE() AS  StatusDate
,DB_NAME()
FROM sys.indexes si
JOIN sys.all_objects so
  ON so.object_id = si.object_id
WHERE si.index_id NOT IN (
SELECT index_id
    FROM sys.dm_db_index_usage_stats diu
    WHERE si.object_id = diu.object_id
AND si.index_id = diu.index_id
                          )
AND so.is_ms_shipped <> 1

5、查看阻塞情况

SELECT
t1.resource_type
,t1.resource_database_id
,t1.resource_associated_entity_id
,OBJECT_NAME(sp.OBJECT_ID) AS ObjectName
,t1.request_mode
,t1.request_session_id
,t2.blocking_session_id
FROM sys.dm_tran_locks as t1
JOIN sys.dm_os_waiting_tasks as t2
  ON t1.lock_owner_address = t2.resource_address
LEFT JOIN sys.partitions sp
  ON sp.hobt_id = t1.resource_associated_entity_id

6、已连接用户的信息

SELECT login_name, count(session_id) as session_count
FROM sys.dm_exec_sessions
GROUP BY login_name

7、内存使用情况

SELECT
 name
,type
,SUM(single_pages_kb + multi_pages_kb) AS MemoryUsedInKB
FROM sys.dm_os_memory_clerks
GROUP BY name, type
ORDER BY SUM(single_pages_kb + multi_pages_kb) DESC

相关教程