老秘网_材夜思范文

标题: 浅谈优化SQLServer数据库服务器内存设置的策略 [打印本页]
作者: 文秘114    时间: 2009-10-8 16:55
标题: 浅谈优化SQLServer数据库服务器内存设置的策略
浅谈优化SQLSe。笔者经过仔细摸索，发现系统对硬、软件的需求较高，为充分发挥设计效能，达到最佳运作效果，需要对计算机硬、软件系统进行较为完备的性能测试和最佳设置，特别是内存设置的好坏对系统的运行速度具有决定性的作用。下面，笔者就怎么优化SQLServer数据库服务器的内存设置提出一些认识和看法。

一、有关内存的基本概念

　　1?物理内存和虚拟内存
　　视窗系统NT使用两类内存：物理内存和虚拟内存。
　　物理内存：作为RAM芯片安装在计算机内部的存储器。
　　虚拟内存：用于模拟RAM芯片功能的磁盘(硬盘)空间，其实质是通过将内存中当前没有使用的部分内容临时存储到磁盘上，使系统能使用到比机器物理内存更多的内存。
　　2?分页和分页文件
　　视窗系统NT系统通过使用磁盘空间使得对内存的需求得到部分缓解，从而使用到比物理内存更多内存的技术就称为“交换”或分页，也就是通常所说的虚拟内存技术。通常视窗系统 NT 4.0系统安装时将在引导驱动器上设置一个大小为16MB的交换(分页)文件(pagefile.sys)。

二、优化视窗系统 NT 4.0系统内存设置

　　在大多数情况下，为了充分发挥视窗系统 NT 4.0系统效能，内存的作用比起处理器的处理能力更具有影响力，特别是在客户／服务器模式环境下更是如此，因为通常在这种环境下并不十分强调处理器的能力，相反却十分注重是否采用足够的内存来满足各个客户的应用需要。此外，为了获得容错功能和保护应用程式，确保应用程式高速运行、充分发挥设计功能都需要有足够多的内存，特别是工业绘图设计和各种工程应用程式更需要占用大量的内存来进行复杂的计算。
　　物理内存(RAM)方便快速的好处显而易见，但由于其价格昂贵，也就不可能做到多多益善了，因此通过合理优化内存设置、扩充虚拟内存提高计算机运算速度也就成了一项非常重要的应用技术手段。
　　1?确保视窗系统 NT系统基本内存需求
　　视窗系统 NT 4.0系统至小应设置12MB内存，16MB内存基本够用，正常情况下确保NT系统有32MB内存就能了，因为并不是所有的16MB基本内存在所有时候都被同时使用。如果添加一些服务和应用程式，则对内存的需求就会急剧增大。如：
　　(1)添加网络服务需要4MB内存空间；
　　(2)容错功能和系统保护功能需要8MB内存(如磁盘映像和分条功能)；
　　(3)进行图像图象处理需要增加16MB内存空间；
　　(4)安装VC、VB研发系统需要增加16MB内存空间；
　　另外，如在视窗系统 NT上构建大型数据库如SYBASE、Microsoft SQL Server等，对内存的需求就更多了。
　　2?优化内存性能
　　为了使视窗系统NT不至于过分占用较多的内存或浪费处理器的时间用于换页，能采用以下方法优化内存性能。
　　(1)减少显示颜色的数量；
　　(2)降低显示分辨率；
　　(3)尽可能不使用或使用位宽度较小的墙纸；
　　(4)关闭不必的服务程式或驱动程式，尽量不要在服务器上使用其他应用程式。
　　停用服务或驱动程式的操作步骤如下：
　　①确定需要停用的服务或驱动程式的名称；
　　②从“控制面板”中双击“服务”或“设备”图标；
　　③在列表中选择想要停用的服务或设备驱动程式的名称，单击“停止”按钮，这时出现确认操作对话框；
　　④选择“是”确认操作，然后关闭对话框完成设置。
　　3?优化虚拟内存
　　在对视窗系统 NT虚拟内存进行设置时需要合理确定各个驱动器分页文件的“起始大小”和“最大值”两个参数，他们用于指定分页文件的起始空间和最大空间。下面对这两个参数作一些解释：
　　起始大小：指初始创建该分页文件时的文件大小，单位为MB，根据缺省设置，这个值被设置为系统中的物理内存的大小。
　　最大值：指出该分页文件的最大尺寸，单位为MB。
　　(1)分页文件的设置原则：
　　①分页文件起始大小应保留缺省设置，一般情况下请不要改动；
　　②分页文件最佳的最大尺寸为系统物理内存尺寸的2.5倍至4倍。需要说明的是：如果系统工作时不必大量内存，请选择靠近下限的值，即用系统物理内存的2.5倍作为这个尺寸的起始值；如果系统工作时需要大量内存，请选择靠近上限的值。
　　(2)视窗系统 NT虚拟内存设置步骤：
　　①从“控制面板”中双击“系统”图标；
　　②在“系统特性”对话框中单击“性能”标签；
　　③在虚拟内存对话框中单击“更改”按钮，这时出现“虚拟内存”对话框，上端的驱动器框逐一列出了 视窗系统 NT所有页面文件的大小；
　　④在驱动器列表中，选择需要设置分页文件的驱动器盘符，在“驱动器页面文件大小”对话框中列出了 “起始大小”和“最大值”两个参数栏，填入按照上面的原则确定的数值；
　　⑤单击“设置”确认以上操作，然后依次单击“确定”按钮退出各个对话框，完成设置。
　　(3)Win95／98虚拟内存设置。
　　Win95／98虚拟内存设置方法、步骤和原则和视窗系统 NT 4.0的设置大致相同，请参照上面视窗系统 NT的设置。
　　4?注意事项
　　(1)合理确定分页文件的最大值，根据系统需求随时进行调整，使用过多虚拟内存将导致整个系统处理性能的下降。设置虚拟内存最大值的目的是使用户不必在视窗系统NT的交换文件上消耗过多的磁盘空间，通常情况下如果超过了系统需要的最佳值后，生成交换文件的磁盘空间就被浪费了。
　　(2)尽可能设立专用硬盘设置内存交换区，或将交换空间放到主硬盘的另一个分区，同时应将主硬盘的交换文件大小降至16MB，这样主硬盘(分区)仅用来放置操作系统和应用程式，就能减少交换次数，防止频繁交换耗费大量 CPU时间。
　　(3)虚拟内存技术的确改善了视窗系统 NT系统的性能，但也受到机器硬盘空间大小、硬盘速度、处理器 (CPU)速度的影响，从最佳角度出发，要提高计算机的性能就必须减少交换操作的次数，不过没有一个视窗系统NT计算机不发生交换，这就需求计算机要有足够的物理内存，以保持最少的交换操作。

