監視服務器性能的語句

 以下語句只能在SQLSERVER2005和2008上使用
/*查詢CPU瓶頸問題*/
select scheduler_id,current_tasks_count,runnable_tasks_count from sys.dm_os_schedulers where scheduler_id<255 --runnable_tasks_count 為非0，則可能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.plan_handle from sys.dm_exec_query_stats qs group by qs.plan_handle order by sum(qs.total_worker_time)desc --查詢哪一組T-SQL或進程占用了最多的CPU時間
select spid,startTime,textdata,eventsubclass,objectid,databaseid,sqlhandle from fn_trace_gettable('e:\recompiletrace.trc',1) where eventclass in(37,75,166)--對事件數據的分析，之前要建立trace(包含SP的Recompile,SQL的StmtRecompile),查詢過 度編譯和重編譯的進程
select * from sys.dm_exec_query_optimizer_info--查詢剛才CPU優化的時間
select top 25 sql_text.text,sql_handle,plan_generation_num,execution_count,dbid,objectid from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num>1 order by plan_generation_num desc--查詢哪些T-SQL,SP重新編譯數量
/*tempdb瓶頸問題*/
/*sqlserver錯誤日志 :1101/1105 任何會話都必須分配tempdb中的空間;3959:版本存儲區已滿，此錯誤在日志中通常出現在錯誤1105或1101之后;3967:由于tempdb 已滿,版本存儲區被強制收縮;3958/3966:事務在tempdb中找不到所需的版本記錄*/
select sum(user_object_reserved_page_count)*8 as user_objects_kb,sum(internal_object_reserved_page_count)*8 as internal_objects_kb,sum(version_store_reserved_page_count)*8 as version_store_kb,sum(unallocated_extent_page_count)*8 as freespace_kb from sys.dm_db_file_space_usage where database_id=2--監視tempdb中的用戶數量,內部對象,版本存儲區使用的空間量以及可用空間量
select transaction_id from sys.dm_tran_active_snapshot_database_transactions order by elapsed_time_seconds desc--如果版本存儲區使用了tempdb中大量空間,則需要確定運行時間最長的事務
create view all_task_usage as select session_id,sum(internal_objects_alloc_page_count)as task_internal_objects_alloc_page_count,sum(internal_objects_dealloc_page_count) as task_internal_objects_dealloc_page_count from sys.dm_db_task_space_usage group by session_id--獲取每個會話中當前運行的所有任務中的內部對象占用的空間
create view all_session_usage as select r1.session_id,r1.internal_objects_alloc_page_count+r2.task_internal_objects_alloc_page_count as session_internal_objects_alloc_page_count,r1.internal_objects_dealloc_page_count+r2.task_internal_objects_dealloc_page_count as session_internal_objects_dealloc_page_count from sys.dm_db_session_space_usage as r1,all_task_usage as r2 where r1.session_id=r2.session_id--查詢當前會話中正在運行的任務和已完成任務的內部對象占用的空間,注:建立的這視圖要在上個 數據庫內

/*查詢死鎖*/
with tb
as
(
select blocking_session_id,
session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests  a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 as
(
select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)',
total_scheduled_time,reads,writes,logical_reads
from tb a inner join master.sys.dm_exec_sessions b
on a.session_id=b.session_id
)
select a.*,connect_time,client_tcp_port,client_net_address
        from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id
go

use master
go
select   a.spid, a.blocked
from     sysprocesses a, sysprocesses b
where  a.blocked > 0 and
            b.blocked > 0 and
            a.spid != b.spid and
            a.blocked = b.spid and
            b.blocked = a.spid
go