Siebel Data Warehouse Installation and Administration Guide > Preinstallation Considerations for Siebel Data Warehouse >
SQL Server-Specific Database Guidelines for Siebel Data Warehouse
This section lists 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.
SQL Server databases should be created with ANSI NULL option selected. To set the ANSI NULL option
- In Enterprise Manager, select Database.
- Right-click and choose Database properties.
- Click the Options tab and select the box for ANSI NULL default.
In a SQL Server 2000 environment, when loading Analytics tables with international data, or loading more than one language, modify the DB Library Options setting. To modify the DB Library Options setting
- In the program menu Microsoft SQL Server, launch the Client Network utilities.
- 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 12.
Table 12. 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.
|