阿辉的博客

系统 网络 集群 数据库 分布式云计算等 研究

Performance Dashboard运行错误

Microsoft SQL Server 2005 Performance Dashboard Reports 是一组运行于 SQL Server 2005 SP2 Management Studio 及更高版本中的 Reporting Services 报表文件。他几乎统计了sql server 2005各个方面的报表,如cpu,内存,IO,未使用索引的查询等等,通过这些报表,SQL Server 用户可以更容易确定性能问题发生的时间以及可能存在的原因。这样可以更快地解决问题。

 

下载地址:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=22602

 

今天运行 Performance Dashboard报了一个错,之前所有安装过程都是挺正常的,但执行“performance_dashboard_main.rdl”报表时出现“两个datetime 列的差别导致了运行时溢出。”错误。

解决办法:

1) 修改setup.sql脚本中的procedure MS_PerfDashboard.usp_Main_GetSessionInfo的内容中的“convert(bigint, datediff(ms, login_time, getdate()))”为“convert(bigint,datediff(dd,login_time,getdate()))*24*60*60*1000”

2) 将recent_cpu.rdl中的“convert(bigint, datediff(ms, login_time, getdate()))”都换成“convert(bigint,datediff(dd,login_time,getdate()))*24*60*60*1000”就可以了 

然后再执行setup.sql重新安装,再次运行Performance Dashboard,没有错误了。

 

 

Sql Server SQL调优环境配置

–显示有关由Transact-SQL 语句运行的时间的信息
set statistics time on
–关闭有关由Transact-SQL 语句运行的时间的信息
set statistics time off

–显示有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO ON
–关闭有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO OFF

–显示[返回有关语句执行情况的详细信息,并估计语句对资源的需求]
SET SHOWPLAN_ALL  ON
–关闭[返回有关语句执行情况的详细信息,并估计语句对资源的需求]
SET SHOWPLAN_ALL  OFF

SQL Server2005性能调优(简化翻译版)

名次解释

DMVs:dynamic management views

三个点

· 资源瓶颈: CPU、内存、I/O(这里面不考虑网络问题)

· Tempdb瓶颈:

· User query瓶颈,可能是统计信息的变化、不恰当的索引、阻塞或者死锁等

上述三点,可能是相互影响的。

资源瓶颈

工具

1. System Monitor (PerfMon):windows自带

2. SQL Server Profiler: 2005继续有

3. DBCC commands: 参考联机文档

4. DMVs: 见上名次解释

CPU瓶颈

CPU瓶颈,是突然并且不可预料的。一般来讲,没有优化的查询计划、系统低配置、设计不合理等,很容易导致这些问题。

在perfmon中,我们一般需要监视Processor:% Processor Time,如果每个CPU持续高于80%,CPU就是瓶颈了。当然,在强大的2005下我们也可监视sys.dm_os_schedulers ,如果有内容,表明有任务等待CPU来分配给它。如下面这个DMVs的查询:

select scheduler_id,current_tasks_count,runnable_tasks_count from sys.dm_os_schedulers where scheduler_id < 255

下面的查询,更高级点。分析方法是,看结果的number_of_statements,如果该值大于1,说明可能有问题,要进一步分析。

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

执行计划的编译与重新编译

在sql2005中的一个改进,就是对于某个sp,进行recompile的时候,只需要针对改变的部分进行编译,sql2000只能是全部都搞一遍。

Recompile的原因很多,如:

· Schema的变更 changed

· Statistics变更

· 延迟编译

· SET option的执行

· 临时表的变化

· Sp使用了RECOMPILE提示或者使用了OPTION (RECOMPILE)

诊断方法,老朋友了,继续使用perfmon或者sql profiler。

对于perfmon,监视下面的 计数器

· SQL Server: SQL Statistics: Batch Requests/sec

· SQL Server: SQL Statistics: SQL Compilations/sec

· SQL Server: SQL Statistics: SQL Recompilations/sec

对于profiler抓到的trace,分析这几个event:SP:Recompile / SQL:StmtRecompile / Showplan XML For Query Compile。如果我们抓到了trace,对于文件,可以这么做:

select spid,StartTime,Textdata,EventSubclass,ObjectID,SQLHandle from fn_trace_gettable ( ‘e:recompiletrace.trc’ , 1) where EventClass in(37,75,166)

这里面,EventClass 37 = Sp:Recompile, 75 = CursorRecompile, 166=SQL:StmtRecompile

如果你事先保存到了 table jq中,那么把上面的from修改为from jq即可。

或者使用这个DMVs: sys.dm_exec_query_optimizer_info(注意一个技巧!多执行几次,看中间的差异)

select * from sys.dm_exec_query_optimizer_info

另外一个DMVs是:sys.dm_exec_query_stats,如执行这个sql:

select * from sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

另外, plan_generation_num标示出了被recompile的所有query。如下面这个

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

解决办法

· 如果因为使用了Set option,那么通过profiler来观察是什么引起的变化。

· 如果因为临时表,那么尽量使用表变量,而不是临时表。(对于表变量的限制,请查看联机文档)另一个解决办法,使用KEEP PLAN查询提示,这会把临时表当作普通表一样对待,会进行statistics的跟踪

· 关闭对于索引或者索引视图上的状态自动更新(偶个人不建议)

· 对象名称写全了,如dbo.Table1

· 尽量少用延迟编译。如你的SP或者query里面,有N多的if/else之类的。

· 运行索引调优向导(sql2000里面就有)

· 看看sp是不是使用了WITH RECOMPILE来建立的,或者RECOMPILE查询提示。

弱智的查询计划

每个查询执行之前,sqlserver都会“试图”优化一个最快的查询计划出来。注意的是,这里的最快的,不代表I/O最小,也不代表CPU占用最小。它是一个权衡后的值。

对于Hash join或者sort等,它们都是与CPU密切相关的。对于nested loop,很可能会因为大量的index lookups,导致I/O迅速上涨。如果search的数据散落在各个pages里面,很可能会导致缓冲命中率下降。

诊断方法

使用这个DMVs: sys.dm_exec_query_stats,它可以有效地监视CPU的使用情况。

select highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time,q.dbid,q.objectid,q.number,q.encrypted,q.[text] from (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc

解决办法

· 使用优化向导调优

· 检查一下,WHERE条件是不是限制的足够好?

游标问题

强烈建议,尽量减少使用游标。可以使用perfmon监视SQL Server:Cursor Manager By Type – Cursor Requests/Sec。或者使用DMVs:

select cur.* from sys.dm_exec_connections con cross apply sys.dm_exec_cursors(con.session_id) as cur where cur.fetch_buffer_size = 1 and cur.properties LIKE ‘API%’

如果使用profiler,可以监视sp_cursorfetch(前提是包含了RPC:Completed这个event class)

内存瓶颈

对于VAS和AWE概念,请自行查找MSDN。我印象中,M$的人强烈建议不要在32bit windows上面使用AWE或者3BG之类的东西。

检测内存问题

打开taskmgr看物理内存中的Avaiable,如果持续低于10M,恭喜你,系统内存压力太大!通过perfmon,监视Memory: Available Mbytes,一样的效果。

对于AWE使用,可以用这个DMVs来看:

select sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]  from sys.dm_os_memory_clerks

对于虚拟内存等,可以观察Commit Charge中的Total,与limit的对比。如果两者很接近,虚拟内存可能不够了。如果你好奇,想看看sqlserver自己的内存分配,可以执行DBCC MEMORYSTATUS。具体内容,见联机文档。

