15年专业服务器托管租用服务商!
咨询热线 : 400-880-5868

首页 > 客服中心 > 帮助中心

帮助中心

服务器托管如何优化sql server内存配置

发布时间:2018-4-13 9:43:59    返回首页

  服务器托管用户在配置数据库的时候对于庞大的数据库所占用的内存问题也是很头痛,这里就介绍一下优化的方法。

  最小和最大Server内存

  Min Server Memory (MB) 和 Max Server Memory (MB)控制所有SQL Server内存使用的许可大小。比起之前的版本,SQL Server 2012的Memory Manager可以更简单地设置SQL Server内存需求的大小。SQL Server服务是以所需的最小量启动的,并根据需要增长。一旦内存使用增长超过Min Server Memory设置,SQL Server将不会释放任何低于该量的内存。Min Server Memory设置内存使用的下限,而Max Server Memory则设置上限。这两个值可以使用sp_configure或通过Management Studio中的SQL Server属性窗口的内存页面进行设置。两个设置中,配置缓冲池的最大值更重要,它会阻止SQL Server占用过多的内存。这在64位系统中尤其重要,因为缺少可用物理内存能够导致Windows裁剪SQL Server的工作集。后面的“锁定内存页”有关于这个问题的完整描述。对于配置Max Server Memory,有一些不同的方法来计算合适的值,最直接的方法是看看SQL Server的最大使用量,及确定SQL Server之外内存需求的最大潜在用量。

  查看SQL Server的最大使用量

  设置SQL Server动态管理内存,然后使用性能监视器监视计数器MSSQL$:Memory Manager\Total ServerMemory (KB)。这个计数器测量SQL Server的总缓冲池使用量。如果SQL Server以外的其他需求需要比当前可用内存更多的物理内存时,Total Server Memory值会降低,然后使用任何可用内存再增加。如果您监视此计数器一段时间(包括忙时和淡时),然后你就可以将Max Server Memory设置为你观察到的Total Server Memory (KB)的最低值,你不必担心SQL Server在正常操作期间收缩它的使用量。

  确定SQL Server之外内存需求的最大潜在用量

  此选项是最流行的,因为其目的是计算出最坏的情况下,除SQL Server之外的内存需求。你应该考虑如下情况:

  ·2GB用于Windows

  ·xGB用于SQL Server工作线程。你可以查询sys.dm_os_sys_info来获取最大工作线程数,每个线程会使用0.5MB(在x86系统),2MB(在x64)

  ·512MB,如果你用Linked servers、扩展存储过程dll,或通过自动化程序(Automation     procedures (sp_OA calls))创建的对象

  ·1~3GB,用于其他可能再系统上运行的应用程序,如备份程序或反病毒软件

  例如,在一个有8个CPU内核、64GB RAM、运行SQL Server 2012、一个第三方备份工具和病毒检测程序的服务器上,你将考虑如下情况:

  ·2GB用于Windows

  ·1GB用于工作线程(576     X 2MB四舍五入)

  ·512MB用于Linked Server等

  ·1GB用于备份程序和病毒检测程序

  共计4.5GB,你可以将Max Server Memory配置为59.5GB

  检查你的Max Server Memory是否有效

  当你建立一个服务器时决定要如何配置max server memory,没有服务器运行它期望的负载时衡量其有效性并调整它重要。要做到这一点,一个简单的方法是使用性能监视器计数器,具体地说,MSSQL$:Buffer Manager\Page LifeExpectancy (PLE)和Memory\Available MBytes。这两个计数器之间的平衡将告诉您max server memory设置有多大效力。PLE显示了SQL Server希望一个页面保持在数据缓存中多少秒,它是一个好的衡量SQL Server内存压力的办法。Available MBytes显示了Windows有多少可用的物理RAM。

  如果你的PLE低(<300),就检查Available MBytes,看看有多少未使用的内存可用。如果可用的少于5MB,Windows就会开始侵略性地裁剪所有的应用程序的工作集,这被认为是一个紧急问题。如果PLE低,并且你有大量Available MBytes,你就应该增大Max Server Memory的设置,从而增加PLE;相反,如果Available MBytes低,而PLE很高,那么你就应该降低Max Server Memory设置,从而把一些RAM返还给Windows。下面是一些示例场景来说明这一点:

  ·有32GB RAM的服务器Max Server Memory是30GB,PLE平均为10000,Available MBytes是90MB。解决方案:至少降低Max     Server Memory 500MB。

  ·有50GB RAM的服务器Max Server Memory是46GB,PLE平均是10,Available MBytes是1500MB。解决方案:增加Max     Server Memory 500MB到1000MB。

  ·有64GB RAM的服务器Max Server Memory是60GB,PLE平均是50,Available MBytes是20MB。解决方案:降低Max     Server Memory 100MB,并购买更多RAM(尽快)。

  Lock Pages in Memory

  Lock Pages in Memory (LPIM)被用作应急措施(work-around),当Windows和SQL Server之间发生错误时,这在旧版本的SQLServer上特别不好。如果Windows没有足够的可用物理内存,服务来至来自驱动器或其他应用程序的资源请求,Windows将会裁剪在服务器上运行的所有应用程序的工作集(它指的是一个进程的物理内存使用情况)。这是正常的行为,应该不会有太显著的影响。Windows Server 2003中并没有很好地处理写得很糟糕的驱动程序(badly written drivers),实际上可以强制所有的应用程序清空他们的工作集。这就是所谓的侵略性工作集裁剪(aggressive working set trimming),它对SQL Server的内存分配有破坏性的影响 - 因此影响性能。所以,你可以看到,当发生这种情况时,微软增加了一个消息到SQL Server错误日志。下面是一个例子:

  A significant part of sql server process memory hasbeen paged out.This may result in a performance degradation. Duration: 0seconds.Working set (KB): 1086400, committed (KB): 2160928, memory utilization:50%.

  在Windows Server 2008中,这种行为得到显著改变,防止了最大的问题--写得很糟糕的驱动程序引起的应用程序工作集被清空。这不会影响SQL Server 2012,因为它只运行在Windows Server 2008+中。在SQL Server 2012中,当Windows执行工作集裁剪时,你仍然会得到记录的消息。几个消息能够表明SQL Server的工作集逐渐下降(这仍然是个问题)。有两种方式解决这个问题:

  ·恰当地设置Max Server Memory,确保Windows和服务器上运行的其他进程有足够的物理内存来执行它们的工作,而不必要求SQL Server裁剪。

  ·如果你仍然看到的问题(或者,如果它的影响是非常严重,你不想再冒险见到它),你可以使用Locked Pages in Memory     (LPIM)来配置你的SQL Server。

  当LPIM启用,SQL Server的缓冲池页面是“锁定”且不可分页的(non-pageable),因此Windows在裁剪时不能占有它们。一旦页面被锁定,它们就不会被考虑成为用于工作集裁剪的可用内存的一部分。然而,只有SQL Server缓冲池分配才可以被锁定,不过Windows仍然可以裁剪其他进程的工作集,这影响了SQL Server所依赖的资源。如果设置了合适的max server memory后你还继续获得工作集裁剪,或者SQL Server的工作集再次被裁剪的成本过于冒险,LPIM就应该被使。

  如果LPIM在工作,你会在SQL Server的错误日志中看到如下信息:UsingLocked Pages in the Memory Manager。

  优化Ad-Hoc Workloads

  每次生成的执行计划存放在计划缓存,以希望可以被重用,这是SQL Server管理工作负载的有效办法之一。如果一个执行计划将不会被重用,那么它只是不必要地占用资源,使用未参数化(unparameterized)的ad-hoc T-SQL是最有可能的原因。当您在SQL Server中执行代码,它会生成代码的哈希值,并使用该值来决定计划重用。如果你执行一个存储过程,散列值会根据存储过程名称生成,计划将在每个后续的过程调用中被重用,不管其所使用的参数值。

  如果您运行存储过程(ad-hoc T-SQL)之外的相同的代码,哈希会对整个语句采用,包括任何文字值。当您改变文字值再次执行时,哈希是不同的,因此SQL Server不能找到匹配的哈希值,并产生一个新的执行计划,而不是重用前一个。这种情况可能会导致一个场景称为计划缓存膨胀(bloat),由此可能会产生成千上万的ad-hoc计划,并且缓存了为1的usecount,即使代码基本上是相同的。理想的解决方案是使用存储过程或函数,或参数化所有的ad-hoc T-SQL ,但是这可能是非常具有挑战性的,而且往往无法实现,因为复杂性和公司策略。所以微软在SQL Server 2008推出了服务器级别选项Optimize for Ad-hoc Workloads来帮助解决这个问题。当启用了此选项,则一块ad-hoc T-SQL在首次被执行时,SQL Server将仅仅缓存一个计划存根,而不是完整的计划。如果SQL Server随后尝试重用这个计划,则计划将再次产生,但这个时候是全部被缓存。这就避免了这种场景,即数以千计的单次使用的计划占用了宝贵的缓存空间。

  浦东数据中心14年专业服务器租托管服务商,为用户提供专业服务器租用技术支持。


在线咨询
QQ 咨询
服务热线
扫一扫

扫一扫
关注我们

全国免费服务热线
400-880-5868

返回顶部