Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 14 / 536

Contents
xiv
Working space for inserting, deleting, and synchronizing ..... 447
Options for tuning resource use ................................................... 448
Restricting concurrent queries............................................... 448
Limiting a query’s memory use.............................................. 449
Limiting queries by rows returned ......................................... 449
Forcing cursors to be non-scrolling ....................................... 449
Limiting the number of cursors .............................................. 450
Limiting the number of statements ........................................ 450
Lowering a connection’s priority ............................................ 450
Prefetching cache pages....................................................... 450
Optimizing for typical usage .................................................. 451
Other ways to improve resource use ........................................... 451
Restricting database access ................................................. 451
Disk caching .......................................................................... 451
Using RAM disk..................................................................... 452
Indexing tips ................................................................................. 452
Picking the right index type ................................................... 452
Using join indexes ................................................................. 453
Allowing enough disk space for deletions ............................. 453
Managing database size and structure ........................................ 454
Managing the size of your database ..................................... 454
Denormalizing for performance ............................................. 454
Denormalization has risks ..................................................... 455
Disadvantages of denormalization ........................................ 455
Performance benefits of denormalization.............................. 455
Deciding to denormalize........................................................ 456
Improving your queries................................................................. 456
Tips for structuring queries.................................................... 456
Planning queries.................................................................... 457
Setting query optimization options ........................................ 458
Network performance ................................................................... 459
Improving large data transfers............................................... 459
Isolate heavy network users.................................................. 460
Put small amounts of data in small packets .......................... 461
Put large amounts of data in large packets ........................... 462
Process at the server level .................................................... 463
CHAPTER 13 Monitoring and Tuning Performance ......................................... 465
Viewing the Adaptive Server IQ environment .............................. 465
Getting information using stored procedures ........................ 465
Monitoring the buffer caches ........................................................ 467
Starting the buffer cache monitor .......................................... 467
Stopping the buffer cache monitor ........................................ 472
Examining and saving monitor results................................... 472