对于内存问题,偶认为采用sql的默认设置,一般情况下足够了。

I/O瓶颈

除非操作系统能够,并且内存足够大,把你的db放到物理内存里,否则,I/O我们永远回避不过去。使用perfmon的话,可以监视

· PhysicalDisk Object: Avg. Disk Queue Length,如果经常性的大于2*磁盘个数,磁盘有性能问题。

· Avg. Disk Sec/Read,如果<10ms,很好。20以下,一般。50以下,密切观察。50以上,换硬盘吧!

· Avg. Disk Sec/Write,这个和上面的两个值,如果持续大于物理磁盘的指标的85%,说明磁盘已经到极限了。

· Physical Disk: %Disk Time,一般如果超过50%,I/O有瓶颈。

如果用了raid,采用下面这个公式来计算:

Raid 0 — I/Os per disk = (reads + writes) / number of disks

Raid 1 — I/Os per disk = [reads + (2 * writes)] / 2

Raid 5 — I/Os per disk = [reads + (4 * writes)] / number of disks

Raid 10 — I/Os per disk = [reads + (2 * writes)] / number of disks

如下面这个例子,2个磁盘,raid1,监测到的结果:

Disk Reads/sec            80

Disk Writes/sec           70

Avg. Disk Queue Length    5

那么I/O平均是80/2+70=110,队列长度上限是2*2=4

解决办法

· 检查sqlserver的内存配置

· 增加或者替换更快的硬盘,读写缓存越高越好

· 检查执行计划,找到I/O大的地方。如这个DMVs

select top 50 (total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes, (total_physical_reads/execution_count) as  avg_phys_reads, Execution_count, statement_start_offset as stmt_start_offset, sql_handle, s2.text plan_handle from sys.dm_exec_query_stats cross APPLY sys.dm_exec_sql_text(sql_handle) AS s2 order by  (total_logical_reads + total_logical_writes) Desc

小TIP,如果要清除缓存的作用,执行这个:

checkpoint

dbcc freeproccache

dbcc dropcleanbuffers

监控 SQL Server 的运行状况()

         Microsoft 提供了一些工具来监控。方法之一是动态视图。动态管理视图 (DMV) 和动态管理函数 (DMF) 返回的服务器状态信息可用于监控服务器实例的运行状况、诊断问题和优化性能。

常规服务器动态管理对象包括:

  • dm_db_*:数据库和数据库对象

  • dm_exec_*:执行用户和关联的连接

  • dm_os_*:内存、锁定和时间安排

  • dm_tran_*:事务和隔离

  • dm_io_*:网络和磁盘的输入/输出

此部分介绍为监控 SQL Server 运行状况而针对这些动态管理视图和函数运行的一些常用查询。

示例查询

您可以运行以下查询来获取所有 DMV 和 DMF 名称:

SELECT * FROM sys.system_objects
WHERE name LIKE ‘dm_%’
ORDER BY name监控 CPU 瓶颈

CPU 瓶颈通常由以下原因引起:查询计划并非最优、配置不当、因素不良或硬件资源不足。下面的常用查询可帮助您确定导致 CPU 瓶颈的原因。

下面的查询使您能够深入了解当前缓存的哪些批处理或过程占用了大部分 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.sql_handle
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC

下面的查询显示缓存计划所占用的 CPU 总使用率(带 SQL 文本)。

SELECT
      total_cpu_time,
      total_execution_count,
      number_of_statements,
      s2.text
      –(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) – statement_start_offset) / 2) ) AS query_text
FROM
      (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.sql_handle –,
            –MIN(statement_start_offset) AS statement_start_offset,
            –MAX(statement_end_offset) AS statement_end_offset
      FROM
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_worker_time) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2

下面的查询显示 CPU 平均占用率最高的前 50 个 SQL 语句。

SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(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 Time] DESC

下面显示用于找出过多编译/重新编译的 DMV 查询。

select * from sys.dm_exec_query_optimizer_info
where
      counter = ‘optimizations’
      or counter = ‘elapsed time’

下面的示例查询显示已重新编译的前 25 个存储过程。plan_generation_num指示该查询已重新编译的次数。

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

效率较低的查询计划可能增大 CPU 占用率。

下面的查询显示哪个查询占用了最多的 CPU 累计使用率。

SELECT
    highest_cpu_queries.plan_handle,
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
from
    (select top 50
        qs.plan_handle,
        qs.total_worker_time
    from
        sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) as highest_cpu_queries
    cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

下面的查询显示一些可能占用大量 CPU 使用率的运算符(例如 ‘%Hash Match%’、‘%Sort%’)以找出可疑对象。

select *
from
      sys.dm_exec_cached_plans
      cross apply sys.dm_exec_query_plan(plan_handle)
where
      cast(query_plan as nvarchar(max)) like ‘%Sort%’
      or cast(query_plan as nvarchar(max)) like ‘%Hash Match%’

如果已检测到效率低下并导致 CPU 占用率较高的查询计划,请对该查询中涉及的表运行UPDATE STATISTICS以查看该问题是否仍然存在。然后,收集相关数据并将此问题报告给 PerformancePoint Planning 支持人员。

如果您的系统存在过多的编译和重新编译,可能会导致系统出现与 CPU 相关的性能问题。

您可以运行下面的 DMV 查询来找出过多的编译/重新编译。

select * from sys.dm_exec_query_optimizer_info
where
counter = ‘optimizations’
or counter = ‘elapsed time’

下面的示例查询显示已重新编译的前 25 个存储过程。plan_generation_num指示该查询已重新编译的次数。

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

如果已检测到过多的编译或重新编译,请尽可能多地收集相关数据并将其报告给Planning 支持人员。

内存瓶颈

开始内存压力检测和调查之前,请确保已启用 SQL Server 中的高级选项。请先对 master 数据库运行以下查询以启用此选项。

sp_configure ‘show advanced options’
go
sp_configure ‘show advanced options’, 1
go
reconfigure
go

首先运行以下查询以检查内存相关配置选项。

sp_configure ‘awe_enabled’
go
sp_configure ‘min server memory’
go
sp_configure ‘max server memory’
go
sp_configure ‘min memory per query’
go
sp_configure ‘query wait’
go

运行下面的 DMV 查询以查看 CPU、计划程序内存和缓冲池信息。

select
cpu_count,
hyperthread_ratio,
scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb,
bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_infoI/O 瓶颈

检查闩锁等待统计信息以确定 I/O 瓶颈。运行下面的 DMV 查询以查找 I/O 闩锁等待统计信息。

select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats 
where wait_type like ‘PAGEIOLATCH%’  and waiting_tasks_count > 0
order by wait_type

如果waiting_task_countswait_time_ms与正常情况相比有显著变化,则可以确定存在 I/O 问题。获取 SQL Server 平稳运行时性能计数器和主要 DMV 查询输出的基线非常重要。

这些wait_types可以指示您的 I/O 子系统是否遇到瓶颈。

使用以下 DMV 查询来查找当前挂起的 I/O 请求。请定期执行此查询以检查 I/O 子系统的运行状况,并隔离 I/O 瓶颈中涉及的物理磁盘。

select
    database_id,
    file_id,
    io_stall,
    io_pending_ms_ticks,
    scheduler_address
from  sys.dm_io_virtual_file_stats(NULL, NULL)t1,
        sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

在正常情况下,该查询通常不返回任何内容。如果此查询返回一些行,则需要进一步调查。

您还可以执行下面的 DMV 查询以查找 I/O 相关查询。

