SQLSERVER 2005技术内幕：查询、调整和优化笔记（1）

dbcc 数据库控制台命令

 --检查数据库一致性

dbcc checkdb

--监控内存使用情况

dbcc memorystatus

--跟踪当前被阻塞的任务

select * from sys.dm_os_waiting_tasks

--采用粒度较小的锁如行锁则会增加对资源的消耗
--采用较为粗糙的锁如表锁则可能导致较为严重的阻塞
--sql语句尽量使用谓词，避免全表扫描

--检测可运行状态下的工作进行数量

select COUNT(*) as workers_wating_for_cpu, t2.scheduler_idfrom sys.dm_os_workers as t1, sys.dm_os_schedulers t2where t1.state = 'runnable'  and t1.scheduler_address = t2.scheduler_addressand t2.scheduler_id < 255group by t2.scheduler_id

--工作进程在可运行状态下话费的时间

select SUM(signal_wait_time_ms)from sys.dm_os_wait_stats 

--查询每次执行中占用cpu最多的前十位查询,也列出sql语句的查询计划以及这个计划被执行的次数
--显示当前北环村的查询合计统计信息

select top 10 total_worker_time / execution_count as avg_cpu_cost, plan_handle, execution_count,(    select SUBSTRING(text, statement_start_offset/2 + 1,        (            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_textfrom sys.dm_exec_query_statsorder by avg_cpu_cost DESC

--找出工作负荷中运行最频繁的查询

select top 10 total_worker_time, plan_handle, execution_count,(    select SUBSTRING    (        text,        statement_start_offset/2 + 1,        (            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_textfrom sys.dm_exec_query_statsorder  by execution_count desc 

--sql server在优化查询计划上话费的时间

select *from sys.dm_exec_query_optimizer_infowhere counter = 'optimizations' or counter = 'elapsed time'

--查询被编译得最多的前十位的查询计划

select top 10 plan_generation_num, execution_count,(    select SUBSTRING(        TEXT,        statement_start_offset/2 + 1,        (            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_textfrom sys.dm_exec_query_statswhere plan_generation_num > 1order by plan_generation_num desc

--查询缓冲池消耗的内存总量

select SUM        (            multi_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb +awe_allocated_kb        ) as [used by bpool, kb]from sys.dm_os_memory_clerkswhere type = 'memoryclerk_sqlbufferpool'

--查询哪些内部部件窃取缓冲区池中大部分的页面

select  top 10 TYPE,SUM(single_pages_kb) as stolen_mem_kbfrom sys.dm_os_memory_clerksgroup by TYPEorder by SUM(single_pages_kb) desc

--查询多页分配器来确认在缓冲区池外分配内存的内部组件

SELECT TYPE, SUM(multi_pages_kb) as memory_allocated_KBFROM sys.dm_os_memory_clerksWHERE multi_pages_kb != 0group by type