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_id
from sys.dm_os_workers as t1, sys.dm_os_schedulers t2
where t1.state = 'runnable'  and t1.scheduler_address = t2.scheduler_address
and t2.scheduler_id < 255
group 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_text
from sys.dm_exec_query_stats
order 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_text
from sys.dm_exec_query_stats
order  by execution_count desc 

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

select *
from sys.dm_exec_query_optimizer_info
where 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_text
from sys.dm_exec_query_stats
where plan_generation_num > 1
order 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_clerks
where type = 'memoryclerk_sqlbufferpool'

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

select  top 10 TYPE,
SUM(single_pages_kb) as stolen_mem_kb
from sys.dm_os_memory_clerks
group by TYPE
order by SUM(single_pages_kb) desc

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

SELECT TYPE, SUM(multi_pages_kb) as memory_allocated_KB
FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb != 0
group by type