Tom Mathews
Microsoft Corporation

November 2004

Summary: This paper describes the registry entries that control the behavior of the Microsoft® SQL Server™ 2000 Analysis Services server, and how they can be used to optimize performance during OLAP and data mining operations. (48 printed pages)

Table of Contents

Introduction
Registry Entries
     ActiveDirectoryEnabled
     AgentCacheSize
     AuditEvents
     BackgroundInterval
     BuildIndexThreshold
     CleanerInterval
     CompareCaseNotSensitiveStringFlags
     CommitLockTimeout
     CompareCaseSensitiveStringFlags
     DataCompressionSettings
     DataPlacementOptimization
     DatasetLockTimeout
     DefaultSendTimeout
     DimSecFlavor
     DiskCachePageSize
     DiskCacheSize
     ExcludeMachineAdminFromOLAPAdmin
     ForceCommitTimeout
     FastIOCS
     HighMemoryLimit
     HugeLevelThreshold
     InitWorkerThreads
     IOSectorSize
     IOSparsityControl
     LazyInterval
     LFHHeap
     LinkSyncInterval
     Locale
     LoggerInterval
     LowMemoryLimit
     MaxOLEDBTimeout
     MDXCompatibilityValue
     MDXUniqueNames
     MiningPersistenceFormat
     OLEDBPoolExpiration
     OpenFilesLimit
     PagePoolInitSize
     PoolProcessThreads
     PoolWorkerThreads
     ProcessPoolExpiration
     ProcessReadAheadSize
     ProcessReadSegmentSize
     ProcessRecordsReportGranularity
     ProcessThreads
     ProtocolReceiveTimeout
     ProxyServer
     QueryLogConnectionString
     QueryLogSampling
     ReadAheadBufferSize
     RemoteQueryLogConnectionString
     ROLAPDimProcessingEffort
     RootDir
     SQLCompatibilityValue
     SocketsUseSelect
     SocketsBufferSize
     SocketsUseBufferForSend
     SocketsEnableNagle
     SSFastLoadOptions
     TempDirectory
     TempDirectory2
     TraceLogString
     UnbufferedThreshold
     VersionNum
     VLDMThreshold
     WorkerThreads
Conclusion
Appendix

Introduction

The Analysis server is the server component of Microsoft® SQL Server™ 2000 Analysis Services. It is designed specifically to create and maintain multidimensional data structures and to provide multidimensional data in response to client queries. This release introduces data mining, which integrates significant data analysis and prediction capabilities into Analysis Services. The service that is associated with Analysis server is the MSSQLServerOLAPService service.

Many of the values can be modified to improve server performance; however, modification of the values can also result in severely degraded performance. Keep in mind that these values should always be tested on a non-production server.

Although the Analysis server is configured to perform well in the broadest possible range of situations, there are circumstances in which the default settings are not optimal. This paper describes the various registry entries and their impact on Analysis server.

Use the registry editor to modify many of these entries (Analysis Manager does not expose all of the entries). You may also need to edit the registry for the entries that were not installed to the registry during the setup process. Several entries cannot be set to their maximum value through Analysis Manager; this can be avoided by modifying by the registry directly.

For more information about Analysis Services and performance tuning, see the Microsoft SQL Server 2000 Resource Kit.

Registry Entries

These registry entries can be found in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\CurrentVersion. Several entries in the registry are not documented. Changing the undocumented registry entries is not recommended. If the registry key has been documented, but is not installed in the registry during the setup process, you must create the entry manually. To do this, follow these steps:

1. Run REGEDIT.EXE.
2. Expand HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\CurrentVersion.
3. If the entry is a string (in this document, indicated by the text "Not applicable" under the "Minimum Value" sections for the entry), on the Edit menu, point to New, and then click String Value. If the entry is numeric, on the Edit menu, point to New, and then click DWORD Value.
4. Type the entry name and click OK.
5. Double-click the newly created entry.
6. Type the value and click OK.

Caution   Many of these registry entries do not employ bounds checking, and modification of their values can lead to unpredictable behavior. Always test new entry values in a test environment before implementing them on a production server.

The "Default Value" section for each entry indicates the default value for the entry on a new installation. Unless stated otherwise, this is also the value used by the server if the entry is missing from the registry.

Several entries are bit mask entries. The value to which an entry has been set can be determined by performing a logical OR operation with the chosen values. For example, if you choose the values 1, 2, and 4, the value that is entered into the entry is 7. Conversely, determining the values to which a bit mask entry has been set can be done with a logical AND operation.

Several entries make references to pagefile size. The pagefile is a file that the operating system manages to improve performance by swapping rarely-used pages of memory to the disk. You can determine the pagefile size by right-clicking My Computer and selecting Properties. On the Advanced tab, click Performance Options. The size is indicated under Virtual Memory.

ActiveDirectoryEnabled

The ActiveDirectoryEnabled entry indicates whether the server has registered itself in Microsoft Active Directory™.

Minimum Value

0x00000000 (0)

Maximum Value

0x00000001 (1)

Default Value

0x00000000 (0)

Remarks

In Analysis Manager, you can modify this entry in the Properties dialog box. Right-click the server, choose Properties, and then click the Active Directory tab.

The ActiveDirectoryEnabled entry is read from the registry only when the server starts. You can use Analysis Manager to modify this entry.

The following table lists the available values and describes the server behavior.

AgentCacheSize

The AgentCacheSize entry controls the percentage of total physical memory that can be allocated to caches utilized by the server to perform certain queries.

