June 5, 2012 at 11:12 am
Hi, I m trying to analyze performance metrics on one of our servers. Basically we are planning to roll out this box to production. Before we roll out, Just want to make sure it's performance is good. I have these counters below, Are these enough to say the server is good at performance? Basically we have ETL Packages and it is subscriber for over 200 servers (replication). Can someone help me on this!
Server : SQL Server 2008 R2, Windows 2008 R2
Memory : 164000 MB RAM
Processor : Xeon 2.67GHz(24 CPUs)
Memory :
Available MBytes 38,047
page reads/sec 0.192
page writes/sec 0.000
Physical Disk:
avg.disk queue length 1.055
avg.Disk read queue length 0.627
avg.Disk sec/Read 0.001
avg.Disk sec/Write 0.000
avg. disk write queue lenght 0.427
Disk Read Bytes/Sec 6,179,037
Disk Reads/sec 119.27
Disk Write Bytes/Sec 21,397,111.759
Disk Writes/Sec 1,417.796
Processor
% Processor Time 34.283
Thank you !
June 5, 2012 at 11:41 am
I would also include "Network Interface\Output Queue Length" since this is subscribed to 200 servers.
Due to network bottleneck, sometimes server cannot use other resources (CPU/MEMORY/IO) as expected and can lead to overall slowness.
June 5, 2012 at 12:29 pm
There isn't really enough information here for anyone to use to judge whether or not this looks good, bad, or otherwise. In order to even begin to do so, you would have to explain the following:
1. Expectations for performance of the server in production and how it will be used.
2. The workload running against the server during your test and comparison of that workload to what you expect in production.
3. Are the counter values you gave averages over time? If so, how much time, and what were the min and max values observed?
That doesn't even start to go into individual query performance, file-level IO, waits, etc...
June 5, 2012 at 2:00 pm
Thanks for the reply.
We applied the similar load as in production, the load will be exactly same after it goes to production.
I collected the above data in for 20 hours, It is scheduled to run for 20 hours. We already started doing waits and query performance analyzations. Meanwhile I m trying to figure out these coutners.
here are the max and min values:
Memory :
Available MBytes 38,047 Min : 38,020 Max : 38,060
page reads/sec 0.192 Min : 0.001 Max : 0.983
page writes/sec 0.000 Min : 0.000 Max : 0.000
Physical Disk:
avg.disk queue length 1.055 Min : 0.213 Max : 314.245
avg.Disk read queue length 0.627 Min : 0.001 Max : 313.245
avg.Disk sec/Read 0.001 Min : 0.000 Max : 0.032
avg.Disk sec/Write 0.000 Min : 0.000 Max : 0.001
avg. disk write queue lenght 0.427 Min : 0.205 Max : 2.423
Disk Read Bytes/Sec 6,179,037 Min : 31,267.862 Max : 178,044,895
Disk Reads/sec 119.27 Min : 1.567 Max : 13,372.448
Disk Write Bytes/Sec 21,397,111.759 Min : 10,092,027 Max: 64,545,927
Disk Writes/Sec 1,417.796 Min : 660.57 Max : 2,401.666
Processor
% Processor Time 34.283 Min : 17.39 Max : 69.37
June 5, 2012 at 2:57 pm
Chapter 1 (and several others): http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 5, 2012 at 3:00 pm
I'm sure you're not going to like what I'll say...
"Fast" and "Slow", as well as "Good" and "Bad" are relative measures.
They must be related to a baseline, which is a snapshot of the server's performance under the typical load.
They must also be related to the stakeholders' performance expectations. A query running in 100 ms could be ok for me and a disaster for you.
If you want to monitor SQL Server performance, use SQL Server's performance counters instead of OS generic counters. They will give you a better picture.
If you want to investigate performance on your instance, I highly suggest taking a look at this book. It's concise and very easy to follow.
Hope this helps
Gianluca
-- Gianluca Sartori
June 5, 2012 at 3:02 pm
GilaMonster (6/5/2012)
Chapter 1 (and several others): http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Dang! Two recommendations in less than three minutes.
If I had $.01 for every time I recommended this book, I would be a millionaire!
-- Gianluca Sartori
June 5, 2012 at 3:18 pm
Gianluca Sartori (6/5/2012)
GilaMonster (6/5/2012)
Chapter 1 (and several others): http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/Dang! Two recommendations in less than three minutes.
If I had $.01 for every time I recommended this book, I would be a millionaire!
I should have negotiated royalties. 🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 5, 2012 at 3:28 pm
GilaMonster (6/5/2012)
Gianluca Sartori (6/5/2012)
GilaMonster (6/5/2012)
Chapter 1 (and several others): http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/Dang! Two recommendations in less than three minutes.
If I had $.01 for every time I recommended this book, I would be a millionaire!
I should have negotiated royalties. 🙂
It's never too late... 🙂
-- Gianluca Sartori
June 6, 2012 at 11:28 am
Thank you very much for your inputs. The provided links are the real place to start with .
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply