数据库性能3-CSDN博客

使用Profiler找出低效的查询

监控列表	你的答案
你分辨过所有长时间运行的查询吗？
对这些查询你区分优先次序了吗？
你重新查看过上面区分优先次序的查询的执行计划了吗？

在上表输入你的结果

第一步是分辨出长时间运行的查询

到SQLServer性能监控的这一步止，你应该已经能分辨出所有容易纠正的性能问题了。现在是着手处理更差的查询（包括存储过程）的时候了，那些比预期运行时间更长的占用大量SQLServer共享资源的查询和存储过程。

运行慢的查询执行要花费太长的时间。那么究竟多长才算长呢？这得由你决定。通常说来，我用5秒作为一个坎儿。换句话说，任何一个查询运行5秒或更少通常就算足够快了，而查询超过5秒就算长了。这是一个你不得不做出的武断的决定。在我工作的公司，报表开发人员要写大量的和我有不同标准的征对数据库的查询。他们考虑的时长为30秒。所以，第一步就是决定多长时间的查询才算长，然后在你的服务器性能监控期间使用这个作为你的标准。

我们不能无限制的调优查询。我们所能做的就是分辨出那些需要更多工作的查询，然后征对它们进行调优。如果有时间的话，为了全面提升SQLServer的性能，可以着眼于那些稍慢但仍然讨厌的查询。记住有些时候，无论你怎么努力调优一个特殊的查询，可能仅有一点或根本没有性能上的改善。

开始之前

对于这部分性能监控，你将使用SQLServer自带的事件探查器。本篇文章仅着眼于怎样进行性能监控，而不是工具的使用，所以假定你知道怎样使用事件探查器。如果你以前没有用过它，查看BOL以获得一些基本的帮助。

在你使用事件探查器捕捉你的SQLServer查询活动之前，记住下面的：

不要在你要监控的同一台服务器上运行事件探查器，这对服务器性能有一个明显的影响。相反，在另一个服务器或工作站上运行，然后在那儿收集数据。
当运行事件探查器时，不要选择比需要收集更多的数据。你收集的数据越多，用来收集它们而使用的资源就越多，这会降低性能。仅仅选择那些你真正需要的事件和数据列。我的建议是所收集的真正的要简短。
在一段典型的服务器运行时间内收集数据，即典型的3-4小时的时间。这也可以改变，依赖于你服务器繁忙的程度。如果你没有这样的时间，你可以通过一个典型的生产日的几个不同时间段来收集你需要的所有数据。

当你使用事件探查器时，你有两个选项去启动它。一个是使用GUI界面，或者如果你喜欢的话，可以使用内建的事件探查器系统存储过程。虽然使用GUI有点简单，但使用系统存储过程收集数据的开销稍微的少一些。本文将使用GUI界面。

收集什么数据

事件探查器允许你指定哪些事件需要捕捉，那些事件的哪些数据列需要捕捉。另外，你可以使用筛选来减少数据而仅要哪些分析需要的社会局。下面是我的建议：

要捕捉的事件

Stored Procedures--RPC:Completed
TSQL--SQL:BatchCompleted

你也许会惊讶怎么只有两个不同的事件需要捕捉：一个用来捕捉存储过程一个用来捕捉所有其他的TSQL查询。

需要捕捉的数据列

Duration (数据需要通过duration来分组)
Event Class
DatabaseID (如果服务器上有多个数据库的话)
TextData
CPU
Writes
Reads
StartTime (可选的)
EndTime (可选的)
ApplicationName (可选的)
NTUserName (可选的)
LoginName (可选的)
SPID

你实际上要捕捉和查看的数据包括一些对你来说很重要的数据，特别是duration和TextData；一些就不那么重要了，但也有用，如ApplicationName和NTUserName。

用于筛选

Duration > 5000 毫秒 (5秒)
不要收集系统事件
通过单独的数据库ID而不是一次所有的数据库都收集数据
其他适当的筛选

筛选被用来收集数据的数量，使用筛选越多，你能筛选掉的不重要的数据就越多。一般说来，我使用3个筛选，但其他的也能根据你的情形适当的使用。其中最重要的是duration。我仅收集那些对我来说很重要的有足够duration的信息，正如已经讨论的那样。

收集数据