select top 5 (total_logical_reads/execution_count) as avg_logical_reads,
                   (total_logical_writes/execution_count) as avg_logical_writes,
           (total_physical_reads/execution_count) as avg_physical_reads,
           Execution_count, statement_start_offset, p.query_plan, q.text
from sys.dm_exec_query_stats
      cross apply sys.dm_exec_query_plan(plan_handle) p
      cross apply sys.dm_exec_sql_text(plan_handle) as q
order by (total_logical_reads + total_logical_writes)/execution_count Desc

下面的 DMV 查询可用于查找哪些批处理/请求生成的 I/O 最多。如下所示的 DMV 查询可用于查找可生成最多 I/O 的前五个请求。调整这些查询将提高系统性能。

select top 5
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
     Execution_count,
    statement_start_offset as stmt_start_offset,
    sql_handle,
    plan_handle
from sys.dm_exec_query_stats 
order by  (total_logical_reads + total_logical_writes) Desc阻塞

运行下面的查询可确定阻塞的会话。

select blocking_session_id, wait_duration_ms, session_id from
sys.dm_os_waiting_tasks
where blocking_session_id is not null

使用此调用可找出 blocking_session_id 所返回的 SQL。例如,如果 blocking_session_id 是 87,则运行此查询可获得相应的 SQL。

dbcc INPUTBUFFER(87)

下面的查询显示 SQL 等待分析和前 10 个等待的资源。

select top 10 *
from sys.dm_os_wait_stats
–where wait_type not in (‘CLR_SEMAPHORE’,’LAZYWRITER_SLEEP’,’RESOURCE_QUEUE’,’SLEEP_TASK’,’SLEEP_SYSTEMTASK’,’WAITFOR’)
order by wait_time_ms desc

若要找出哪个spid正在阻塞另一个 spid,可在数据库中创建以下存储过程,然后执行该存储过程。此存储过程会报告此阻塞情况。键入sp_who可找出 @spid;@spid 是可选参数。

create proc dbo.sp_block (@spid bigint=NULL)
as
select
    t1.resource_type,
    ‘database’=db_name(resource_database_id),
    ‘blk object’ = t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id   
from
    sys.dm_tran_locks as t1,
    sys.dm_os_waiting_tasks as t2
where
    t1.lock_owner_address = t2.resource_address and
    t1.request_session_id = isnull(@spid,t1.request_session_id)

以下是使用此存储过程的示例。

exec sp_block
exec sp_block @spid = 7

http://technet.microsoft.com/zh-cn/library/bb838723.aspx

使用DMV和DMF分析数据库性能(转)

服务器等待的原因
SELECT TOP 10
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
/ SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE ‘%SLEEP%’
ORDER BY wait_time_ms DESC;

读和写
SELECT TOP 10
[Total Reads] = SUM(total_logical_reads)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;
SELECT TOP 10
[Total Writes] = SUM(total_logical_writes)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;

数据库缺失索引
SELECT
DatabaseName = DB_NAME(database_id)
,[Number Indexes Missing] = count(*)
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;

缺失索引列表信息

SELECT DatabaseName = DB_NAME(database_id),* FROM sys.dm_db_missing_index_details Order BY DB_NAME(database_id)

高开销的缺失索引
SELECT TOP 10
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM    sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

确定开销最高的未使用索引
SELECT TOP 10 [Total Cost]=ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0),avg_user_impact,TableName=statement, [EqualityUsage]=equality_columns,[InequalityUsage]=inequality_columns,[Include Cloumns] = included_columns FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle ORDER BY [Total Cost] DESC;

确定最高开销索引所使用的脚本并显示结果。
— Create required table structure only.
— Note: this SQL must be the same as in the Database loop given in the following step.
SELECT TOP 1
[Maintenance cost] = (user_updates + system_updates)
,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
,DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
INTO #TempMaintenanceCost
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], ‘IsMsShipped’) = 0
AND (user_updates + system_updates) > 0 — Only report on active rows.
AND s.[object_id] = -999 — Dummy value to get table structure.
;
— Loop around all the databases on the server.
EXEC sp_MSForEachDB ‘USE [?];
— Table already exists.
INSERT INTO #TempMaintenanceCost
SELECT TOP 10
[Maintenance cost] = (user_updates + system_updates)
,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
,DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL — Ignore HEAP indexes.
AND OBJECTPROPERTY(s.[object_id], ”IsMsShipped”) = 0
AND (user_updates + system_updates) > 0 — Only report on active rows.
ORDER BY [Maintenance cost] DESC
;

— Select records.
SELECT TOP 10 * FROM #TempMaintenanceCost
ORDER BY [Maintenance cost] DESC
— Tidy up.
DROP TABLE #TempMaintenanceCost

显示索引已被使用的次数,并按“使用率”排序。
— Create required table structure only.
— Note: this SQL must be the same as in the Database loop given in the — following step.
SELECT TOP 1
[Usage] = (user_seeks + user_scans + user_lookups)
,DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
INTO #TempUsage
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], ‘IsMsShipped’) = 0
AND (user_seeks + user_scans + user_lookups) > 0
— Only report on active rows.
AND s.[object_id] = -999 — Dummy value to get table structure.
;
— Loop around all the databases on the server.
EXEC sp_MSForEachDB ‘USE [?];
— Table already exists.
INSERT INTO #TempUsage
SELECT TOP 10
[Usage] = (user_seeks + user_scans + user_lookups)
,DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL — Ignore HEAP indexes.
AND OBJECTPROPERTY(s.[object_id], ”IsMsShipped”) = 0
AND (user_seeks + user_scans + user_lookups) > 0 — Only report on active rows.
ORDER BY [Usage] DESC
;

— Select records.
SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC
— Tidy up.
DROP TABLE #TempUsage

逻辑上最零碎的索引所使用的脚本
— Create required table structure only.
— Note: this SQL must be the same as in the Database loop given in the — following step.
SELECT TOP 1
DatbaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.[object_id] = -999 — Dummy value just to get table structure.
;
— Loop around all the databases on the server.
EXEC sp_MSForEachDB ‘USE [?];
— Table already exists.
INSERT INTO #TempFragmentation
SELECT TOP 10
DatbaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL — Ignore HEAP indexes.
AND OBJECTPROPERTY(s.[object_id], ”IsMsShipped”) = 0
ORDER BY [Fragmentation %] DESC
;

— Select records.
SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
— Tidy up.
DROP TABLE #TempFragmentation

获得IO高的查询
SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;

获得I/O统计
Select wait_type, waiting_tasks_count, wait_time_ms from sys.dm_os_wait_stats where wait_type like ‘PAGEIOLATCH%’ order by wait_type

查询当前I/O锁
select DB_NAME(database_id), file_id, io_stall,io_pending_ms_ticks,scheduler_address from sys.dm_io_virtual_file_stats(NULL, NULL) t1,sys.dm_io_pending_io_requests as t2 where t1.file_handle = t2.io_handle

看是那5条语句导致I/O高
select top 5 (total_logical_reads/execution_count) as avg_logical_reads,(total_logical_writes/execution_count) as avg_logical_writes,(total_physical_reads/execution_count) as avg_phys_reads,Execution_count, statement_start_offset as stmt_start_offset, sql_handle, plan_handle from sys.dm_exec_query_stats order by (total_logical_reads + total_logical_writes) Desc

根据句柄得到语句
select text from sys.dm_exec_sql_text(0x03000E00D4AB884E808214016B9A00000100000000000000)

