1

This would be more of General approach question rather than too technical:

I have one of my application reach out to me wandering if we need to scale up SQL server in terms of CPU memory disk or CPU as they are planning to increase the volume by double the current one.

From the current volume, i have captures which shows CPU/memory and disk running without issues.

But how can i measure with confidence what all will change if there is increased in volume which might need a bump in capacity at my end?

Need your expertise how can i be pro-active and what all can i measure to make up a decision , atleast close to what can be expected with the growth in volume?

Yes i do have SQL server monitoring in place which stores performance data for last 15 days. Most of that data comes from performance counters and DMV's.

Update- Adding more data as i get to update- Avg 800 transactions/sec i day and 400 batch req/sec in a day followed by steady 40% cpu usage over the day, includes peak day. Disk response are well under 5 ms for both read/write. PLE throughout the day is over 10K.

Any help on how to approach this is greatly appreciated.I know answer is broad to this question, but some great ideas can really help me not only for this server but something similar in future also comes up , thanks!

Improve this question
3
  • 4
    Well if you do have a non-production environment, you could do some testing there prior to change the production. It is a really hard question cause it depends on so many factors... If your database is really nicelly done and you have very good indexes that covers the application query, you could not even see a change with more data. But if all the table are heaps and you are doing full scan all the time, then the impacts will be bigger. I would say it's nearly impossible to guess on the ressources you will need without having a deep understanding of the database structure and new data
    – Dominique Boucher
    Commented Mar 30, 2020 at 18:10
  • @DominiqueBoucher: Yes we do have but its no where close to prod.I will review the db structure but just updated question with over all capacity in terms of infra. Just looking how DBA's will approach this and not looking for exact answers. I know answer is broad to this question, but some great ideas can really help me not only for this server but something similar in future also comes up
    – BeginnerDBA
    Commented Mar 30, 2020 at 18:25
  • If you have historical metrics with the volumes from an initial low amount up to present then you could extrapolate that out to get a prediction of the required capacity and how much more volume the server can handle before it needs to be scaled up.
    – Martin Cairney
    Commented Mar 30, 2020 at 23:07

2 Answers 2

Reset to default
1

As others have said, there is no magic formula that will tell you how your database server will respond to a sudden "increase the volume by double the current one" (volume of what exactly, by the way).

The two things that can help you to get a general idea (neither of which you have, apparently):

  • Historical (over many months) data that show you the correlation between your server load, the volume of data, and the application workload (concurrent connections, user sessions, registered users, daily orders, or whatever makes sense in you case). "[P]erformance data for last 15 days" is completely useless.

  • Performance testing environment that's configured meaningfully similar to your production. In this day and age spinning up a virtualized test environment on one of the cloud providers and running it for a week costs peanuts, compared to the price you will pay for a production outage caused by unexpected performance problems. Performance burn-down tests should be a part of your release process.

If you start preparing both of these tomorrow, in half a year you will be able to answer your own question with some degree of confidence.

Improve this answer
1

Just perform a stress test of your application like:

  1. Start with the current load
  2. Gradually increase the load until it becomes anticipated (or even more)

If the application is a web-based one you can consider using a free load testing tool which supports HTTP protocol.

If you're able to collect the SQL queries which are being executed during normal usage of your application you can replay them with increased load using a load testing tool which supports ODBC or JDBC protocols.

During load test execution monitor SQL server performance counters and correlate increasing load with these counters. It should give you an overview what happens when the load is doubled

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.