数据库优化方案.docx
《数据库优化方案.docx》由会员分享，可在线阅读，更多相关《数据库优化方案.docx（15页珍藏版）》请在冰点文库上搜索。
数据库优化方案
通过网络查找资料以及测试整理，现将sqlserver大内存配置办法贴出来。
SQL服务使用的内存一般默认不会超过1.8G，因此就算你的服务器有N个G的内存，也未见访问会变快很多。
这就是很多由于数据大，访问慢，升级服务器后看到的情况。
我大致讲一下配置的过程.（如果服务器的内存少于4G，不用配置）（网上完全讲这个的，没有见过，随手写一个）
1.打开系统中的大内存支持（windows）
若要启用Windows2003大于4GB的物理内存，必须将参数/pae添加到boot.ini文件中。
[bootloader]
timeout=0
default=multi（0）disk（0）rdisk（0）partition
（1）WINNT
[operatingsystems]
multi（0）disk（0）rdisk（0）partition
（1）WINNT="MicrosoftWindows2003...."
/fastdetect
改为
[bootloader]
timeout=0
default=multi（0）disk（0）rdisk（0）partition
（1）WINNT
[operatingsystems]
multi（0）disk（0）rdisk（0）partition
（1）WINNT="MicrosoftWindows2003....""
/fastdetect/pae
这个改好后，要重启系统的
2.启用锁定内存页选项（windows）
启用锁定内存页选项
在"开始"菜单上单击"运行"子菜单，然后在"打开"框中键入"gpedit.msc"。
在"组策略"控制台上，展开"计算机配置"，然后展开"Windows设置"。
展开"安全设置"，然后展开"本地策略"。
选择"用户权限分配"复选框。
详细资料窗格中随即显示出策略。
在详细资料窗格中，双击"锁定内存页"。
在"本地安全策略设置"对话框中，单击"添加"按钮。
在"选择用户或组"对话框中，添加有权运行sqlservr.exe的帐户。
3.启用SQL的AWE
若要启用AWE，请将aweenabled设置为1。
除非指定了maxservermemory的值，否
则SQLServer将保留几乎所有可用内存，只留下128MB或更少。
如果已成功启用该选项，则当SQLServer2000实例启动时，SQLServer错误日志中将
出现"已启用地址窗口扩展"这条消息。
aweenabled是高级选项。
如果正在使用sp_configure系统存储过程更改该设置，则只有
当showadvancedoptions设置为1时才能更改aweenabled。
code如下，设定SQL使用6G的内存
sp_configure'showadvancedoptions',1
RECONFIGURE
GO
sp_configure'aweenabled',1
RECONFIGURE
GO
sp_configuremaxservermemory,6144
RECONFIGURE
GO
必须重新启动SQLServer2000实例才能使更改生效。
netstopmssqlserver
netstartmssqlserver
这样，SQL服务启动后，就会把6G的内存给锁定，完全供SQL使用
参照SQL帮助文件
=================
//查看是否开启awe
sp_configure'showadvancedoptions',1
go
reconfigure
go
sp_configure'aweenabled'
go
如果run_value设置为1，则服务器上启用了AWE。
/////
为SQLServer2005启用对4GB以上物理内存的支持
若要启用MicrosoftSQLServer地址窗口化扩展插件（AWE），必须使用已指定“锁定内存页”选项的MicrosoftWindows帐户运行SQLServer2005数据库引擎，并使用sp_configure将aweenabled选项设置为1。
必须选中控制面板中“网络连接”的“最大化网络应用程序数据吞吐量”选项。
注意：
只有SQLServer2005EnterpriseEdition、StandardEdition和DeveloperEdition支持AWE，并且AWE只能应用于32位操作系统。
SQLServer2005AnalysisServices（SSAS）不能利用AWE映射内存。
如果可用物理内存小于用户模式虚拟地址空间，则无法启用AWE。
一、锁定内存页
此策略将确定哪些帐户可以使用进程将数据保留在物理内存中，从而阻止系统将数据分页到磁盘的虚拟内存中。
在SQLServer2005中，“锁定内存页”选项默认设置为OFF。
如果您具有系统管理员权限，则可以使用Windows组策略工具（gpedit.msc）手动启用该选项，并将此权限指定给正在运行SQLServer的帐户。
有关如何启用“锁定内存页”选项的详细信息，请参阅如何启用“锁定内存中的页”选项（Windows）。
虽然没有要求，但我们建议在使用64位操作系统时锁定内存中的页。
对于32位操作系统，在将AWE配置为用于SQLServer之前，必须授予“锁定内存页”权限。
二、aweenabled选项
若要为SQLServer2005实例启用AWE，请使用sp_configure将aweenabled选项设置为1，然后重新启动SQLServer。
因为AWE在SQLServer启动时已启用并一直持续到SQLServer关闭，所以SQLServer会向SQLServer错误日志发送“AddressWindowingExtensionsenabled”消息，通知用户aweenabled正在使用中。
有关aweenabled配置选项的详细信息，请参阅aweenabled选项。
最大化网络应用程序数据吞吐量
如果在“网络连接”中选中了“最大化文件共享的数据吞吐量”选项，则操作系统将在文件系统缓存中缓存应用程序的I/O页面，从而优先处理执行缓冲输入/输出（I/O）操作的应用程序。
此选项可能会限制可用于SQLServer正常操作的内存。
三、检查当前操作系统的设置：
在“控制面板”中，双击“网络连接”，再双击“本地连接”。
在“常规”选项卡上，单击“属性”，选择“Microsoft网络的文件和打印机共享”，再单击“属性”。
选中“最大化网络应用程序数据吞吐量”，单击“确定”，再关闭其余对话框。
SQLServer使用内存有两种情况：
第一种情况：
动态改变它的内存需求。
默认情况下，SQLServer会依据可获得的系统资源动态改变它的内存需求。
如果SQLServer需要更多的内存，它会要求操作系统确定是否有空闲的物理内存可用，并使用可用的内存。
若SQLServer不再需要当前分配给它的内存，它就将内存释放给操作系统。
当SQLServer动态使用内存时，它要求系统定期地检测可用的物理内存数量。
SQLServer根据服务器活动增大或收缩高速缓冲存储器，以使可用物理内存保持在4MB到10MB之间。
这就避免了系统进行换页操作。
[也就是说，这种情况下SQLSERVER本身不会使物理可用内存小于4M，如果比较长的时间内都小于4M的话，则要看一下是不是该服务器上其它应用程序有问题]
第二种情况：
限制使用内存
使用setworkingsetsize为sqlserver保留等于服务器内存设置的物理内存空间。
即使是sqlserver进程此时是空闲的，系统也不会将SQLServer页交换出去。
使用minservermemory保证sqlserver使用的最小内存。
SQLServer启动时不立即分配minservermemory中所指定的内存量。
但是，当内存使用由于客户端负荷而达到该值后，SQLServer将无法从已分配的缓冲池中释放内存。
使用maxservermemory则防止SQLServer使用多于指定数量的内存，这样剩余的可用内存可以快速运行其它应用程序。
SQLServer启动时不立即分配maxservermemory中所指定的内存。
内存使用随SQLServer的需要而增长，直到达到maxservermemory中所指定的值。
SQLServer无法超过该内存使用值，除非增加maxservermemory值。
第一种情况比较适用于服务器专做sqlserver服务器的情况，第二种情况适用于为在同一台计算机上运行的其它应用程序保留一定的内存以便于快速响应。
（另：
如果想动态分配sqlserver的内存，则不要设置setworkingsetsize选项，使用默认值即可。
至于这些参数如何设置参见另外的文档）
监视SQLServer所使用的内存和计数器有助于确定：
是否由于缺少可用物理内存存储高速缓存中经常访问的数据而导致瓶颈存在。
如果是这样，SQLServer必须从磁盘检索数据。
是否可通过添加更多内存或使更多内存可用于数据高速缓存或SQLServer内部结构来提高查询性能。
SQLServer需要从磁盘读取数据的频率。
与其它操作相比，例如内存访问，物理I/O会耗费大量时间。
尽可能减少物理I/O可以提高查询性能。
对sqlserver服务器内存的监视：
Memory:
AvailableBytes
计数器表示当前进程可使用的物理内存字节数。
如果小于4M或更小，说明计算机上总的内存可能不足，或某个程序没有释放内存
●Memory:
PageFaults/sec
每秒软性页面失效的数目（包括有些可以直接在内存中满足而有些需要从硬盘读取）较page/sec只表明数据不能在内存指定工作集中立即使用。
如果该值偶尔走高，表明当时有线程竞争内存。
如果持续很高，则内
存可能是瓶颈。
Memory:
Pages/sec
计数器表示由于缺页处理而从磁盘取回的页数，或由于缺页处理而写入磁盘以释放工作集空间的页数。
●PageReads/sec
每秒发出的物理数据库页读取数。
这一统计信息显示的是在所有数据库间的物理页读取总数。
由于物理I/O的开销大，可以通过使用更大的数据高速缓存、智能索引、更高效的查询或者改变数据库设计等方法，使开销减到最小。
●PageWrites/sec
所发出的物理数据库页写入的数目。
监视SQLServer正在使用的内存量：
Process:
WorkingSet
计数器表示的是一个进程所占用的内存数量。
若这一数值持续低于SQLServer配置使用的内存数量（由"最小服务器内存"和"最大服务器内存"服务器选项设置，（minservermemory的默认设置为0，maxservermemory的默认设置为2147483647。
），则表示SQLServer所配置的内存比它所需要的多。
否则，用"设置工作集大小"服务器选项修改工作集大小。
[在设置了sqlserver使用的最小和最大内存后，查看此参数比较有意义。
如果没有设置sqlserver内存使用范围，则该值可以与系统总的内存比较，设置分配给sqlserver的最大最小内存分配的是物理内存＋虚拟内存的一部分]
●SQLServer:
BufferManager:
BufferCacheHitRatio
数据请求可以从数据缓冲区中获得所需数据的成功率
计数器值依应用程序而定，但比率最好为90%或更高。
增加内存直到这一数值持续高于90%，表示90%以上的数据请求可以从数据缓冲区中获得所需数据。
●SQLServer:
BufferManager:
TotalPages
缓冲区池中页的数目（包括数据库、可用页和被盗用页）
若TotalServerMemory（KB）计数器值与计算机的物理内存大小相比一直很高，可能表示需要更多的内存
结论：
对sqlserver服务器内存的监视，可以看出该服务器整体的内存的占用情况
对sqlserver使用内存的监视，可以看出是否是sqlserver使用了大多数内存
根据监视的结果确定是否是内存不够。
其它：
内存相关参数调整（见后面）
SQLServer对象内存使用情况说明（见后面）
疑问：
使用setworkingsetsize=1后，系统是在sqlserver启动时就分配给它要求的内存还是开始不分配这么多，而是等到用了这么多之后不释放就行了？
SQLServer对象内存使用情况说明
下表列出Microsoft®SQLServer™中不同对象的内存使用量。
下表中的信息不适用于Microsoft®SQLServer2000™Windows®CE版。
对象大小
对象SQLServer7.0SQLServer2000
锁96字节每个所有者64字节外加32字节。
开放式数据库2.880字节每个文件3924字节外加1640字节，每个文件组336字节。
开放式对象1276字节每个对象上打开的索引256字节外加1724字节2。
用户联接12KB+（3*网络数据包大小）3。
12KB+（3*网络数据包大小）3。
3开放式对象包括所有的表、视图、存储过程、扩展存储过程、触发器、规则、默认值及约束。
2在表或视图上可以打开索引。
3网络数据包大小是表格格式数据方案（TDS）数据包的大小，该数据包用于应用程序和关系数据库引擎之间的通讯。
默认的数据包大小为4KB，由networkpacketsize配置选项控制。
内存相关：
服务器虚拟内存的配置
页面文件和物理内存或RAM构成虚礼内存
虚拟内存设置方法：
启动"控制面板"的"系统"选项，然后选择"虚拟内存"按钮来创建一个附加的页面文件或增加当前页面文件的大小。
虚拟内存不足：
当服务器上运行的应用程序请求的内存超过服务器上可用的内存时，Microsoft®Windows®打开"服务器进程—虚拟内存用完"对话框，其文本如下所示：
系统的虚拟内存已经不足。
请关闭一些应用程序。
可以启动"控制面板"的"系统"选项，然后选择"虚拟内存"按钮来创建一个附加的页面文件或增加当前页面文件的大小。
一般情况下，将虚拟内存大小设置为计算机中安装的物理内存的1.5倍
如果另外安装了全文检索功能，并打算运行Microsoft搜索服务以便执行全文索引和查询，可考虑：
将虚拟内存大小配置为至少是计算机中安装的物理内存的3倍。
●将SQLServermaxservermemory服务器配置选项配置为物理内存的1.5倍（虚拟内存大小设置的一半）。
如考虑其它服务需要的虚拟内存，则配置SQLServermaxservermemory选项，使得留有足够的虚拟内存满足全文检索内存需求。
全部虚拟内存-（SQLServer最大虚拟内存+其它服务需要的虚拟内存）>=1.5倍物理内存。
Sqlserver使用的内存参数的配置：
手工配置给SQLServer多于物理内存数量的虚拟内存会导致性能较低。
默认情况下，SQLServer能够可用系统资源动态改变它的内存需求。
minservermemory的默认设置为0，maxservermemory的默认设置为2147483647。
可以为maxservermemory指定的最小内存量为4MB。
作用：
如果在运行SQLServer实例的计算机上频繁启动或停止其它应用程序，启动这些应用程序所需的时间可能会因SQLServer实例分配和释放内存而延长。
另外，如果SQLServer是几个在一台计算机上运行的服务器应用程序中的一个，系统管理员可能需要控制分配给SQLServer的内存量。
在这些情况下，可以使用minservermemory和maxservermemory选项控制SQLServer可以使用的内存量。
对SQLSserver使用内存的配置主要是对以下三个参数进行配置：
minservermemory保证了SQLServer实例使用的最小内存量。
maxservermemory则可防止SQLServer使用多于指定数量的内存，这样剩余的可用内存可以快速运行其它应用程序。
setworkingsetsize为SQLServer保留等于服务器内存设置的物理内存空间。
手工设置SQLServer内存选项有两种主要方法：
第一种方法，设置minservermemory和maxservermemory为同一数值。
该数值与分配给SQLServer的固定内存量相对应。
●第二种方法，把minservermemory和maxservermemory数量设置到一个范围段内。
这种方法在系统或数据库管理员希望配置SQLServer实例，使其适应在同一台计算机上运行的其它应用程序的内存需求时很有用。
如果只设置了minservermemory和maxservermemory，sqlserver使用的内存值在最小和最大值之间变动（如果sqlserver使用的值超过过最小值的话）
如果设置setworkingsetsize=1，必须先将minservermemory和maxservermemory的值设成同一个值，这个值就是为sqlserver保留的物理内存空间。
即使当SQLServer空闲，另一个进程可以使用SQLServer页时，系统也不会将SQLServer页交换出去。
参数的设置方法：
1.在查询分析器中运行[要先运行下面的语句，否则内存页面打不开]
usemaster
execsp_configure'showadvancedoption','1'
2.在企业管理器中配置，步骤：
打开SqlServer属性配置，选择内存页面，然后在里面配置就行了。
（不过这里用图形界面配置内存的时候好像不能超过物理内存的大小，可以用命令来分配大于物理内存的内存空间，下面有简单的介绍。
）
对上图的说明：
动态配置SQLServer内存
指定在更改服务器属性之后立即配置Microsoft®SQLServer™内存。
使用固定的内存大小
为SQLServer指定固定的内存大小。
为SQLServer保留物理内存
为SQLServer保留与内存设置相等的物理内存空间。
这意味着MicrosoftWindowsNT®4.0或Windows®2000不会将SQLServer页交换出去，即使当SQLServer闲置时可以更容易地使用这些页。
最小查询内存
设置可以分配给每个用户执行查询的最小内存大小。
默认为1024KB。
配置值[显示现在配置的值，不做其它用途]
查看或更改此选项卡上的选项的配置值。
如果更改了这些值，单击"运行值"查看更改是否已生效。
如果没有，必须重新启动SQLServer实例才能使更改生效。
运行值[显示现在运行值，不做其它用途]
查看此选项卡上的选项的当前运行值。
这些值为只读值。
另：
如果要用命令来设置比较麻烦。
如下简单举个例子：
Setworkingsetsize设置方法：
setworkingsetsize是一个高级选项。
如果要用sp_configure系统存储过程改变该选项，必须把showadvancedoptions设置为1，该选项在停止并重新启动服务器后生效。
设置showadvancedoptions设置为1，在查询分析器中运行以下命令：
usemaster
execsp_configure'showadvancedoption','1'
如果成功，会返回如下结果：
DBCC执行完毕。
如果DBCC输出了错误信息，请与系统管理员联系。
已将配置选项'showadvancedoptions'从1改为1。
请运行RECONFIGURE语句以安装。
设置setworkingsetsize=1,运行如下命令：
usemaster
execsp_configure'setworkingsetsize','1'
如果成功，会返回如下结果：
DBCC执行完毕。
如果DBCC输出了错误信息，请与系统管理员联系。
已将配置选项'setworkingsetsize'从0改为1。
请运行RECONFIGURE语句以安装。
…………………………………………
CacheHitRatio（高速缓存命中率，所有Cache”的命中率。
在SQLServer中，Cache可以包括LogCache，BufferCache以及ProcedureCache，是一个总体的比率。
）高速缓存命中次数和查找次数的比率。
对于查看SQLServer高速缓存对于你的系统如何有效，这是一个非常好的计数器。
如果这个值很低，持续低于80%，就需要增加更多的内存。
设置架构高速缓存：
架构高速缓存可显著提高XPath查询的性能。
当对带批注的XDR架构执行XPath查询时，架构存储在内存中，而必要的数据结构内置在内存中。
如果设置了架构高速缓存，架构仍保留在内存中，因而提高了后续Xpath查询的性能。
解释：
架构－SCHEMA
CREATESCHEMA
创建一个架构，并且可以在概念上将其看作是包含表、视图和权限定义的对象。
CREATESCHEMA提供了在单个语句中创建表、视图以及授予对象权限的方法。
如果在创建任何对象或授予任何权限（这些是在CREATESCHEMA语句中指定的）时发生错误，则不会创建任何对象。
XPath查询－Xpath（XMLPath语言）是一种图形导航语言
个人理解简单的说就是在URL中指定查询。
[详细知识参照sqlserver联机帮助文档]
设置方法：
升级会员 