A good server is one that stays up and running at its peak performance level for the longest time possible. Whenever the performance falters, the whole service fails to provide the most optimal experience. For this reason, it is important to optimise it every now and then. But, it’s also just as important to know what holds the server down.
Before you do some SQL server performance troubleshooting, you have to know what causes a slowdown in a server. Commonly, these three things cause performance issues:
CXPacket Wait Type
Large SQL servers often run into this performance burden. The problem is that most people assume the issue gets resolved by simply changing the query’s or the server’s MAXDOP configuration to 1.
The good news is that solving the problem doesn’t really take much time and effort. Simple statistics maintenance and proper indexing go a long way in dealing with the CXPacket wait type conundrum. It goes to show that the MAXDOP change is best only as a last resort, because it could bog down the system’s performance without the administrator’s knowledge.
Memory pressure is not the same as poor disk performance, although many DBAs assume the problem as the latter even though the former is the culprit. The number of data caches available in a server can make the scenario confusing. Higher latency across a certain disk subsystem could become an issue, as well as a low page lifetime expectancy. These things, however, may not simply be a disk-related problem, as it could boil down to memory pressure.
TempDB PAGELATCH Contention
The TempDB could also be the main reason for a performance slowdown. This is most noticeable in longer PAGELATCH waits and poorer overall process performance whilst tempdb is in use. The best course of action once this becomes apparent is to check the Disk Activity tab of the system’s Performance Advisor. Reconfiguration of the tempdb could solve the issue.
A lot of things could go wrong in a system. It’s best to be certain about the root cause of the problem before adjusting or reconfiguring the system’s parts.