查询可以确定按 CPU 使用率衡量的、开销最高的查询
SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END –
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

高开销的 CLR 查询
SELECT TOP 10
[Average CLR Time] = total_clr_time / execution_count
,[Total CLR Time] = total_clr_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE total_clr_time <> 0
ORDER BY [Average CLR Time] DESC;

最常执行的查询
SELECT TOP 10
[Execution count] = execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;

受阻塞影响的查询
SELECT TOP 10
[Average Time Blocked] = (total_elapsed_time – total_worker_time) / qs.execution_count
,[Total Time Blocked] = total_elapsed_time – total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average Time Blocked] DESC;

最低计划重用率
SELECT TOP 100
[Plan usage] = cp.usecounts
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),
qt.text)) * 2 ELSE qs.statement_end_offset END –
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
,cp.cacheobjtype
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
WHERE cp.plan_handle=qs.plan_handle
ORDER BY [Plan usage] ASC;

数据库连接情况
SELECT session_id,connect_time,endpoint_id,auth_scheme,num_reads,num_writes,client_net_address,connection_id from sys.dm_exec_connections order by client_net_address

查询优化器信息
select * from sys.dm_exec_query_optimizer_info

当前执行请求
select * from sys.dm_exec_requests

当前执行session
select * from sys.dm_exec_sessions

所有的调度器并产看等待运行的任务数量
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255

所有的调度器并产看等待运行的任务数量
select
*
from
sys.dm_os_schedulers
where
scheduler_id < 255

整个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

所有的调度器并产看等待运行的任务数量
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255

所有的调度器并产看等待运行的任务数量
select
*
from
sys.dm_os_schedulers
where
scheduler_id < 255

整个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

得到在给定的时间段内花费在查询优化的时间
select * from sys.dm_exec_query_optimizer_info

重编译次数最多的25个存储过程
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

累计使用cpu最多的查询
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

清空统计项的方法

checkpoint 检查点
dbcc freeproccache 释放缓存,小心
dbcc dropcleanbuffers 清空缓存,小心
DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR); 清空某一项
GO

综合分析:
SELECT top 50  DB_ID(DB.dbid) ‘数据库名’
, OBJECT_ID(db.objectid) ‘对象’
, QS.creation_time ‘编译计划的时间’
, QS.last_execution_time ‘上次执行计划的时间’
, QS.execution_count ‘执行的次数’
, QS.total_elapsed_time / 1000 ‘占用的总时间(秒)’
, QS.total_physical_reads ‘物理读取总次数’
, QS.total_worker_time / 1000 ‘CPU 时间总量(秒)’
, QS.total_logical_writes ‘逻辑写入总次数’
, QS.total_logical_reads N’逻辑读取总次数’
, QS.total_elapsed_time / 1000 N’总花费时间(秒)’
, 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 ‘执行语句’,
[Parent Query] = st.text
FROM    sys.dm_exec_query_stats AS QS CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN
( SELECT    *
FROM      sys.dm_exec_cached_plans cp CROSS APPLY
sys.dm_exec_query_plan(cp.plan_handle)
) DB
ON QS.plan_handle = DB.plan_handle
where   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) not like ‘%fetch%’
ORDER BY QS.total_elapsed_time / 1000 DESC

SQL Server:揭开隐藏数据的面纱,优化应用程序性能(下)

常用索引循环遍历所有数据库sys.indexes DMV 是一个特定于数据库的视图。因此,联接到 sys.indexes 的查询只报告针对当前数据库的结果。但是,您可以使用系统存储过程 sp_MSForEachDB 遍历服务器上的所有数据库,然后提供服务器范围的结果。下面是我对这些情况所采取的做法。

  1. 创建一个临时表,其所需的结构与代码主体类似。我将一个不存在的记录(object_id 为 -999)放入其中,以便可以创建临时表结构。
  2. 代码的主体执行,即遍历服务器上的所有数据库。请注意,从每个数据库检索到的记录数量(使用 TOP 语句)应与要显示的记录数量相同。否则,这些结果可能并不真正代表服务器上所有数据库的最前 n 个记录。
  3. 这些记录将提取自临时表,并按我关注的列(在本例中为 user_updates 列)排序。

您可以使用 DMV 确定哪些索引最常使用,这些是到基础数据的最常用路径。如果这些索引能够实现自身改进或优化,则可以极大地提高整体性能。sys.dm_db_index_usage_stats DMV 包含了一些详细信息,它们有关通过搜寻、扫描和查找来检索数据方面对索引的使用频率。此 DMV 已联接到 sys.indexes DMV,后者包含创建索引时所使用的详细信息。“使用率”列的计算方法是将所有 user_* 字段相加。使用图 7 所示的脚本即可实现此目的。此查询结果显示索引已被使用的次数,并按“使用率”排序。  Figure 7 Identifying Most-Used Indexes — Create required table structure only.
— Note: this SQL must be the same as in the Database loop given in the — following step.
SELECT TOP 1
[Usage] = (user_seeks + user_scans + user_lookups)
,DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
INTO #TempUsage
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], ‘IsMsShipped’) = 0
AND (user_seeks + user_scans + user_lookups) > 0
— Only report on active rows.
AND s.[object_id] = -999 — Dummy value to get table structure.
;

— Loop around all the databases on the server.
EXEC sp_MSForEachDB ‘USE [?];
— Table already exists.
INSERT INTO #TempUsage
SELECT TOP 10
[Usage] = (user_seeks + user_scans + user_lookups)
,DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL — Ignore HEAP indexes.
AND OBJECTPROPERTY(s.[object_id], ”IsMsShipped”) = 0
AND (user_seeks + user_scans + user_lookups) > 0 — Only report on active rows.
ORDER BY [Usage] DESC
;

— Select records.
SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC
— Tidy up.
DROP TABLE #TempUsageFigure 7 Identifying Most-Used Indexes (单击该图像获得较大视图)使用次数最多的索引代表对基础数据的最重要访问路线。显然,您不想删除这些索引;但应对这些索引进行研究,以确保它们最优化。例如,应确保索引的碎片很少(对以顺序检索的数据更是如此),且基础统计数据保持最新。而且应该删除所有未使用的表索引。
逻辑上零碎的索引逻辑索引碎片表示索引中无序条目所占的百分比。这与页填充度类型的碎片不同。逻辑碎片会影响任何使用索引的顺序扫描。应尽可能消除此碎片。可以通过重新生成或重新组织索引来消除碎片。用下面的 DMV 可以确定逻辑上最零碎的索引。使用 sys.dm_db_index_physical_stats DMV 可以查看有关索引的大小和碎片的详细信息。它已联接到 sys.indexes DMV,后者包含创建索引时使用的详细信息。图 8 显示了确定逻辑上最零碎的索引所使用的脚本。结果按碎片的百分比排序,显示了所有数据库中逻辑上最零碎的索引以及有关的数据库/表。请注意,此脚本在最初运行时可能需要一段时间(几分钟),因此我已在脚本下载中将其注释掉。  Figure 8 Identifying Logically Fragmented Indexes — Create required table structure only.
— Note: this SQL must be the same as in the Database loop given in the — following step.
SELECT TOP 1
DatbaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.[object_id] = -999 — Dummy value just to get table structure.
;

— Loop around all the databases on the server.
EXEC sp_MSForEachDB ‘USE [?];
— Table already exists.
INSERT INTO #TempFragmentation
SELECT TOP 10
DatbaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL — Ignore HEAP indexes.
AND OBJECTPROPERTY(s.[object_id], ”IsMsShipped”) = 0
ORDER BY [Fragmentation %] DESC
;

