August 2, 2011 at 8:32 am
We have a database set up which is about 200 Gb of data.
With roughly 70 users, with rarely more than 5 conncurrent.
The work load during the day is mostly parameter based stored procs.
It is a data warehouse, with no data entry or transactional type of applications connecting to this instance.
It is set up on a VM, the particular set of VM hardware is dedicated to SQL servers, of which there are 25-30. Most of these other DBs are transactional/data entry types of DBs for applications.
What level of disk read/write would be at a middle ground performance level? Out of curiosity I had run a tool which looked at disk performance and the results seem exceptionally low. The tool reported out Reads: 46 MB/s and Writes: 52 MB/s
Are these results as far below average or reasonable as I think they are?
(As a test, I ran the tool on my desktop, and ended with ~130 MB/s for both Read and Write)
It could also be this sort of measurement is unrelated to either SQL performance, or VM. If so, what metric is important for disk I/O?
August 4, 2011 at 5:44 am
I generally don't worry about how much I'm reading or writing on a database, but whether or not the reads or writes are slowing down. Instead of focusing on the volume, take a look at the average disk queue. Also, look at the wait states on the server. Knowing what is causing stuff to slow down is a better metric than just volume. Volume is too likely to change one day to the next.
Here's a good article on performance tuning with wait states.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 4, 2011 at 8:44 am
Great!
Thanks Grant.
After years of arguing we finally are having a real go through effort at addressing all the various DB issues we have, and to arrive at standards and so on, all the things that should have been done long long ago in a gal.. wait not quite that far. I am attempting to get a better feel for the I/O needs and bottlenecks. Articles are just what I need.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply