Transcript Siebel Database Considerations

Siebel Database
Considerations
MISSION CRITICAL COMPUTING
Overview
Database Setup Guidelines
Statistics and Indexes
Execution plans
Database Operations and Maintenance
Monitoring
MISSION CRITICAL COMPUTING
Lesson: Database Setup Guidelines
Guidelines – Server Settings
Guidelines – Database Settings
MISSION CRITICAL COMPUTING
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
MISSION CRITICAL COMPUTING
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
MISSION CRITICAL COMPUTING
Lesson: Statistics and Indexes
Statistics
Indexes
MISSION CRITICAL COMPUTING
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.)
MISSION CRITICAL COMPUTING
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
MISSION CRITICAL COMPUTING
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
MISSION CRITICAL COMPUTING
Database Operations and Maintenance
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
MISSION CRITICAL COMPUTING
Lesson: Monitoring
Monitoring – Event Viewer
Monitoring – Enterprise Manager
Monitoring – Performance Monitor (perfmon)
MISSION CRITICAL COMPUTING
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)
MISSION CRITICAL COMPUTING
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.
MISSION CRITICAL COMPUTING
Monitoring – Performance Monitor (perfmon)
Provided with the OS
Can create Alerts and log them to Event Viewer’s Application log
Numerous 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
MISSION CRITICAL COMPUTING
Review
Database Setup Guidelines
Statistics and Indexes
Execution plans
Database Operations and Maintenance
Monitoring
MISSION CRITICAL COMPUTING