依赖于使用的筛选、运行事件探查器收集数据的时间、服务器繁忙程度，你可以收集大量的数据行。虽然你有几个选择，我建议你配置事件探查器保存数据到本地计算机的文件上（而不是你跟踪的服务器上）并且不设置文件的最大尺寸，相反，让文件按需增长。你要查看文件的增长量，万一它无法控制。大多数情况下，如果你使用了正确的筛选，文件大小会便于处理的。我建议使用一个大的文件因为如果你那样做的话很容易分辨出长时间运行的查询。

正如前面所述，在一个典型的生产期间收集你的跟踪文件，大约3-4小时为一期限。当收集数据后，可使用duration来分类，运行时间最长的查询出现在跟踪窗口的底部。当你收集数据的时候有兴趣的话可以看一会儿窗口。如果你喜欢，可以配置在适当的时候自动关闭事件探查器，也可以手动关闭。

一旦时间到跟踪停止了，事件探查器的跟踪现在存在本地计算机的内存和磁盘上。现在你准备去分辨那些长时间运行的查询了。

分析数据

我猜你已经能分辨出所有在跟踪收集期间运行的超过你指定的duration的所有查询，不管是不是。所以如果你指定duration为5秒，那么你将只看到那些运行超过5秒的查询。根据定义，你捕捉的所有查询都需要调优。"什么!但捕捉到了500多个查询啊! 那可是一项大工程!"那并不是你想象的那么糟。大多数情况下，你捕捉的很多查询是重复的。换句话说，你可能在你的跟踪里一再地捕捉了同样的查询。所以，那些500多个捕捉到的查询也许仅仅只有10个或50个或100不重复的查询。另一方面，也许捕捉到的只是少数的查询，如果你够幸运的话。

无论是少数查询还是大量运行慢的查询，你接下来的工作是首先决定哪一个查询对你的分析和调优来说是最重要的。这需要你设置优先级，因为你可能没有足够的时间去分析所有的查询。

为了设置这些长时间运行的查询的优先级，你可能首先要着眼于那些运行最长的查询。但当你这么做时，要记住每个查询运行的频率。

例如，如果你指定一个特定的查询仅仅是为了生成报表而一个月只运行一次（碰巧在它运行的时候你捕捉到了），这个查询运行花了60秒，它可能没有那些运行花了10秒但1分钟运行了10次的查询的优先级高。换句话说，你需要平衡查询运行的时长和频率。谨记这一条：你需要分辨并设置那些花费最多SQLServer物理资源的查询的优先级。一旦你做完这件事，就可以准备分析和调优了。

通过查看执行计划分析查询

为了分析你捕捉到的已经设置优先级的查询，你需要把代码移到查询分析器里以便能查看执行计划，分析查询。本篇文章着重在监控，而不是分析，我们不会在这里花费时间去向你展示怎样分析特定的查询。这本身是一个很大的课题，将在其他地方做介绍。

为了分析你怎样移到代码到查询分析器里依赖于代码。如果你捕捉到的代码是TSQL，你可以剪切，然后直接在查询分析器里粘贴。但如果代码是在存储过程里，你不得不稍微多做一点工作，因为事件探查器不会显示存储过程里的代码，而仅显示存储过程的名称，包括传给它的所有参数。这样，为了在查询分析器里分析查询，你必须考虑到存储过程里，将代码复制粘贴到查询分析器里。然后，假定那儿也有一些参数了，你不得不手工更改代码以便它能带着参数运行而被事件探查器捕获。

现在耗时的杂事开始了，分析每一个查询的执行计划看看有没有能改善性能的查询需要调优。但是因为你已经分辨和设置这些查询的优先级可，所以你的时间将更有效率。

使用Profiler找出低效的查询

监控列表	你的答案
你分辨过所有长时间运行的查询吗？
对这些查询你区分优先次序了吗？
你重新查看过上面区分优先次序的查询的执行计划了吗？

不要在你要监控的同一台服务器上运行事件探查器，这对服务器性能有一个明显的影响。相反，在另一个服务器或工作站上运行，然后在那儿收集数据。
当运行事件探查器时，不要选择比需要收集更多的数据。你收集的数据越多，用来收集它们而使用的资源就越多，这会降低性能。仅仅选择那些你真正需要的事件和数据列。我的建议是所收集的真正的要简短。
在一段典型的服务器运行时间内收集数据，即典型的3-4小时的时间。这也可以改变，依赖于你服务器繁忙的程度。如果你没有这样的时间，你可以通过一个典型的生产日的几个不同时间段来收集你需要的所有数据。

Stored Procedures--RPC:Completed
TSQL--SQL:BatchCompleted

