Value Added
SQL Server can do a whole lot of things, aside from return the results of your queries. The thing is, the more of those you add into the mix the tougher it becomes to scale, and maintain reliable performance.
Of course, there are other design considerations that introduce the same kind of complexity:
- Lots of tables in your schema leads to more joins
- Lots of nested views lead to more choice for the optimizer
- Lots of databases lead to cross-database joins, which can lead to tricky issues if they’re in different compat levels, etc.
- Lots of functions and triggers that have entire books of business logic in them make everything go to hell
But beyond that, consider some more internal design choices:
- Change Tracking
- Change Data Capture
- Auditing
- Temporal Tables
Now you’re asking SQL Server to log a bunch of stuff every time you do something in your database. You have the action, and then writing that action down.
On top of that, you’re also opening yourself up to more queries because people are going to want to access and report on that data. This isn’t even touching all the additional internal monitoring that goes into SQL Server to track everything going on in there.
Observer overhead is a real thing.
Now let’s add in data protection and synchronization
- Availability Groups
- Replication (God forgive me)
I’m leaving Log Shipping out of the equation because it doesn’t really add much complexity on its own. You may add complexity do it by needing to copy files to strange places and choosing to report off shipped databases. I’m also leaving Failover Clusters out, because there’s one set of data that sits in one place. Only the SQL Server instance moves between nodes, unless you decide to span Failover Clusters and synchronize the data using your SAN or something. That stuff usually has a big enough price tag on it to dissuade most folks from tramping down that Path Of Misery©.
Both Availability Groups and Replication can make life miserable. I work with some very nice people who can’t create indexes unless they switch their Availability Groups to asynchronous mode first.
I also work with some very nice people who use Replication and don’t get much sleep at night. Me? I won’t touch the stuff.
The point, though, is that if you decide to have SQL Server start doing a whole lot of extra stuff every time you run a query, or make your queries really complicated to come up with an execution plan for, performance might suffer.
It’s up to you to make sure that you plan accordingly for adding in new features.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.