SQL Server Performance Forum – Threads Archive
Hardware config for large transactions per second
Hello Group, Background==========
On of our current SQL 2000 servers encounter HDD (I/O) bottlenecks when it gets busy so we plan on upgrading it. The SQL server is being used to generate dynamic web content and all the queries are well tuned and tables indexed. However, when we get in excess of 500 transactions per second (1 transaction for each web visitor) then the web servers, which are connecting to the SQL server, being to queue requests. In order to resolve this we will upgrade the SQL server. Question
========
Is there some mathematical formula we can use to calculate the I/O capacity required in the new SQL server to reach 1,000 transactions per second? So far we have been advised to get 6 scsi drives which are 15,000 rpm. We will put them on 2 RAID controllers, 4 drives for OS with RAID 5 and 2 drives for Log files with RAID 0. Does anybody have a better suggestion? Thanks in advance.
When you get in excess of 500 transactions per second (which really doesn’t mean anything btw), what do your disk read and write queue lengths look like? How much IO capacity are you trying to push through the system now? Are the queue lengths happening on the data or log drives? Post the numbers for each drive, and tell us if it’s the current data or log drives. When you say 1000 t/sec, what kind of transactions are they? Is this an OLTP or OLAP system? What is the read vs write ratio? What do you have running on the system? There’s a lot more to consider than just…I need a system that will allow me to process x t/sec. If you can give us the information above, we can help you a lot better. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Are you driving benifits from connection pooling in IIS Server?
Is this an OLTP System or OLAP system?
What about the disk queue lengths for the system and buffer cache hit raito for SQL Server.
]]>