常用Sqlserver中的查詢語句

/* 連線數 */

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