mission critical computing siebel database considerations

MISSION CRITICAL COMPUTING

Siebel Database Considerations


Overview

Database Setup Guidelines

Statistics and Indexes

Execution plans

Database Operations and Maintenance

Monitoring


Lesson: Database Setup Guidelines

Guidelines – Server Settings

Guidelines – Database Settings


Guidelines – Server Settings

Format the NTFS drive(s) or SAN to 64k blocks (default is 4k)

Review /3GB and /PAE switches

Install SQL Server with same Code Page / Collation as the Siebel database

Allocate sufficient memory to SQL Server

For memory, consider number of concurrent users and workload

Disable Query Parallelism

Set the Index Fill Factor prior to building Indexes


Guidelines – Database Settings

Preallocate enough space to Siebel database to avoid Autogrow

Create Siebel database with the correct Collation (code page)

Increase size of Model database

Resize tempdb

Place tempdb, Data Files, and Transaction Log on distinct physical devices (plan for 3+ physical drives)

Allocate sufficient space for Data Files and Transaction Log, including file growth

Disable the Auto Shrink parameter

Enable the Auto Create Statistics and Auto Update Statistics parameters

Run script to identify Indexes with poor cardinality


Lesson: Statistics and Indexes

Statistics

Indexes


Statistics

Statistics are collected so SQL Server understands the volume and distribution of data within a Table

With auto statistics, 20% of the rows in a Table must change before auto statistics is invoked for that Table

Manually update statistics if appropriate

Can choose between FULLSCAN (all rows) or sampling a percentage of the rows

FULLSCAN mode may take time for large Tables

Update statistics for all Tables in Siebel database (including Repository, EIM, etc.)


Indexes

Will generally observe more intelligent decisions when SQL Server selects an Index due to statistics on Tables

SQL Server does not do “Sparse Indexing”

Run script to identify Indexes with poor cardinality


Execution Plans

Analogous to EXPLAIN

Use Query Analyzer

Provides a graphical representation of the Execution Plan

Move mouse over each icon for details

Index Tuning Wizard may help to ultimately provide a more efficient Execution Plan, but beware unknown impact on other functionality



Use the Database Maintenance Plan Wizard to automate important DBA tasks

Backups

Check Database Integrity

Reorganize Data and Index pages

Update Statistics

Consider having different Maintenance Plans and schedules for each task


Lesson: Monitoring

Monitoring – Event Viewer

Monitoring – Enterprise Manager

Monitoring – Performance Monitor (perfmon)


Monitoring – Event Viewer

Provided with the OS

Application, Security, and System logs

Events may include the date, time, source, user, etc.

May provide a wealth of information when attempting to diagnose the root cause (preceding event) of an issue (e.g. disk drive failure)


Monitoring – Enterprise Manager

Create Alerts for events such as Table Integrity error, Hardware Error, etc.

Alert response (action) may be to run a job, notify/email operators, etc.


Monitoring – Performance Monitor (perfmon)

Provided with the OSCan create Alerts and log them to Event Viewer’s Application logNumerous Counters for OS and SQL Server

Processor - % Processor Time Physical Disk - %Disk Time, Avg. Disk Queue Length Memory – Available MBytes System – Context Switches / sec SQL Server Locks – Lock Waits/sec, Number of Deadlocks/sec SQLServer: Access Methods

Full Scans/sec, Page Splits/sec, Table Lock Escalation/sec SQLServer: Buffer Manager

Buffer Cache Hit Ratio, Lazy Writes/sec, Page Reads/sec, Page Writes/sec, ReadAhead Pages/sec

SQLServer: Databases - Transactions/sec SQLServer: General Statistics - User Connections


Review

Database Setup Guidelines

Statistics and Indexes

Execution plans


Monitoring

mission critical computing siebel database considerations

Documents