当前位置:
首页 > 数据库 > SQL Server 2008 教程 >
-
SQL语句大全之监视SQL Server
本站最新发布
SQL Server 2016数据库视频教程
试听地址 https://www.xin3721.com/eschool/SQLxin3721/
(1)处理器使用率;
(2)磁盘活动;
(3)内存使用率;
,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()
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
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
FROM sys.dm_exec_sessions
GROUP BY login_name
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
试听地址 https://www.xin3721.com/eschool/SQLxin3721/
监视SQL Server
1、监视工具
sp_who、sp_who2、sp_lock2、性能监视器
三个方面:(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、查看阻塞情况
SELECTt1.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_countFROM sys.dm_exec_sessions
GROUP BY login_name
7、内存使用情况
SELECTname
,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
最新更新
MongoDB 复制
vb.net教程之VB.NET中TextBox的智能感知应用实
数据库是如何定义的?
Mysql数据库
Python在Mysql中使用的数据类型
c#教程之运算符
变量
C#语法
C#入门简介
C#教程之C# 环境
MySQL LIKE 子句
MySQL 查询数据
MySQL 插入数据
MySQL 修改数据表
MySQL 删除数据表
MySQL 创建数据表
MySQL 数据类型
mysql教程-如何选择数据库
mysql教程-如何使用MySQL 删除数据库
如何MySQL 创建数据库
数据类型之对象
数据类型之布尔型、整型、浮点型和字符
php教程之数据类型之数组
php教程之PHP 常量
php教程之变量
php教程之语法
PHP简介与安装
phpMyAdmin配置安装全攻略
PHP是什么
win2003 iis整合php后 环境变量无法读取到