— Select records.
SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
— Tidy up.
DROP TABLE #TempFragmentationFigure 8 Identifying Logically Fragmented Indexes (单击该图像获得较大视图)
高 I/O 开销的查询I/O 是查询所进行的读/写数量的一种度量。这可以用来指示查询的效率——使用许多 I/O 的查询通常是性能改进研究的适当对象。sys.dm_exec_query_stats DMV 提供缓存查询计划的汇总性能统计信息,包括有关物理和逻辑读/写以及查询执行次数的详细信息。它包含从实际 SQL 的父 SQL 中提取实际 SQL 所使用的偏移量。此 DMV 已联接到 sys.dm_exec_sql_text DMF,后者包含与 I/O 有关的 SQL 批处理的信息。将各种偏移量应用到此批处理可以获得各基础 SQL 查询。脚本如图 9 所示。结果按平均 I/O 排序,显示了平均 I/O、总 I/O、单个查询、父查询(如果单个查询是批处理的一部分)以及数据库名称。  Figure 9 Identifying Most Costly Queries by I/O SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;Figure 9 Identifying Most Costly Queries by I/O (单击该图像获得较大视图)因 为 I/O 是数据量的一种反映,所以“单个查询”列中显示的查询可帮您确定减少 I/O 和提高性能的地方。可以将查询提供给数据库优化顾问,以确定是否应添加任何索引/统计数据来提高查询的性能。统计数据包括有关基础数据分布和密度的详细信 息。查询优化器在确定优化查询访问计划时使用此统计数据。它还有助于检查在这些查询中的表和“缺失索引”部分列出的索引之间是否存在联系。(但请注意,如果表经历许多更新,那么研究对这些表创建索引所产生的影响就很重要,因为任何附加索引都会增加更新基础表数据所使用的时间。)此脚本可以更改为只报告读操作或写操作,这分别对报告数据库或事务性数据库很有用。还可以报告总值和平均值,并相应地进行排序。读操作的值较高可能表示缺失索引或索引不完整,也可能表示查询或表设计不良。解释使用 sys.dm_exec_query_stats DMV 所得的结果时,请务必小心。例如,某个查询计划可能会随时从过程缓存中被删除,并且不是所有查询都得到缓存。虽然这会影响结果,但这些结果仍指示出开销最高的查询。
高 CPU 开销的查询另一个可采取的有用方法是分析在 CPU 使用率方面开销最高的查询。此方法可能更能体现出性能不良的查询。在此使用的 DMV 与我刚就 I/O 对查询进行研究时所使用的 DMV 相同。使用图 10 所示的查询可以确定按 CPU 使用率衡量的、开销最高的查询。  Figure 10 Identifying Most Costly Queries by CPU SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END –
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;Figure 10 SQL Query Records Causes of Wait Times (单击该图像获得较大视图)此查询返回平均 CPU 使用率、总 CPU 使用率、单个查询和父查询(如果单个查询是批处理的一部分)以及相应的数据库名称。而且,如前所述,可能有必要针对查询运行数据库优化顾问,以确定是否有可能进一步改进。
高开销的 CLR 查询SQL Server 不断增加对 CLR 的使用。因此,确定哪些查询使用 CLR(包括存储过程、函数和触发器)最多会非常有帮助。sys.dm_exec_query_stats DMV 包含有关 total_clr_time 和查询已执行次数的详细信息。它还包含从实际查询的父查询中提取实际查询所使用的偏移量。此 DMV 已联接到 sys.dm_exec_sql_text DMF,后者包含有关 SQL 批处理的信息。应用各偏移量可以获取基础 SQL。图 11 显示了用于确定开销最高的 CLR 的查询。  Figure 11 Identifying Most Costly CLR Queries SELECT TOP 10
[Average CLR Time] = total_clr_time / execution_count
,[Total CLR Time] = total_clr_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE total_clr_time <> 0
ORDER BY [Average CLR Time] DESC;Figure 11 Identifying Most Costly CLR Queries (单击该图像获得较大视图)此查询返回平均 CLR 时间、总 CLR 时间、执行计数、单个查询、父查询以及数据库名称。再次指出,可能有必要针对查询运行数据库优化顾问,以确定是否有可能进一步改进。
最常执行的查询您 可以修改前一个用于高开销 CLR 查询的示例来确定最常执行的查询。请注意在此适用相同的 DMV。与优化很少使用的大型查询相比,改进频繁执行的查询可以极大地提高性能。(要进行完整性检查,可能要通过检查使用累积 CPU 或 I/O 最高的查询进行交叉检查。)改进频繁运行的查询的另一个好处是还可以减少锁的数量和事务长度。当然,最终结果是改进了系统的整体响应能力。您可以使用图 12 所示的查询来确定最常执行的查询。运行此查询会显示执行计数、单个查询、父查询(如果单个查询是批处理的一部分)以及相关数据库。再次指出,有必要在数据库优化顾问中运行查询,以确定是否有可能进一步改进。  Figure 12 Identifying Queries that Execute Most Often SELECT TOP 10
[Execution count] = execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Execution count] DESC;Figure 12 Identifying Queries that Execute Most Often (单击该图像获得较大视图)
受阻塞影响的查询受阻塞影响最大的查询通常运行时间都很长。在确定这些查询后,可以对其进行分析,以确定是否可以(以及应该)对其重写来减少阻塞。阻塞原因包括以不一致的顺序使用对象、事务范围发生冲突以及更新未使用的索引。已 讨论过的 sys.dm_exec_query_stats DMV 包含一些列,可用于确定受阻塞影响最大的查询。平均阻塞时间计算方法是,total_elaspsed_time 和 total_worker_time 之间的差除以 execution_count。sys.dm_exec_sql_text DMF 包含与阻塞有关的 SQL 批处理的详细信息。对其应用各偏移量可以获取基础 SQL。使用图 13 所示的查询可以确定受阻塞影响最大的查询。查询结果显示了平均阻塞时间、总阻塞时间、执行计数、单个查询、父查询以及相关数据库。尽管这些结果按“平均阻塞时间”排序,但按“总阻塞时间”排序也会有用。  Figure 13 Identifying Queries Most Often Blocked SELECT TOP 10
[Average Time Blocked] = (total_elapsed_time – total_worker_time) / qs.execution_count
,[Total Time Blocked] = total_elapsed_time – total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average Time Blocked] DESC;Figure 13 Identifying Queries Most Often Blocked (单击该图像获得较大视图)如果您研究此查询,可能会发现设计问题(如缺失索引)、事务问题(无序使用资源)等。数据库优化顾问还会重点指出可能的改进。
最低计划重用率使用存储过程的一个优点是,查询计划已得到缓存,因而可在不编译查询的情况下重用。这一优点既节省时间和资源,又改进了性能。您可以确定重用率最低的查询计划,从而进一步调查为何没有重用这些查询计划。您可能会发现,某些查询计划可通过重写来优化重用。图 14 显示了我编写的脚本,用来确定计划重用率最低的查询。此技术使用已讨论过的 DMV 以及尚未提到过的 DMV:dm_exec_cached_plans。此 DMV 还包含已被 SQL Server 缓存的查询计划的详细信息。如您所见,查询结果提供了已使用计划的次数(“计划使用率”列)、单个查询、父查询和数据库名称。  Figure 14 Identifying Queries with Lowest Plan Reuse SELECT TOP 10
[Plan usage] = cp.usecounts
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),
qt.text)) * 2 ELSE qs.statement_end_offset END –
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
,cp.cacheobjtype
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
WHERE cp.plan_handle=qs.plan_handle
ORDER BY [Plan usage] ASC;Figure 14 Identifying Queries with Lowest Plan Reuse (单击该图像获得较大视图)然 后,您可以研究显示的各个索引,以确定没有更多地重用这些计划(如果有可能)的原因。一个可能的原因是,每次运行查询时,都要重新编译查询——如果查询包 含多个 SET 语句或临时表,则可能发生这种情况。有关重新编译和计划缓存的详细讨论,请参阅文章“Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005”(SQL Server 2005 中的批处理编译、重新编译和计划缓存)(可在 microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx 找到)。请注意,您还应确保查询有充足的机会执行多次。可以使用关联的 SQL 跟踪文件来确认这一点。
进一步探讨请记住,各 DMV 提供的指标不会永久存储,而只保留在内存中。SQL Server 2005 重新启动后,这些指标都会丢失。您可以基于 DMV 的输出定期创建表,以存储结果,并附上时间戳。然后,可以按时间戳的先后顺序检查这些表,确定任何应用程序更改所带来的影响,或确定给定作业或基于时间的处理所带来的影响。例如,月末处理过程能带来什么影响?同样,您可以将给定跟踪文件工作负荷与此类表中的更改关联起来,以确定给定工作负荷(如每日或月末工作负荷)给缺失索引、最常使用的查询所带来的影响。您可以编辑我在此提供的脚本以创建这些表,并作为日常维护任务的一部分定期运行。还可以用 Visual Studio 2005 创建自定义报告,它们使用本文中讨论的脚本。这些报告可轻松集成到 SQL Server Management Studio,从而提供更令人满意的数据表示。如有可能,应尝试将我讨论过的方法与其他方法(如跟踪和比率分析)结合起来使用。这样可以更全面地了解提高数据库性能所需的更改。我 已在此演示了 SQL Server 2005 在正常工作过程中所累积的信息的用处。对此信息进行查询可提供一些在当前工作中已证明可提高查询性能的有用线索。例如,您可能会发现服务器等待的原因,查 找对性能产生不良影响的未使用索引,以及确定哪些查询是最常用的查询,哪些查询开销最高。一旦您开始探索这些隐藏数据,就会发现无数的线索。DMV 还有许多需了解的内容,我希望本文能促使您进行深入研究。
Ian Stirk 自 1987 年起,作为开发人员、设计师和架构师一直在 IT 行业工作。他拥有以下资格认证:M.Sc.、MCSD、MCDBA 和 SCJP。Ian 是一名居住在英国伦敦的 Microsoft 技术兼职顾问。可以通过 与他取得联系。

