[SqlServer02]-数据库优化及管理常用命令

select * from sys.dm_os_schedulers dos;
select * from sys.dm_os_waiting_tasks dowt;
--高密度查询语句占用CPU资源
select * from sys.dm_os_wait_stats where wait_type = 'SOS_SCHEDULER_YIELD';
--最耗时的sql命令
select * from sys.dm_exec_query_stats deqs;
--查询前50个最耗时的sql命令
SELECT TOP 50 *, (total_worker_time / execution_count) AS avgworkertime
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle)
ORDER BY avgworkertime desc;
--查询最常用的50个sql指令
SELECT TOP 50 * 
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.plan_generation_num desc
--占用CPU时间最长的前50个sql指令
SELECT TOP 50
substring(ST.text, ( QS.statement_start_offset / 2 ) + 1, 
( ( case statement_end_offset when -1 then DATALENGTH (st.text) else QS.statement_end_offset end - QS.statement_start_offset ) / 2 ) + 1 ) as statement_text , 
total_worker_time / 1000 as total_worker_time_ms, 
execution_count, 
( total_worker_time / 1000 ) / execution_count as avg_worker_time_ms, 
total_logical_reads, 
total_logical_reads / execution_count as avg_logical_reads,
qp.query_plan 
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle ) st 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
ORDER BY total_worker_time desc;
--查询各数据库在内存中缓存的数据页情况
select * from sys.dm_os_buffer_descriptors dobd;
--------------data cache-------------
--查询各数据库占用的data cache的大小
--data cache 是数据库引擎最耗费内存的部分，用于缓存所有用户数据库与系统数据库的表数据页，索引数据页及各种空间管理数据页的内容
select case database_id when 32767 then 'resource' else db_name(database_id) end as db_name,count(*) as data_page_count from sys.dm_os_buffer_descriptors dobd
group by db_name(database_id),dobd.database_id order by 2;
--resource数据库的id编号是32767，使用db_name无法得到其名称
--sys.dm_os_buffer_descriptors的is_modified列的值标识一个数据页是否为脏数据页，1表示脏数据页，0表示干净
--数据页，查询干净数据页或者脏数据页的语句如下
--脏数据页
select case database_id when 32767 then 'resource' else db_name(database_id) end as db_name,count(*) as data_page_count from sys.dm_os_buffer_descriptors dobd
where is_modified = 1 group by db_name(database_id),dobd.database_id order by 2;
--干净数据页
select case database_id when 32767 then 'resource' else db_name(database_id) end as db_name,count(*) as data_page_count from sys.dm_os_buffer_descriptors dobd
where is_modified = 0 group by db_name(database_id),dobd.database_id order by 2;
-------------plan cache-------------------
--创建执行计划是十分消耗资源的任务，计划缓冲区专门缓存执行计划，以备下次使用时不必对其重新创建
--查询sys.dm_exec_cached_plans以及sys.dm_exec_sql_text可以得到与计划缓存相关的系统信息，如执行以下
--命令获取计划缓存中各个缓存类型所占的内存空间
select cacheobjtype,sum(size_in_bytes) as size  from sys.dm_exec_cached_plans decp group by cacheobjtype;
----------Buffer Cache--------------------
--Buffer 中的workspace用于完成排序和散列操作，如果一个操作涉及排序或散列操作，则可以在其实际执行计划中
--观察到workspace的大小
select * from pms.dbo.smt_Department sd;


sp_configure;