Minimum Value

0x00000000 (0)

Maximum Value

The total physical memory of the server.

Default Value

0x0000000a (10)

Remarks

Because more than one of these caches can be allocated at the same time, it is recommended that you keep this entry value to a small percentage.

If the AgentCacheSize entry value is 0, the agent cache size is set to the size of physical memory. If this entry is from 1 through 99, the server allocates that percentage of physical memory to each agent cache. If this entry value is set to 100 or more, the agent cache can use up to the specified amount of memory, in kilobytes (KB).

The AgentCacheSize entry is read from the registry only when the server starts.

AuditEvents

The AuditEvent entry is a bit mask that determines how detailed the event logging to the Microsoft Windows NT® Event Log is.

Minimum Value

See "Remarks."

Maximum Value

See "Remarks."

Default Value

0x00000000D (13)

If this entry is missing from the registry, the default value is 0x000000001 (1).

Remarks

The AuditEvents entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry.

The following table lists available mask values and describes server behavior.

BackgroundInterval

The Analysis server uses the BackgroundInterval entry to determine the frequency at which background operations are performed.

Minimum Value

0x000000000 (0)

Maximum Value

0x418937 (4294967)

Default Value

0x00000001e (30)

Remarks

These background activities include cleaning the server caches, logging queries, performing lazy processing, and reading many values from the registry. The value is the number of seconds between processing periods.

The BackgroundInterval entry is updated from the registry on a regular basis, the interval of which is defined by this entry.

Note   If you set this entry to a high number and the server reads it into memory, it waits the specified time before reading the entry again. For example, this entry is set to 3600 and the server reads it into memory. If you immediately change the entry back to 30, the server still waits one hour before reading the new value.

BuildIndexThreshold

The BuildIndexThreshold entry represents the number of records within an aggregation below which an index is not created.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x00000400 (1024)

Remarks

The BuildIndexThreshold entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry.

CleanerInterval

The server behaves exactly as if the value in the CleanerInterval entry was contained within the BackgroundInterval entry.

This entry is not installed in the registry during the setup process.

Minimum Value

See "BackgroundInterval."

Maximum Value

See "BackgroundInterval."

Default Value

See "BackgroundInterval."

Remarks

If specified, the CleanerInterval entry overrides the value in the BackgroundInterval entry.

The CleanerInterval entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry.

CompareCaseNotSensitiveStringFlags

The CompareCaseNotSensitiveStringFlags entry is a bit mask that is used to determine how to perform case-insensitive string comparisons and sort order.

Minimum Value

See "Remarks."

Maximum Value

See "Remarks."

Default Value

0x00011001 (69633)

If this entry is missing from the registry, the default value is 0x00000001 (1).

Remarks

This entry controls how case-insensitive string comparisons are made in character sets that do not support uppercase and lowercase characters, such as Katakana (for Japanese) and Hindi.

The client application can temporarily override the entry for case-insensitive string comparisons by setting an identical registry entry on the client machine (affecting all connections made from that machine), or by setting the CompareCaseNotSensitiveStringFlags property in the connection string (affecting only that connection). PivotTable® Service can have only one value for this property for each process.

The CompareCaseNotSensitiveStringFlags entry is read from the registry only when the server starts. You can use Analysis Manager to modify this entry if the user currently logged on is running Microsoft Windows® with a Japanese locale.

The following table lists available mask values and describes server behavior.

For more information about comparing strings in OLE DB, search on "CompareString" in the Platform SDK section of the MSDN Library.

CommitLockTimeout

The CommitLockTimeout entry controls the ability of the Analysis Services server to cancel any pending-for-completion (uncommitted) DSO operations.

This entry is not installed in the registry during the setup process.

This entry was added for SP4.

Minimum Value

0x000000000 (0)

Maximum Value

0x418937 (4294967)

Default Value

No default value is provided.

Remarks

The value of this entry indicates how long, in seconds, a running DSO operation is going to wait before the operation will timeout and cancel itself.

If the CommitLockTimeout entry does not exist, the server will not try to stop a DSO operation. Each DSO operation will wait indefinitely to obtain a write lock and complete itself.

CompareCaseSensitiveStringFlags

The CompareCaseSensitiveStringFlags entry is a bit mask that is used to determine how to perform case-sensitive string comparisons and sort order.

Minimum Value

See "Remarks."

Maximum Value

See "Remarks."

Default Value

0x00000000 (0)

If this entry is missing from the registry, the default value is 0x00000001 (1).

Remarks

This entry controls how case-sensitive string comparisons are made in character sets that do not support uppercase and lowercase characters, such as Katakana (for Japanese) and Hindi. The default is the value of the CompareCaseSensitiveStringFlags entry on the client computer.

For information on the usage of this entry and the values that it can be set to, see CompareCaseNotSensitiveStringFlags.

DataCompressionSettings

The DataCompressionSettings entry is a bit mask that is used to determine the level of compression on the server, as well as index creation and usage directives.

Minimum Value

See "Remarks."

Maximum Value

See "Remarks."

Default Value

0x44000011 (1140850705)

Remarks

Analysis Services can use either the bitmap or traditional index to optimize query response. Although the bitmap index generally performs better than the traditional index, there are some circumstances in which the traditional index performs better than the bitmap index. If you decide to use traditional indexes, keep in mind that they are slower to create than binary indexes.

