《Sybase数据库系统性能调优详解》pdf电子书免费下载
下载方式一：
百度网盘下载地址：https://pan.baidu.com/s/1ubENgUDtbs99Cj8Qun6g9g
百度网盘密码：1111
下载方式二：
http://ziliaoshare.cn/Download/ae_123660_do_SybaseSJKXTXNDYXJ.zip
|
|
作者：empty 出版社：empty |
《Sybase数据库系统性能调优详解》介绍
浅析Sybase数据库系统性能调优
性能调优”是对应用程序的性能优化。SYBASE数据库“性能调优”的主要目的是减少对系统公共资源的争用。对sybase数据库系统的性能进行优化,是一项长期且受诸多因素影响的工作,它可划分为以下4个层次：
(1) 服务器层：包括对内存的合理分配,锁操作和临时表的使用,与系统配置关联的磁盘的I/O性能。
(2) 数据库层：:包括数据库对象的设计,索引的创建!表中数据类型的选择,数据库设备的分配及使用。
(3) 应用层：包括T_SQL查询语句的优化,应用级封锁,事务和游标的使用。
(4) 运行环境层:包括硬件、操作系统和网络对总体性能的影响。
在数据库应用系统的管理维护中，运行环境引起的性能劣化只有通过硬件的升级才能得到优化，在系统硬件配置和网络设计确定的情况下，影响系统性能的主要是数据库层和服务器层。笔者就数据层和服务器层优化进行总结。
一、数据库表的优化
对于Sybase11.9以前的版本，由于数据库管理系统只提供了页级锁和表级锁,因此减少页的竞争将有助于提高系统的性能.
当大多数更新都发生在同一页上时，该页将成为热点，通过分析性能监测报告,我们可以得到系统的页竞争情况,例如下述报告表明有99%的插入发生在堆表的最后一页上,并且正在等待锁:
Last Page Loocks on Heaps
Granted 3.0 0.4 185 88.1%
Waited 4.0 0.0 25 11.9%
解决上述问题的方法有两种:一种是将表分区,表分区后可产生多个页链,这样就有多个“最后一页”来满足插入要求，从而减少并发插入时的相互等待；另一种是采用非簇类索引，将更新分布于表中不同的数据页上，但该方法会增加数据物理顺序的开销。
对于一些竞争非常激烈且记录条数较少的表，我们可以通过减少数据页或索引页上行的数量来进行优化，即将一条记录分布在一页上， 这样各进程需要的页都不相同，从而可以大大减少数据页的竞争。
在建表时Sybase提供了fillfactor和max_roms_per_page两个参数，分别用来改变索引页和数据页的填充程度。
在定义数据库表时,字段的数据类型选择是否合理对数据库的性能和操作有很大影响,
（1）Identify字段不要作为表的主键与其它表关联，这将会影响该表的数据迁移。
（2）TEXT与IMACE字段常用来存放二进制对象，这类数据的操作相比其它数据类型较慢，因此要避免使用。
建立一个好的索引对优化数据库的查询性能是非常重要的。要设计一个合理，索引关键就在于创建什么字段作为索引以及创建哪种类型的索引，这是因为定义哪个字段作为索引，涉及到执行一次详细的查询分析需检查其查找子句中哪些字段引用以及索引的有用性，并把这些查询按重要性排队。由于SQL SERVER一般在每张表上只选一个索引来满足查询，因此索引中的第一个元素最好是惟一性最好的。
群集索引通常用于主键标，因为主键标一般是一张表的主访问路径。不过，在下列情况下也可采用群集索引。
范围查找，含有大量重复值的字段；
ORDER BY中常引用的字段；
连接子句中引用的不是主键标的字段；
非常频繁地被访问的字段。
非群集索引一般用于以下情况：
单行查找；
连接运算以及在选择性很高的字段上的查询。
带有小范围检索的查询。
虽然采用索引可以提高数据库的查询性能，但过多的索引会适得其反，这是因为在修改、插入或删除数据时为了保持最新的索引，必须引发系统I/O开销。因此当索引列中的大量数据被增加、改变或删除时，应使用命令UP_DATE STATISTICS保持索引的最新状况。
同时，SQL SERVER所具有的基于成本的查询优化器将比较表扫描与利用索引进行查询系统的I/O开销,以找出最佳途径,因此,并非在表上建立了群集索引或非群集索引就一定会被使用,而是取决于对检索数据的查寻命令的写法和应用的要求，索引的使用效果在相当程度上是依赖于应用程序的设计的,究竟是让索引满足程序的设计需要,或是程序的设计遵循已建立的索引,两者之间是相辅相成的,只有正确地使索引与程序结合起来,才能使系统的性能优化到最佳状态。
二、内存性能调优
为了最大限度的减少对应用系统运行状态的影响，对sybase数据库的调优主要从内存的使用和tempdb的优化来进行
（一）、ASE对内存使用
1、内存对ASE性能的影响
由于访问内存比访问硬盘快，有充足内存可减少硬盘I/O，从而提高ASE性能
2、ASE内存分配情况：见下图
ASE安装好后可供数据库使用的内存有两大部分：过程缓存和数据缓存。其中： 过程缓存：用于存放查询计划、存贮过程和触发器；数据缓存：用于所有数据、索引和日志数据页。过程缓存、数据缓存的大小用系统存储过程 “sp_configure”进行配置。对内存的忧化可从以下几点进行：
①、 查看并配置ASE内存：
Sybase ASE安装完成后“total memory”缺省为21504页（2K页），在实际应用中应根据服务器的物理内存大小进行配置：例如256M的物理内存“total memory”可配置为90000-100000
Sp_configure “total memory”,100000
配置完成后重启ASE服务使配置生效。
②、配置足够大的命名数据高速缓存以容纳关键表和索引。这样可防止其它服务器活动争用高速缓存空间，并加速使用这些表的查询，因为所需页始终都可在高速缓存中找到。同时，可以考虑将“热”表如：用户应用程序对其需求较大的表绑定到一个高速缓存上，而表上的索引绑定到其它高速缓存，以提高并发性。
具体做法如下：
创建命名缓存
sp_cacheconfig cache_name,”size[P|K|M|G]”
例如创建一个10MB的命名缓存pubs_cache ：sp_cacheconfig pubs_cache,”10M”
把表绑定到指定的命名缓存：
sp_bindcache cache_name,dbname[,[owner.]table_name[,indexname|”text only”]]
例如把titles表绑定到上面刚建的命名缓存中：
sp_bindcache pubs_cache,pubs2..titles
③、配置完成后，可以使用dbcc命令检查内存的使作情况。
（二）对tempdb使用优化
缺省情况下，tempdb数据库是放置在master设备上，容量为2M，而临时数据库是活动最为平凡的数据库常常被用来排序、创建临时表、重格式化等操作，所以tempdb的优化应该受到特别的关注,缺省情况下，用于tempdb的system、default和logsegment段在主设备上分配了2MB空间。将第二个设备分配给tempdb后，即可在default和logsegment段中将主设备删除。使用这种方式，可以确保tempdb中的工作表和其它临时表不会和主设备上的其它使用相互争用。
具体操作如下：
第一步：将tempdb移到新的设备上
(1) 创建tempdb_dev设备，扩展tempdb空间
例如d: device dempdb_dev.dat
disk init
name:”tempdb_dev”
phyname:”d: device dempdb_dev.dat”,
vdevno=4,size=5120
（2）将tempdb移动到新的设备tempdb_dev
alter database tempdb on tempdb_dev=5
（3）从tempdb段上移走master设备
（4）校验defaule段已包括主设备
说明：若将临时数据库放在多个磁盘设备上，可以更好的利用并行查询特性来提高查询性能。
第二步：将临时数据库与高速缓冲进行绑定。
由于临时表的创建、使用，临时数据库会频繁地使用数据缓存，所以应为临时数据库创建高速缓存，从而可以使其常驻内存并有助于分散I/O：
1、创建命名高速缓存：
sp_cacheconfig“tempdb_cache”,“1000m”,”mixed”
查询命名CACHE情况,sp_cacheconfig或sp_helpcache
2、重新启动server使配置参数生效
3、捆绑临时数据库到tempdb_cache高速缓存：
sp_bindcache “tempdb_cache”,tempdb
若有大的I/O，配置内存池
sp_poolcache“tempdb_cache”，“5M”，“16k”
第三步：优化临时表
大多数临时表的使用是简单的，很少需要优化。但需要对临时表进行复杂的访问则应通过使用多个过程或批处理来把表的创建和索引分开。以下两种技术可以改善临时表的优化
1、在临时表上创建索引
1）临时表必须存在
2）统计页必须存在（即不能在空表上创建索引）
2、把对临时表的复杂的使用分散到多个批处理或过程中，以便为优化器提供信息。
为了便于确定性能问题所在，我们把数据库应用系统分为几个层次(或称为调优层次)。下面就几个调优层提几点建议：
l 应用层： OLTP与DSS、事务设计(尽量采用短事务从而减少锁争用)、索引可增加查询速度但减少数据修改速度、用参照完整性会在修改数据时需表的连接、使用存贮过程l 数据库层：把数据分布在不同数据库设备上以减少I/O争用、将关键表和索引放在缓存中、对有大量数据加载的表可将该表分片l 服务器层：调整内存和其它服务器参数、配置缓存和I/O大小、增加多个CPU、避免批操作与OLTP争用。设备层：使用多个中等大小设备和多个硬盘控制器可提高I/Ol
网络层：配置网包大小、配置多个网络引擎、配置子网、提高主干网络速度
l 硬件层： CPU吞吐量、硬盘读写速度、内存使用效率
l 操作系统层： 文件系统与裸设备的选择 ：文件系统作为数据库设备，速度快但安全性差，一般用于开发环境；裸设备作为数据库设备，速度慢但安全性好。
sybase 性能诊断sp_sysmon_并行处理
本篇文章描述了通过sp_sysmon对Adaptive Server系统运行情况有一个全面系统了解，有利于更好地熟悉系统性能，更为有效地进行系统管理，合理地利用和配置系统资源，达到系统性能调优的目的。
从18个方面了解在用系统性能状况，并在适当的时候利用环境参数进行性能调优：
1、内核管理（kernal） 2、应用管理（appmgmt） 3、数据缓存管理（dcache）
4、ESP管理（esp） 5、索引管理（indexmgmt） 6、锁管理（locks）
7、内存管理（memory） 8、元数据高速缓存管理（mdcache）9、任务管理（taskmgmt）
10、监视器访问SQL的执行（monaccess） 11、网络I/O管理（netio）
12、并行查询管理（parallel）13、过程缓存管理（pcache）14、恢复管理（recovery）
15、事务管理（xactmgmt）16、事务概要（xactsum） 17、磁盘I/O管理（diskio）
18、工作进程管理（wpm）
括号后英文短词是该模块参数。
环境： 1、用户数据库中有练习所用数据表auths和article
2、数据表各有10万行数据
3、用户具有查询、修改、删除等基本的数据库表操作权限
步骤：执行sp_sysmon “00:10:00”（server级系统存贮过程，不需要打开某个数据库），或者执行如下格式的过程，查看具体操作批命令对应系统性能情况：
sp_sysmon begin_sample
SQL语句或者存贮过程
sp_sysmon commit_sample
本实验采用 sp_sysmon “hh:mm:ss”,性能模块名。
结论：通过此练习，可了解当前系统在各方面的系统运行状况，性能出现什么问题和不平衡不协调之处，学会使用相应的参数和措施进行解决和调优，不断比较对照调整前后的性能状况，最终改善系统性能。
说明：1、该命令执行结果集的开头相同如下，各分块练习不再一一列示：
======================================================================
Sybase Adaptive Server Enterprise System Performance Report
======================================================================
Server Version: Adaptive Server Enterprise/11.9.2/1031/P/NT (IX86)/OS 3.
Server Name: Server is Unnamed
Run Date: May 28, 2001
Statistics Cleared at: 15:57:27
Statistics Sampled at: 16:07:28
Sample Interval: 00:10:00
2、执行结果集的每列信息提示：
per sec ： 采样期间每秒的平均值
per xact： 采样期间每提交一个事务的平均值
count ： 采样期间每秒的总计值
% of total： 占总数的百分比，根据不同情况各有不同
3、结果集对应给出性能情况描述、分析以及可调性说明
4、本练习只给出部分模块的监视结果（可能有删节），用sp_sysmon “hh:mm:ss”可看全部详细情况。
监视并行查询管理
命令行：sp_sysmon “00:10:00”,parallel
结果：
报告并行查询次数、执行期间调整了多少工作进程，以及在merge和sort操作时加锁情况。
Parallel Query Management
-------------------------
Parallel Query Usage per sec per xact count % of total
------------------------- --------- --------- ------- ----------
Total Parallel Queries 0.1 8.0 16 n/a
优化器自动确定是否并行操作，以及为此使用多少工作进程。
WP Adjustments Made
Due to WP Limit 0.0 0.0 0 0.0 %
会话级的限制受“set parallel_degree” or “set scan_parallel_degree”参数控制。
Due to No WPs 0.0 0.0 0 0.0 %
缺乏可用的工作进程导致申请工作进程数减少。可适当增加“number of worker processes”
Merge Lock Requests per sec per xact count % of total
报告并行merge操作的锁请求数，很快授予锁的数目，下面3种类型锁的等待情况：
------------------------- --------- --------- ------- ----------
Network Buffer Merge Locks
Granted with no wait 4.9 438.5 877 56.2 %
Granted after wait 3.7 334.5 669 42.9 %
Result Buffer Merge Locks
Granted with no wait 0.0 0.0 0 0.0 %
Granted after wait 0.0 0.0 0 0.0 %
Work Table Merge Locks
Granted with no wait 0.1 7.0 14 0.9 %
Granted after wait 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total # of Requests 8.7 780.0 1560
Sort Buffer Waits per sec per xact count % of total
------------------------- --------- --------- ------- ----------
Total # of Waits 0.0 0.0 0 n/a
并行排序所用“排序缓冲区等待”锁。如果等待数较高，可考虑加大“number of sort buffers”的值。
sybase性能优化经验谈！
2007-05-17 18:46
首先，有一句话要认识 ： 80%的性能问题由SQL语句引起。
经过看 SYBASE 的书，结合从 MSSQL 迁移过来的系统过程 ，发现以下几个问题比较重要：
经验一、where 条件左边最好不要使用函数，比如 ★★★★★
select ... where datediff(day,date1,getdate())>;0
这样即使在 date1 列上建立了索引，也可能不会使用索引，而使用表扫描。
这样的语句要重新规划设计，保证不使用函数也能够实现。通过修改，一个系统过程的运行效率提高大约100倍！此外不要使用诸如like '%ab',不能充分利用索引，而要在%前加字符
经验二、两个比较字段最好使用相同数据类型，而不是兼容数据类型。比如 int 与 numeric（感觉一般不是太明显）★★
经验三、复合索引的非前导列做条件时，基本没有起到索引的作用。★★★★★
比如 create index idx_tablename_ab on tablename(a,b)
update tablename set c = XX where b>;= XXX and ...
在这个语句中，基本上索引没有发挥作用。 导致表扫描引起blocking 甚至运行十几分钟后报告失败。
一定要认真检查 改正措施： 在接口中附加条件
update tablename set c = XX where a = XXX and b>;= XXX
或者建立索引类似于
create index idx_tablename_ba on tablename(b,a)
经验四、 多个大表的关联查询，如果性能不好，并且其中一个大表中取的数据比较少，可以考虑将查询分两步执行。★★★★
先将一个大表中的少部分数据 select * into #1 from largetable1 where ...
然后再用 #1 去做关联，效果可能会好不少。（前提：生成 #1表应该使用比较好的索引，速度比较快）
经验五、 tempdb 的使用。★★★★★
最好多用 select into ，这样不记日志 ，尤其是有大量数据的报表时。虽然写起来麻烦，但值得。
create table #tmp1 (......)这样写性能不好。尤其是大量使用时，容易发生tempdb 争用。
经验六、 系统级别的参数设置 ★★★★
一定要估计一下，不要使用太多，占用资源 ，太少，发生性能问题。
连接数，索引打开个数、锁个数 等、 当然 ，内存配置不要有明显的问题，比如，procedure cache
不够 （一般缺省20%，如果觉得太多，可以减少一些）。如果做报表经常使用大数据量读，可以考虑使用
16Kdata cache
经验七、索引的建立。很重要。★★★★★
clustered index /nonclustered index 的差异，自己要搞清楚。各适用场合，另外如果
clustered index 不允许 重复数，也一定要说明。
索引设计是以为数据访问快速为原则的，不能 完全（！！） 参照数据逻辑设计的，逻辑设计时的一些东西，
可能对物理访问不起作用
经验八、统计数据的更新：大约10天进行 update statistics ,sp_recompile table_name（★★★）
经验九、强制索引使用 （★★★★）
如果怀疑有表访问时不是使用索引，而且这些条件字段上建立了合适的索引,可以强制使用
select * from tableA (index idx_name) where ...
这个对一些报表程序可能比较有用。
经验十、找一个好的监视工具 ★★★
工欲善其事，比先利其器，一点都不错呀。
我用 DBartisian 5.4 ,监视哪些表被锁定时间长， blocking 等
还有 sp_object_status 20:00:00 ， sp_sysmon 20:00:00 等
以上是我的一点经验，在不到一个月的时间内，我修改了20个左右的语句和系统过程 ，
系统性能明显改善，cpu利用 高峰时大约50% 平时 不到30%IO 明显改善。所有月报表能顺利完成 5min 以内。
经验十一： 综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
另外，系统中确认不使用的中间数据，可以进行转移。这些要看系统的情况哦
呵呵 写完后忘记一个 一定要注意热点表 ，这是影响并发问题的一个潜在因素。！解决方法： 行锁模式 如果表的行比较小，可以故意增加一些不用的字段
比如 char(200) 让一页中存放的行不要太多。
SYBASE内存和缓冲区管理
内存和缓冲区管理
-------------------------------------
为SQL SERVER提供的可用内存越多，SQL SERVER就有越多的资源使用于缓冲区，即可减少服务器
从磁盘读取数据以获取静态信息或编译过程计划的次数。调整SQL SERVER所占内存可通过在服务器配
置文件中设置total memory参数进行，该参数的值指定了SQL SERVER在启动时所需的内存总量，如若
该值为10000页，则SQL SERVER在启动时就将试图获得10000*2K=19.5M的内存，若不能满足则启动失败。
注意，若操作系统在进程的整个生命周期中支持动态内存分配，则可在SQL SERVER启动后为其分配额
外的内存。
SQL SERVER启动时将内存分配给SQL SERVER可执行代码，SQL SERVER使用的静态内存，用户可配
置参数占用的内存及不驻留在缓存上的数据结构，剩余的内存分配给两种SQL SERVER缓冲区即：数据
缓冲区和过程缓冲区。两缓冲区的大小对整个系统的性能影响很大，在一个开发系统上可能要增加过多端
程缓冲区的专用内存，而在一生产系统上则需减少过程缓冲区大小以便获得更大的数据缓冲区。
1。 确定缓冲区大小
在total memory配置的总空间中，首先要除去可爱执行代码所占空间，其大小因平台和版本
不同而异，可使用sp_configure查询executable codesize参数的值得知，一般为3-4MB；其次需
除去内部结构所占空间，内部结构分成内核结构和服务器结构，亦可将此部分内存看成静态开销
和用户配置参数占用两个部分，前者大小不受用户可配置参数的影响，一般为2-3MB，而后者即用
户可配置参数的大小则取决于配置参数的类型及其值的大小，包括number of user connections,
number of open databases,number of devices,number of open objects,number of locks等，
要得到这些用户配置参数使用内存大小的精确估计，可调用不带选项的sp_configure以显示所有
参数和每个参数使用的内存量，其和即是用户配置参数所占用内存。
除去上述占用，剩余内存将以procedure cache percent配置参数的值按比例分配给过程缓冲
区和数据缓冲区，例若值为20，即表示剩余内存的20%分配给过程缓冲区而80%分配给数据缓冲区。
确定缓冲区大小的另一种方法是检查SQL SERVE启动时写入SQL SERVER错误日志的内存信息，
其中准确地说明了分配给过程缓冲区和数据缓冲区的数据量，以及多少个过程或其他编译对象能
同时驻留在缓冲区中，如相关信息为：
server:Number of proc buffers assocated:556
server:Number of blocks left for proc headers:629
server:Memory allocated for the default data cache:4144kb
前两行为过程缓冲区信息，过程缓冲区总大小是分配给过程缓冲区的内存(第一行)及分配给过
程头(即存放编译对象如存储过程的地方，根据被存储对象的大小，可能需要一个或多个过程头)
的内存(第二行)之和，可存储在过程缓冲区中的编译对象受到此二者中较小者的限制。上述信息
中，第一行指明了分配给过程缓冲区的缓冲区数量为556，每个缓冲区大小为76B，故过程缓冲区
大小即为42256B，合21页；第二行则指明了分配给过程头的空间为629页，由此可以得出，过程
缓冲区总大小为629+21=650页，合1.27M。
信息中第三行则指明了分配给默认数据库缓冲区的空间大小。此外，若配置了命名缓冲区，则
相关信息还将包含每个命名缓冲区的信息，再加上这些命名缓冲区所占内存大小即得到总的数据
缓冲区大小。欲知数据缓冲区大小还可通过sp_helpcache过程得到每个缓冲区和捆绑到这些缓冲
区中的对象以及与不同缓冲区大小相关的开销的详细信息。
2。 缓冲区管理
数据缓冲区保存SQL SERVER当前使用和最近使用过的数据页,索引页和日志页.初装SQL SERVER
时,会产生一个默认的数据缓冲区.SQL SERVER允许系统管理员把数据缓冲区分成独立的命名数据
缓冲区,然后可把数据库或数据库对象捆绑到这些命名缓冲区上,以对缓冲区空间进行更合理的组
织,控制数据库,表和索引在内存的驻留.另外,系统管理员还可在缓冲区内创建缓冲池,用于执行
大块的磁盘I/O,改善数据查询性能,减少磁盘I/O.有关数据库缓冲区的指令有:
sp_cacheconfig 创建或删除命名缓冲区,改变缓冲区的大小或类型
sp_poolconfig 创建或删除I/O缓冲池,并改变其大小
sp_bindeache 将数据库或数据库对象捆绑到缓冲区
sp_unbindcache 从一个缓冲区中取消对指定对象或数据库的捆绑
sp_unbindcache_all 从一个缓冲区中取消所有对象的捆绑
sp_helpcache 报告有关数据缓冲区的小结信息,并显示捆绑于缓冲区的数据库和对象
sp_cachestrategy 报告有关为表,索引设置的缓冲区策略,禁止或重新允许先提或MRU策略
sp_logiosize 为日志改变默认的I/O大小
sp_spaceused 估计表和索引大小或有关数据库使用的空间量的信息
sp_estspace 估计表和索引的大小,给出表包含的行的数目
sp_help 报告表将捆绑于哪个缓冲区
sp_helpindex 报告索引将捆绑于哪个缓冲区
sp_helpdb 报告数据库将捆绑于哪个缓冲区
setshowplanon 报告有关用于查询的I/O大小和缓冲区的应用策略
setstatisticsioon 报告为查询进行的读操作数量
setprefetch on/off 允许或禁止某个会话的预先提取
(1) 查询数据库缓冲区的信息
查看默认数据缓冲区的大小,可使用指令sp_cacheconfig default data cache ,显示
结果如下,由两部分构成,上部分报告每个缓冲区中的内存池配置信息并总计了所有配置缓
冲区的大小;下部分提供了每个缓冲区中的其他详细信息.
第一部分输出各列含义为:cache name为缓冲区名;status指示该缓冲区是否激活(值为
pend/act时表示该缓冲区刚建立,重启后将被激活;值为active时表示该缓冲区目前正处于
激活状态;值为pend/del时表示该缓冲区处于激活状态,但在服务器重启后将被删除);type
指示缓冲区是否可以存储数据( mixed 和日志页( logonly ,只有默认缓冲区才会有
default 类型,且默认的数据缓冲区类型不能改变,其他类型缓冲区也不能改为 default ;
config value显示下次SQL SERVER重启后缓冲区的大小,在此情况下,默认缓冲区不能显式
配置,故信息中其大小为0;run value显示SQL SERVER目前使用的大小,对于默认数据缓冲
区,该值将作为未显示配置到其他缓冲区中的所有数据缓冲区空间.
第二部分输出各列含义为:IO size显示缓冲池中的缓存大小,所有缓冲池缺省都分配2K
空间(有效空间大小为2K,4K,8K,16K);wash size指示缓冲池的刷洗区大小;config size和
run size显示配置大小和目前使用的大小,该两值在2K的缓冲池中是不同的(因为不能显式
配置其大小),而对于其他缓冲池,若试图在缓冲池间移动空间而一些空间又不能被释放时,
上述两值也不同.
Cache Name Status Type Config Value Run value
---------------- ------ ------ ---------------- -------------
default data cache Active Default 0.00MB 59.36MB
-------------------------------
Total 0.00MB 59.36MB
===================================================================
Cache:default data cache, status:Active, Typeefault
Config Size:0.00MB, Run size:59.36MB
IO Size Wash Size Config Size Run Size
------- --------- ----------- --------
2KB 512KB 0.00MB 59.36MB
(2) 配置数据缓冲区
配置数据缓冲区有两种途径:一是使用过程sp_cacheconfig和sp_poolconfig进行交互
式配置,二是编辑配置文件中的total memory参数后重启使之生效.
A. 交互式配置
> 创建命名缓冲区 (新创建的命名缓冲区从缺省缓冲区default data cache中分配空间)
每次执行sp_cacheconfig或sp_poolconfig时,SQL SERVER都将新的缓冲区或缓冲池信
息写入配置文件中并将文件的旧版本拷贝到一个备份文件中,给出备份文件名的信息被
送至错误日志.
创建一个新的名为pubs_cache的10M缓冲区指令如下,大小单位除M外,还可为P页,K,G,
(默认为K).该指令将改变系统表并将新值写入配置文件但暂不激活缓冲区,要使其生效
须重启SQL SERVER.
sp_cacheconfig pubs_cache, 10M
在重启SQL SERVER使新值生效前,可先用sp_cacheconfig pubs_cache 查看配置信息
如下,其中的status列值为pend/act说明该缓冲区的配置是挂起的,要待重启后生效,故
Config value(配置的值)和Run value(正在使用的值)不同.
Cache Name Status Type Config Value Run value
---------------- ------ ------ ---------------- -------------
pubs_cache pend/act mixed 10.00MB 00.00MB
-------------------------------
Total 10.00MB 00.00MB
重启后再允许不带缓冲区名参数的sp_cacheconfig,则信息改变如下,可见pubs_cache
已被激活,默认缓冲区从其空间中分出了10M给该缓冲区.可利用sp_cacheconfig过程设置
默认数据缓冲区的最小值,该部分空间将被锁定给默认数据缓冲区.例如设置默认数据缓
冲区大小最少不低于25M则指令为sp_cacheconfig default data cache 25M .
Cache Name Status Type Config Value Run value
---------------- ------ ------ ---------------- -------------
default data cache Active Default 0.00MB 49.28MB
pubs_cache Active mixed 10.00MB 10.00MB
-------------------------------
Total 10.00MB 59.28MB
===================================================================
Cache:default data cache, status:Active, Typeefault
Config Size:0.00MB, Run size:49.28MB
IO Size Wash Size Config Size Run Size
------- --------- ----------- --------
2KB 512KB 0.00MB 49.28MB
===================================================================
Cache:pubs_cache, status:Active, Type:mixed
Config Size:10.00MB, Run size:10.00MB
IO Size Wash Size Config Size Run Size
------- --------- ----------- --------
2KB 512KB 0.00MB 10.00MB
若要保留一个只用于事务日志的缓冲区的类型,可以将缓冲区的类型设为 logonly ,
如创建 logonly 类型的pubs_log缓冲区为sp_cacheconfig pubs_log, 7M , logonly ,
下示为该缓冲区在重启前的状态.
Cache Name Status Type Config Value Run value
---------------- ------ ------ ---------------- -------------
pubs_cache pend/act log only 7.00MB 0.00MB
-------------------------------
Total 7.00MB 0.00MB
创建命名缓冲区后一般都须将某些对象捆绑到该缓冲区上。若建立命名缓冲区后
未将任何对象捆绑到该命名缓冲区上，则该命名缓冲区所占内存将被浪费，因为任
何未指定缓冲区的对象都将缺省使用默认数据缓冲区default data cache.
> 将数据缓冲区划分为缓冲池
建立数据缓冲区后,可将其划分成缓冲池,各不同的缓冲池具有不同的I/O大小,当
SQL SERVER进行大I/O操作时,可将许多页一次读入缓冲区中,故可提高I/O效率.这些
页总是被当作一个单元进行处理,它们共存于缓冲区中,作为一个单元被写到磁盘中.
缓冲池的页大小可为2K,4K,8K,16K,缓冲池的总大小不能低于512K.所有关于缓冲池
的配置都是动态的,无需重启SQL SERVER即可生效.SQL SERVER内2K页缓冲池的个数
必须保持一个合理的值，否则将会影响SQL SERVER性能，因为某些命令只能使用2K
的I/O块如disk init及某些dbcc命令。
在pubs_cache数据缓冲区中创建一个每页16K,总空间为7MB的缓冲池,指令如下.通
常,创建命名数据缓冲区时,其空间总是被默认划分为每页2K的缓冲池,创建其它页大
小的缓冲池时,该2K页缓冲池空间被部分分配给新缓冲池.下例中2K页大小的缓冲池
原共有10M空间,分配给7M给16K页的新缓冲池后,2K页缓冲池还剩3M空间.
>sp_poolconfig pubs_cache, 7M , 16K
>go
>sp_cacheconfig pubs_cache
Cache Name Status Type Config Value Run value
---------------- ------ ------ ---------------- -------------
pubs_cache Active mixed 10.00MB 10.00MB
-------------------------------
Total 10.00MB 10.00MB
===================================================================
Cache:pubs_cache, status:Active, Type:mixed
Config Size:10.00MB, Run size:10.00MB
IO Size Wash Size Config Size Run Size
------- --------- ----------- --------
2KB 512KB 0.00MB 3.00MB
16KB 1424KB 7.00MB 7.00MB
在默认数据缓冲区中也可创建缓冲池,如创建16K缓冲池,空间大小为8M,则指令为:
sp_poolconfig default data cache , 8M , 16K .
若要为一个数据库的事务日志创建缓冲区,应配置缓冲区中大部分空间来匹配日志
I/O的大小.默认值为4K,若没有4K的可用缓冲池,SQL SERVER就为日志使用2K的I/O.
日志I/O的大小可通过系统过程sp_logiosize来改变,每个数据库的日志I/O大小会在
SQL SERVER启动后的错误日志中报告,也可通过使用数据库和执行不带参数的过程
sp_logiosize来检查一个数据库的日志I/O大小.如为pubs_log缓冲区配置4K页大小
的缓冲池指令为sp_poolconfig pubs_log, 3M , 4K ,也可在默认数据缓冲区中创
建一个4K页缓冲池,以供未被捆绑至其它缓冲区的任何数据库的事务日志来使用:
sp_poolconfig default data cache , 2.5M , 4K .
此外，可修改缓冲池大小，如从16K页I/O的缓冲池中取出1M空间增加到4K页I/O缓
冲池中:sp_poolconifg pub_cache, 1M , 4K , 16K
> 缓冲区悃绑操作
系统管理员把数据缓冲区分成独立的命名数据缓冲区后,可将数据库对象捆绑到这
些缓冲区上,以控制数据库,表和索引在内存的驻留.若建立命名缓冲区后未将任何对
象捆绑到该命名缓冲区上，则该命名缓冲区所占内存将被浪费，因为任何未指定缓
冲区的对象都将缺省使用默认数据缓冲区default data cache.为了将任何系统表,
包括事务日志syslogs捆绑至缓冲区中,数据库须处于单用户模式.对象被捆绑后立即
生效而无需重启SQL SERVER.注意,进行捆绑或删除捆绑操作时,SQL SERVER需锁定相
应对象,故相应对象上的其它操作可能有延迟.此外,可不删除现有的捆绑而重新捆绑
对象.另外,当被捆绑对象上有脏的读活动或有打开的游标时,捆绑或删除捆绑的操作
将不能进行.
1>sp_dboption pubs2,single,true 置pubs2库为单用户状态
2>use pubs2
3>checkpoint
4>go 准备进行数据库对象的捆绑。注意，捆绑对象须在对象所在的库中进行。
>sp_bindcache pubs_cache,pubs2,titles
将pubs2库中的表titles捆绑至缓冲区pubs_cache.
>sp_bindcache pubs_cache,pubs2,titles.titleind
在titles上捆绑索引,并将pubs2库中的表titles捆绑至缓冲区pubs_cache.
>sp_bindcache pubs_cache,pubs2, hj.sale_east
将pubs2库中用户hj的表sale_east捆绑至缓冲区pubs_cache.
>sp_bindcache pubs_log,pubs2,syslogs
将pubs2库的事务日志syslogs捆绑至缓冲区pubs_log.
>sp_bindcache pubs_cache,pubs2,au_pix, text only
表的text和image列存储在一个单独的数据结构中,若将此表捆绑至缓冲区,需加
text 参数.
1>sp_dboption pubs3,single,true 置pubs3库为单用户状态
2>use pubs3
3>checkpoiot
4>use master
5>go 准备进行数据库的捆绑，注意，捆绑数据库须在master库中进行。
>sp_bindcache tempdb_cache,tempdb
将数据库tempdb捆绑至缓冲区tempdb_cache。
注意，被捆绑到日志缓冲区上的对象只能是syslogs表。
可通过sp_helpcache过程查询所有或指定缓冲区及其中被捆绑对象的捆绑信息.如下
例示,其中status列报告缓冲区的捆绑是( V 否( I 有效.若数据库或对象被捆绑至缓
冲池而该缓冲区已被删除,则捆绑信息仍保留在系统表中但缓冲区捆绑被标记无效,所有
无效的捆绑对象均使用默认的数据库缓冲区.若随后用与被删除的缓冲区相同的名字创建
了另一个缓冲区,则当该缓冲区通过重启SQL SERVER而被激活时,上述捆绑又将变为有效.
删除缓冲区捆绑可用两个过程:sp_unbindcache用于删除缓冲区中对一个实体的捆绑;
sp_unbindcache_all则用于删除缓冲区中对所有对象的捆绑,但若捆绑至缓冲区的数据库
超过8个,或数据库的对象超过8个,就不能使用sp_unbindcache_all,此情况下,必须使用
sp_unbindcache删除单个的数据库或对象,使捆绑的数据库不超过8个.此外,当删除缓冲
区对一个对象的捆绑时,内存中所有当前的页都被从缓冲区中清除.
>sp_unbindcache pubs2
删除对数据库pubs2的捆绑
>sp_unbindcache pubs2,titles
删除对pubs2库中对titles表的捆绑
>sp_unbindcache pubs2,titles,titleidind
铲除对pubs2库中titles表上titleidind索引的捆绑.
> 改变命名数据缓冲区大小
可通过sp_cacheconfig指定一个新的空间以增加或减少指定缓冲区的大小,所有新增空
间都将从缺省数据缓冲区default data cache内的2K缓冲池中被加到指定缓冲区内的2K
页缓冲池中,所有减少的空间亦从指定缓冲区内的2K页缓冲池中被释放到缺省数据库缓冲
区default data cache内的2K缓冲池中.
>sp_cacheconfig pubs_cache
Cache Name Status Type Config Value Run value
---------------- ------ ------ ---------------- -------------
pubs_cache Active mixed 10.00MB 10.00MB
-------------------------------
Total 10.00MB 10.00MB
===================================================================
Cache:pubs_cache, status:Active, Type:mixed
Config Size:10.00MB, Run size:10.00MB
IO Size Wash Size Config Size Run Size
------- --------- ----------- --------
2KB 720KB 0.00MB 3.00MB
4KB 1024KB 4.00MB 4.00MB
16KB 1424KB 3.00MB 3.00MB
现以sp_cacheconfig pubs_cache, 20M 后重启,则改变如下:
>sp_cacheconfig pubs_cache
Cache Name Status Type Config Value Run value
---------------- ------ ------ ---------------- -------------
pubs_cache Active mixed 20.00MB 20.00MB
-------------------------------
Total 20.00MB 20.00MB
===================================================================
Cache:pubs_cache, status:Active, Type:mixed
Config Size:20.00MB, Run size:20.00MB
IO Size Wash Size Config Size Run Size
------- --------- ----------- --------
2KB 512KB 0.00MB 13.00MB
4KB 1024KB 4.00MB 4.00MB
16KB 1424KB 3.00MB 3.00MB
返回pubs_log缓冲区报告:
>sp_cacheconfig pubs_log
Cache Name Status Type Config Value Run value
---------------- ------ ------ ---------------- -------------
pubs_log Active log only 7.00MB 7.00MB
-------------------------------
Total 7.00MB 7.00MB
===================================================================
Cache:pubs_log, status:Active, Type:log only
Config Size:7.00MB, Run size:7.00MB
IO Size Wash Size Config Size Run Size
------- --------- ----------- --------
2KB 512KB 0.00MB 4.00MB
4KB 1024KB 3.00MB 3.00MB
再运行以指令sp_cacheconfig pubs_log, 6M 后重启,则改变为:
>sp_cacheconfig pubs_log
Cache Name Status Type Config Value Run value
---------------- ------ ------ ---------------- -------------
pubs_log Active log only 6.00MB 6.00MB
-------------------------------
Total 6.00MB 6.00MB
===================================================================
Cache:pubs_log, status:Active, Type:log only
Config Size:6.00MB, Run size:6.00MB
IO Size Wash Size Config Size Run Size
------- --------- ----------- --------
2KB 512KB 0.00MB 3.00MB
4KB 1024KB 3.00MB 3.00MB
欲彻底删除一个数据缓冲区,将其大小复位为0可使用sp_cacheconfig pubs_log, 0 .
则将缓冲区状态变为 pend/del ,重启SQL SERVER后此改变生效.在执行该操作前,缓冲
区需保持激活状态,捆绑至缓冲区的所有对象仍使用该缓冲区执行I/O操作.若有对象捆
绑于待删除的数据缓冲区,则SQL SERVER重启后,缓冲区的捆绑被标记为无效,所有无效
缓冲区捆绑的对象都使用默认数据缓冲区.当捆绑被标记为无效时,警告信息将被输入
错误日志.注意,默认数据缓冲区不能删除
sybase 临时数据库
缺省情况下，tempdb数据库是放置在master设备上，容量为2M，而临时数据库是活动最为平凡的数据库常常被用来排序、创建临时表、重格式化等操作，所以tempdb的优化应该受到特别的关注。本篇文章目的在于使你掌握临时数据库的优化策略以及临时表的优化使用。本文中，你将以调整临时库的位置开始，有步骤的完成临时数据库的优化，并在此过程中了解到优化临时数据库和临时表的一些方法和规则。
实验环境的要求：你应具有系统管理员的权限，系统中有auths和article表。
步骤：
第一步：调整临时库的位置
tempdb数据库缺省放在master设备上，将临时数据库发在分离的设备上是更可取的。
1） 初始化一个用来存放临时数据库的设备
disk init
name= tempdb_dev ,
physname= d: sybase example tempdb.dat ,
vdevno=13,
size=15360
(注意：如果将tempdb数据库放在多个设备上，需初始化多个数据库设备)
2)将临时数据库扩展到该一个设备上
alter database tempdb on tempdb_dev=30
3)打开tempdb数据库，从段上删除master设备
sp_dropsegment default ,tempdb,master
sp_dropsegment logsegment,tempdb,master
4)发出如下命令，检查default段中是否不再包含master设备
select dbid,name,segmap from sysusages,sysdevices
where sysdevices.low =syusages.size+vstart
and sysdevices.high>=sysusages.size+vstart-1
and dbid=2
and(status=2 or status=3)
说明：若将临时数据库放在多个磁盘设备上，可以更好的利用并行查询特性来提高查询性能。
第二步：将临时数据库与高速缓冲进行绑定。
由于临时表的创建、使用，临时数据库会频繁地使用数据缓存，所以应为临时数据库创建高速缓存，从而可以使其常驻内存并有助于分散I/O：
1、创建命名高速缓存
sp_cacheconfig “tempdb_cache”,”10m”,”mixed”
2、重新启动server
3、捆绑临时数据库到tempdb_cache高速缓存
sp_bindcache “tempdb_cache”, tempdb
4、若有大的I/O，配置内存池
第三步：优化临时表
大多数临时表的使用是简单的，很少需要优化。但需要对临时表进行复杂的访问则
应通过使用多个过程或批处理来把表的创建和索引分开。以下两种技术可以改善临时表的优化
slash; 在临时表上创建索引
1） 临时表必须存在
2） 统计页必须存在（即不能在空表上创建索引）
slash; 把对临时表的复杂的使用分散到多个批处理或过程中，以便为优化器提供信息
下面的这个过程需要进行优化：
create proc base_proc
as
select * into #huge_result from auths
select * from article, #huge_result where article.author_code=
#huge_result.author_code and sex=”0”
使用两个过程可以得到更好的性能
1）create proc base_proc
as
select *
into #huge_result
from auths
exec select_proc
2) create proc select_proc
as
select * from article,#huge_result
where article.author_code=#huge_result.author_code and sex=”0”
说明：在同一个存储过程或批处理中，创建并使用一个表时，查询优化器无法决定这个表的大小。
结论：通过本实验我们知道，临时数据库经过优化可以极大的提高系统性能。实际工作中，必须考虑具体应用的情况，需长时间经验的积累。
#!/usr/bin/ksh
isql -Upsdss_dm -Pneusoft -w2000 -SSGDM ! >> out.txt
declare @starttime char(25)
declare @stoptime char(25)
select current time || datepart(millisecond,current time) into @starttime
select case
when grouping(DY_DEPT.PROVINCE_ID) = 1 then
'111001'
else
DY_DEPT.PROVINCE_ID
end dm,
case
when grouping(DY_DEPT.PROVINCE_NAME) = 1 then
'国家电网公司'
else
DY_DEPT.PROVINCE_NAME
end dm_name,
sum(fact_table.sum_fee) / 10000 index_1,
sum(fact_table.sum_fee_ly) / 10000 index_2,
case
when sum(fact_table.sum_fee_ly) != 0 then
(sum(fact_table.sum_fee) - sum(fact_table.sum_fee_ly)) /
sum(fact_table.sum_fee_ly) * 100
else
0
end index_3,
'' index_4,
'' index_5,
sum(fact_table.sum_elec) / 10000 index_6,
sum(fact_table.sum_elec_ly) / 10000 index_7,
sum(fact_table.sum_fee) / 10000 - sum(fact_table.sum_fee_ly) / 10000 index_8
from (SELECT DISTINCT PROVINCE_ID, PROVINCE_NAME FROM DY_DEPT) DY_DEPT,
fy_elecfee_province fact_table
where fact_table.MONTH_ID = '200801'
and fact_table.DEPT_ID = DY_DEPT.PROVINCE_ID
group by rollup(DY_DEPT.PROVINCE_ID, DY_DEPT.PROVINCE_NAME)
having((DY_DEPT.PROVINCE_ID is null and DY_DEPT.PROVINCE_NAME is null) or (DY_DEPT.PROVINCE_NAME is not null))
order by DY_DEPT.PROVINCE_ID
select current time || datepart(millisecond,current time) into @stoptime
select 'time',@starttime,@stoptime
go
exit
!