:::: MENU ::::

Data and Temporary File Locations for SQL Server Analysis Services

  • May 13 / 2009
  • 0
Analysis Services SSAS, dbDigger

Data and Temporary File Locations for SQL Server Analysis Services

An Analysis Services instance has a Data folder and a Temporary folder. Analysis Services uses the Data folder to store the multidimensional structures for all the objects defined on the Analysis Services instance. It uses the Temporary folder to supplement the memory allocated to each process buffer when a process buffer is too small for the aggregations being processed. The default location for both of these folders is C:Program FilesMicrosoft Analysis Services Data. You can change the location for either or both during setup or after installation. To change the location after setup, right-click the Analysis server object in Analysis Manager, and then click Properties. You can also use the sample script provided in Appendix D, “Sample Script for Changing the Data Folder Location, to change the Data folder programmatically. If you use virus-scanning software on the Analysis Services computer, you should disable scanning of the Analysis Services Data, Temporary, and Bin folders.
You should place the Data folder on its own RAID array; RAID 10 or RAID 1 + 0 provides the best performance but RAID 5 is frequently fast enough for many Analysis Services installations. The main activity of Analysis Services is reading data from the files in the Data folder in response to user queries, not writing to files in the Data folder. Once you determine the amount of space required for the data, index, and aggregation
structures, you should allocate approximately double that amount of disk space to allow sufficient space to enable you to refresh the data and hold shadow files during processing.
Note Because the Data folder stores security files that control end users’ access to Analysis Services objects, you must secure the Data folder against unauthorized access. Only members of the OLAP Administrators group and the Administrators group should have access to the Data folder. If you move the Data folder location after installation, you must configure these security settings manually.
You should place the Temporary folder, if it is actually used, on a RAID array that yields excellent write performance and that is on a different physical drive than the Data folder. Consider using RAID 0, 1, 0+1, or 10 depending on your budget requirements and amount of use. However, for best performance, it is more important to allocate a sufficiently large process buffer to obviate the need for temporary files during processing. If processing requires temporary files, the algorithm is an order of magnitude slower than if the process buffer was large enough to perform the processing entirely in memory. If you find that the files in the Temporary folder structure are used extensively and you cannot eliminate their use, you can add a second Temporary file folder on a different physical drive by adding the TempDirectory2 registry key
(HKEY_LOCAL_MACHINESOFTWAREMicrosoftOLAP ServerCurrent Version) and specifying a location on a separate physical drive for the second Temporary folder. When you must use temporary files, using two Temporary folders increases processing performance because the data in one Temporary folder is sequentially read, merged with new segment data, and then written to the second Temporary folder (in 64- kilobyte segments). The data in the second Temporary folder is then read, merged with new segment data, and written to the first Temporary folder. This process continues until the calculation of aggregations is complete.

Consult us to explore the Databases. Contact us