Caution   The MAP_MEASURES_COMPRESSION and MAP_MEASURES_COMPRESS_EXACTLY flags instruct the server to compress the measure values. This compression can introduce subtle rounding errors when working on numbers with many digits, as defined in the table below. Turning off compression results in a precision of 15 digits.

Removing the compression increases stability in rare circumstances, but drastically decreases performance. For troubleshooting purposes, you can turn off MAP_MEASURES_COMPRESSION and MAP_MEASURES_COMPRESS_EXACTLY.

To use the MAP_MEASURES_COMPRESS_EXACTLY flag, you must also use the MAP_MEASURES_COMPRESSION flag. The MAP_MEASURES_COMPRESS_EXACTLY flag cannot be used alone.

The DataCompressionSettings entry is read from the registry only when the server starts.

The following table lists available mask values and describes server behavior.

DataPlacementOptimization

This entry defines the level of clustering used by the server to create a clustered index.

Minimum Value

See "Remarks."

Maximum Value

See "Remarks."

Default Value

0x00000001 (1)

Remarks

The DataPlacementOptimization entry is read from the registry only when the server starts.

The following table lists the available constant values and describes the server behavior for each value.

DatasetLockTimeout

The DatasetLockTimeout entry is the timeout, in milliseconds, used when adding to the in-memory aggregation cache.

This entry is not installed in the registry during the setup process.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0xFFFFFFFF (4294967295)

Remarks

When a query executes, the server puts the answers into the in-memory aggregation cache. If a lock cannot be obtained within the DatasetLockTimeout entry, an error occurs.

DatasetLockTimeout is read from the registry only when the server starts.

DefaultSendTimeout

The DefaultSendTimeout entry identifies, in seconds, how long the server allows itself to be blocked by a client read request.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x0000003c (60)

Remarks

After the time-out expires, the connection to the client is closed. This setting also applies indirectly to HTTP connections, because Internet Information Server bases the timeout of Web clients from this setting.

If the DefaultSendTimeout entry is set to 0, the server does not time out connections.

The DefaultSendTimeout entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry.

DimSecFlavor

The DimSecFlavor entry controls how the Analysis Services server creates a dimension replica.

This entry is not installed in the registry during the setup process.

This entry was added for SP4.

Minimum Value

0x000000000 (0)

Maximum Value

0x000000001 (1)

Default Value

If this entry is missing from the registry, the default value is 0x000000000 (0).

Remarks

If this entry is set to 0, the Analysis Services server will first attempt to find an existing best replica of the dimension. A best replica can be used from another user that is a member of the same roles, as long as dynamic security is not implemented.

If this entry is set to 1, the Analysis Services server will create one replica per user per roles combination, regardless if the user is has the same exact replica as another user. This is useful for situations when dynamic security is used extensively with MDX expressions.

DiskCachePageSize

The DiskCachePageSize entry is reserved for future use.

Minimum Value

0x00000000 (0)

Maximum Value

0x00000000 (0)

Remarks

The DiskCachePageSize entry is read from the registry only when the server starts.

DiskCacheSize

The DiskCacheSize entry is reserved for future use.

Minimum Value

0x00000000 (0)

Maximum Value

0x00000000 (0)

Default Value

0x00000000 (0)

Remarks

The DiskCacheSize entry is read from the registry only when the server starts.

ExcludeMachineAdminFromOLAPAdmin

The ExcludeMachineAdminFromOLAPAdmin entry controls the inclusion of the local Administrators group as administrators of the Analysis Services server.

This entry is not installed in the registry during the setup process.

This entry was added for SP4.

Minimum Value

0x000000000 (0)

Maximum Value

0x000000001 (1)

Default Value

If this entry is missing from the registry, the default value is 0x000000000 (0).

Remarks

If the value of this entry is 1, the local Administrators group will be excluded from the Analysis Services administrators list.

ForceCommitTimeout

The ForceCommitTimeout entry controls the ability of the Analysis Services server to cancel any running queries in order to complete a DSO operation.

This entry is not installed in the registry during the setup process.

This entry was added for SP4.

Minimum Value

0x000000000 (0)

Maximum Value

0x418937 (4294967)

Default Value

No default value is provided.

Remarks

The value of this entry indicates how long, in seconds, a running DSO operation will wait before it will stop queries that block the DSO operation from committing.

If the CommitLockTimeout entry does not exist, the server will not try to stop queries blocking the DSO operation's commit.

FastIOCS

The FastIOCS entry defines the maximum number of bytes that are worked with at one time when using asynchronous disk access during queries.

Minimum Value

0x000003E8 (1000)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x0000FDE8 (65000)

If this entry is missing from the registry, the default value is 0x0000FFDC (65500).

Remarks

The FastIOCS entry is read from the registry only when the server starts.

Caution   Modification of this entry could lead to system instability and unexpected results.

HighMemoryLimit

The HighMemoryLimit entry is used when determining the priority of the cleaner thread. Modifying this value can change the frequency that the server flushes older items from the cache.

Minimum Value

The value contained within the LowMemoryLimit entry.

Maximum Value

4 MB less than the total virtual memory available.

Default Value

Half the pagefile size, in bytes.

If this entry is missing from the registry, the default value is 0x00A00000 (10485760), which is 10 MB.

Remarks

While the memory allocated to the server meets or exceeds half this limit (determined by subtracting the LowMemoryLimit registry value from the HighMemoryLimit entry and dividing the result by two), the server begins to flush older items from the cache at an increased rate with the use of a cleaner thread. This thread runs at a frequency defined by the BackgroundInterval registry value.

