Have you ever noticed that servers with small databases never have performance problems? That’s because SQL Server is such a robust and capable product that it can mask database design and query implementation problems – up to a point.
The point where hidden problems become visible is typically when a database reaches a certain size. This tipping point will vary from server to server, from database to database, from query to query, from application to application. But the tipping points are there, in every production environment.
Because all database and application designs, no matter how scalable, have paradigm limits. Designers may have been excellent at anticipating the paradigm limits of the the database they designed to support their application, but if a database grows large enough, users will eventually experience performance problems.
I once read an article that, at the time, stated that Google had had to redesign and rescale its database designs and implementations, along with its applications, 3 times over a period of years to accommodate the massive data growth its databases had experienced. The company literally outgrew every design paradigm it created.
But database and/or application redesign is very expensive and not always an option, so other methods of dealing with large database size should be explored first.
I have considerable experience solving problems associated with maintaining and accessing large and very large databases. I have yet to find a situation for which I could not provide good solutions to those problems.