Siebel Data Loading Best Practices on SQL Server

1. Siebel Data Loading Best Practices on SQL Server Frank Earl McBath frankmcb@microsoft.com

2. How many of you have used SQL Server and EIM?

3. Objectives What is EIM? • What makes SQL Server different than other platforms? • What can we optimize? • How do we optimize? • What tools can I use? • What techniques can I try?

4. What is EIM? • EIM is the process that Siebel uses to load data into the database. • EIM is used on every platform. • It validates the data (PK/FK relationships) and generates unique ROWIDs. • Every customer uses it. • It tends to be the first problem that every customer hits with Siebel. • You cannot bypass EIM.

5. Symptoms of EIM? • The first several thousand will always go fast. • Performance deteriorates over time in a logarithmic pattern. • Why? Because the b-trees grow to more levels. • Ex. The first 5K rows go in at a rate of 2 minutes. After 2 weeks of loading that same 5K rows takes 1 hour.

6. What makes EIM query different? (vs. a normal Siebel query) • EIM is batch oriented • Have better logging • Typically more complicated • Reads system catalog on each run. • You can alter the indexes on the EIM tables. • Typically less configurable than a business rule.

7. Common Siebel Performance Problems • Docking replication turned on • Too many indexes • Wrong indexes • Clustered index issues • Table spools (non-EIM) • Implicit cursor conversion (non-EIM) • Too much meta data being loaded • Batch size not optimum • Blocking/Deadlocks while loading in parallel • Stats are stale (EIM only) • Maybe hints in the Siebel EIM job

8. The pre-EIM Loading Issue • Siebel provides EIM, but no mechanism to populate the EIM_ tables. • Every customer has to invent the wheel. • Some customers do it good… others not. • Don’t distribute over network • Don’t validate/scrub while loading • What the Alliance is working on… • A better wheel.

9. What are the typical problems with EIM? (I) • Too many indexes • Both on EIM_* tables… and… • Base tables • Incorrect indexes • Poor selectivity

10. What are the typical problems with EIM? (II) • You are allowed to modify/add/drop to the EIM_* tables • IMPORTANT: You are not allowed to change base table indexes without ES approval. • Build a case and present it to them. • They use “expert” mode for Siebel Tools and alter the meta data. • Poor statistics & Bad plans

11. Tools to Use (I) • SQL Server Profiler • Gives you a SQL Server kernel view of the query. • Level 8 EIM Trace • Will show you network times, etc… • Will not always show you hints • Will not show you plans

12. Tools to Use (II) • Index Wizard • Will never suggest a better index. Why? Because Siebel indexes just about every possible configuration! • What it will show you is what indexes ARE being used. Hence, you can deduce what indexes ARE NOT being used. • DBCC SHOWSTATISTICS • Will show you what indexes are 100% of one value. • Probably not being used.

13. Tools to Use (III) • ISQLW • Use ODBC wrapper • Need to use proper “wrapper” • Will get incorrect plan if you do not • SET STATISTICS PROFILE • Will show plans with costing • SET STATISTICS IO • Shows the IO only • Only use one at a time. • DBCC DBREINDEX • UPDATE STATISTICS • Long Running Query

14. SQL Profiler • Powerful tool, but can burn up a lot of CPU if you are not careful. • Not a “Black Box” flight recorder. Use it sparingly. • Filter on the SPID or CLIENT PROCESS • Capture on the following Events: Stored Procedures -> RPC:Completed and TSQL -> SQL:BatchCompleted • Save off to a file, then to a table and analyze

15. How To Optimize EIM (I): • Turn on “Level 8” EIM logging. • Very verbose • Turn on SQL Server Profiler for the run. • Run an EIM batch.

16. How to Optimize EIM (II): • Look at the EIM log. What is the longest execution time? • Tip: Load this log into Excel and sort. Then search the original log. • Look at the SQL Profiler trace. Sort on longest duration and then on reads. • These queries should match those in the EIM log. • Look at the execution plans. Why is it taking a long time? • Use Excel to help sort. • Once the problem has been isolated, figure out the fix.

17. Hints (I) SQL Server: Siebel uses hints by default. This helps rule based optimizers, but hurts cost based ones. • Big performance gains can be made by just taking the hints out. • Why you just can’t drop the index: the query will not compile • DBCC trace flag will cause optimizer to bypass all hints • DBCC TRACEON (-1,8602) • Query will compile even if index not there.

