偶然间在电脑里发现的很实用的东东 /* 连接数 */{转} 感觉还不错 收下了

 /* 连接数 */
select connectnum=count(distinct net_address)-1 from master..sysprocesses

/* 返回一组有关计算机和有关 SQL Server 可用资源及其已占用资源的有用杂项信息 */
select * from sys.dm_os_sys_info

/* 从操作系统返回内存信息 */
select * from sys.dm_os_sys_memory

/* 返回有关调用进程的虚拟地址空间中的页范围的信息 */
select * from sys.dm_os_virtual_address_dump

/* 命令提供了SQL Server的当前内存状态的快照，也可以作为我们分析内存瓶颈的重要依据 */
DBCC MemoryStatus

/* 计数器视图，主要对缓冲区管理器和内存管理器的一些计数器进行监控，比如页面的生存周期、检查点、惰性写入器和缓冲命中率等指标 */
select * from sys.dm_os_performance_counters

/* 缓冲池内数据库缓冲池中各个数据库的分布情况 */
select case database_id when 32767 then 'resourceDb' else db_name(database_id) end as database_name,count(*) as cached_pages_count
from sys.dm_os_buffer_descriptors
group by db_name(database_id),database_id
order by cached_pages_count desc;

/* 返回数据和日志文件的 I/O 统计信息 */
select * from sys.dm_io_virtual_file_stats(null,null)

/* 当前缓存的哪些批处理或过程占用了大部分 CPU 资源 */
SELECT TOP 50
      SUM(qs.total_worker_time) AS total_cpu_time,
      SUM(qs.execution_count) AS total_execution_count,
      COUNT(*) AS  number_of_statements,
      qs.sql_handle
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC

/* 缓存计划所占用的 CPU 总使用率 */
SELECT
      total_cpu_time,
      total_execution_count,
      number_of_statements,
      s2.text
      --(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM
      (SELECT TOP 50
            SUM(qs.total_worker_time) AS total_cpu_time,
            SUM(qs.execution_count) AS total_execution_count,
            COUNT(*) AS  number_of_statements,
            qs.sql_handle --,
            --MIN(statement_start_offset) AS statement_start_offset,
            --MAX(statement_end_offset) AS statement_end_offset
      FROM
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_worker_time) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2

/* CPU 平均占用率最高的前 50 个 SQL 语句 */
SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC

/* 查看 CPU、计划程序内存和缓冲池信息 */
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

/* 查询显示 SQL 等待分析和前 10 个等待的资源 */
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

/* 计算可运行状态下的工作进程数量，来观察CPU压力 */
SELECT COUNT( * ) as workers_waiting_for_cpu FROM sys.dm_os_workers
WHERE o.state = 'RUNNABLE'
GROUP BY s.scheduler_id

/* 检查闩锁等待统计信息以确定 I/O 瓶颈 */
select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats 
where wait_type like 'PAGEIOLATCH%'  and waiting_tasks_count > 0
order by wait_type

/* 系统中的每个工作线程 */
select * from sys.dm_os_workers

/* 系统中的所有SQLOS工作线程 */
select COUNT(*) from sys.dm_os_threads

/* SQL Server 实例中的每个活动任务 */
select * from sys.dm_os_tasks

/* 连接的所有用户名 */
select   hostname   from   master..sysprocesses   where   hostname <> ' '   group   by   hostname
/* 不同计算机连接的用户数 */
select   count(distinct(hostname))   from   master..sysprocesses   where   hostname <> ' '
/* 所有连接的用户数 */
select   count(hostname)   from   master..sysprocesses   where   hostname <> ' '

sp_monitor

sp_who     

Select * from master.dbo.spt_monitor

Select * from sys.dm_os_tasks

 

Select * from master..sysprocesses where hostname <> ' '

Select count(hostname) from master..sysprocesses where hostname <> ' '

Select * from sys.dm_os_tasks

 

Select connectnum=count(distinct net_address)-1 from master..sysprocesses

 

Select connectnum=count(distinct net_address)-1 from master..sysprocesses

 

 

select * from master.dbo.spt_monitor