SQL Server:揭开隐藏数据的面纱,优化应用程序性能(上)

本文转自:http://msdn.microsoft.com/zh-cn/magazine/cc135978.aspx#S2

 目录服务器等待的原因
读和写
数据库缺失索引
高开销的缺失索引
未使用的索引
高开销的已使用索引
常用索引
逻辑上零碎的索引
高 I/O 开销的查询
高 CPU 开销的查询
高开销的 CLR 查询
最常执行的查询
受阻塞影响的查询
最低计划重用率
进一步探讨许多应用程序性能问题追根溯源都可以归咎到性能欠佳的数据库查询;但是,有许多方法可以用来提高数据库性能。SQL ServerTM 2005 收集许多信息,可以使用这些信息来确定产生此类性能问题的原因。SQL Server 2005 收集与运行查询有关的数据。此数据存放在内存中,并从服务器重新启动后开始累积,可用于确定许多问题和指标,包括那些与表索引、查询性能和服务器 I/O 相关的问题和指标。可以通过 SQL Server 动态管理视图 (DMV) 和相关动态管理函数 (DMF) 查询此数据。这些都是基于系统的视图和函数,提供可用于诊断问题和调整数据库性能的服务器状态信息。本文重点介绍使用 SQL Server 2005 收集的信息来提高性能的领域。这种方法在很大程度上是一种非入侵式方法,因为它收集并研究现有数据,通常是查询基础系统数据。我将演示如何获取此信息、讨论基础 DMV、确定解释数据时要注意的所有问题,并指出其他一些可能会提高性能之处。为此,我将提供并分析一系列 SQL 脚本,这些脚本详细说明了 SQL Server 2005 所收集数据的各个方面。可以从《MSDN® 杂志》网站下载此脚本的完整版本(全部带注释)。我将讨论的某些步骤会将服务器作为一个整体进行专门分析,包括给定服务器上承载的所有数据库。如果需要,可以添加相应的筛选(如将其名称添加到查询)来专门分析给定的数据库。相反,某些查询会联接到 sys.indexes DMV,这是一个特定于数据库的视图,只报告针对当前数据库的结果。在这些示例中,我已将查询修改成使用系统存储过程 sp_MSForEachDB 对服务器上的所有数据库进行遍历,从而提供服务器范围的结果。为了将给定性能指标的最相关记录作为目标,我将限制使用 SQL TOP 函数返回的记录数量。
服务器等待的原因用户通常会遇到因一系列等待而导致性能下降的情况。每当 SQL 查询能够运行但需等待其他资源时,它都会记录有关等待原因的详细信息。可以使用 sys.dm_os_wait_stats DMV 访问这些详细信息。您可以使用图 1 所示的 SQL 脚本分析所有等待的累积原因。  Figure 1 SQL Query Records Causes of Wait Times SELECT TOP 10
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
/ SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE ‘%SLEEP%’
ORDER BY wait_time_ms DESC;Figure 1 SQL Query Records Causes of Wait Times 运行此脚本所得的结果会列出等待类型,并按花费的总等待时间排序。通过示例结果可看出,I/O 作为一种等待原因排在相对靠前的位置。请注意,我只关注逻辑 I/O(在内存中读/写数据)而不关注物理 I/O,因为最初加载数据后,数据通常位于内存中。
读和写I/O 使用率较高可能表示数据访问机制不良。SQL Server 2005 跟踪每个查询满足其需要所使用的读写总数。您可以将这些数字相加,确定哪些数据库执行的总体读写操作最多。sys.dm_exec_query_stats DMV 包含已缓存查询计划的汇总性能统计数据。此统计数据包含有关逻辑读写数量和已执行查询次数的信息。将此 DMV 联接到 sys.dm_exec_sql_text DMF 后,可以按数据库将读写数量加起来。请注意,我使用新的 SQL Server 2005 CROSS APPLY 运算符处理此联接操作。图 2 显示了用于确定哪些数据库使用读写操作最多的脚本。  Figure 2 Identifying the Most Reads and Writes SELECT TOP 10
[Total Reads] = SUM(total_logical_reads)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;

