关于SQL-SERVER的性能指标的请教

生如火花

各位做过SQL-SERVER或其他数据库性能测试的同仁们，麻烦指点一下
1，我应该着重检查哪些指标？
2，这些指标不正常的标准是什么？
3，相应的开发人员应该修改的目标是什么？

jojoto2008

我添加了服务器，只能看到服务器的使用资源，不能看到SQL2005的，这里需要怎样设置吗

BiSheng

我没有这方面的实际经验，以上只是以前下的资料，希望能对大家有所帮助

BiSheng

数据库(SQL Server)：
? Cache Manager Cache Hit Ratio(all instances)：
显示在高速缓存中找到数据的命中率。如果数值持续小于 85%，则表示内存有问题，需增加内存直到这个数值持续高于90％。

? Access Methods Full Scans/sec :
全表扫描/秒，每秒不受限的完全扫描数。可以是基本表扫描或全索引扫描。如果该计数器显示的值大于1或2，应该分析你的查询语句以确定是否确实需要全表扫描，以及SQL查询语句是否可以被优化。
? Locks Number of Deadlocks/sec：死锁的数量/秒，导致死锁的锁请求的数量。
? General Statistics User Connections：显示当前 SQL 用户数。与 Active Server Pages：Requests/Sec 计数器进行比较，可帮助了解脚本对 SQL Server 的影响程度。如果差别过大，则表示测试脚本不能有效地对 SQL Server 进行压力测试。
? General Statistics Logins/sec：这是每秒登录到 SQL Server 的计数。
? SQL Statistics Batch Requests/sec：每秒收到的 Transact-SQL 命令批数。这一统计信息受所有约束（如I/O、用户数、高速缓存大小、请求每秒收到的 Transact-SQL 命令批数。这一统计信息受所有约束（如I/O、用户数、高速缓存大小、请求的复杂程度等）影响。批请求数值高意味着吞吐量很好。

oracle的性能
CPU used by this session: This is the amount of CPU time (in 10s of milliseconds) used by a session between the time a user call started and ended. Some user calls can be completed within 10 milliseconds and, as a result, the start and end user-call time can be the same. In this case, 0 milliseconds are added to the statistic. A similar problem can exist in the operating system reporting, especially on systems that suffer from many context switches.
OS User level CPU time:
OS System call CPU time :  Bytes received via SQL*Net from client: The total number of bytes received from the client over Net8.
Logons current: The total number of current logons.
Opens of replaced files: The total number of files that needed to be reopened because they were no longer in the process file cache
User calls:  Oracle allocates resources (Call State Objects) to keep track of relevant user call data structures every time you log in, parse, or execute. When determining activity, the ratio of user calls to RPI calls gives you an indication of how much internal work gets generated as a result of the type of requests the user is sending to Oracle.Oracle.
SQL*Net roundtrips to/from client: The total number of Net8 messages sent to, and received from, the client.
Bytes sent via SQL*Net to client: The total number of bytes sent to the client from the foreground process(es).
Opened cursors current: The total number of current open cursors
DB block changes: Closely related to consistent changes, this statistic counts the total number of changes that were made to all blocks in the SGA that were part of an update or delete operation. These are changes that are generating redo log entries and hence will be permanent changes to the database if the transaction is committed. This statistic is a rough indication of total database work and indicates (possibly on a per-transaction level) the rate atwhich buffers are being dirtied.
Total file opens: The total number of file opens being performed by the instance. Each process needs a number of files (control file, log file, database file) in order to work against the database.
oracle内存测定方面的指标
测定库缓存命中率,推荐＞95%
Select sum(pins)/(sum(pins)+sum(reloads)) *100 from v$librarycache
测定共享sql和pl/sql的固定对象命中率,推荐＞95%
Select sum(pinhits)/(sum(pins)) *100 from v$librarycache
测定共享sql和pl/sql的重载命中率，希望值为0，若＞1%，成功率太低
Select sum(reloads)/sum(pins) *100 from v$librarycache
库快存:select(sum(pins-reloads))/sum(pins) from v$librarycache; 应接近1共享池中的自由内存可以查看：
select * from v$sgastat where name='free memory'
如果共享池满了并不一定存在问题，只要上面所说的比率接近于1，就不需要增加共享池大小；
如果自由内存接近于0而且库快存或数据字典快存的命中率小于0.95，那么需要增加共享池的大小。

测定数据字典的命中率，推荐高于95%
Select (1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100
From V$rowcache
数据字典行缓存丢失率，值应小于10~15%：
select sum(getmisses)/sum(gets)*100 from v$rowcache
2．数据缓冲区
测定数据的缓存命中率推荐持续高于95%
select 1-(sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db_block gets',value,0))+(sum(decode(name,'consistent gets',value,0)))))＊100
from v$sysstat
优化DB－BLOCK－BUFFERS
select 1-a.value/(b.value+c.value)
from V$SYSSTAT a,V$SYSSTAT b,V$SYSSTAT c
where a.name='physical reads' and b.name='db block gets' and c.name='consistent gets'
如果命中率低于0.6～0.7，则应增大DB－BLOCK－BUFFERS
3.日志缓冲区
日志缓冲区的申请失败率：
申请失败率＝requests/entries，申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加ORACLE数据库的日志缓冲区。
select A.VALUE / B.VALUE FROM V$SYSSTAT A,V$SYSSTAT B WHERE A.NAME='redo log space requests' AND B.NAME='redo entries'
参数LOG_BUFFER指定了REDO LOG的缓冲区的保留大小。若日志缓冲区已满，进程就会等待，造成性能下降。
Select value from v$sysstat where name='redo log space requests'
以上结果尽可能为零，如果持续上升就要调整LOG_BUFFER
测定内存排序命中率，推荐高于95%
select round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2)
from v$sysstat a,v$sysstat b
where a.name='sorts (disk)' and b.name='sorts (memory)'
2．Unix/Linux操作系统
Unix/Linux操作系统在企业级应用中非常广泛，我们通常会碰到，那么我们如何监视Unix/Linux操作系统的资源呢？
1.首先要在Unix/linux下安装一个守护进程，rstatd ，这个安装包，可以到网上下载，我也可以提供
2.安装方法; 解压到一个目录，然后./configure, make , make install ，安装完成
3.执行./rpc.rstatd start rstatd， 启动这个守护进程
4.从LR里面add measurement， 填写linux机器的IP，出现所有unix/linux的计数器，包括cpu的，mem的，disk，network的，看看这个图片
5.每个计数器的含义：
average load :在过去的1分钟，的平均负载
cpu utilization: cpu的使用率
disk traffic: disk传输率
paging rate： 每秒从磁盘读到物理内存，或者从武力内存写到页面文件的内存页数
Swap-in rate: 每秒交换到内存的进程数
Swap-out rate: 每秒从内存交换出来的进程数

guanqunqun

顶一个！我也需要

生如火花

请大家做过相关方面的指点一下，谢谢