I am building a cube in SQL Server Analysis Services which is using the local SQL Server instance as its data source. The bulk of the data in the data source is a large fact table (~200GB) which is too large for the local hard drive, so I moved the .mdf and .ldf files for that database to an external USB hard drive. I want to keep these files on the external hard drive but store the processed cube on the local hard drive. My hope is/was that while it will make cube processing slow, the cube querying will still be fast when clients connect
And when I can process the cube, this works: the cube querying is still fast because the cube queries don't hit the slow external USB hard drive. The problem I am having is that often when I go to process the cube, I get errors like this:
"OLE DB error: OLE DB or ODBC error: Time-out occurred while waiting for buffer latch type 2 for page (1:1785), database ID 5.; 42000
Going to the event log, I see errors like these:
SQL Server has encountered 29351 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [U:\sqldata\X.mdf] in database [X] (5). The OS file handle is 0x0000000000000164. The offset of the latest long I/O is: 0x00002f4b664000
This seems to be happening because the USB hard drive is slow. I don't mind the queries needed for processing taking a long time, but it seems like SQL Server has a hard limit for I/O requests. Is there a way around this?