If the allocated cache memory meets or exceeds the HighMemoryLimit entry, the priority of the cleaner thread is set to normal and the cache is cleaned again. If this step does not reduce allocated cache memory back to the halfway point, the priority of the cleaner thread is set to above normal and the server cache is cleaned again. This behavior is not guaranteed to be initiated.

The HighMemoryLimit entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry. You can use Analysis Manager to modify this entry.

Note   When the /3GB switch is used, the upper bound on memory must be set using the HighMemoryLimit registry entry, because the memory conservation threshold property cannot be set to greater than 2 GB from Analysis Manager.

HugeLevelThreshold

The HugeLevelThreshold entry represents the minimum number of members that a level can have before it is loaded incrementally to conserve dimension memory.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x000003e8 (1000)

Remarks

The client with the Large Level Threshold property can override HugeLevelThreshold.

If 0 is specified, the server defaults to 1000.

The HugeLevelThreshold entry is read from the registry on a regular basis, as defined by BackgroundInterval. You can use Analysis Manager to modify this entry.

InitWorkerThreads

The server creates n worker threads during the system startup process (where n is the value stored in the InitWorkerThreads entry). These threads manage the process threads to complete queries.

Minimum Value

0x00000004 (4)

Maximum Value

The number of processors multiplied by 4.

Default Value

The number of processors multiplied by 4.

Remarks

The maximum and default values are determined by multiplying the number of processors by 4. For example, on a two-processor server the maximum value would be 8.

The InitWorkerThreads entry is read from the registry only when the server starts.

IOSectorSize

The IOSectorSize entry is used by the server to determine the number of bytes per sector on the hard drive.

This entry is not installed in the registry during the setup process.

Minimum Value

0x00000000 (0)

Maximum Value

0x00002000 (8192)

Default Value

0xFFFFFFFF (4294967295)

Remarks

If the value is double the sector size, the server reads from two sectors at one time. If this entry is set to 0xFFFFFFFF, the server calculates it from the operating system.

The IOSectorSize entry is read from the registry only when the server starts.

Caution   This value must be in increments of the physical sector size of the drive. System instability and unexpected results occur if this value is not in increments of the physical sector size of the drive.

IOSparsityControl

The IOSparsityControl entry is used by the server to determine how many separate disk accesses are performed to retrieve data.

This entry is not installed in the registry during the setup process.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x00000000 (0)

Remarks

When accessing records from the drive, the server analyzes the density of needed records in each location on the disk. The density is calculated by determining how many unneeded records lie between needed records.

If two needed records are separated by n unneeded records (where n is the value stored in the IOSparcityControl entry), the server separates the request into two separate disk accesses. For example, two needed records are separated by 15 unneeded records. If the IOSparcityControl entry is set to 10, the server makes two separate requests to the disk. Conversely, if the IOSparcityControl entry is set to 20, it makes only one request to the disk.

This capability is disabled if the IOSparsityControl entry is set to 0, and the server attempts to pull in as much data as possible, regardless of the sparseness of the data.

The IOSparsityControl entry is read from the registry only when the server starts.

Caution   Modification of this entry could lead to system instability and unexpected results.

LazyInterval

The Analysis server uses the LazyInterval entry to determine the frequency, in seconds, at which the lazy-indexer threads are run.

This entry is not installed in the registry during the setup process.

Minimum Value

0x000000000 (0)

Maximum Value

0x418937 (4294967)

Default Value

0x00000001e (30)

Remarks

The LazyInterval entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry.

LFHHeap

The LFHHeap entry controls if the Analysis Services server can make use of the NT Low Fragmentation Heap.

This entry is not installed in the registry during the setup process.

This entry was added for SP4.

Minimum Value

0x000000000 (0)

Maximum Value

0x000000001 (1)

Default Value

If this entry is missing from the registry, the default value is 0x000000000 (0).

Remarks

If this entry is set to 1, the Analysis Services server will use the NT Low Fragmentation (LFH) Heap. Using the LFH Heap reduces fragmentation and lock contention, and enables memory tracking through heapstat.exe

Note    To enable this for the OLEDB Provider, this entry must occur in HKCR\CLSID\{a07ccd0c-8148-11d0-87bb-00c04fc33942}\InprocServer32..

Note    The LFH Heap is only available on or after Windows 2000 Service Pack 4.

LinkSyncInterval

The Analysis server uses the LinkSyncInterval entry to determine the frequency, in seconds, at which the synchronization of linked cubes occurs.

This entry is not installed in the registry during the setup process.

Minimum Value

0x00000000 (0)

Maximum Value

0x418937 (4294967)

Default Value

0x00000010 (10)

Remarks

If LinkSyncInterval is set to 0, the server synchronizes without pause.

This entry is the server-side equivalent to the Auto Synch Period connection string property in the PivotTable Service client.

The LinkSyncInterval entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry.

Locale

The Locale entry is the locale identifier that is used for string comparison.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x00000000 (0)

Remarks

The value is a locale identifier created by the MAKELCID macro. If 0 is specified, then the server uses LOCALE_SYSTEM_DEFAULT.

The Locale entry is read from the registry only when the server starts.

LoggerInterval

The Analysis server uses the LoggerInterval entry to determine the frequency, in seconds, at which logs are submitted to the logging database specified within the QueryLogConnectionString entry.

This entry is not installed in the registry during the setup process.

Minimum Value

0x000000000 (0)

Maximum Value

0x418937 (4294967)

Default Value

0x00000001e (30)

Remarks

The LoggerInterval entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry.

LowMemoryLimit

The LowMemoryLimit entry is the minimum amount of memory, in bytes, that the server keeps for processing, regardless of the current need.

Minimum Value

0x00000000 (0)

Maximum Value

8 MB less than the total virtual memory available.

If this entry is missing from the registry, the default value is 0x00400000 (4194304), which is 4 MB.

Default Value

One quarter of the pagefile size, in bytes.

Remarks

See HighMemoryLimit for additional remarks.

The LowMemoryLimit entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry. You can use Analysis Manager to modify this entry.

MaxOLEDBTimeout

The MaxOLEDBTimeout entry is the time-out, in milliseconds, for any OLE DB queries sent from Analysis Services to the underlying database.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x00000000 (0)

Remarks

The server retains the timeout values on a per connection basis. You must refresh the data source from within Analysis Manager for a changed value to be recognized. If this entry is set to 0, no time-outs occur.

The MaxOLEDBTimeout entry is read from the registry only when the server starts. You can use Analysis Manager to modify this entry.

Note   This value is passed directly to the underlying database. Several databases have a limit of 64 K milliseconds, and use only the first two bytes of this value. For example, 0x12345678 is treated as 0x00005678.

MDXCompatibilityValue

The MDXCompatibilityValue entry determines how the server treats empty positions in the hierarchy in MDX.

Minimum Value

See "Remarks."

Maximum Value

See "Remarks."

Default Value

0x00000000 (0)

Remarks

This entry can be overridden by the registry entry value of the client and by the MDX Compatibility connection string used to connect to the server. The decision to default to the compatibility level of SQL Server version 7.0 was made for backwards compatibility reasons. Many applications cannot correctly interpret responses that do not expose placeholders.

MDXCompatibilityValue is read from the registry only when the server starts.

The following table lists the available constant values and describes the server behavior for each value.

For more information about this entry, see "Using the MDX Compatibility Property," "Ragged Dimension Support," and "Ragged Hierarchies" in SQL Server Books Online.

MDXUniqueNames

The MDXUniqueNames entry determines which algorithm is used to generate unique names.

Minimum Value

See "Remarks."

Maximum Value

See "Remarks."

Default Value

0x00000000 (0)

Remarks

This entry can be overridden by the registry entry value of the client, and by the MDX Unique Name Style connection string used to connect to the server.

The MDXUniqueNames entry is read from the registry only when the server starts.

The following table lists the available constant values and describes the server behavior for each value.

For more information about this entry, see "MDX Unique Name Style Property" in SQL Server Books Online.

MiningPersistenceFormat

The MiningPersistenceFormat entry determines the persistence format for the mining model when performing data mining operations.

This entry is not installed in the registry during the setup process.

Minimum Value

See "Remarks."

Maximum Value

See "Remarks."

Default Value

0x00000002 (2)

Remarks

The MiningPersistenceFormat entry can be overridden by connection parameters.

The MiningPersistenceFormat entry is read from the registry only when the server starts.

The following table lists the available constant values and describes server behavior for each value.

OLEDBPoolExpiration

The Analysis server uses the OLEDBPoolExpiration entry to determine how long, in seconds, to wait before disconnecting unused OLE DB connections.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x00000258 (600)

If this entry is missing from the registry, the default value is 0x000000E10 (3600).

Remarks

If the value of this entry is 0, unused OLE DB connections is never be disconnected due to a time-out.

The OLEDBPoolExpiration entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry.

OpenFilesLimit

The OpenFilesLimit entry is reserved for future use.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x00000400 (1024)

Remarks

PagePoolInitSize

The PagePoolInitSize entry is reserved for future use.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x00000050 (80)

Remarks

PagePoolSize

The PagePoolSize entry is reserved for future use.

Minimum Value

0x00000001 (1)

Maximum Value

0x00002710 (10000)

Default Value

0x00000050 (80)

Remarks

PoolProcessThreads

The PoolProcessThreads entry is the maximum number of process threads maintained by the process thread pool.

Minimum Value

0x00000028 (40)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

Forty times the number of processors.

Remarks

The default value is determined by multiplying 40 by the number of processors. For example, on a two-processor server the maximum value would be 80.

This entry should typically be set to 125 percent of the value that is stored within PoolWorkerThreads because the worker threads can make use of more than one process thread to accomplish the work.

If the Analysis server consistently handles numerous and complex requests, you can reduce queuing and improve perceived performance by raising the value of the PoolProcessThreads entry to 150 percent or more of the PoolWorkerThreads entry. Also consider increasing this entry if the Analysis server consistently returns errors during peak usage periods; this provides more available process threads and reduces the rate of errors related to non-available worker threads.

The PoolProcessThreads entry is read from the registry only when the server starts.

PoolWorkerThreads

The PoolWorkerThreads entry is the maximum number of worker threads maintained by the worker thread pool.

Minimum Value

Greater than or equal to InitWorkerThreads.

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

Thirty times the number of processors.

Remarks

The default value is determined by multiplying 30 by the number of processors. For example, on a two-processor server the maximum value would be 60.

The server allows the worker thread pool to grow beyond this entry if there are no available worker threads when a new client connects.

If the Analysis server consistently returns errors during peak usage periods, increasing this entry value provides more available worker threads and reduces the rate of errors related to nonavailable worker threads.

The PoolWorkerThreads entry is read from the registry only when the server starts.

Note   This value is extremely important in situations when a large number of clients are accessing the server. Increasing this value resolves several possible timeout issues.

