Working with an SQL server can cause headaches for newcomers, as the first time you encounter common problems, they can feel insurmountable.
Because of this it makes sense to go in forearmed with knowledge of the issues you will likely face, so that you are better prepared to tackle them.
To that end, here are some of the snarl-ups that anyone developing a server based on SQL will stumble across.
Blocking & deadlocks
Blocking and deadlocking are both aspects of an SQL server which are essential to preserving data integrity, yet also likely to cause sluggish performance in their most problematic forms.
Blocking arises when 2 processes need access to the same resource simultaneously, and a lock-based system determines which gets priority, leaving the other waiting until the resource is freed up. Basic blocking is entirely normal and expected, but it can become more of a concern if it results in excessive wait times, as explained here https://www.sentryone.com/sql-server/sql-server-blocking.
Deadlocks are a bigger dilemma, since they occur when processes occupy server resources and cannot make the leap to another one because they are trying to effectively switch places. In this case, one will be selected for termination, which is why more of your troubleshooting and query optimization efforts should be focused on this.
Storage limitations
Server storage needs to be effectively implemented and carefully monitored to make sure that it is behaving as it should from moment to moment. However, problems can also arise if you fail to plan for how your storage needs will evolve over time, because of course the resources you start off with will not be adequate forever.
This is why it is important to track usage over time and forecast ahead to determine the point at which it will be sensible to upgrade your server so that the capacity does not unexpectedly become a limiting factor.
There are of course other hardware-based limits which must be considered, such as memory allocation and CPU utilization. However, storage should definitely be put at the top of the agenda, as problems here can be a real hindrance to performance and data integrity.
TempDB issues
TempDB is another crucial component of an SQL server, acting as the temporary repository for data while it is in use and only persisting for as long as a given session.
If TempDB is not configured correctly, it could hobble server performance and bring entire instances to their knees.
One of the common complications which arises with regards to TempDB is that information is not deleted after it has been leveraged by whatever process needed it in the moment, and thus this database fills up and has no more room for other temporary entries.
While you may not always be aware of what is going on with TempDB, it is certainly an aspect worth keeping tabs on because of the serious ramifications of problems going unchecked. The same goes for all of the issues mentioned, and is a sign that consistent, persistent monitoring is the best solution.