SQL Server Performance Forum – Threads Archive
TPS
How could i measure a database server TPS rate? Also is it possible to do a Read vs write comparison against a database? If yes, could somebody please share? Thanks,Cali
You can get the TPS using PERFMON (Sysmon)….Check "SQL Server Database Object:
Transactions/Sec" counter… Check the "I/O Bottlenecks" topic from the following article…
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EFRAE
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Thank you mohammedU. But how could i get a comparison between read vs write against a production database? Thanks,
cali
the counter: SQL Server<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />QL Statistics->Batch Requests/sec<br />is the total (read + write) requests to SQL Server<br />the counters MohammedU mentioned is the writes (but 1 call can generate more than 1 transaction if you neglected to enclose write procs with BEGIN TRAN/COMMIT TRAN)<br /><br />so now you know the write, and the total<br />you need to figure out how to get the read<br /><br />
What you are looking for is the ratio of disk reads to writes on your DB correct? You can use these counters to determine this Physical Disk Object: % Disk Read Time and Physical Disk Object: % Write Time, to also get a feel of I/O generated by SQL Server components SQL Server:Buffer Manager<img src=’/community/emoticons/emotion-4.gif’ alt=’
‘ />age reads/sec, SQL Server:Buffer Manager<img src=’/community/emoticons/emotion-4.gif’ alt=’‘ />age writes/sec and PhysicalDisk: avg. disk queue length this should always be under 2.<br /><br />Raulie<br /><br /><br /><br />
YOu can get read and write information through sql trace/profiler but it will be expesive…it is not advisable to run against hight OLTP servers… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>