SQL Server 2005性能排错(1)

select 
检测
内部查询的并行问题可以通过下列方法检测。
系统监视器(Perfmon)
考虑SQL Server:SQL Statistics – Batch Requests/sec 计数器，并查看SQL Server联机丛书中的“SQL Statistics Object”获取更多信息。
因为在考虑使用并行计划前，查询必须评估开销超过为并行配置设置的开销阀值（默认被设置为5），服务器每秒处理的批小于运行在并行计划中的批。运行很多并行查询的服务器一般配置为较小的每秒批请求数（例如，小于100的值）。
DMVs
在运行的服务器上，你可以使用下列查询确认在给定会话中是否可以并行运行任何活动的请求。



select 
拙劣游标使用
SQL Server 2005之前的SQL Server 版本仅支持在每个连接上有单个活动的操作。一个查询正在执行或有了结果等待发送到客户端时将被认为是活动的。在一些情形中，客户端应用程序也许需要从结果中读取并向SQL Server提交其他基于刚刚从结果集中读取的行的查询。这在默认的结果集中是不能实现的，因为还有其他等待的结果。一般的解决方法是改变连接属性是用服务器端游标。
当使用服务器端游标，数据库客户端软件(OLE DB提供者或ODBC驱动)显然会封装客户端请求在特殊的扩展存储过程中，例如sp_cursoropen，sp_cursorfetch等等。这提到了API游标（而不是TSQL游标）。当用户执行查询，查询文本通过sp_cursoropen被发送到服务器，请求读取从sp_cursorfetch指示服务器进发送某些数量的行。通过控制获取行的数量，可以为ODBC驱动或OLE DB提供者缓存行。这阻止发生服务器等待客户端都区所有发送的行的情形。因此，服务器可以在这个连接上接受新的请求。
一次性打开游标并获取1行（或少量行）的应用程序能被网络延时的网络瓶颈影响，特别是在广域网（WAN）。在有快速网络并有不同用户连接时，处理很多游标请求的开销变得更重要。因为开销来自于游标位置的变化来适应在结果集上的位置改变，预请求的处理开销，类似的处理，服务器处理1个请求返回100行必处理100不同请求相同的100行但是每次1行更有效率。
检测
你可以使用下列方法为拙劣游标使用排错。
系统监视器(Perfmon)
通过考虑SQL Server:Cursor Manager By Type – Cursor Requests/Sec计数器，你可以通过这个性能计数器知道有多少游标在系统中使用。系统还有很高的CPU利用率，因为小量的读取通常会有每秒数百个游标请求。这里没有特殊的计数器告诉你关于获取的缓存大小。
DMVs
接下来的查询可以用于测定使用API游标（不是TSQL游标）连接获取一行使用的缓存大小。它对于大的获取缓存更有效，例如100行。



select 
内存压力
内存压力表示当可用内存数量受到限制。识别SQL Server何时运行在内存压力下将帮助你排除内存相关的问题。SQL Server依赖于不同类型的内存压力特征也不一样。下表汇总了内存压力类型，和他们潜在的原因。在所有的情况下，你可以更多的会见到超时或显示的内存不足错误消息。
表 2




压力

外部

内部


物理

物理内存(RAM)运行值低。这导致系统整理当前运行的工具集，导致整体性能下降。
 
SQL Server监测到这种条件，依赖于配置，可以减少缓存池的目的提交并开始清理内部缓存。

SQL Server检测内部较高的内存消耗，导致在不同内部组件间的内存重新分配。
 
内部内存压力可以导致：
·         导致外部内存压力（SQL Server设置地的内存使用能力）。
·         改变内存设置（例如‘max server memory’）。
·         改变内部组件的内存分布（导致预留的高百分比并从缓存池中获取页）。


虚拟的

在系统页面文件运行在较低值。这样可以导致系统分配内存失败。不能扩展当前的内存分配。这可以导致着整个系统响应很慢或者可能导致系统关机。

在VAS运行值低，导致分页（很多VAS可用，但是被分为小块）与/或消耗（直接分配，DLL加载到SQL VAS，大量的线程）。
 
SQL Server检测到这种条件并可以释放VAS中保留的区域，减少缓存池提交的目标并开始收缩缓存。

Windows有通知的机制  假如物理内存运行在过高或过低的情况下。SQL Server在他的内存管理决策中使用这种机制。
一般排错的步骤显示在表3中。
表 3




压力

内部

外部


物理

·         找到主要的系统内存消耗组件。
·         尝试消除消耗(假如可能)。
·         检查适当的系统RAM和考虑添加额外RAM（通常需要更仔细研究）

·         识别SQL Server内主要的内存消耗
·         确认系统配置。
·         进一步操作依赖于研究；检查负载；可能出现的设计问题；其他的资源瓶颈。


虚拟

·         增加交换文件大小。
·         检查主要物理内存的使用和外部物理内存压力调用步骤。

·         外部物理内存压力调用步骤。
工具