ProcessPoolExpiration

The ProcessPoolExpiration entry is the number of seconds for which the server retains unused process objects.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x00000e10 (3600)

Remarks

The ProcessPoolExpiration entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry.

ProcessReadAheadSize

The ProcessReadAheadSize entry represents the approximate size, in bytes, that can be used by the server read-ahead buffer for querying ROLAP cube data and processing MOLAP cube data.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x00400000 (4194304)

Remarks

When working with this read-ahead buffer, the server has four levels of memory allocation. The smallest three levels are of a predefined size. The fourth level, which is the largest, is dynamically sized. The buffer sizes are defined by the number of records prefetched into the read-ahead buffer.

The ProcessReadAheadSize entry is used to determine which of the four buffers should be used. If the server must use the largest buffer, the number of records it can contain is dynamically calculated.

If the ProcessReadAheadSize entry is set to zero, the server chooses the third-largest level (the largest level that is not dynamically set).

The ProcessReadAheadSize entry is read from the registry only when the server starts. You can use Analysis Manager to modify this entry.

ProcessReadSegmentSize

The ProcessReadSegmentSize entry defines the size, in bytes, of the process buffer.

Minimum Value

0x00000001 (1)

Maximum Value

0x80000000 (2147483648)

Default Value

0x02000000 (33554432)

Remarks

The ProcessReadSegmentSize entry directly affects the number of records in a segment. If your fact table contains many duplicate records, increasing this entry value can result in the compression of more records in memory, rather than the storing of the duplicate records to the disk. The file size written to disk is then smaller. If this entry is larger than 80 percent of the physical memory available, however, excessive paging occurs, resulting in decreased system performance.

Process buffer size is central not just to the raw available capacity during processing, but also to the resultant overall performance of the partition once processed. During the indexing phase, the process buffer is used to sort fact table data and construct indexes based on the results of the sort. If the process buffer is sorting the fact table data inefficiently, partition access may be adversely affected.

When modifying this entry, keep in mind the limitations on memory imposed by the HighMemoryLimit entry.

The ProcessReadSegmentSize entry is read from the registry only when the server starts. You can use Analysis Manager to modify this entry.

Warning   This entry must be increased in 64 KB increments.

ProcessRecordsReportGranularity

The ProcessRecordReportGranularity entry serves a twofold purpose. The primary purpose is to define a loose guideline for the number of records that are read into the process buffer at one time. The secondary purpose is to define a loose guideline for the granularity of processing updates to user interfaces.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x000003E8(1000)

Remarks

The server uses the ProcessRecordsReportGranularity entry if it is greater than 0 and less than the defaults based on the levels described in ProcessReadAheadSize.

The ProcessRecordsReportGranularity entry is read from the registry only when the server starts.

ProcessThreads

The ProcessThreads entry contains the number of process threads that can simultaneously use the processors available to perform tasks for the server.

Minimum Value

0x00000001 (1)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

The number of processors multiplied by 2.

Remarks

The default value is determined by multiplying the number of processors by 2. For example, on a two-processor server the maximum value is 4.

The ProcessThreads entry is read from the registry only when the server starts.

ProtocolReceiveTimeout

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x0000000a (10)

Remarks

The ProtocolReceiveTimeout entry is not read by the server from the registry.

ProxyServer

The ProxyServer entry is the HTTP proxy server that is used by the server to connect to a linked cube through HTTP.

Minimum Value

Not applicable.

Maximum Value

Not applicable.

Default Value

See "Remarks."

If this entry is missing from the registry, the default value is Null.

Remarks

The default value is retrieved from the HKEY_CURRENT_USER registry hive for the user who is logged on while performing the setup of Analysis Services. If the server has multiple connections defined under Microsoft Internet Explorer 5.0 or later, the default value set is the "static" proxy server setting.

The format is server:port#.

The ProxyServer entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry.

QueryLogConnectionString

The QueryLogConnectionString entry is the connection string used by the log thread to connect to the query log database.

Minimum Value

Not applicable.

Maximum Value

Not applicable.

Default Value

See "Remarks."

If this entry is missing from the registry, the default value is Null.

Remarks

The default connection string references the Msmdqlog.mdb database in the Bin folder installed with Analysis Services.

The QueryLogConnectionString entry is read from the registry only when the server starts.

QueryLogSampling

If query logging is enabled, the log thread records every nth query request (where n is the value of the QueryLogSampling entry) for later analysis.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x0000000a (10)

If this entry is missing from the registry, the default value is 0x000000000 (0).

Remarks

A value of 0 prevents the server from logging query requests.

The QueryLogSampling entry is read from the registry only when the server starts. You can use Analysis Manager to modify this entry.

ReadAheadBufferSize

The ReadAheadBufferSize entry is reserved for future use.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x00100000 (1048576)

Remarks

RemoteQueryLogConnectionString

The RemoteQueryLogConnectionString entry is the connection string used by Analysis Manager to connect to the query log database.

Minimum Value

Not applicable.

Maximum Value

Not applicable.

Default Value

See "Remarks."

Remarks

The default value contains a Data Source property set to the UNC path of the Msmdqlog.mdb file in the Bin folder installed with Analysis Services.

The share, named MsOLAPRepository$, is created during the installation of Analysis Server. This share is used by Analysis Manager to perform usage-based optimization on remote and local servers.

The RemoteQueryLogConnectionString entry is not read by the server from the registry.

ROLAPDimProcessingEffort

