`
darrenzhu
  • 浏览: 815813 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

Sybase性能调优建议清单

阅读更多
http://stackoverflow.com/questions/18783/favourite-performance-tuning-tricks
Here is the handy-dandy list of things I always give to someone asking me about optimisation.
We mainly use Sybase, but most of the advice will apply across the board.

SQL Server, for example, comes with a host of performance monitoring / tuning bits, but if you don't have anything like that (and maybe even if you do) then I would consider the following...

99% of problems I have seen are caused by putting too many tables in a join. The fix for this is to do half the join (with some of the tables) and cache the results in a temporary table. Then do the rest of the query joining on that temporary table.

Query Optimisation Checklist
  • Run UPDATE STATISTICS on the underlying tables
  • Many systems run this as a scheduled weekly job
  • Delete records from underlying tables (possibly archive the deleted records)
  • Consider doing this automatically once a day or once a week.
  • Rebuild Indexes
  • Rebuild Tables (bcp data out/in)
  • Dump / Reload the database (drastic, but might fix corruption)
  • Build new, more appropriate index
  • Run DBCC to see if there is possible corruption in the database
  • Locks / Deadlocks
  • Ensure no other processes running in database
  • Especially DBCC
  • Are you using row or page level locking?
  • Lock the tables exclusively before starting the query
  • Check that all processes are accessing tables in the same order
  • Are indices being used appropriately?
  • Joins will only use index if both expressions are exactly the same data type
  • Index will only be used if the first field(s) on the index are matched in the query
  • Are clustered indices used where appropriate?
  • range data
  • WHERE field between value1 and value2
  • Small Joins are Nice Joins
  • By default the optimiser will only consider the tables 4 at a time.
  • This means that in joins with more than 4 tables, it has a good chance of choosing a non-optimal query plan
  • Break up the Join
  • Can you break up the join?
  • Pre-select foreign keys into a temporary table
  • Do half the join and put results in a temporary table
  • Are you using the right kind of temporary table?
  • #temp tables may perform much better than @table variables with large volumes (thousands of rows).
  • Maintain Summary Tables
  • Build with triggers on the underlying tables
  • Build daily / hourly / etc.
  • Build ad-hoc
  • Build incrementally or teardown / rebuild
  • See what the query plan is with SET SHOWPLAN ON
  • See what’s actually happenning with SET STATS IO ON
  • Force an index using the pragma: (index: myindex)
  • Force the table order using SET FORCEPLAN ON
  • Parameter Sniffing:
  • Break Stored Procedure into 2
  • call proc2 from proc1
  • allows optimiser to choose index in proc2 if @parameter has been changed by proc1
  • Can you improve your hardware?
  • What time are you running? Is there a quieter time?
  • Is Replication Server (or other non-stop process) running? Can you suspend it? Run it eg. hourly?

分享到:
评论

相关推荐

    oracle to sybase迁移向导英文版

    了解Sybase ASE的性能调优机制可以帮助提高迁移后系统的整体性能。 #### 四、数据与SQL迁移 ##### 4.1 迁移数据 迁移数据时需要注意的关键点包括数据类型的映射、数据转换规则等。 ##### 4.2 迁移SQL应用代码 ...

    Oracle向Sybase ASE迁移指南

    为了确保新系统能够在生产环境中稳定高效地运行,还需要进行性能调优。这包括: - **索引优化**:根据查询模式和数据分布来优化索引。 - **查询优化**:分析慢查询日志,对性能瓶颈进行定位并优化。 - **资源分配**...

    MySQL向Sybase ASE迁移指南

    包括索引优化、查询优化等基本调优技巧,帮助提升性能。 ##### 3.13 其他管理员任务 还包括监控工具的使用、空间管理、事务跟踪等日常管理任务。 #### 四、管理差异 从MySQL迁移到Sybase ASE的过程中,还需要...

    Sybase ASE 15.7 开发文档:系统管理指南(卷二)

    Sybase ASE 15.7 开发文档:系统管理指南(卷二)共两卷 第 1 章 限制对服务器资源的访问 第 2 章 镜像数据库设备 第 3 章 配置内存 第 4 章 配置数据高速缓存 第 5 章 管理多处理器服务器 第 6 章 创建和管理用户...

    SQLAnywhereStudio9DevelopersGuide

    此外,还会有专门的章节讲解性能调优,包括查询优化、内存管理和数据库参数调整。 对于开发者来说,接口和API的使用至关重要。书中会详细介绍如何使用SQL Anywhere提供的SDK,如JDBC、ODBC和ADO.NET,进行客户端...

Global site tag (gtag.js) - Google Analytics