下列工具和资源可以用于排错。
◆内存相关的DMVs
外部虚拟内存压力
你需要确定是否页面文件为当前内存的分配能提供足够的空间。为了检查，可以打开任务管理器中的性能视图，并检查Commit Charge节。假如Total接近于Limit则说明可以被提交的最大数量内存没有扩展页面的空间。注意任务管理器中的Commit Charge Total指出潜在使用的页面文件，而不是实际使用值。实际使用的页面文件将增加物理内存压力。
同样可以通过下列技术起获取相关信息：Memory: Commit Limit, Paging File: %Usage, Paging File: %Usage Peak。
你可以通过每个进程的Process: Working Set减去Process Private Bytes计数器来评估内存总数。
假如Paging File: %Usage Peak（或Peak Commit Charge）很高，检查系统日志中是否有指出页面文件增长或通知“running low on virtual memory”的信息。你可能需要增加你的页面文件大小。High Paging File: %Usage指出物理内存超过要提交的值并也要考虑外部物理内存压力（大量的内存需求，没有足够的RAM）。
内部物理内存压力
内部内存压力来自于SQL Server自身，应首先通过检查在缓存分布中的异常来考虑在SQL Server内存分布。通常在SQL Server中缓存会占用最多提交的内存。为了确定在缓存池中的内存总数，我们可以使用DBCC MEMROYSTATUS命令。在Buffer Counts节，可以找到Target值。下列输出显示了在服务器达到正常负载时DBCC MEMORYSTATUS的结果。



Buffer Counts                  Buffers
------------------------------------------ ---------
每个组件的详细信息可以通过下列语句获取（这包括从缓存池内和缓存池外分配的内存）。



declare @total_alloc bigint 
Ring buffers
更多有意义的内存调试信息可以通过sys.dm_os_ring_buffers的ring buffers DMV获取。每个ring buffer保留了之前几次某种类型的通知。指定ring buffer的详细信息将在下面描述。
RING_BUFFER_RESOURCE_MONITOR
你可以使用从资源监视器的通知识别内存改变的状态。在内部，SQL Server有一个监视不同内存压力的架构。当内存状态改变，资源监视器任务生成一个通知。这个通知用于内部组件根据内存状态调整它们内存使用并通过sys.dm_os_ring_buffers DMV来暴露，如下列代码所示。



select record 
RING_BUFFER_BUFFER_POOL
ring buffer将包含预示严重的缓存池失败的记录，包括缓存池溢出的条件。



select record 
排错
不论是否错误是持续的和可重复的（同样状态）或是随机的（显示为随机的不同状态），当你看到这个错误时你需要研究服务器内存分布。当这个错误出现时，可能导致诊断查询失败。可以开始从外部进行评估。接下来的步骤在一般的内存错误排错步骤中描述。
可能的解决方案包括：除去外部内存压力；增加max server memory设置。使用下列语句释放缓存DBCC FREESYSTEMCACHE，DBCC FREESESSIONCACHE，或 DBCC FREEPROCCACHE。假如问题还是出现，则应减少工作负载。
802 - There is insufficient memory available in the buffer pool.
原因
这个错误不是引起内存不足的必要条件。它可能预示缓存池内存被其他的一些组件使用。在SQL Server 2005中很少出现。
排错
使用常规的排错步骤和701错误的建议。
8628 - A time out occurred while waiting to optimize the query. Rerun the query.
原因
这个错误指出查询编译过程失败因为它不能获取完成编译所需的内存。当查询经历了这种编译过程，包括解析，代数求解和优化，它的内存需求会增加。因而查询将和其他的查询争夺内存资源。假如查询超过的预定的超时时间（查询增加内存的占用） 当等待资源时，这个错误会返回。这种情况的最可能的原因是在服务器上出现很多大型查询的编译。
排错
1.在常规的排错步骤后查看是否服务器内存占用正常。
I/O 瓶颈
SQL Server性能非常依赖于I/O子系统。除非你的数据库适合物理内存，SQL Server经常地会有数据库页面进出缓存池。这样就发生了实质的I/O流量。同样，在事务被明确的提交前，日志记录需要写入磁盘。SQL Server为各种目的可以使用tempdb，例如存储中间结果，排序，保持行的版本或其他。所以好的I/O子系统对于SQL Server性能非常重要。
除了当事务需要回滚时，访问日志文件是非常频繁的，而包括访问数据文件，包括tempdb，是随机访问的。所以作为一个通常的规则，为获取更好的性能，你最好将日志文分布不到不同的物理磁盘。本文重点于不是在如何配置你的I/O设备，而是描述识别你的I/O瓶颈的方法。一旦识别了I/O瓶颈，你也许需要重新配置你的I/O子系统。
假如你有一个慢速的I/O子系统，你的用户会遇到性能问题，例如很长的响应时间，任务由于超时而中断。
你可以使用下列性能参数来识别I/O瓶颈。注意，假如你的收集间隔过长，这些平均值可能会倾向于过小。例如，很难通过60秒一次的快照获取正确的I/O值。所以，你不能依赖于一个计数器来确定瓶颈；要考虑多个技术起来反复校验。
◆PhysicalDisk Object: Avg. Disk Queue Length表现在采样周期中所选择的物理磁盘队列中的物理读和写平均请求数量。假如你的I/O系统过载，更多的读/写操作将被等待。假如在很少使用SQL Server时，你的磁盘队列长度经常超过2，这样你可能遇到了I/O瓶颈
解决
当你识别到I/O瓶颈时，你可以下列方法解决：
◆检查SQL Server的内存配置。假如SQL Server配置的内存不足，将导致更多的I/O开销。你可以考察下列计数器来识别内存压力。
同样的你可以运行下列查询，找到查询计划确定是否当选择了较差的计划时有大量的I/O。 