The server generates an error if a client-requested dimension tree requires processing more than n records (where n is the value stored in the ROLAPDimProcessingEffort entry).

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x000493e0 (300000)

If this entry is missing from the registry, the default value is 0x000186A0 (100000).

Remarks

This entry is used when the server is working with ROLAP or MOLAP data to create a dimension tree to return to the client. Before working on the data, the server first verifies that the number of records that must be processed from the underlying database is less than the value contained in the ROLAPDimProcessingEffort entry. If the number of records that must be processed from the underlying database is greater than the ROLAPDimProcessingEffort entry, an error is returned to the client.

If this entry is not in the registry, the server defaults to 0x000186A0 (100000). If the ROLAPDimProcessingEffort entry is set to 0, the server does not perform the check.

The ROLAPDimProcessingEffort entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry.

RootDir

The RootDir entry is the directory in which cubes and databases are stored.

Minimum Value

Not applicable.

Maximum Value

Not applicable.

Default Value

See "Remarks."

If this entry is missing from the registry, the server will not start.

Remarks

The default is the Data folder installed with Analysis Services; you can change this during the setup process of Analysis Services.

The RootDir entry is read from the registry only when the server starts. You can use Analysis Manager to modify this entry.

SocketsUseSelect

The SocketsUseSelect entry defines whether Analysis Services determines if a WinSock socket is ready for a send or receive operation..

This entry is not installed in the registry during the setup process.

Minimum Value

0 (0x00000000)

Maximum Value

1 (0x00000001)

Default Value

1 (0x00000001)

Remarks

If the entry is set to 1, Analysis Services checks the state of the Winsock socket to determine if it is ready for a send or receive operation. If the entry is set to 0, Analysis Services does not check the state of the socket.

The SocketsUseSelect entry is read from the registry only when the server starts.

Support for this entry was added in SP3.

SocketsBufferSize

The SocketsBufferSize entry determines the size, in bytes, that are allocated in the kernel for the receive buffer.

This entry is not installed in the registry during the setup process.

Minimum Value

0 (0x00000000)

Maximum Value

65535 (0x0000FFFF)

Default Value

1460 (0x000005B4)

Remarks

It is recommended that the SocketsBufferSize entry be set to at least the size of the TCP/IP window.

The SocketsBufferSize entry is read from the registry only when the server starts.

Support for this entry was added in SP3.

SocketsUseBufferForSend

The SocketsUseBufferForSend entry determines whether Analysis Services uses the buffer size specified in SocketsBufferSize.

This entry is not installed in the registry during the setup process.

Minimum Value

0 (0x00000000)

Maximum Value

1 (0x00000001)

Default Value

1 (0x00000001)

Remarks

Set this entry to 1 to use the buffer size specified in SocketsBufferSize. The SocketsUseBufferForSend entry is read from the registry only when the server starts.

Support for this entry was added in SP3.

SocketsEnableNagle

The SocketsEnableNagle entry determines whether the Nagle algorithm is used to reduce the number of transmitted packets.

This entry is not installed in the registry during the setup process.

Minimum Value

0 (0x00000000)

Maximum Value

1 (0x00000001)

Default Value

0 (0x00000000)

Remarks

If the SocketsEnableNagle entry is set to 1, Analysis Services uses the Nagle algorithm to automatically concatenate a number of small buffer messages, reducing the number of packets that are sent across the network.

The SocketsEnableNagle entry is read from the registry only when the server starts.

Support for this entry was added in SP3.

SQLCompatibilityValue

The SQLCompatibilityValue entry is reserved for future use.

Minimum Value

Not applicable.

Maximum Value

0x00000000 (0)

Default Value

0x00000000 (0)

Remarks

Not applicable.

SSFastLoadOptions

The SSFastLoadOptions entry optimizes writebacks, such as those made with UPDATE CUBE statements, through the use of SQL Server's bulk insert feature and OLEDB's SSPROP_FASTLOADOPTIONS OLEDB property. For more information on SSPROP_FASTLOADOPTIONS, see IRowsetFastLoad Rowsets.

This entry is not installed in the registry during the setup process.

This entry was added for SP4.

Minimum Value

Not applicable.

Maximum Value

Not applicable.

Default Value

If this entry is missing from the registry, the default value is "<disabled>"

Remarks

If entry exists, and the value is set to anything but "<disabled>", Analysis Server will interpreted the value in the same manner as the hint parameter is interpreted in the bcp command. Specifically, the string will be passed to the SSPROP_FASTLOADOPTIONS OLEDB property.

When this entry is set to "TABLOCK", and using SQL Server 2000 as the storage for writeback partitions with the "Microsoft OLEDB Provider for SQL Server" driver, this entry can cause a large improvement on the commit phase.

When this entry is set to "TABLOCK, FIRE_TRIGGERS", and using SQL Server 2000 as the storage for writeback partitions with the "Microsoft OLEDB Provider for SQL Server" driver, this entry can cause an improvement on the commit phase, while still firing triggers.

Finally, for paramount writeback speed, consider:

• turning on the "select into/bulkcopy" dboption,
• turning off the "torn page detection" dboption,
• configuring the SQL Server that hosts the writeback partition to have a simple recovery model,
• not replicating the writeback tables, and
• not creating indexes on the writeback table (this will improve write performance at the possible expense of read performance).

Caution   Only make the changes recommended in this entry with the full understanding of the implication that these changes will have on your database.

Note    To see a performance improvement, the "Microsoft OLEDB Provider for SQL Server" driver must be used while SQL Server 2000 stores the writeback partition.