三、优化Microsoft SQL Server数据库内存设置

　　内存是影响Microsoft SQL Server系统性能的一个重要因素，SQL Server数据库安装时将为具有32MB物理内存的机器缺省设置16MB可用内存，16MB物理内存的机器缺省设置4MB可用内存。应在Microsoft SQL Server数据库安装后进行内存选项(Memory)设置，最大设置值为2GB。
　　为了确定SQL Server系统最适宜的内存需求，能从总的物理内存中减去视窗系统 NT4.0需要的内存及其他一些内存需求后综合确定，最佳的情况是给SQL Server分配尽可能多的内存，而不产生页面调度。
　　1?根据物理内存合理规划SQL Server可用内存
　　在大多数的生产环境中，服务器配备的物理内存是64MB～128MB，偶尔也有256MB的，只要设置恰当是完万能满足SQL Server的内存需求的。下表是笔者关于SQL Server内存分配的建议规划，供参考。


物理内存 分配给SQL Server 设置值(单位：2KB)
8MB 4MB 2048
16MB 8MB 4096
32MB 16～18MB 8192～9216
48MB 28～34MB 14336～17408
64MB 40～46MB 20480～23552
128MB 100～108MB 51200～55296
256MB 216～226MB 110592～115712
512MB 464～472MB 237568～241664

　　以下是SQL Server内存选项(Memory)设置方法
　　(1)从Microsoft SQL Server程式集中启动SQL Enterprise Manager；
　　(2)从Server Manager窗口中选择“Server”菜单选项；
　　(3)在“Server”菜单中选择“Configurations”选项；
　　(4)在“Server Configuration”对话框中选择”Configuration”标签，Configuration窗口显示设置选项列表；
　　(5)选中“Memory”项目，在“Current”栏填入新值；
　　(6)停止并重新启动SQLServer服务，使设置生效。
　　2?合理扩充虚拟内存、增大SQL Server可用内存
　　当SQL Server系统确实需要扩大可用内存时，应在磁盘空间充足的情况下扩充供虚拟内存，并相应增大 SQL Server可用内存。具体做法是，系统管理员首先扩充服务器的虚拟内存，然后再参考上表增大SQL Server可用内存，关键是要根据系统的负载情况综合决定是否扩充内存、优化设置。
　　3?使用tempinRAM
　　SQL Server使用tempdb临时数据库作为一些查询连接操作时排序或创建临时表的工作空间。将tempdb创建在RAM中能使系统操作性能有较大提高，而且因为tempdb在每次重启动服务器时都重建，这样即使有非正常的关闭也是较为安全的，例如停电故障。要将tempdb创建在RAM中，能使用sp_configure进行设置，具体用法请参阅有关资料。
　　由于tempdbinRAM使用的内存是由系统从内存体独立分配的，和SQL Server的内存选项设置的可用内存池是分开的，使用tempdbin RAM将减少整个系统的可用内存，应根据SQL Server和服务器运行情况进行设置，否则就可能适得其反，影响系统性能。另外，适当增加tempdb数据库空间，即使不使用tempdbin RAM，也能提高数据库的运行速度。
　　4?注意事项
　　(1)建议在生产环境中SQL Server不要设置小于32MB内存，而且数据库服务器上尽量不要运行其他应用程式；
　　(2)扩充供虚拟内存、增大SQL Server可用内存，应考虑物理内存使用状况和磁盘空间许可情况；
　　(3)在可能的情况下，要为系统留有部分额外的内存，这样在服务器上打开一个服务或添加一个进程且不改动SQL Server内存设置时，不致于使NT服务器的运行速度受到影响(变得非常慢)，一般认为最小为2MB最大为20MB。
四、巧用内存设置，解决统计服务器问题