你也许会惊讶怎么只有两个不同的事件需要捕捉：一个用来捕捉存储过程一个用来捕捉所有其他的TSQL查询。

需要捕捉的数据列

Duration (数据需要通过duration来分组)
Event Class
DatabaseID (如果服务器上有多个数据库的话)
TextData
CPU
Writes
Reads
StartTime (可选的)
EndTime (可选的)
ApplicationName (可选的)
NTUserName (可选的)
LoginName (可选的)
SPID

Duration > 5000 毫秒 (5秒)
不要收集系统事件
通过单独的数据库ID而不是一次所有的数据库都收集数据
其他适当的筛选

服务器端的事件探查器跟踪

当我们要监控SQL Server上运行的某些查询语句时，大部分人都是启动事件探查器来进行跟踪。但大部分人不了解其实启动事件探查器有两种方法：
1. 启动事件探查器的图形界面来跟踪
2. 服务器端的事件探查器跟踪

图形界面下启动的事件探查器跟踪会对性能有影响，根据捕捉事件的不同，这种影响可能会高达25％。所以，如果你使用事件探查器跟踪来长时间的监控你的SQL Server的话，一般总是建议你设置服务器端的事件探查器跟踪。

设置服务器端的事件探查器跟踪的步骤：

1、启动事件探查器的图形界面来创建一个跟踪，然后将这个跟踪用脚本的形式导出，假设导出的文件名为sql_trace.sql（在SQL Server 2000的事件探查器里，单击文件→脚本跟踪→用于SQL Server 2000即可，在SQL Server 2005的事件探查器里，单击文件→导出→编写跟踪定义的脚本→用于SQL Server 2005即可）。
2、在查询分析器或者SSMS的查询窗口里打开sql_trace.sql文件并做下面的修改

3、在行“exec @rc = sp_trace_create @TraceID output, 2, N'', @maxfilesize, NULL”里，更改你想要保存跟踪文件的位置
4、确保@maxfilesize参数设置为350（这确保你翻滚到的下一个跟踪文件最大只能达到 350M ）
5、执行这个脚本来创建跟踪。这会产生一个跟踪ID号
6、要停止跟踪，使用sp_trace_setstatus @TraceID,0
7、要关闭指定的跟踪并从服务器里删除它的定义，使用sp_trace_setstatus @TraceID,2

在SQLServer2005和SQLServer2000里怎样监视阻塞

综述
本文讲述怎样设计和使用一个存储过程来诊断阻塞和性能问题。

在SQLServer2005里，你可以在事件探查器里使用Blocked Process Report事件类来捕捉阻塞。关于该事件类的更多信息，请查看MSDN网站：http://msdn2.microsoft.com/en-us/library/ms191168.aspx

更多信息

下面描述了存储过程sp_blocker_pss80捕获的信息，该信息也适用于SQLServer2005。

开始时间（根据运行SQLServer的计算机确定），便于阻塞的取样能和其他性能信息的时间对照，如性能监视器或事件探查器日志
关于连接到SQLServer的信息，通过查询系统表sysprocesses可以得到
关于锁资源，通过查询系统表syslockinfo可以得到
关于资源等待信息，通过运行DBCC SQLPERF(WAITSTATS)可以得到
目前运行的被其他连接阻塞或阻塞其他连接的批语句，通过可以DBCC INPUTBUFFER得到
结束时间，根据运行SQLServer的计算机确定

为了尽可能的减少运行该存储过程的影响，创建过程时按照下面的规则：

除非至少有一个连接在等待资源，否则不输出任何结果。
为了提升性能并禁止该过程被阻塞，系统表sysprocesses和syslockinfo直接从master数据库里查询。因此，该过程用于SQLServer2000及以上版本。
为了获得DBCC INPUTBUFFER的输出结果，通过游标创建一个小表。这对tempdb数据库的使用没有明显的影响。
由于收集信息时阻塞可能改变，fast mode的存在可以通过减少系统表sysprocesses和syslockinfo的相关行数来增加性能。
如果试图跟踪non-lock资源等待，latch mode的存在可以忽略锁的输出结果。

从任何一个查询工具里运行该存储过程是有用的。然而，微软建议你按照下面的步骤进行阻塞分析：

