For the last blog post of the year 2016 I chose something that has been bothering me a bit as of late. Over the past couple months I have come across a number of cases where, after migrating databases to a new server, the end users are reporting increasingly bad performance. What has been common to these situations is that the new server has more and faster CPU cores, more memory, faster disks and should offer better performance, not worse.
Now the first thing I usually do when troubleshooting performance issues, is to check what kind of hardware we have and how the SQL Server has been configured. This is a really simple step to start with: Run msinfo32 to get the hardware details and then query sys.configurations to see how the SQL Server is configured. In these cases, all of the SQL Server configurations were left to their default settings. The result: Systems running to dangerously low levels of available memory which leads to extensive paging of the memory to the disk, and some really wild-looking query parallelization issues.