2

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?

4 Answers 4

2

You can turn this behavior off with trace flag 830, for more information check this article on msdn

dbcc traceon(830, -1)

or as a start-up parameter if you want this turned on permanently: add /T830 as a service start-up parameter.

Seeing as you have such cheap disks & your running sql server 2008, take a look at turning on page compression for the whole database, you will have a small CPU overhead, but I'd say in your situation the pro's outweigh the cons.

0

You could also check the disk for fragmentation ? That might speed it up a bit.

(obviously, actually defragging the disk, rather than just checking it, will speed it up....)

0

Since you are running SQL Server 2008, I would suggest looking in to using Data Compression on your Datawarehouse tables (if indeed you are using a DW, which I assumed from your wording). It may be possible to place your fact table on the higher speed drive(s) using this tech.

0

Make sure you have excluded your database files from any antivirus checking, this can kill performance on large files .

You must log in to answer this question.