　　一单位的统计服务器投入使用后，运行速度较慢，经排查原因，发现SQLServer中的内存选项(Memory)仅为安装缺省值??16MB(而服务器有128MB的物理内存)，在将内存值调整为100MB时却误将其改成了 1000MB，使得SQL Server服务不能启动，统计数据库打不开，也就不能再次进入SQL Enterprise Manager修改内存设置了。由于近期未备份业务数据，不到万不得已不能重装SQLServer数据库，就试图用命令行参数命令来重新启动SQL Server服务，但均不能奏效，陷入了困境。我们经过仔细分析提出：既然SQL Server可用内存设置值远远大于物理内存，造成SQLServer服务不能启动，何不扩充虚拟内存呢?经设法将机器虚拟内存扩充至1000MB并重新启动，SQL Server数据库成功启动，问题迎刃而解。

五、结束语

　　目前，大多数单位投入使用的Microsoft SQL Server数据库服务器的物理内存一般都在64MB以上，如农业银行省、市分行的统计服务器配备128MB物理内存，只要按照上面提出的方法合理规划、优化NT和SQL Server的内存设置，使设置尽可能达到最优，应用系统就一定能够充分发挥设计功能、满足业务需求。□ rver数据库服务器内存设置的策略
作者: 大明老秘    时间: 2009-10-8 18:21
客户端：  
  sql:='Select   top   n   from   tablename   where   id>'+curmaxid;  
  Datas:=ClientDm.ATest.MSearch(Sql);  
          if   Not   VarisNULL(dATAS)   THEN  
          BEGIN  
              CdsTypes.Close;  
              CdsTypes.Data:=NULL;  
              CdsTypes.Data:=DATAS;  
              DATAS:=Null;  
          END;  
   
  用语句来控制的查询，，分页等。这样服务器始终保持最小的内存使用状态！！！说的有点模糊，，见晾  
作者: 大明老秘    时间: 2009-10-8 18:29

SQLServer2000里自动重建索引

, i+ b- W, X6 b6 i. q6 ^; K. `3 r

SQLServer2000里自动重建索引

' S9 ^! \7 u- A/ d) e/ u/ K

--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com

4 d8 r/ T& d0 F) Y

--原帖地址

) z, M- O- F8 e

在所有的OLTP环境里，实质上所有的索引都将随着时间产生碎片。几乎所有的UPDATE、INSERT、DELETE活动都将引起索引比最初创建时变得更无组织。页拆分更多，大量的页上只有很少的数据，因此满足每个SELECT需要更多的I/O。数据和索引的碎片越多，应用程序就会更慢，数据花费的空间就更多。对此你能做什么呢？你可以定期的重建索引。 

/ S0 E& C7 `. |* C+ E- H4 \

那么什么可以立即使用呢？

+ X$ ^4 N) f6 {

基本上你可以使用数据库维护向导来执行索引重建，创建维护计划来完成。如果你原意接受它固有的缺陷，这也可以使用。首先，用维护向导来配置和完成索引重建是不慎重的。它将重建每一个索引，不管它是否需要重建。如果你有一个有很多大表和大量索引的大数据库，这会出问题，因为不加区别的重建整个数据库的索引会花费很长的时间，会使你的维护窗口不可用。问题在于，要么全部重建，要么全部不重建，你根本不能以任何方式分批处理数据库的表。

! y. O; d& T% K6 a

 

; b' r6 |5 H% O5 w& N7 v$ \$ h9 a. l

 

& _5 L& {$ Q. n* q; s; D% z' w

那么有什么别的能做吗？你可以写一个脚本来重建选择的表的索引。这样你能对数据库分批处理以减少在重建索引时你维护窗口执行的时间。你需要将这个时间减小到最少，因为重建索引会对表执行排它锁，在重建索引期间禁止用户访问。所以你可以每周的每个工作日的晚上重建五分之一表的索引，所有的索引至少一周做一次。然而，这也是不慎重的――你将重建所有表的索引而不论数据和索引是否是有碎片。

- s) x ?; {. ]) n1 Y$ z1 D: B