Note    Only use FIRE_TRIGGERS when keeping in mind the considerations relavent to using FIRE_TRIGGERS with BCP. For more information, see Using bcp and BULK INSERT.

TempDirectory

The TempDirectory entry is the directory in which temporary files are placed. The default is the same as the value contained in the RootDir entry.

Minimum Value

Not applicable.

Maximum Value

Not applicable.

Default Value

See "Remarks."

Caution   Removing this entry from the registry can lead to system instability and unexpected results.

Remarks

The TempDirectory entry is read from the registry only when the server starts. You can use Analysis Manager to modify this entry.

TempDirectory2

The TempDirectory2 entry is used by the server when it no longer has the memory to accumulate aggregations, and needs to merge the buffer with the temporary file already on the disk.

This entry is not installed in the registry during the setup process.

Minimum Value

Not applicable.

Maximum Value

Not applicable.

Default Value

See "Remarks."

If this entry is missing from the registry, the default value is the value contained within the TempDirectory entry.

Remarks

The server alternates between the two temporary directories.

The default is the same as that of the RootDir entry.

The TempDirectory2 entry is read from the registry only when the server starts.

Note   The TempDirectory2 entry is especially useful when set to a drive that is controlled by a separate drive controller than the drive in the TempDirectory entry. This creates a speed improvement by using one drive for read access and the other for write access during aggregation processing.

TraceLogString

The TraceLogString entry is used by the server to determine which of the possible events are logged to the binary file.

This entry is not installed in the registry during the setup process.

Minimum Value

Not applicable.

Maximum Value

Not applicable.

Default Value

Null.

Remarks

The format for the entry is LogFileName;1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17 where the comma-separated numbers represent various events that can be logged. Each event has a corresponding structure in the binary file, and is discussed individually in the Appendix.

For information on the structure of the binary log file, see the Appendix.

The TraceLogString entry is read from the registry only when the server starts.

UnbufferedThreshold

The UnbufferedThreshold entry is the threshold file size, in megabytes; after a file has reached this size, the server opens that file without utilizing the operating system file-buffering functionality.

This entry is not installed in the registry during the setup process.

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x00000000 (0)

Remarks

Windows NT uses approximately 1 MB of kernel page pool for each 1 GB of file size, with a limit of approximately 190 MB in Windows NT. After this limit, disk operations may begin to fail.

The server always attempts to use the NT buffer if the UnbufferedThreshold entry is set to 0.

The UnbufferedThreshold entry is read from the registry only when the server starts.

VersionNum

The VersionNum entry is used by the setup process to compare the currently installed server version with the version being installed.

Minimum Value

0x00000000(0)

Maximum Value

0x00000003(3)

Default Value

0x00000003(3)

Remarks

If this entry value is less than 3, the setup process instructs the user to uninstall their Microsoft SQL Server OLAP Services software before installing Analysis Services.

VLDMThreshold

The VLDMThreshold entry is the threshold, in bytes, beyond which a dimension tree is managed by the Very Large Dimension Manager (VLDM).

Minimum Value

0x00000000 (0)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

0x04000000 (67108864)

Remarks

Member properties are not to be used to calculate the size of the dimension tree.

The VLDMThreshold entry is read from the registry on a regular basis, as defined by the BackgroundInterval entry.

WorkerThreads

The WorkerThreads entry contains the number of worker threads that can work concurrently on the server.

Minimum Value

0x00000001 (1)

Maximum Value

0xFFFFFFFF (4294967295)

Default Value

The number of processors times 2.

Remarks

The default value is determined by multiplying 2 by the number of processors. For example, on a two-processor server the maximum value is 4.

The WorkerThreads entry is read from the registry only when the server starts. You can use Analysis Manager to modify this entry.

Conclusion

You can use the registry to fine-tune SQL Server 2000 Analysis Services. Analysis Manager does not expose all of these documented entries. Additionally, you need to edit the registry for the entries that were not installed to the registry during the setup process. Analysis Manager also imposes limits on several of the entry values; this can be surpassed by modifying by the registry directly.

Although many of the values can be modified to improve server performance, modification of the values can also result in severely degraded performance. Keep in mind that these values should always be tested on a non-production server.

Appendix

The TraceLogString contains the location of the binary log, which contains various structures that correspond to events initiated within the server. Each structure records the status of the server when the event is initiated.

Note   When the server starts, it will open the log file without clearing it. As events are logged, they will be written over the existing information in the log file.

The following table lists the available trace logging structure names, the values used in the entry to enable logging for the event, and a description of the corresponding event to be logged.

The following table lists the elements located in the 64-byte header of the binary log file, the size of each element, and a description of the element.

Each structure has different elements within the binary log file. The following tables describe the contents of each structure.

LOG_TRACE_TEXT Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_ERROR Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_CON_OPEN Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_CON_CLOSE Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_SEIZE_DC Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_LOAD_DC Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_FILTER_DC Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_RECEIVE_DC Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_SEND_DC Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_BUILD_PRT Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_BUILD_AGR Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_BUILD_DIM Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_BUILD_PRP Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_SQL_CONNECTION Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_SQL_QUERY Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_LAZY Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

LOG_TRACE_COMPLETE Structure

The following table lists the elements located in the structure, the size of each element, and a description of the element.

STATE_MASKS

The following table lists the known mask values for the STATE element, and describes the state of the server that the mask represents.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Copyright 2001 Microsoft Corporation. All rights reserved.

Microsoft, MS-DOS, Active Directory, ActiveX, MSDN, Win32, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.