SAP/Sybase 数据库系统性能优化10大技巧 DBA篇视频课程

1

课程简介Top 30 Miscellaneous tipsMore DBA focus than Developer focusMainly server tuning (with a little bit of SQLtuning)Tools to aid in performance tuning10 Tips and tricksSybase ASE 12.5 to -SAP ASE 16

2

30小技巧部分一1. Backup with compression2. Plan your backup ( weekly full, daily incremental, t-log every 15minutes)3. Backup to multiple stripes ( multiple files on difference devices)4. Backup master database on a daily basis to OS file5. Regluar restore/load testing6. bcp out syslogins, sysdatabases, sydevices to flat files7. Update statistics with hashing8. Use a larger packet size for large bulk-copy operations:

「仅限付费用户」点击下载“PerfTuning_DBA.pdf”

3

30小技巧部分二9. Practice and document steps to recover a master database10. Practice and document steps to repair suspect database11. Practice and document steps to rebuild logs12. Once long-running sql is logged, focus on to tune top nslow queries13. keep watching SAP/Sybase website for new whitepapersand document, tips14. When you tune, focus to reduce logical io15. Make good friends with your system admin and storageadmin16. Load balancing:

4

30小技巧部分三17. Make sure within Sybase database, you have 20-30% free space18. Make sure at OS level, you have 20-30% free space19. Make sure you have spare RAM within Sybase databaseinstance20. Make sure you don't give out your sa password to others21. 'print deadlock information',and review deadlock in errorlog andfix deadlocks22. Tail your Sybase ASE error log file , backup log file and alerterrors, stack trace alert23. Backup your Sybase.cfg file24. Backup your Sybase binary files and license files25. Reverse engineering your whole Prod, cr

5

30小技巧部分四26. Set up your own DEV servers, where you can make stupidmistakes27. Watch out for identity columns, make sure you won't run out ofnumbers, int data type, 2 billion bigint28. Make sure you run dbcc checkdb regularly to detect datacorruption.Set up dbccdb to run dbcc checkstorage for VLDB TBsize29. Reindex for tables which got lots delete/update/insert30. Watch out scalar user defined function. Performance killer

6

Toolsset showplan onset statistics io,time onlogical I/O more useful indicator than physical I/Oset noexec onset fmtonly onTo display query plans for a stored procedure, but not executethem, use the set fmtonly command. optdiagmda tablesapplication tracingsp_sysmonset option show_missing_stats onset nocount onset nodata on

7

Watch out!Table scan

8

#temp table

9

dbcc traceflags, set optiontraceflagsset optionsLocking

10

server conf, capture SQL

11

ASE 12.5->16, StatsBig changes in 12.5 and 15, 16update statisticsetc.