这里推荐选择性的重建索引。你需要检查表的索引和数据的碎片，保留数据，据此操作，重建索引要用确定的且区别对待的方式。仅仅通过这样系统的方法，你可以仅重建那些实际需要重建的表的数据和索引。而且也只有这种方式能最小化索引重建的时间。在整个索引重建期间，如果你不想影响你的用户的话，减少索引重建的时间是至关重要的。

; n6 @/ ] Y2 \# ]2 R" X

那么我们怎样可以解决呢？

' a+ m$ U }7 p* K3 W# d! K9 x

可以使用命令

0 K' ^# L0 S* s- G$ S: ^1 K

DBCC SHOWCONTIG()

& H# i2 W5 A' }' }# e2 [

SQLServer2000比以前版本有一个大的改进就是这个简单而又至关重要的命令。DBCC SHOWCONTIG是SQLServer提供来检查索引碎片情况的工具。在以前的版本里（7.0和更早的版本），这个命令只输出文本，如果手工处理这个命令很好，然而，要实现自动化目的，它会带来严重的问题。那意味着你要循环执行每一个表并将结果输出到文本文件，然后为了读和解释原文的输出结果以便获得你寻找的信息，需要进行烦人的结构化处理。

, F5 `% y7 \- C- q

SQLServer2000对DBCC SHOWCONTIG()命令引进了一个关键子句，名为WITH TABLERESULTS。这意味着你能运行这个命令然后将捕获的数据直接输出到表里，而不是还需要使用XP_CMDSHELL来操作的文本文件里。

9 V) c& q( x& U/ d9 i. n

在SQLServer2000里，这意味着你能结构化的循环处理表，通过在它们上面运行DBCC SHOWCONTIG命令以将捕获碎片信息插入表中。然后你能循环使用这个结果，根据碎片的情况，选择性的进行碎片整理。可以用下面的存储过程实现：

2 A9 [# d: l" ~% v9 s" q

CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL

1 [, t" M4 z" E

AS --王成辉翻译整理，转贴请注明出处

0 w) R1 n5 c! Z8 }7 {! K7 G

--声明变量

% [- [4 V; K8 q7 V

SET NOCOUNT ON

( j* g+ R: k* I7 W5 A7 \4 \/ Y6 ^

DECLARE @tablename VARCHAR (128)

5 G/ d$ h$ x' ?6 }

DECLARE @execstr VARCHAR (255)

; n0 F$ N. f- C' ?0 y+ V

DECLARE @objectid INT

$ [0 r9 X$ ]. P9 c( y8 f: t8 ?/ D# D9 [

DECLARE @objectowner VARCHAR(255)

# u" q H. _: y+ g" f7 e

DECLARE @indexid INT

1 l0 m* `0 b+ F% R, ~$ d

DECLARE @frag DECIMAL

M# s5 _+ K. u" x: ?8 i: f

DECLARE @indexname CHAR(255)

. ^, f7 x n( G) q4 a

DECLARE @dbname sysname

' a/ d7 v; U! [/ z

DECLARE @tableid INT

3 C* T+ g7 K" Y8 o7 R

DECLARE @tableidchar VARCHAR(255)

V) `% {& j2 f

--检查是否在用户数据库里运行

5 j- E$ p; J# d( k1 j) N3 W: t

SELECT @dbname = db_name()

: \( U1 p0 I+ y6 w2 X) H( u7 P

IF @dbname IN ('master', 'msdb', 'model', 'tempdb')

1 O) F& n% x- K6 V/ y1 H% A

BEGIN

% Y6 Z) _/ G% n2 @( G2 M

PRINT 'This procedure should not be run in system databases.'

# H, g3 `$ S1 j

RETURN

' k- V1 l* s1 S( k; L/ E) r. A* q

END

" G' L6 J4 H. Z" e4 `1 ~9 \3 L8 W7 [0 o/ h

--第1阶段:检测碎片

% R {0 K2 E3 c7 D

--声明游标

; s1 } z) u1 r$ _: |4 k

DECLARE tables CURSOR FOR

5 L* F. {# f* h) {: v1 ?) {

SELECT convert(varchar,so.id)

) w$ [* d0 k* O0 c

FROM sysobjects so

5 c+ U" f: _! M& K

JOIN sysindexes si

% ?/ F& D/ q/ e

ON so.id = si.id

8 |" x, u) O, p/ [ t0 b2 r

WHERE so.type ='U'

- p( i e" S, K" e. d# k# v& x3 A; }

AND si.indid < 2

1 C6 g. g3 _& L) \

AND si.rows > 0

) L: M/ f, c. \8 @" r

-- 创建一个临时表来存储碎片信息

: a7 s7 P# h1 V2 T5 O4 w

CREATE TABLE #fraglist (

4 J2 m3 _4 c+ o/ x$ `+ u& Y

ObjectName CHAR (255),

+ `! ?% M% Q1 V" E V: u# K3 b

ObjectId INT,

. m* u" L$ y$ P$ G- `. G( Q9 I

IndexName CHAR (255),

" t$ B, A: T! h4 y

IndexId INT,

- w4 M$ [8 F8 J& M- U9 J

Lvl INT,

4 \4 e, d) R# e! L

CountPages INT,

/ F' ?) D7 ^4 W9 G$ e

CountRows INT,

7 ^3 k; ]0 _1 o# H1 l' R

MinRecSize INT,

9 p& Y+ t) N# I8 i0 Z% R

MaxRecSize INT,

3 s3 a) r& h. |( ~3 ?

AvgRecSize INT,

+ J$ R& V/ A# |

ForRecCount INT,

" T z6 o4 k9 G* T! [- a3 ]

Extents INT,

# v2 e; E& m' Y; A/ Q! V3 F3 d) f* n

ExtentSwitches INT,

$ d2 M/ k- Z4 d- k

AvgFreeBytes INT,

+ v& H& }8 g& N2 H7 z2 s5 d/ z

AvgPageDensity INT,

5 q0 h# F8 r) u8 q5 W

ScanDensity DECIMAL,

3 Q) q5 E' I( ^# {

BestCount INT,

" I6 a7 b1 V- ^% K( h

ActualCount INT,

' a! u w) Y* \2 w

LogicalFrag DECIMAL,

( l8 j- ^8 I# ?$ W

ExtentFrag DECIMAL)