创建存储过程sp_blocker_pss80，本文结束有脚本，从任何一个查询工具用具有sysadmin权限登录到SQLServer服务器或你计划监控的实例。
创建一个如下的脚本循环的运行存储过程。注意应该延迟5到60秒：
WHILE 1=1
BEGIN
EXEC master.dbo.sp_blocker_pss80
-- Or for fast mode
-- EXEC master.dbo.sp_blocker_pss80 @fast=1
-- Or for latch mode
-- EXEC master.dbo.sp_blocker_pss80 @latch=1
WAITFOR DELAY '00:00:15'
END
GO
一并使用性能监视器日志和事件探查器日志时输出结果更有用，所以建议同时创建它们。关于事件探查器和性能监视器需要捕捉的信息和信息结果的解释，请点击这里查看微软知识库。
为了让监视用的查询工具避免网络不能连接连的问题，从Isql.exe、Osql.exe查询工具或者运行SQLServer的计算机的命令提示符工具 Sqlcmd运行第二步创建的脚本文件。下面的例子是从命令行运行Osql.exe工具，假定客户端运行在SQLServer运行的计算机上，脚本文件名为Checkblk.sql。确保-S参数正确，将server替换为你SQLServer服务器的名称（或服务器名称/实例名，如果你监视的是一个命名实例的话）。-i 参数也要正确，将checkblk.sql替换为你第二步里创建的脚本文件的路径和文件名。
osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000

注意由于下面的原因你必须使用其他命令行

为了更易读，禁止在输出文件里换行
为了将输出结果保持到文件而不是屏幕，使用-o参数，以便查询工具有问题的话，你仍然有到查询工具失败时的输出结果。

存储过程sp_blocker_pss80的脚本如下：

create procedure dbo.sp_blocker_pss80 (@latch int = 0, @fast int = 1, @appname sysname='PSSDIAG')
as
--version 17SP3
if is_member('sysadmin')=0
begin
print 'Must be a member of the sysadmin group in order to run this procedure'
return
end

set nocount on
SET LANGUAGE 'us_english'
declare @spid varchar(6)
declare @blocked varchar(6)
declare @time datetime
declare @time2 datetime
declare @dbname nvarchar(128)
declare @status sql_variant
declare @useraccess sql_variant

set @time = getdate()
declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,
ignore_app tinyint, primary key (blocked, spid, ecid))
insert @probclients select spid, ecid, blocked, waittype, dbid,
case when convert(varchar(128),hostname) = @appname then 1 else 0 end
from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)
begin
set @time2 = getdate()
print ''
print '8.2 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2))

insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients
where blocked not in (select spid from @probclients) and blocked != 0

if (@fast = 1)
begin
print ''
print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

select spid, status, blocked, open_tran, waitresource, waittype,
waittime, cmd, lastwaittype, cpu, physical_io,
memusage, last_batch=convert(varchar(26), last_batch,121),
login_time=convert(varchar(26), login_time,121),net_address,
net_library, dbid, ecid, kpid, hostname, hostprocess,
loginame, program_name, nt_domain, nt_username, uid, sid,
sql_handle, stmt_start, stmt_end
from master.dbo.sysprocesses
where blocked!=0 or waittype != 0x0000
or spid in (select blocked from @probclients where blocked != 0)
or spid in (select spid from @probclients where blocked != 0)

print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))

print ''
print ' SYSPROC FIRST PASS '
select spid, ecid, waittype from @probclients where waittype != 0x0000

if exists(select blocked from @probclients where blocked != 0)
begin
print 'Blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
print 'SPIDs at the head of blocking chains'
select spid from @probclients
where blocked = 0 and spid in (select blocked from @probclients where spid != 0)
if @latch = 0
begin
print 'SYSLOCKINFO'
select @time2 = getdate()

select spid = convert (smallint, req_spid),
ecid = convert (smallint, req_ecid),
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
Type = case rsc_type when 1 then 'NUL'
when 2 then 'DB'
when 3 then 'FIL'
when 4 then 'IDX'
when 5 then 'TAB'
when 6 then 'PAG'
when 7 then 'KEY'
when 8 then 'EXT'
when 9 then 'RID'
when 10 then 'APP' end,
Resource = substring (rsc_text, 1, 16),
Mode = case req_mode + 1 when 1 then NULL
when 2 then 'Sch-S'
when 3 then 'Sch-M'
when 4 then 'S'
when 5 then 'U'
when 6 then 'X'
when 7 then 'IS'
when 8 then 'IU'
when 9 then 'IX'
when 10 then 'SIU'
when 11 then 'SIX'
when 12 then 'UIX'
when 13 then 'BU'
when 14 then 'RangeS-S'
when 15 then 'RangeS-U'
when 16 then 'RangeIn-Null'
when 17 then 'RangeIn-S'
when 18 then 'RangeIn-U'
when 19 then 'RangeIn-X'
when 20 then 'RangeX-S'
when 21 then 'RangeX-U'
when 22 then 'RangeX-X'end,
Status = case req_status when 1 then 'GRANT'
when 2 then 'CNVT'
when 3 then 'WAIT' end,
req_transactionID As TransID, req_transactionUOW As TransUOW
from master.dbo.syslockinfo s,
@probclients p
where p.spid = s.req_spid