18. Hints (II) Siebel: • From the IFB file configuration • Test with / without Index Hints • USEINDEXHINTS, FALSE • USEESSENTIALHINTS, FALSE

19. More IFB File Configuration • Consider using the parameters as appropriate: • ONLY BASE TABLES, • IGNORE BASE TABLES, • ONLY BASE COLUMNS, • IGNORE BASE COLUMNS • This will reduce the amount of processing performed by EIM. • ONLY and IGNORE are mutually exclusive.

20. Index Tuning Wizard • Tends not to help much because Siebel has just about every index on there already. • Will not suggest a clustered index. • Just takes a long time to run… • … but… it can help you determine which indexes to drop. • By showing you what indexes are used, you can infer which ones are not.

21. Which Indexes Are Used? • Look at the Profiler trace from the EIM run. • Within the trace, look at the execution plan. Will tell you which indexes used. • Remember, you have to capture with the following: • Event: Performance -> Show Plan Statistics • Data Columns: BinaryData • Output Example: • Clustered Index Scan(OBJECT:([master].[dbo].[sysobjects].[sysobjects]))

22. Index Removal (I) • Why remove indexes? • Penalties on INSERTS/DELETES which is what the bulk of EIM • Many indexes are not used • Only keep the EIM indexes that are used. • Fewer indexes mean that there are fewer choices and fewer chances of a “wrong plan” being generated. • Less information to the optimizer can be good sometimes.

23. Index Removal (II) • Feel free to remove indexes on the EIM_* tables • Work with Siebel ES to remove un-needed indexes on the base tables. • Build a case. For example, show that a column is 100% NULL, etc… • Optimizer probably will never use single value indexes. • Only in a “COUNT” on that column would it use it.

24. Index Removal (III) • DBCC SHOWSTATISTICS • Look at “Density” and “Steps”

25. Does the Table have a Clustered Index? • You’d be surprised… Many tables don’t • Run the script in the notes to find which ones don’t • Compare the results to your mappings and ER diagram • Ask Siebel why they don’t have a clustered index…

26. Clearly Think Out Your Clustered Index • Smart design can help in covering the index • Non clustered indexes are built on top of clustered indexes • Point to the cluster key • Every Siebel query has ROWID in it, that’s why it’s the clustered index on all base tables. • Look at DBCC SHOW STATISTICS

27. Index Strategies (I) • A successful strategy used has been: • Analyze what columns are being used • Put them in the clustered index • Use “BOE” method for selectivity • Drop all non-clustered indexes from the EIM table. • The premise is that EIM is operating on every row in the batch anyways, so why not just scan them all. • Is it worth the extra overhead of more indexes for what amounts to be a scan?

28. Index Strategies (II) • This strategy works because the BATCH_NUM “firewalls” the scan from a full table scan. • The BATCH_NUM is the first column in the clustered index. Thus, bounding the scan.

29. Performance Testing • After query is isolated… • Make a baseline • Use ISQLW • Use SET STATISTICS IO ON • Use SET STATISTICS PROFILE ON • (One at a time. Turn off between.) • DBCC DROPCLEANBUFFERS • DBCC FREEPROCCACHE • Change/Add index, rerun the query with IO and PROFILE. • The McBath Oil Argument

30. The Oil Argument • Do you notice when you change the oil in your car? Your engine does. • Looking at something that is massively iterated on and saving 1 IO. • Reindex, fill factor • Resequencing columns in an index • Check IO with SET STATISTICS IO

31. Fragmentation Happens… • During big data loads • Run DBCC REINDEX to correct • Think about: • Fill Factor • Pad Index • Look at sample script to defrag • Defrag & update stats between runs • DBCC INDEXDEFRAG, REINDEX, and drop recreate • Samples and run times • Pro’s and con’s

32. Stale Statistics (I) • What happens when stats get old… • Bad plans. A query that normally runs in a few seconds can take 30 minutes. • How do the stats get stale? • EIM updates every row in the EIM_* table. • The thread that auto updates stats doesn’t “wake up” in time between runs. • Small tables will never be updated.

33. Stale Statistics (II) • Correct this by running UPDATE STATISTICS between runs or a SQL AGENT job that wakes up and runs. • Consider turning off auto update stats for the data load. • It’s all about getting predictable performance.

34. Multiple EIM Batches • Number of batches directly related to your clustered index design • Good index will keep deadlocks from happening • You can run multiple batches against the same _IF table! • EIM Partioned tables • Use SP_INDEXOPTION to enable row level locks only. Cuts down on blocking issues.

