[转载]SQL压力测试用的语句和相关计数器

[转载]SQL压力测试用的语句和相关计数器 – CareySon – 博客园.

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


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)记下来

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