Clay Lenhart’s Blog

Here is an article on moving SSAS databases. The only thing to add is to be sure to set the file permissions on the folder.

http://www.ssas-info.com/ssas_faq/ssas_management_faq/q_how_do_i_move_analysis_services_database_to_a_different_folder.html

This blog entry has a good description of the pros (and some cons) of surrogate keys:

http://rapidapplicationdevelopment.blogspot.com/2007/08/in-case-youre-new-to-series-ive.html

I recently ran into a problem where the list of values in a filter were missing for a dimension. It was due to having a fact relationship from the dimension to a very small fact table. In fact, the table was empty. I resolved it by changing the relationship to a regular relationship. Sure it will be less efficient, however the fact table is expected to be very small.

It is difficult to find information on how different RAID levels perform. I did some performance testing with 14 SCSI disks in a SAN, and came up with the following conclusions (heavy dose of salt needed):

1. More disks == faster array, but doubling the number of disks does not double the performance. I found that to double the performance of RAID 5 w/ 3 disks, the array needed 10 disks.
2. Sequential reading/writing is faster than random reading/writing.
3. When reading, the RAID level does not matter much.
4. When writing, the RAID level is very important. RAID 1 (or RAID 10 depending on the number of disks) is the fastest by far, then RAID 5 and then RAID 6.
5. Server SCSI is faster than a SAN
6. A SAN is faster than my laptop.

SQL Server notes:

1. OLTP databases generally have random reading and writing.
2. OLAP databases generally have sequential reading and writing.
3. When committing a transaction, the log must write to disk, but the MDF does not need to write to disk. So the write speed of the database it dependant on the write speed of the LDF file. This is why it is common to have the MDF on RAID 5 and the LDF on RAID 1 (or RAID 10).
4. SQL Server recommends RAID 5 for read-only databases. I assume it is because you'll get the most disk space compared to other RAID levels.
5. Since sequential reading and writing is faster than random reading and writing, and since you need a lot of disks to double the performance of an array, it can be faster to isolate arrays for certain tasks, rather than combining all the disks into a large fast array. (I hope you took that heavy dose of salt)

More Information:

• Physical Database Storage Design http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
• SQLIOSim http://support.microsoft.com/kb/231619
• SQLIO http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en
• Aligning sectors http://technet.microsoft.com/en-us/library/0e24eb22-fbd5-4536-9cb4-2bd8e98806e7.aspx
• Predeployment I/O Best Practices http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

Graphs:

(I don't show MB/s above 150 MB/s because the 2Gb/s cable was saturated at 160 MB/s).

The RAID level matters when writing:

The RAID level doesn't matter when reading:

The RAID level matters when writing: