Common performance issue patterns
So far, we've seen how SQL Server works internally and which tools we can use for monitoring. We will find many areas that can be optimized on SQL Server. We can work with memory, physical disks, or CPUs, but we should always start our performance tuning work with the most basic thing – query response times. This section provides a real-life example that shows us how to explore response issues, as well as how to save system resources with just a couple of simple steps.
No real-time scenario has just one potential cause. Hence, there is not just one way to resolve an issue. In many cases, administrators try to resolve every issue with more system resources, but this approach is very expensive, and we do not have a bottomless pool of system resources (and, by the way, most performance bottlenecks are NOT caused by insufficient resources). That is why we should find the root cause of the performance issue and try to resolve it using resource...