Sql-server – Backup up database to compressed local folder – what will be the performance implication

Great question, and I did a session about this at TechEd a few years ago called Building the Fastest SQL Servers:

https://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI328

In it, I explain that for data warehouses, you need storage that can provide data fast enough for SQL Server to consume it. Microsoft built a great series of white papers called the Fast Track Data Warehouse Reference Architecture that goes into hardware details, but the basic idea is that your storage needs to be able to provide 200-300MB/sec sequential read performance, per CPU core, in order to keep the CPUs busy.

The more of your data that you can cache in memory, the slower storage you can get away with. But you've got less memory than required to cache the fact tables that you're dealing with, so storage speed becomes very important.

Here's your next steps:

• Watch that video
• Test your storage with CrystalDiskMark (Here's how)
• With 4 cores, you'll want at least 800MB/sec of sequential read throughput
• If you don't have that, consider adding memory until the pain goes away (and caching the entire database in RAM isn't unthinkable)

Say you've got a 200GB database that you're dealing with, and you can't get enough storage throughput to keep your cores busy. It's not unthinkable to need not just 200GB of RAM, but even more - because after all, SSIS and SSAS really want to do their work in memory, so you have to have the engine's data available, plus work space for SSIS and SSAS.

This is also why people try to separate out SSIS and SSAS onto different VMs - they all need memory simultaneously.

The best answer I can come up with is that Sql really doesn't have any room in the buffer pool to give up. Meaning, we have too many queries running, or the queries running have plans that are taking up too much space. This link was somewhat helpful No Free Buffers. My solution at this point is to evaluate the queries that were running when the error occurs, so see if I can tune them.