If you saw this graph of a SQL Server showing transactions/sec, what would you think?
This is a 24 hour snapshot of the SQLServerCentral database server. This is what it looks like most of the time. Can you guess what happens at 9pm my time each night?
If you guessed the newsletter is sent, you'd be correct. We send about 700,000 newsletters out each night, starting at 9:00pm MST and for 6 hours, we do a lot of work on these servers. If you check the network utilization, it shows a similar graph:
However the CPU isn't that heavily used.
That's quite a change from what it used to be. We got new servers, and a cluster, this past summer, and it was a relief as the previous machine was over 3 years old and was showing signs of its age, often having the CPU well above 60% and often above 80%. Comparing this graph with one of the old server would have shown my boss that the hardware was vastly improved. And worth the money as I would use this to back my claims that the website was accessible during the newsletter send. In the past, it wasn't.
It also tells me that we need to make sure that for planning purposes, we need to focus most on bandwidth and disk, not CPU or RAM. Why not RAM? What can you learn from this graph?
For a hint, look at the statistics on the right. This is for the last 30 days of activity on the server.
What about disk activity? It mirrors the first graph as well
What does this tell you? I'll let you debate and respond as to what you think this means for our current setup. If you have some ideas or thoughts, we'd love to hear them. Or if you have tuning recommendations, I'd be curious what you think of the performance of our cluster. We are starting to make a few changes to our servers, based on the SQL Monitor data, which you can see for yourself at monitor.red-gate.com. Take a look, browse around, and tell us what mistakes we're making.