" f& o8 T" b- y p- x5 ~$ C2 q( K

--打开游标

: y* x( A$ L4 ~: z+ @! z

OPEN tables

, \5 Z# p# h0 d

-- 对数据库的所有表循环执行dbcc showcontig命令

5 b' X3 E0 ]8 x

FETCH NEXT

: a4 D; `$ W9 k/ g: Q

FROM tables

+ q: P0 b. k% x4 L( ?/ a0 T

INTO @tableidchar

b, D2 p5 v& r$ A! ~# \& J

WHILE @@FETCH_STATUS = 0

! K& P$ t9 Z; O9 V1 F

BEGIN

/ m5 ~% v( b; A( e; Z. q/ z+ l I p

--对表的所有索引进行统计

( w8 r& p3 f5 A9 x1 ] t0 G0 R% l

INSERT INTO #fraglist

" Y" E+ f: K& x7 [0 u' r) ?# _

EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

0 G" ^. h) [: Y7 M Z6 L, T0 y

FETCH NEXT

: d9 A2 j. |0 n- g, _$ a/ X

FROM tables

! Z1 @* x* p& t# {; I5 _7 `: S

INTO @tableidchar

. t, I; l2 [7 T6 n4 h" F

END

( r( m( b* Y! D9 i) \

-- 关闭释放游标

( N& `6 ^9 ]3 J& c1 n$ H- Q& o

CLOSE tables

9 x- M7 x8 |1 g/ O9 _

DEALLOCATE tables

% M6 U. _5 J6 U6 [) |8 H$ c& L* u

-- 为了检查，报告统计结果

6 n# ?& }1 u% k/ L, O6 ^ T

SELECT * FROM #fraglist

0 r. C4 q2 c. Y d" C7 X S

--第2阶段: (整理碎片) 为每一个要整理碎片的索引声明游标

) Y0 ?7 C& Z7 r( Q2 ` Z5 V! |) |: i

DECLARE indexes CURSOR FOR

' }+ C& U% _' B7 M: l- }7 g0 b

SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity

" n( p# a8 O c J! Y) ~

FROM #fraglist f

' i/ m5 x a- U

JOIN sysobjects so ON f.ObjectId=so.id

( |2 m1 R4 v" v$ V4 s% `1 R% V

WHERE ScanDensity <= @maxfrag

5 v) ]4 S" f' H1 \" z* M: q/ s8 p

AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

( \: i1 o! |* E; h$ k1 e

-- 输出开始时间

2 M& |; s3 B2 A& R

SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

2 g* F0 E+ V) \! z6 K

--打开游标

h9 X6 B8 s( W! z* T

OPEN indexes

# X, H( A4 f: D$ ?4 n

--循环所有的索引

% L% L. `4 R P w+ B# v, T

FETCH NEXT

/ g- U5 u( [+ c( t7 J1 Y

FROM indexes

! m) H& [& d* x# @ @8 |

INTO @tablename, @objectowner, @objectid, @indexname, @frag

* h4 Q" K4 ?3 g3 V

WHILE @@FETCH_STATUS = 0

: X* y# j$ p1 ~

BEGIN

$ W: v7 F$ T/ `, ~5 v$ P Z- \

SET QUOTED_IDENTIFIER ON

* C( q& J! K* W* y7 n3 ~

SELECT @execstr = 'DBCC DBREINDEX (' + '''' +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + '''' +

$ U: |) r: t2 ]6 `& ~

', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'

6 O- P: c( R- x+ E4 M9 m3 B7 R

SELECT 'Now executing: '

5 K/ q# p6 U2 ?2 G3 k7 @* K

SELECT(@execstr)

- L+ C1 K9 U' W; B, j" C0 p

EXEC (@execstr)

$ f+ l; ^" @- @7 s

SET QUOTED_IDENTIFIER OFF

+ [5 ?4 T& l! U' x

FETCH NEXT

7 q0 h& l5 G" E/ z/ }

FROM indexes

4 L) l4 o! U {! V, x o- N( u

INTO @tablename, @objectowner, @objectid, @indexname, @frag

5 q3 G0 p& r- l; \' q1 U

END

- U" J1 p$ Q/ i- a1 r6 Q' k+ D4 C6 J3 z

-- 关闭释放游标

2 a) ?$ w, F6 F+ a

CLOSE indexes

( p% k4 @' e/ q1 J5 ^$ ?/ ?: I: m4 T. X

DEALLOCATE indexes

6 k# I4 r5 e8 N* T# ^" A: R' E

-- 报告结束时间

) Z2 Y* O3 M! q8 `4 ~: |

SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

8 u F5 t6 a( O' T0 I# _# y5 {

-- 删除临时表

: I# P# V5 i% |" \- E

DROP TABLE #fraglist

) P n: u- i! M: N3 U

GO

+ M( B: c3 B4 q

使用

7 H4 f6 ^7 e) n! s& }8 f

这个存储过程应该创建在master数据库里，以便你能在服务器上的任何用户数据库里使用。

: ~6 e/ `* a: e9 B/ f2 C. b

在用户数据库里通过传递一个参数（MAXFRAG）来运行。该参数是一个百分比值。意思是任何索引的碎片扫描密度小于这个值。例如，如果你想要整理那些扫描密度小于95％的索引的碎片：

m( Z* R! [! b; A5 v V: |

USE pubs

; Z9 W8 ^& Q! k( a" z4 m

GO

: r9 a. |) b) Q6 i+ t7 b% w2 r2 G! z

EXEC sp_deframent_indexes 95.00

0 j. |0 J& a5 U

局限

; |$ W8 e: ]. {% F/ p3 f" k

这个过程依赖于的标准是扫描密度，但扫描密度对于那些跨越多个文件的索引来说不是一个有效的标准。如果你的索引确实跨越多个文件，你需要用另一个标准（如Logical Frag）来更改这个存储过程。然而，这类更改超过本文的范围；如果你的索引跨越多个文件，你需要做更多的工作。 

4 y7 d) x) ` ]* a4 w

怎样做，做什么？

. f( f; s$ W% Z5 [ K5 ~: U

这个存储过程有两个独特的部分。

* a5 E: E! w0 a$ t

第1阶段

% r; M& b: I6 Z7 G: L: ^) K1 Y

在这部分里，存储过程通过在数据库里的每个表上运行下面的命令来检查索引碎片：

0 R* l0 P/ n y' G8 }8 V

DBCC SHOWCONTIG (‘tablename') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

c" ?; r) K/ x3 v

命令的结果存储在预先创建的临时表#fraglist里。这里就会用到DBCC SHOWCONTIG 语句的WITH TABLERESULTS的好处，仅这一点，真正的节省了太多的以前版本得到同样结果所花费的麻烦和精力。

! t; H' ]& e2 G' }9 p

你应该注意该存储过程工作的数据库的表的拥有者是不是dbo，通常是。我发现我最初的版本不起作用，当时一个软件经销商给我们提供的新系统的数据库里就出现了拥有者不是dbo的表。当我在这个新系统上第一次运行我的碎片整理过程时，这个程序的缺点就暴露无遗了，最后彻底失败。这个问题实际上出现在碎片整理阶段（阶段2），因为表在这里要引用表名，而在阶段1，DBCC SHOWCONTIG命令引用的时表的ID即object_id。

8 I. Q7 u; Z" n/ i; P0 |3 Z

第2阶段

% Z) _ I0 k7 Z; ?

这儿使用了另一个游标来循环处理表#fraglist里的记录，这些记录是那些扫描密度低于传给存储过程参数的那个阈值的表：

9 ^4 x/ S. y' b

DBCC DBREINDEX()

0 f$ _9 a; u2 D# b

执行的结果以输出文件的形式显示在表#fraglist的内容之后，以便你能查看表和索引的碎片，正如屏幕上所显示的那样，也可以通过查看DBCC DBREINDEX()执行的结果列表来查看采取的动作。利用这些你也能推导出每个索引重建的时间。 

6 O, ]* z8 Y f) s% z

输出结果是什么意思？

! ?* {" z9 }+ o a: Z

输出示例：

) @9 Z6 G- Q8 L+ r

 SQLServer2000里自动重建索引 - yanam0518=

3 H8 {( g l8 M+ s

上面是在Excel里打开的存储过程输出文本文件的一个截屏。为了简洁一些列已经删掉了。你需要用文本文件向导来打开它，选择固定列宽，打开导入从第三行起。

# y; j8 z0 u/ m0 Q0 m

这里，你能够检查你选择检查的数据库里的表的扫描密度。

2 a; i5 S2 @- l- A# J

在接下来的输出文件里（DBCC SHOWCONTIG输出结果的后面），你会发现正被重建索引的每个表或索引的细节，这部分的开始和结束部分都有重建索引的开始和结束时间。如下面例子显示的那样：

- z- x" Q# j: Y6 G6 n% q& d

SQLServer2000里自动重建索引 - yanam0518=

1 U9 V" k7 [0 }1 G) V

为什么不使用DBCC INDEXDEFRAG()去减少阻塞？

9 h5 |/ \- V" c7 G; }5 |6 \, [

答案是如果你想要或者需要的话就使用它。如果你需要7×24小时的在线操作，那么DBCC DBREINDEX()的排他表锁不适合你的业务，你可以使用它来代替DBCC DBREINDEX()。然而，你需要适当改变一下语法，因为它们是不相同的（谢谢，微软！）。如果你不知道它们的区别，这里有一个简单的摘要：当运行DBCC DBREINDEX()的时候，必须对表有排他锁，因为它是一个完全的，彻头彻尾的索引重建操作。而DBCC INDEXDEFRAG()就不那么完全了，在线的操作试图改善你索引的环境而不至于引起阻塞和中断OLTP（希望如此）。我必须承认我从来不用DBCC INDEXDEFRAG()，因为很幸运的是我的系统不需要严格的7×24在线且要求不阻塞，所以我不敢担保是否有效率。我已经理解它不是和DBCC DBREINDEX一样有效率。然而它的确比什么都没有强，所以如果你的数据库运行一个全球的WEB站点并且从来不能停止，这在今天这也很普遍，那么你需要使用它来代替以改变这个存储过程。 

7 ?/ d s; B b% R, Z

添加到调度任务里

( D& l0 q/ V0 A7 b

对于有相当经验的DBA来说这是一个相关的微不足道的任务，所以我在这里不会提供它的代码。作为一个独立的任务或在你存在的维护作业里的一个步骤都行。你要确保作业的步骤或作业里的输出结果是一个文本文件以便你能保存和查看所有重要的输出文件。 

6 [4 O3 A; e) h; N, P) F9 s

结论

! P5 w8 ]: u0 }7 [

希望这篇文章和代码能帮助你完成一个对数据库服务器维护来说更好的更精确的方法。现在的维护窗口开销很大，所以在影响用户和执行时间上保持最小的同时也提供了有效率和良好的数据库服务器维护。用调度作业实现这个过程，小心的监控它的输出结果。

作者: 大明老秘    时间: 2009-10-8 18:31
http://topic.csdn.net/t/20050115/10/3727581.html
作者: 大明老秘    时间: 2009-10-8 18:32
--执行这个语句  
  exec   sp_configure   'min   memory   per   query',1024
作者: 大明老秘    时间: 2009-10-8 18:33
1。   你的内存设置的太小了！企业管理器--右键SQL实例--内存--选择"动态地配置SQL   Server内存--将最小值设置为0--最大值设置大一点  
  2。   要不就是你的内存容量不够了！需要加内存容量
作者: 大明老秘    时间: 2009-10-8 18:36
MSSQLSERVER_8651
9 R- L5 Z8 M% g
3 g( S! z8 X& O' T, r- X8 ^; t' |
5 [! G( W! _3 q! D

% I8 M6 Y7 o8 T4 G2 d- F
$ `8 }3 h4 b# x* ]0 X1 D* s
 详细信息
; w9 h! C1 k6 n, H2 V
# E* c9 k) o, m# x0 B: ]

" E: f7 @# ^2 w6 _8 L. ?7 l9 j8 L' n+ W+ }0 s' \* E4 \' ]! ^% \- }; { [2 P4 B7 D& c' w8 i* G1 e* O6 z Q/ n- k- i4 l( P: U' E$ z+ a. g9 M/ W1 ?2 B/ { @; @' O- b6 k$ y E4 U: x! p7 e5 v$ F4 X* A9 N Z4 S1 w+ E& |9 Z: r: a) x% [$ k. k. i l( V- I, F( ?+ N# }! I: D$ T4 j2 c# ]+ K8 ]* R2 f- ^+ _, J6 E% d5 r1 Y% B5 ^: W3 A6 s* E; A/ f! P. ?4 [; E. P( s* S5 V' G0 n. o ~$ v5 ^, y4 t, _. t4 Q3 ?" ^4 o5 h" C9 [8 `$ M$ r( @1 ^" a4 i; Y3 b' T4 H2 x& m4 Q4 d$ n; V& ~& ~; [; L4 g% w. _4 |! l" L% z* G6 r" T6 _; B) t6 i3 z D/ c# A1 E0 U5 a9 S5 O6 `. |! j. r) c6 e- w4 J5 i$ e/ C, c4 n0 s! b& S+ S0 L4 E/ e
$ [2 r6 J3 l+ A j# W) n* U; c

产品名称

 ' ^. n, H8 t, Q# n8 `9 Z

SQL Server

4 k, H8 i' ]( M3 @8 e

产品版本

 , {7 d4 o- Q! ]# u8 R0 T1 L) P" E

10.0

0 D# _6 Z& V* _# R- U1 \

产品内部版本号

 0 H7 D+ t# R3 z3 m! B

10.00.0000.00

$ f7 o' q1 N2 Q9 T) j

事件 ID

 % T$ S+ Q$ x, ]

8651

1 |8 |6 _# ?8 g: P1 \: v$ ~0 b

事件源

 * u& Y& r5 ^! T6 L- C3 ~

MSSQLSERVER

" d6 V$ F. m4 |- `

组件

 6 u" w$ q; x% ?; t

SQLEngine

- [, M0 [+ N W7 S# F m. L

符号名称

 " T d! e! E( O( x7 n# L. D4 t' r

MEMGRANT_ERR

$ w. }, D, E7 G. c, N

消息正文

 4 m! P1 u2 y/ d% w4 h* a& o" r' U

未能执行所请求的操作，因为可用内存少于最小查询内存。请减小“每次查询占用的最小内存”服务器配置选项的配置值。

 / e, s7 \0 l q4 p
7 U; Q+ R. Q, E Y8 F
 解释
1 o+ S- l% w9 Y. q
% V# Z0 x0 R, P4 I

其他进程正在占用服务器内存（在服务器中施加内存压力）。

 " `4 B4 h) a0 p: i: x8 _
1 k2 I. W4 D' l% z! i# h
 用户操作
4 K0 k" A2 h9 g9 m- r
9 Y/ n- E6 A F

减小“每次查询占用的最小内存”服务器配置选项的配置值，或者减少服务器的查询负载。

4 |9 E1 b* m4 T* v

下面的列表概述了有助于解决内存错误的一般步骤：

5 G; s( {7 \* Y
    4 z: m+ [ `+ V6 d6 o6 p! [0 G
  1. 验证其他应用程序或服务是否占用此服务器上的内存。重新配置不太重要的应用程序或服务，使其占用更少的内存。
    7 w: [3 _7 {- p% p) N2 O
  2. 开始收集 SQL Server: Buffer Manager、SQL Server: Memory Manager 的性能监视器计数器。
    % U* E4 l4 o1 W: m8 G& I8 [9 z$ q
  3. 检查下面的 SQL Server 内存配置参数：
    ! M' j' _, o1 i& {
      / d; v4 I7 ^: s# \* V1 [) C
    • max server memory
      / o( V- p- [' ?( \ T3 l" r6 {' j
    • min server memory
      9 u/ r. O* B% {& U" O& r0 [
    • awe enabled
      0 s9 t7 |+ G3 t
    • min memory per query
    注意不正常的设置。根据需要将其更正。SQL Server 联机丛书中的“设置服务器配置选项”中列出了默认设置。
    . c; k; k0 \+ F- G( g k# ~1 L
  4. 如果正在使用地址窗口化扩展插件 (AWE)，请验证是否启用了 Windows 安全设置“锁定内存页”。
    [2 n/ ~5 B0 P9 c4 G: A! Z
  5. 检查工作负荷（例如，并发会话数，当前执行的查询）。
( g5 _2 v3 \; L* G6 c# A6 p

以下操作可以使更多内存可供 SQL Server 使用：

! `& e$ \: g: q# O; @8 _
    1 F' V- v/ D# `/ x1 ^& S+ z7 Q
  • 如果除 SQL Server 外的应用程序正在占用资源，请尝试停止运行这些应用程序，或者考虑在单独的服务器上运行它们。这样做将消除外部内存压力。
    E I; o) G3 I) A2 g {
  • 如果已配置 max server memory，请增大其设置。
C7 }1 \ T+ n: `* Y6 M

运行以下 DBCC 命令以释放几个 SQL Server 内存缓存。

5 D+ l" Y8 a! E/ V
    , x; |4 X, _ b1 O: _7 \7 {! y s0 E
  • DBCC FREESYSTEMCACHE
    6 n2 h. n- f$ R* W( ?& K
  • DBCC FREESESSIONCACHE
    N! h, c0 r. x2 B1 \/ U
  • DBCC FREEPROCCACHE
) D( _ B3 [" ]4 B6 r

如果问题仍存在，则您将需要进一步调查，可能需要减小工作负荷。





欢迎光临 老秘网_材夜思范文 (http://www.laomiw.com/) Powered by Discuz! X3.4