SELECT TOP 10
[Total Writes] = SUM(total_logical_writes)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;Figure 2 Identifying the Most Reads and Writes 结果显示了哪些数据库读写的逻辑页最多。顶部数据集按总读取次数排序,底部数据集按总写入次数排序。可 以清楚地看到,DatabaseName 在几个实例中设置为 NULL。此设置确定特定的和准备好的 SQL 语句。确定本机 SQL 的使用程度时,可使用此详细信息,它本身也可能引起许多不同的问题。(例如,这说明没有重用查询计划、没有重用代码,以及安全领域可能存在问题。)tempdb 值较高可能表示过度使用临时表、过度重新编译或设备效率低。这些结果可以用来确定主要使用哪些数据库进行报告(许多数据选择),这与事务性数据库(许多更 新)相对。每个数据库类型(报告数据库或事务性数据库)都有不同的索引需求。稍后我将更详细地探讨此问题。
数据库缺失索引当 SQL Server 处理查询时,优化器会针对它用来尝试满足查询的索引保留一条记录。如果找不到这些索引,则 SQL Server 会创建缺失索引的记录。可以使用 sys.dm_db_missing_index_details DMV 来查看此信息。使用图 3 所示的脚本,可以显示给定服务器上的哪些数据库缺失索引。发现这些缺失索引很重要,因为这些索引通常提供检索查询数据所需的最佳路径。这可以随之减少 I/O,从而提高整体性能。我的脚本会检查 sys.dm_db_missing_index_details,并按数据库将缺失索引数相加,从而轻松确定需进一步调查哪些数据库。  Figure 3 Identifying Missing Databases SELECT
DatabaseName = DB_NAME(database_id)
,[Number Indexes Missing] = count(*)
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;Figure 3 Identifying Missing Databases 数据库常分为基于事务的系统和基于报告的系统。将所显示的缺失索引应用到报告数据库相对容易。另一方面,对事务性数据库而言,通常需要进一步调查对基础表数据附加索引的影响。
高开销的缺失索引索引可对查询性能产生不同程度的影响。您可以深入了解服务器上所有数据库中开销最高的缺失索引,找出哪些缺失索引可能对性能产生最显著的正面影响。sys.dm_db_missing_index_group_stats DMV 记录了 SQL 尝试使用特定缺失索引的次数。sys.dm_db_missing_index_details DMV 详细显示缺失索引的结构,例如查询所需的列。这两个 DMV 通过 sys.dm_db_missing_index_groups DMV 联系在一起。缺失索引的开销(总开销列)的计算方法是,用户平均总开销与用户平均影响的积,再乘以用户搜寻次数与用户扫描次数的和。可以使用图 4 所示的脚本来确定开销最高的缺失索引。此查询的结果(按“总开销”排序)显示最重要缺失索引的成本以及有关数据库/架构/表和缺失索引中所需列的信息。特 别是,此脚本可确定哪些列在相等和不相等 SQL 语句中使用。另外,它还报告应将哪些其他列用作缺失索引中的包含性列。使用包含性列可以在不从基础页获取数据的情况下满足更多的覆盖查询,因而使用的 I/O 操作更少,从而提高性能。  Figure 4 Cost of Missing Indexes SELECT TOP 10
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;Figure 4 Cost of Missing Indexes (单击该图像获得较大视图)请注意,这些结果没有指定应在必需索引中创建列的顺序。为确定顺序,您应检查整体 SQL 代码库。一般而言,选择最多的列应在索引中最先显示。还 要指出的是,在计算缺失索引的开销时,只考虑用户列(如 user_seeks 和 user_scans)。这是因为系统列一般代表对统计数据、数据库一致性检查 (DBCC) 和数据定义语言 (DDL) 命令的使用,而且这些列对完成业务功能(与数据库管理功能相比较)不太重要。请记住,对于在基础表中发生任何数据修改时可能产生的额外索引开销,要特别引起注意,这非常重要。因此,应对基础 SQL 代码库进行进一步的研究。如果您发现系统建议将许多列作为要包含的列,则应检查基础 SQL,因为这可能表示正在使用 catchall“SELECT *”语句——如果确实如此,则可能要修改选择查询。
未使用的索引未使用的索引可能会对性能产生不良影响。这是因为,修改基础表数据后,可能也需更新索引。当然,这需要额外的时间,而且可能增加阻塞的几率。如果执行查询时使用某个索引,而且由于将更新应用到基础表数据而更新了该索引,则 SQL Server 会更新相应的索引使用详细信息。可以通过查看这些使用详细信息来确定任何未使用的索引。通 过 sys.dm_db_index_usage_stats DMV 可以了解使用索引的频率和程度。它已联接到 sys.indexes DMV,后者包含创建索引时所使用的信息。您可以在各用户列中检查值 0 以确定未使用的索引。根据上述原因,再次忽略系统列所产生的影响。使用图 5 所示的脚本可以确定开销最高的未使用索引。  Figure 5 Identifying Most Costly Unused Indexes — Create required table structure only.
— Note: this SQL must be the same as in the Database loop given in the following step.
SELECT TOP 1
DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
,user_updates
,system_updates
— Useful fields below:
–, *
INTO #TempUnusedIndexes
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], ‘IsMsShipped’) = 0
AND user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND s.[object_id] = -999 — Dummy value to get table structure.
;

— Loop around all the databases on the server.
EXEC sp_MSForEachDB ‘USE [?];
— Table already exists.
INSERT INTO #TempUnusedIndexes
SELECT TOP 10
DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
,user_updates
,system_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], ”IsMsShipped”) = 0
AND user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND i.name IS NOT NULL — Ignore HEAP indexes.
ORDER BY user_updates DESC
;

— Select records.
SELECT TOP 10 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
— Tidy up.
DROP TABLE #TempUnusedIndexesFigure 5 Identifying Most Costly Unused Indexes (单击该图像获得较大视图)此查询结果会显示尚未用于检索数据但已因基础表更改而更新的索引。这些更新显示在 user_updates 列和 system_updates 列中。结果按已应用到索引的用户更新数排序。为确保索引未被使用,您必须要收集足够多的信息,因为您不希望无意删除对可能仅每季度或每年运行一次的查询非常重要的索引。另外,请注意某些索引用于限制插入重复记录或对数据排序;在删除任何未使用的索引前,还必需考虑这些因素。查 询的基本形式仅适用于当前数据库,因为它联接到 sys.indexes DMV,而后者只与当前数据库有关。您可以使用系统存储过程 sp_MSForEachDB 提取服务器上所有数据库的结果。提取模式在侧栏“循环遍历所有数据库”中介绍。在其他要对服务器所有数据库遍历的脚本部分中,我也使用此模式。另外,我过 滤掉堆类型的索引,因为这些索引表示没有正式索引的表的本机结构。
高开销的已使用索引在已使用的索引中确定开销(即对基础表进行的更改)最高的索引很有用。此开销对性能有不良影响,但索引本身可能对数据检索非常重要。使 用 sys.dm_db_index_usage_stats DMV 可以了解使用索引的频率和程度。此 DMV 已联接到 sys.indexes DMV,后者包含创建索引时所使用的详细信息。检查 user_updates 和 system_updates 列将显示维护性最高的索引。图 6 提供了确定最高开销索引所使用的脚本并显示结果。  Figure 6 Identifying the Most Costly Indexes — Create required table structure only.
— Note: this SQL must be the same as in the Database loop given in the following step.
SELECT TOP 1
[Maintenance cost] = (user_updates + system_updates)
,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
,DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
INTO #TempMaintenanceCost
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], ‘IsMsShipped’) = 0
AND (user_updates + system_updates) > 0 — Only report on active rows.
AND s.[object_id] = -999 — Dummy value to get table structure.
;

— Loop around all the databases on the server.
EXEC sp_MSForEachDB ‘USE [?];
— Table already exists.
INSERT INTO #TempMaintenanceCost
SELECT TOP 10
[Maintenance cost] = (user_updates + system_updates)
,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
,DatabaseName = DB_NAME()
,TableName = OBJECT_NAME(s.[object_id])
,IndexName = i.name
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL — Ignore HEAP indexes.
AND OBJECTPROPERTY(s.[object_id], ”IsMsShipped”) = 0
AND (user_updates + system_updates) > 0 — Only report on active rows.
ORDER BY [Maintenance cost] DESC
;