35. Efficiently Loading Data (I) • Load into pre-staging tables • Scrub in tempdb • Minimal logging • Scrub in SQL Server: • Efficiencies of cache • Use set wise processing, not cursors • If have to use cursors, use Fast Forward/Read Only • Run all local on the database server. Not distributed over the network.

36. Efficiently Loading Data (II) • BULK INSERT vs BCP • BULK INSERT is “in memory” • BCP is more configurable • Both are single threaded • Only run on one CPU • Run multiple BULK INSERTS at once across multiple CPUs. • If the order of the data is not a concern, or you'd rather take the hit when creating the index, it's best to run BULK INSERT into the EIM tables in parallel by deploying a separate thread per CPU. You can use the TABLOCK hint to allow multiple non-blocking inserts.

37. Efficiently Loading Data (III) • Rules of thumb for previous: • Use only 2 - 3 threads at max (only if you have the processors) • Limit the batch Commit Size (batch size) to about 2,000 rows per batch. • Adjust up or down based on your testing. • Remember, if loading in clustered index sequence, only use one thread. • Bulk operations are very high performance. • They do log. • Conditions in BOL (ex. TABLOCK)

38. Efficiently Loading Data (IV) • Disk Slices • Even with a SAN, break out the following on separate slices if possible: • EIM_* Tables • TEMPDB • Base (DATA) Tables • INDEXES • Do this by dropping the table and recreating on a different file group • Cuts down on fragmentation and contention

39. Efficiently Loading Data (V) • RAID and EIM • Due to the constant UPDATE, INSERT and DELETE, try and use RAID 0+1 if possible. • Parity bit calculation penalty can be significant. • See “Balanced System Design” for more information on slicing • Size the Siebel database (siebeldb or whatever it is named in production) appropriately, and ensure that it will not have to autogrow during the process; that will hurt disk I/O and performance.

40. Efficiently Loading Data (VI) • When running EIM itself, run processes in parallel. Set different batch numbers, but they can be executed against the same interface tables. • Try and run from “opposite” ends of the batch range. Can help cut down on the blocking. • Ex. Run 1 & 5 at the same time, not 1 & 2. • Test to see how many threads can be run on your system. Start with two and add as appropriate. • If you are blocking and getting lock escalations, use sp_indexoption and set the clustered index to no page locks. See BOL for more information.

41. Efficiently Loading Data (VII) • Disable any triggers on the databases (such as workflow triggers) and then re-apply/enable them after the process is done. • If this is not for an initial data load, this means that Workflow Manager or Assignment Manager will not function during the load for the new or updated data.

42. Efficiently Loading Data (VIII) • Load multiple base tables from one interface table. • In the IFB table, set the parameter USING SYNONYMS to FALSE only if you are not associating multiple addresses with accounts. • If you have a 1:1 ratio, you are telling the EIM process that account synonyms do not require processing during the import, reducing the amount of work EIM needs to do (as well as load on the system).

43. Recovery Models During EIM • Use SIMPLE or BULK LOGGED if possible. • Run a full backup freqeuntly during the day. • Weigh the issues of recovery vs. lost data. • Note: Switching from FULL to SIMPLE will break the log chaing and have recovery consequeneces. Always make a full backup after switching to SIMPLE.

44. Bringing It All Together (I) • Optimize your EIM • Batch Size • Hint Removal: Siebel and SQL Server • Turn off docking replication • Get rid of workflow triggers • Only load up the tables needed from the Siebel meta data. Loading the whole catalog can represent 25% the time of your whole batch run. • Run batches in parallel • Exclude validation of non-used data. If you know something is never NULL (ex. EmpID), then don’t check for it.

45. Bringing It All Together (II) • Update Stats • Stale stats can cause problems • Investigate turning autostats off • Defrag between large runs • Defrag both EIM_ and base tables • On large initial loads, put fill factor and pad index to 50%. Cut down on page splits. Default is 100% full. • Use minimally logged operations to load and scrub. • Bulk Insert, SELECT/INTO • Recovery Models • Run all data loading locally • Scrub data inside SQL Server. No cursors. • Make the right indexes • Try monster clustered index only • Get rid of unused indexes • Add them back after runs • Work with ES to resolve support issues.

46. Bringing It Together (III) • Slice your disk right • More spindles = More performance. Don’t believe the vendor when they say cache will solve your problems. It helps hide them. • No RAID 5 if possible • Separate Data, Log, Indexes

47. Questions? • Frank McBath frankmcb@microsoft.com