T-SQL Tuesday 170 – Learn Fast, fix over time

This months invitation for T-SQL tuesday came from Reitse Eskens (B | L | T).

TSQL Tuesday 170

The original invite can be found here.

I know Reitse quite well. He lives in the same in country, I visited one of his user groups, congratulated him on getting an MVP award, volunteerd together with him on two activities this year. I hope to continue this also this (2024) year.

His invite

Reitse’s question to the SQL Family is to share what we have learned from failed projects. It is a difficult question and I cannot think about one or more right now.

But what I did find out is that it is important to sometimes take that helicopter and take a view about a finished task, project or change.

Based on an Incident we got, and that was way back, an taskforce was setup to look at poor performance of SQL servers in the company. We looked at a lot of items. We had performance monitoring in place from SolarWinds. That showed huge delays on the storage. But that is not a small subject in an enterprise with at that time in this part of the organisation about 700 SQL servers. Most of these were virtualized.

The customer extended the system to max memory (256 GB) and max CPU (32 cores)

We talked to windows guys, network guys, storage guys, vendors and more. Lots and lots of information was parsed and used to evaluate in a broad team. We delivered the team a new SQL server. We restored their databases on the new server. Guys from Storage and Windows were monitoring it. Then running the same load on it. It was faster… about 16 times in most of the tasks…

For your information: this new server was build on a different VMWare host.

How could this happen. We saw much better performance. Less CPU Usage, less Harddisk queueing and less IO on the Fibre optics.

What we found:

  • Disks were formatted 4k block size while the optimal disk subsystem was set to 32K
    • This leads to high IO on the disks, but also to high CPU. Reason is that adding al those blocks together into one extend is taking up a lot of resources. Every 4k block is one IO on the disk and one CPU cycle. Adding another block is one extra IO and two CPU cycles (one for reading, one for combining)
  • The Host had 2 CPU’s of 2.3Ghz with 32 cores each. This is slow.
  • IO subsystem was running at 10GB at that time, but later in the fabric there were links of 1GB.
  • System had many added diskspace to the disk, so many different locations of the files which leads to fragmentation on the disk. During High Peak performance one CPU was more used than the others. Disks were spinning disks
  • The problem was not the database but its infra.

Resolutions:

  • The new server had 32K blocks (experiment). All new builds were updated to have 32K disks
  • The IO subsystem was upgraded to have full 10GB
  • We slimmed down the server to 8 cores
  • For test we created a disk with 8 mountpoints and reconfigured the database to use them. Another performance improvement…
  • We upgraded disks to use hot and archive storage types (translates to SSD and spinning disks, virtually smart combined into one disk)
  • we optimized some queries…

Conclusion: The windows team only looked at their installation from a generic point. After implementation of these findings we showed the benefits of a Special SQL build. It improved the performance. The storage guys looked at their disk performance. Nothing to see there. Obvious, because there was no queue at the storage array, but on a San Switch. After moving the fabrics to faster switches… the disk bottleneck became clear… The SQL platform was performing at its best. Could not be bette at that time. But it became better, with a financial benefit also, after we were able to convince the hardware team to buy different and more expensive hardware. Since then we are running SQL workloads on Dual CPU Platinum 8 core servers. These servers outperform the 32 core low speed with ease… Microsoft was not too happy with this… we needed only one third of the Enterprise licenses … which made our management happy also…

Current status: Of course.. these fixes could not be done in one simple step, but the full solution delivered value to all. Nowadays we are still using the same CPU types (only later versions), but with fabrics of 40gb and NVME disks. We check on disk status often. The mountpoints are not needed anymore at this moment becuase of the NVME.

My takeaways If you identify a performance problem, please involve everyone who knows a litle. Keep an helicopterview on the problems Dont solve immediately but investigate properly Make sure every piece of evidence is evaluated

Keep talking to other teams. Keep them updated on changes you do.