|
Oracle® Business Intelligence Applications Installation and Configuration Guide > Preinstallation and Predeployment Considerations for Oracle BI Applications >
SQL Server-Specific Database Guidelines for Oracle Business Analytics Warehouse
This section provides guidelines for SQL Server database usage. NOTE: SQL Server databases with binary sort order or case-sensitive dictionary sort order are supported. Case-insensitive dictionary sort order is not supported.
This section includes the following topics:
Setting the ANSI NULL Option
Oracle Business Intelligence Applications requires that SQL Server databases be created with the ANSI NULL option selected. To set the ANSI NULL option
- In the SQL Server Enterprise Manager, right-click the appropriate database, and choose Properties.
- Click the Options tab and select the box for ANSI NULL default.
Modifying the DB Library Options Setting
In a SQL Server 2000 environment, when loading Analytics tables with international data, or loading more than one language, you need to modify the DB Library Options setting. To modify the DB Library Options setting
- From the Microsoft SQL Server program menu, select Client Network Utility.
- Select the DB Library Options tab.
- Clear the option Automatic ANSI to OEM.
NOTE: SQL Server 2000 automatically tunes many of the server configuration options; therefore, an administrator is required to do little, if any, tuning. Although these configuration options can be modified, the general recommendation is that these options be left at their default values, allowing SQL Server to automatically tune itself based on run-time conditions.
Recommended SQL Server Database Parameters
If necessary, SQL Server components can be configured to optimize performance, as shown in Table 8.
Table 8. Recommended Variable Settings for SQL Server Databases
|
|
|
Affinity mask |
0 |
|
Allow updates |
0 |
|
Awe enabled |
0 |
|
C2 audit mode |
0 |
|
Cost threshold for parallelism |
5 |
|
Cursor threshold |
-1 |
|
Default full-text language |
1033 |
|
Default language |
0 |
|
Fill factor |
95% |
For insert-intensive transactions, set Fill Factor between 90 and 95%. For better query performance, set Fill factor to 95 or even 100%. |
Index create memory |
1024 KB |
Default is 0. |
Lightweight pooling |
0 |
|
Locks |
0 |
|
Max degree of parallelism |
0 |
Default is 0. This turns off parallelism. Max degree of parallelism should be left at 0, which means use parallel plan generation. It should be set to 1 (use only 1 process) if you run multithreaded components (for example, several EIM threads). |
Max server memory |
2000 MB |
Default is 2147483647. |
Max text repl size |
65536 B |
|
Max worker threads |
100 |
Default is 255. |
Media retention |
0 |
|
Min memory per query |
1024 KB |
|
Min server memory |
500 MB |
Default is 0. |
Nested triggers |
1 |
|
Network packet size |
8192 B |
Default is 4096. |
Open objects |
0 |
|
Priority boost |
0 |
|
Query governor cost limit |
0 |
Modify to 60 only if CPU is high. |
Query wait |
-1 sec |
|
Recovery interval |
0 min |
|
Remote access |
1 |
|
Remote login timeout |
20 sec |
|
Remote proc trans |
0 |
|
Remote query timeout |
600 sec |
|
Scan for startup procs |
0 |
|
Set working set size |
0 |
|
Two-digit year cutoff |
2049 |
|
User connections |
0 |
|
User options |
0 |
|
- SQL Server memory: Make sure adequate memory is available.
- Transaction logs and TempDB: Reside on a separate disk from those used by database data.
- Full load: Full Recovery model for the database.
- Incremental (Refresh) load: Change from Full to Bulk-Logged Recovery model.
| 
