通过

SQL Server Sizing Resources

  • 项目

Recently, I was asked whether Microsoft had any SQL Server hardware sizing tools. The asker referred me to https://www.sizinglounge.com as an example of what he was looking for. (Sizing Lounge apparently allows you to select an app like SAP or Exchange, answer a couple of questions about things like concurrent user counts and desired CPU utilization levels, and receive a list of possible server configurations that are supposed to be able to handle the load.) This isn't the first time I've fielded this question, so I decided to put my answer in a blog post. 

When you're trying to size a server that will be running a well-known application -- say, SAP, or Exchange -- you have a chance of getting a recommendation that is reasonably precise. This is because the person or tool that is making the sizing recommendation can make well-educated guesses based on knowledge of the way that users generally use that app, and the app's typical transaction costs. But if you want non-app-specific guidance for an unknown or arbitrary database app, the problem gets much harder. This is because a single business transaction from one application (say, “log in”, or “submit purchase order”) could be orders of magnitude cheaper or more expensive than the exact same type of business transaction in a different app. In other words, there are limits to how precise application-independent sizing guidance can be for an unknown app when the inputs are nothing more than simple metrics like # of concurrent users, queries/second, or transactions/sec. 

 

Application-Specific Server Sizing

My first recommendation is to go to the application developer, not the RDBMS developer, for the most meaningful sizing guidance. I don't think that even Microsoft has explicit sizing guidance for every MS app that runs on SQL, but there are sizing tools or whitepapers that make hardware recommendations for many or most of our SQL apps. A few examples:

If you can't find explicit sizing guidelines for the application you'll be running on SQL, I'd try these alternatives, listed in no particular order:

  • Case studies from the ISV (case studies often include server hardware details and information about concurrent user counts, data volumes, etc)
  • Post a query to online forums dedicated to the app (for Microsoft apps you might try the TechNet forums)
  • Contact the app ISV directly

 

Application-Independent Server Sizing

You may come up empty-handed in your search for application-specific sizing guidance. If the application was developed in-house or if you're sizing a server for an app that doesn't even exist yet, you'll definitely be in this situation. Application-independent hardware sizing guides do exist. The first type of resource in this category may not be as simple to use as you might like, but the conclusions you'll come to will be more trustworthy. The first two whitepapers below take the approach of giving you the tools you need to identify the key characteristics of an existing application workload, then providing you with a process to translate that into a set of hardware requirements. The database sizing tool will give you a fairly accurate estimated database size, but to get it you must provide a lot of schema details. These are not simple processes, so don't expect to have a set of server specs in 30 seconds. But the complexity is inherent to the problem and can't be eliminated without sacrificing precision, so you can consider it time well spent. 

Finally, there are SQL sizing tools that will give you hardware recommendations for an unknown app with little to no time investment on your part.  These might provide a nice starting point, but I’d take their recommendations with a grain of salt.  As mentioned earlier, you can't estimate required hardware with any real precision when the app and the nature of its SQL workload are unknown variables, so be cautious about any recommendation that is made on the basis of a few vague metrics. 

Comments

  • Anonymous
    August 25, 2010
    Another page with links to some useful SQL sizing resources: sqlcoffee.com/tips0012.htm
  • Anonymous
    October 13, 2016
    The comment has been removed