select * 
版本存储
SQL Server 2005提供了行版本架构用于实现一些特性。如下列出了使用行版本架构的特性。更多关于下列特性的信息，请参考SQL Server 联机丛书。
◆触发器
这是示例的输出。



session_id allocated            deallocated
过多的DLL和分配操作
在tempdb中2个原因可以导致这个结果。
◆创建和删除大量的临时表和标变量导致在元数据上的争夺。在SQL Server 2005中，本地的临时表和标变量被缓存来最小化元数据的争用。然而只有下列条件满足时，表才会被缓存。
为了找到准许的锁或等待锁的会话，你可以使用sys.dm_tran_locks DMV。每行数据展现了发送到锁管理器的当前活动的请求。为了有序的锁，准许请求指出了锁已经在资源上被准许给请求者。一个等待的请求指出了请求没有被准许。例如下列查询显示会话56被阻塞在资源1:143:3，该资源被会话53的X模式锁占有。



select 
通过sys.dm_db_index_operational_stats查看阻塞的每个对象
新的SQL Server 2005 DMV Sys.dm_db_index_operational_stats提供了全面的索引使用统计，包括阻塞。根据阻塞，它提供了每个表，索引，分区的锁统计的详细信息。例如，在给定索引和表上的访问历史，锁数量（row_lock_count），阻塞数量（row_lock_wait_count）和等待时间(row_lock_wait_in_ms)等信息。
这个DMV包括的类型信息有：
◆占有的锁的数量，例如行或页。
使用SQL waits阻塞对整体性能的影响
SQL Server 2000提供了76种等待类型来提供等待报告。SQL Server 2005提供了多余100个等待类型来跟踪应用程序性能。任何时间1个用户连接在等待时，SQL Server会累加等待时间。例如应用程序请求资源例如I/O，锁或内存，可以等待资源直到可用。这些等待信息可以跨所有连接将被汇总和分类，所以性能配置可以从给定的负载获得。因此，SQL等待类型从应用程序负载或用户观点识别和分类用户（或线程）等待。
这个查询列出了在SQL Server中前10位的等待。这些等待时累积的，但是你可以使用DBCC SQLPERF ([sys.dm_os_wait_stats], clear)重置这个计数器。



select top 10 *
下列是在SQL Server 2005使规则和任务的定义：
◆SEEK: 识别用于访问数据的B树结构数量。不论B树结构只是访问每级只有少量页面来获取一个数据行，还是是表中使用半索引页面读取如几个G数据或百万行的数据。所以我们希望在这个类别有更多的累计。
分析操作的索引统计
这套存储过程可以用于分析索引的使用。
get_indexstats



create proc dbo.get_indexstats 
case 
insert_indexstats



create proc insert_indexstats (@dbid smallint=NULL,
add_column



create proc dbo.add_column (
get_waitstats_2005



CREATE proc [dbo].[get_waitstats_2005] (
---- compute cpu resource waits

 

目录
简介 
检测

低效率的查询计划通常可以被检测出来。低效率的查询计划可以导致增加CPU的消耗。
查询sys.dm_exec_query_stats是确定哪个查询累计使用CPU时间最多的有效方法。



select 
------------------------------------------ ---------
这是示例的输出。



session_id allocated            deallocated
case 
---- compute cpu resource waits

 
 
 


	
	

	
	

 上一篇返回首页 下一篇
			
	

声明：   此文观点不代表本站立场；转载务必保留本文链接；版权疑问请联系我们。
	
	

				

					
别人在看
					
									
				

			

	


		

			
正版 Windows 11产品密钥怎么查找/查看？
			
		
 

		

			
还有3个月，微软将停止 Windows 10 的更新
			
		
 

		

			
Windows 10 终止支持后，企业为何要立即升级？
			
		
 

		

			
Windows 10 将于 2025年10 月终止技术支持，建议迁移到 Windows 11
			
		
 

		

			
Windows 12 发布推迟，微软正全力筹备Windows 11 25H2更新
			
		
 

		

			
Linux 退出 mail的命令是什么
			
		
 

		

			
Linux 提醒 No space left on device，但我的空间看起来还有不少空余呢
			
		
 

		

			
hiberfil.sys文件可以删除吗？了解该文件并手把手教你删除C盘的hiberfil.sys文件
			
		
 

		

			
Window 10和 Windows 11哪个好？答案是：看你自己的需求
			
		
 

		

			
盗版软件成公司里的“隐形炸弹”？老板们的“法务噩梦” 有救了！
			
		
 	
			
		
		
	
			
		
	
		

	
			

	

		

			

				
				
				

					在上方输入关键词后，回车键 开始搜索。Esc键 取消该搜索窗口。