— Select records.
SELECT TOP 10 * FROM #TempMaintenanceCost
ORDER BY [Maintenance cost] DESC
— Tidy up.
DROP TABLE #TempMaintenanceCostFigure 6 Identifying the Most Costly Indexes (单击该图像获得较大视图)结 果显示了维护性最高的索引以及有关数据库/表的详细信息。“维护开销”列的计算方式是将 user_updates 和 system_updates 列相加。索引的有用性(在“检索使用率”列中显示)的计算方式是将各 user_* 列相加。在决定是否要删除索引时,考虑索引的有用性非常重要。如果涉及大量的数据修改,这些结果可帮您确定在应用更新前应删除的索引。然后,可在应用所有更新后重新应用这些索引。

使用SQL Server动态管理视图确认缺失索引

由于有了很多新功能,我们可以看到在SQL Server 2005和现在有SQL Server 2008中,Microsoft引进了一些动态管理视图来协助确认基于查询历史的可能索引候选人。

  这些动态管理视图是:

  n sys.dm_db_missing_index_details –返回关于缺失索引的详细信息。

  n sys.dm_db_missing_index_group_stats – 返回缺失索引组的摘要信息

  n sys.dm_db_missing_index_groups – 返回一个具体组的缺失索引的信息。

  n sys.dm_db_missing_index_columns(index_handle) – 返回在一个索引中缺失的数据库表列的信息。这是一个函数,它要求传递index_handle。

  和大多数动态管理视图的跟踪统计数据一样,当SQL Server实例重启,这些数据被完全清除时,这些工作方式基本上是一样的。所以如果你在一个测试环境中工作并且重启你的SQL Server实例,那么这些视图有可能不返回数据。

  为了启动,我们将使用一个从SQL Server 2005联机帮助中得到的实例,这要求从AdventureWorks数据库中查询一张表而在StateProvinceID上没有索引,如下所示:

   USE AdventureWorks;
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 1;
GO

  一旦我们运行了上面的查询,数据在动态管理视图中应该可用。让我们来快速看下每一个查询。

   第一个查询从sys.dm_db_missing_index_details视图中获取数据。这可能是最有用的一个查询,因为这给我们展示了 object_id、equality_columns 和inequality_columns。另外,我们可以得到关于所含列的其它具体信息。

  SELECT * FROM sys.dm_db_missing_index_details

使用SQL Server动态管理视图确认缺失索引

  图一

  所以从上面我们执行的查询中,我们可以看到下面的信息:

  n equality_columns = "StateProvinceID",这是因为这个字段和一个相等运算符在WHERE从句中使用。所以SQL Server告诉我们这将是针对索引的很好的选择。

  n inequality_columns = "NULL",如果你使用其它的运算符比如不相等,那么这个字段将会有数据,但是由于我们使用等号,因此没有一个字段将在这里使用。

   n included_columns =这是当创建一个索引时使用的其它字段。由于这个查询只使用City、StateProvinceID 和 PostalCode,因此StateProvinceID将在索引中得到处理,当该索引创建时,其它两个字段可能被用作内嵌的字段。

   下一个索引从sys.dm_db_missing_index_group_stats中获取数据。这个查询使我们更了解其他统计数据,例如编译,用户查找, 用户扫描等,所以从这里我们可以知道这个查询多久会被访问。如果我们创建一个基于这些信息的新索引,这将帮助我们确定多久使用一个索引可以获得数据。

  SELECT * FROM sys.dm_db_missing_index_group_stats

  由于这个查询我们只执行了一次,因此我们的unique_compiles = 1 ,我们的 user_seeks = 1。如果我们再次运行这个查询,我们的user_seeks应该会增加。

使用SQL Server动态管理视图确认缺失索引

  图二

  下一个视图sys.dm_db_missing_index_groups将会给我们提供index_group_handle 和 index_handle的信息。

  SELECT * FROM sys.dm_db_missing_index_groups

使用SQL Server动态管理视图确认缺失索引

  图三

  从上面查询得到的结果基本上将用于从sys.dm_db_missing_index_columns函数中获得数据。index_handle值被传递到下一个查询,如下图所示。

  SELECT * FROM sys.dm_db_missing_index_columns(1)

使用SQL Server动态管理视图确认缺失索引

  图四

  要得到在一个结果集显示的所有数据,下面从SQL Server 2005联机帮助中得到的查询将为我们提供这些数据。

   SELECT mig.*, statement AS table_name,
column_id, column_name, column_usage
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id;

使用SQL Server动态管理视图确认缺失索引

  图五

  总结

  n 基于这个例子我们可以看到,我们可以在表AdventureWorks.Person.Address的字段StateProvinceID上创建一个新索引,也可以包括columns City和PostalCode。

  n 要注意的是当你在一张表中增加或者删除索引时,缺失索引的所有统计数据将在这张表中完全清除。

  n 尽管这可能不是完美的,也存在一些局限,但是这至少让我们了解了之前使用SQL Server旧版本时从来不知道的信息。

SQL Server 压缩日志及数据库文件大小

请按步骤进行,未进行前面的步骤时,请不要做后面的步骤,以免损坏你的数据库.

  一般不建议做第4,6两步,第4步不安全,有可能损坏数据库或丢失数据。第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复。

  1.清空日志

DUMP  TRANSACTION  库名  WITH NO_LOG

  2.截断事务日志

  BACKUP LOG 数据库名 WITH NO_LOG

  3.收缩数据库文件(如果不压缩,数据库的文件不会减小

  企业管理器–右键你要压缩的数据库–所有任务–收缩数据库–收缩文件

  ·选择日志文件–在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了

  ·选择数据文件–在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了,也可以用SQL语句来完成

  ·收缩数据库

  DBCC SHRINKDATABASE(客户资料)

  ·收缩指定数据文件,1是文件号,可以通过这个语句查询到:

select * from sysfiles
DBCC SHRINKFILE(1) 4.为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行)

  ·分离数据库:

  企业管理器–服务器–数据库–右键–分离数据库

  ·在我的电脑中删除LOG文件

  ·附加数据库:

  企业管理器–服务器–数据库–右键–附加数据库

  此法将生成新的LOG,大小只有500多K

  或用代码:

  下面的示例分离 pubs,然后将 pubs 中的一个文件附加到当前服务器。

·分离

  EXEC sp_detach_db @dbname = ‘pubs’

  ·删除日志文件

  ·再附加

EXEC sp_attach_single_file_db @dbname = ‘pubs’,
@physname = ‘c:Program FilesMicrosoft
SQL ServerMSSQLDatapubs.mdf’

  5.为了以后能自动收缩,做如下设置

  企业管理器–服务器–右键数据库–属性–选项–选择"自动收缩"

  SQL语句设置方式:

EXEC sp_dboption ‘数据库名’,
‘autoshrink’, ‘TRUE’

  6.如果想以后不让它日志增长得太大

  企业管理器–服务器–右键数据库–属性–事务日志

  将文件增长限制为xM(x是你允许的最大数据文件大小)

  SQL语句的设置方式:

alter database 数据库名 modify file(name=逻辑文件名,maxsize=20)

个人的验证:

–收缩数据库日志,在这之前需要有完整的数据库备份

BACKUP LOG chinatown2 With no_log

DBCC SHRINKFILE (chinatown_log, 20480)

上面这两步可以