print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))
end -- latch not set
end
else
print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
end -- fast set

else
begin -- Fast not set
print ''
print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))

print ''
print 'SYSPROC FIRST PASS'
select spid, ecid, waittype from @probclients where waittype != 0x0000

print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))
end -- latch not set
end
else
print 'No blocking via locks at ' + convert(varchar(26), @time, 121)
print ''
end -- Fast not set

print 'DBCC SQLPERF(WAITSTATS)'
dbcc sqlperf(waitstats)

Print ''
Print '*********************************************************************'
Print 'Print out DBCC Input buffer for all blocked or blocking spids.'
Print '*********************************************************************'

declare ibuffer cursor fast_forward for
select distinct cast (spid as varchar(6)) as spid
from @probclients
where (spid <> @@spid) and
((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))
or spid in (select blocked from @probclients where blocked != 0))
open ibuffer
fetch next from ibuffer into @spid
while (@@fetch_status != -1)
begin
print ''
print 'DBCC INPUTBUFFER FOR SPID ' + @spid
exec ('dbcc inputbuffer (' + @spid + ')')

fetch next from ibuffer into @spid
end
deallocate ibuffer

Print ''
Print '*******************************************************************************'
Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'
Print '*******************************************************************************'
declare ibuffer cursor fast_forward for
select distinct cast (dbid as varchar(6)) from @probclients
where dbid != 0
open ibuffer
fetch next from ibuffer into @spid
while (@@fetch_status != -1)
begin
print ''
set @dbname = db_name(@spid)
set @status = DATABASEPROPERTYEX(@dbname,'Status')
set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')
print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'
if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'
dbcc opentran(@dbname)
else
print 'Skipped: Status=' + convert(nvarchar(128),@status)
+ ' UserAccess=' + convert(nvarchar(128),@useraccess)

print ''
if @spid = '2' select @blocked = 'Y'
fetch next from ibuffer into @spid
end
deallocate ibuffer
if @blocked != 'Y'
begin
print ''
print 'DBCC OPENTRAN FOR DBID 2 [tempdb]'
dbcc opentran ('tempdb')
end

print 'End time: ' + convert(varchar(26), getdate(), 121)
end -- All
else
print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '
+ convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)')

MSSQL2005处理数据库置疑
--地宝原创，转贴请注明出自微软BI开拓者www.windbi.com

很多朋友在2005中遇到置疑、丢失日志时按照网上常见的MSSQL2000修复方法来做，
结果发现行不通，甚至连一步都做不下去。其实，在MSSQL2005在处理置疑问题的思
路与MSSQL2000是一致的，但具体到一些语句上MSSQL2005 有了很大的变动。我们以
一个被误删日志文件的数据库为例：
1．MSSQL2000在日志被误删除后会提示置疑（suspect），而在MSSQL2005中却没有特
殊标志，但图标前的”＋”不见了当你查询数据库属性时会有Error:945提示：

2．顺着老思路，首先我们想到先进入紧急模式：
MSSQL2000进入紧急模式：
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
update sysdatabases set status = 32768 where name = 'dbname'
MSSQL2005进入紧急模式的方法简单了
alter database dbname set emergency

3.重建日志文件
MSSQL2000使用DBCC REBUILD_LOG。
在MSSQL2005中没有了DBCC REBUILD_LOG
可MSSQL2005中是使用dbcc checkdb('capture',REPAIR_ALLOW_DATA_LOSS)。
当然要想使用dbcc checkdb我们得进入单用户模式：
sp_dboption 'dbname', 'single user','true'
或
alter database dbname set single_user（推荐使用后者）

4.再运行dbcc checkdb看看是否有损坏的页（我会再写一篇专题讨论修复损害页）

5．恢复多用户模式
sp_dboption 'dbname', 'single user','false'
或
alter database dbname set multi_user（推荐使用后者）

6．恢复非紧急模式
alter database dbname set online