发表于:2008.03.06 16:37
分类: SQLSERVER
出处：http://zhouwf0726.itpub.net/post/9689/456617
---------------------------------------------------------------
在查询中涉及的表上运行UPDATE STATISTICS，检查是否还有这种问题。
是否查询使用的构造导致优化器不能精确的评估？
考虑是否可以将查询修改为其他的方法，避免这种问题。
◆如果不能修改架构或查询，SQL Server 2005有一个新的查询计划特性，允许你将指定查询提示添加到满足某种文本的查询中。这可以用在独立查询中，也可以用在存储过程内。例如OPTION (OPTIMIZE FOR)这样的提示允许你影响评估而忘记所有列出的潜在计划。其他的提示，类似OPTION(FORCE ORDER)或 OPITON(USE PLAN)允许你改变控制查询计划的程度。
内部查询的并行
当为一个查询生成执行计划时，SQL Server优化器尝试为该查询选择最快的相应计划。如果查询的开销超过了在cost threshold for parallelism选项中指定的值，并行不会被禁用，优化器尝试生成一个可以用于并行的计划。并行查询计划使用多线程处理查询，每个线程分布在可用的 CPU上并同时利用每个CPU的时间资源。最大的并行度可以通过服务器上的max degree of parallelism选项或每个查询使用OPTION(MAXDOP)提示限制。
用于执行实际并行度（DOP）的结果——度量有多少线程将在给定的操作上并行——是知道执行时才能确定。在执行查询前，SQL Server 2005决定有多少个调度器未充分利用并为查询选择DOP来充分利用剩余的调度器。一旦一个DOP被选择了，直到完成，查询将使用这个选择的并行度来运行。并行查询的使用时CPU有一些偏高，但是它在elapsed time上的时间很短。如果没有其他瓶颈，类似于物理I/O等待，并行计划将会使用所有处理器的100%资源。
查询开始执行后，一个关键的因素（系统有多空闲）可以导致运行并行计划的改变。例如，如果查询运行在空闲时间，服务器可以选择使用并行计划并使用 DOP为4，在4个不同的处理器上产生线程。一旦这种线程开始执行，现存的连接可以提交其他需要大量CPU的查询。在这种情况，所有不同的线程将共享可用的CPU的时间切片，导致更高的查询持续时间。
通过并行计划运行不是一定是不好的，并行可以为查询提供最快的响应时间。然而，给定查询的响应时间必须与整体的吞吐量和系统其他查询的响应进行衡量。并行查询一般最适合批处理和决策支持系统，而不适合一个事务处理环境。
检测
内部查询的并行问题可以通过下列方法检测。
系统监视器(Perfmon)
考虑SQL Server:SQL Statistics – Batch Requests/sec 计数器，并查看SQL Server联机丛书中的“SQL Statistics Object”获取更多信息。
因为在考虑使用并行计划前，查询必须评估开销超过为并行配置设置的开销阀值（默认被设置为5），服务器每秒处理的批小于运行在并行计划中的批。运行很多并行查询的服务器一般配置为较小的每秒批请求数（例如，小于100的值）。
DMVs
在运行的服务器上，你可以使用下列查询确认在给定会话中是否可以并行运行任何活动的请求。
select |
通过这些信息，查询的文本可以通过使用sys.dm_exec_sql_text轻松获取，而查询计划可以使用sys.dm_exec_cached_plan获取。
你也可以搜索符合运行在并行的计划。这可以通过搜索缓存的计划来查看如果关系操作符有Parrallel属性为非零的值。这些计划也许可以不运行在平行中，但是他们如果系统不忙，他们也适合这样做。
-- p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; |
一般来说，查询的持续时间长于CPU时间总量，因为一些时间花费在等待资源上例如锁或物理I/O。查询使用CPU时间长于持续时间的唯一场景是当查询运行在并行计划例如多线程并发使用CPU。注意并不是所有并行查询将证明这种行为（CPU时间大于持续时间）。
select which could be either statements or batches that have CPU time greater than the duration. select |
解决
任何运行在并行计划的查询被查询优化器认为是成本昂贵的，并会超过并行阀值，默认为5（粗略的是在涉及的机器上5秒执行一次）。任何通过上述方法确认的查询都是以后要调节的候选者。
◆使用Database Engine Tuning Advisor查看是否任何索引改变，改变索引视图或分区改变能减少查询的开销◆检查实际值和评估集的重要不同因为评估集在评估查询开销中是重要因素。如果找到重要的不同：
如果auto create statistics数据库设置被禁用，确认在Showplan输出的Warnings列中没有MISSING STATS项。
尝试在关闭评估的表上运行UPDATE STATISTICS。
验证查询没有使用优化器无法精确评估的查询构造，例如多语句表值函数或CLR函数，表值或Transact-SQL变量比较（参数比较是可以的）。
◆评估是否可以使用不同的Transact-SQL语句或表达式将查询写的更有效率
拙劣游标使用
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 -- API cursor (TSQL cursors always have fetch buffer of 1) |
SQL 跟踪
使用包括RPC：Completed事件类的跟踪用于搜索sp_cursorfetch语句。第4个参数的值是通过获取返回的行数。请求返回的最大行数是被指定为与RPC:Starting事件类关联的参数。
解决
◆确定游标是完成操作的最佳方法或是否基于集合这种更有效的操作是可行的。◆当连接到SQL Server 2005，考虑使用多活动结果集(MARS)◆参考你使用的API文档决定如何指定游标的获取缓存大小：
ODBC - SQL_ATTR_ROW_ARRAY_SIZE
OLE DB – IRowset::GetNextRows or IRowsetLocate::GetRowsAt
内存瓶颈
这部分给出了低缓存的条件和对不同内存错误诊断方法，可能的原因和排错方法。
背景
引用不同的内存资源通过使用简单的术语内存。但是却有一些内存资源类型，对于理解和区分特殊的内存资源这是很重要的。
虚拟地址空间和物理内存
在Microsoft Windows®，每个进程都有自己的虚拟地址空间(VAS)。进程可用所有虚拟地址有VAS的大小决定。VAS的大小依赖于架构（32位或64位）和操作系统。在排错的上下文中，理解虚拟地址空间使用内存资源，了解应用程序可以超出VAS即使在64位平台只要物理地址可能一直可用，这些很重要。
更多有关于虚拟地址空间，请查看SQL Server联机丛书中“Process Address Space”和MSDN中的Virtual Address Space。
Windows地址扩展和SQL Server
Windows地址扩展(AWE)是允许32位应用程序跨越32位地址限制操作内存的API。AWE机制技术上不需要64位平台。然而它出现了。内存页通过AWE机制涉及在64位平台上的锁定页。
在32位和64位平台上，内存通过AWE机制分配不能分页出界。这可以有益于应用程序（这是在64位平台上使用AWE机制的原因）。这也影响了系统和其他应用程序可用RAM总数，这可能是有害的影响。因为这个原因为了使用AWE，Lock Pages in Memory权利必须分配该运行SQL Server的账号。
从排错的角度来看，要点是SQL Server缓存池使用AWE映射内存；然而，只有数据库（hash过的）页面可以利用AWE分配内存。通过AWE机制内存分配将不会在任务管理器或在 Process: Private Bytes性能计数器中看到。你需要使用SQL Server特殊的计数器或动态管理视图来获取这些信息。
更多关于AWE映射内存的信息，请在SQL Server联机丛书中查看“Managing memory for large databases” 和 “Memory Architecture”以及MSDN中的Large Memory Support
下列表汇总了不同SQL Server 2005支持的最大内存（注意特殊的SQL Server版本或Windows可以有不同支持内存的限制）
表1
配置 | VAS | 最大物理内存 | AWE/locked pages支持 |
Native 32-bit on 32-bit OS with /3GB boot parameter[1] | 2 GB 3 GB | 64 GB 16 GB | 支持 支持 |
32-bit on x64 OS (WOW) | 4 GB | 64 GB | 支持 |
32-bit on IA64 OS (WOW) | 2 GB | 2 GB | 不支持 |
Native 64-bit on x64 OS | 8 terabyte | 1 terabyte | 支持 |
Native 64-bit on IA64 OS |