Benchmarks for new server
-
What are the first things you do when you on someone SQL Server for the first time and it in production.
What benchmarks do you do to ensure it running and determine what bottlenecks are.
1. WaitStats
2. Check memory configuration
3. TempDB
4. Disk Locations MDF and LDF split on difference disks perferably SAN.
5. Can't do IO tests as on Production.
Any others ?
-
Ideally I would use perfmon for benchmarks, especially disk counters (avg disk read/write) and memory, CPU, Network utilisation.
-
There is always a bottleneck. You just have to decide if it's an issue on your system. Once you fix that bottleneck, or mitigate it, there will be another one.
You want to watch some of the things you've put up there, know what memory/CPU/disk usage is common for your application. You need to benchmark so you can understand if you can improve things. Know the # of transactions, the times, waitstats are good, probably a few more. I haven't done enough perf tuning on 2005 to give you a good list. Someone else, hopefully, will post one.
Then with a benchmark in hand, you can start to try and tune queries. look for relatively long running queries, check for missing indexes, look to make improvements where you can in the db. I start there because those issues can eat up hardware quickly.
If you feel the system gets tuned well from SQL, then is your hardware performing well by the disk throughput capacity? Is memory pressured? How hard does the CPU work? Find what hardware might be holding you back and then look to work on that.
-
I always run a standard series of disk IO tests using SQLIO with a new server. Fixing issues with disk IO are much easier before a server goes live in production. Also, the benchmark results should be saved so that you can compare to them later if you suspect IO problems.
Even if a server is already in production, it is worth scheduling an hour or so of downtime to run the SQLIO benchmark.
-
Micheal, sounds like a good article idea (hint, hint)
-
Michael Valentine Jones (3/28/2009)
I Also, the benchmark results should be saved so that you can compare to them later if you suspect IO problems.I completely agree with Michael you should always think of Creating a Baseline of your work load when you think the server is running normally, later you can compare the monitoring activity with this baseline and quickly identify the bottlenecks.
-
I also run predeployment tests with SQLIO, and later tests for comparison are always a good idea. But what if you use a SAN? An hour's downtime would need to be negotiated with ALL users of the SAN, otherwise a) your test results are not reliable and b) you could adversly affect performance of a production system. In recent tests with a new SAN I have managed to reduce I/O throughput of an unrelated server significantly just by saturating the SAN with random reads on my server.
-
I also run predeployment tests with SQLIO, and later tests for comparison are always a good idea.
In past i used the the sqlio.exe on all the disks on SAN and C: D: E: if local.
and also SAN Disk IO before going live.
Yes good idea to have these on a PRODUCTION and to set aside a few hours early morning or evening to get the benchnmarks is a good idea.
What IO tests do you perform.
This is for a LIVE production system sql server that have no benchmarks.
Cheers.
-
Thanks for link, yes i used the sqlio before.
i add that to my checks to start running in a "slow period" for benchmarks.
Also set up permon on disk/cpu etc and measure this through slow periods and busy times.
Cheers
-
TRACEY (3/28/2009)
Also set up permon on disk/cpu etc and measure this through slow periods and busy times.Yes, and also think of creating multiple baselines corresponding to different timings of the application.
-
Richard Fryar made an excellent point there. Remember that SANs are a cost-saving resource designed to optimise storage usage, not performance. In reality, using a SAN can be a like using a virtual machine : You're sharing resource (in this case disk I/O) and you have no control over how others are using that resource. A dedicated SAN in a database environment is another matter, but how do you know that 73 users aren't copying large files, backups, MP3s etc to shared SAN storage? Even if your SAN administrator intelligently allocates dedicated database space, you cannot guarantee that disk controller bandwidth is dedicated to your usage. As such, trying to baseline a SAN during "normal" working hours is a difficult proposition, as you can never guarantee dedicated database resource between your system and the storage medium.
Jon -
Well i hope if they have a SAN i can ask the network team for the results 🙂 everyone has documentation 😉
-
Which permon counter measures the volume of IO Per second that is being executed
Physical Disk
Disk Write Bytes/Sec
Disk Read Bytes/Sec
Divide the number of I/O Operations /Sec by number of disk drives
i.e I/Os per sec. per drive w/RAID 1 = (Disk Reads/sec + 2*Disk Writes /sec)/(nbr drives in volume)
Is this the way to determine the i/o 8k pages per second value.
-
Thanks just found out more too.
Raid 0 -- I/Os per disk = (reads + writes) / number of disks
Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2
Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks
Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks
So got the equations all determine.
Just checking it is the physical disk counter and not the logical disk counter.
Disk Reads/Sec
Disk Write / Sec
Next question:
Number of disks (Not SAN ) say C: D : E F this be 4 (for number of disks).
If the disks not on SAN how to determine what RAID this is (is this available through the disk management).
If on a SAN (is this number of luns?) for number of disks.
To get the number of IOPS that are possible - run the isoexe tool from ms as a guide.
Or read disk specification if available.
Phew i believe im getting there.
Thanks
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply