SQL压力测试用的语句和相关计数器

 

将数据库中所有表的所有的内容选一遍:

 

IF object_id('tempdb..#temp')   is   not   null      
BEGIN 
DROP TABLE #temp 
END

DECLARE @index int 
DECLARE @count int 
  DECLARE @schemaname varchar(50) 
DECLARE @tablename varchar(50) 
set @index=1 
set @count=(select count(*) from sysobjects where xtype='U')

  select row_number() over(order by name) as rowNumber,name, 
  ( SELECT a.name from sys.tables t inner join sys.schemas a 
ON t.schema_id=a.schema_id 
WHERE t.name=ob.name) as schemaname 
into #temp from sysobjects ob where xtype='U'

WHILE(@index<@count) 
BEGIN 
set @schemaname=(SELECT schemaname from #temp where rowNumber=@index) 
set @tablename=(SELECT name from #temp where rowNumber=@index)

exec('select * from '+ @schemaname+'.'+@tablename)

set @index=@index+1

END


  通常来说,需要看如下几个计数器(下面资料参考自http://www.sqlservercentral.com/articles/Miscellaneous/2634/):

  Memory: Pages/sec

  Memory: Available Bytes

  Network Interface: Bytes Total/Sec

  Physical Disk: % Disk time

  Physical Disk: Avg. Disk Queue Length

  Processor: % Processor Time

  System: Processor Queue Length

  SQL Server Buffer: Buffer Cache Hit Ratio

  SQL Server General: User Connections

  Memory: Pages/sec:最好不要大于5,否则有内存问题

  Memory: Available Bytes :这个可以望文生义,不解释

  Network Interface: Bytes Total/Sec :如果这个计数器下降的太快有可能是网络出现问题

  Physical Disk: Avg Disk Queue Length:每个物理盘的等待队列,大于2有可能是IO瓶颈问题

  Physical Disk: % Disk time: 读/写活动的百分比,不要大于90%,和上面的计数器一起可以显示IO瓶颈

  Processor: % Processor Time :CPU瓶颈,不要大于90%,大多数情况下,内存和IO瓶颈要更多

  System: Processor Queue Length :同样,和上面计数器一起找出IO瓶颈

  SQL Server Buffer: Buffer Cache Hit Ratio :缓存命中率,不要低于85%,否则考虑加内存

  SQL Server General:并发数,压测时快到某一瓶颈看看这个数字,可以作为基准(BaseLine)记下来

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值