/*查詢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--查詢當前會話中正在運行的任務和已完成任務的內部對象占用的空間,注:建立的這視圖要在上個數據庫內
/*情景架設*/
/*script1:create stored procedure*/
use adventureworks
go
create procedure dbo.updater
as
begin tran
update person.contact with (holdlock)
set firstname='Justin'
waitfor delay‘01：00：00’
rollback tran
go
/*script2:execute sp*/
select @@spid
exec dbo.updater
/*script3:select the blocked table*/
select * from person.contact
/*script4:what's going on*/
declare @sessionid int
set @sessionid=54-to be changed
declare @sqlhandle varbinary(64)
declare @planhandle varbinary(64)
select @sqlhandle=sql_handle,@planhandle=plan_handle
from sys.dm_exec_requests
where session_id=@sessionid
select * from sys.dm_exec_sql_text(@sqlhandle)
select * from sys.dm_exec_query_plan(@planhandle)
/*script5:who blocked me*/
select blocked_query.session_id as blocked_session_id,
blocking_query.session_id as blocking_session_id,
sql_text.text as blocking_text,
waits.wait_type as blocking_resource
from sys.dm_exec_requests blocked_query
join sys.dm_exec_requests blocking_query on
blocked_query.blocking_session_id=
blocking_query.session_id
cross apply
(select *
 from sys.dm_exec_sql_text(blocking_query.sql_handle)
)sql_text
join
sys.dm_os_waiting_tasks waits on
waits.session_id=blocking_query.session_id