Databases Tuning

Virat,

What exactly do you mean by database tuning? Robert and Vitaly mention how to tune a table for SQL optimization, but what exactly were you hoping to achieve?

I wonder if you meant something like configuring the memory settings for an instance, for which I'd recommend starting by reviewing this page of documentation:

Architecture > Scalability >Scalability Guide > Vertically Scaling InterSystems IRIS

If you have not already, migrate to solid state disks.

It is weird that the page Vic recommends does not mention that.

DB tuning has many aspects:
1. When the DB is a SQL one (persistent classes) than as some said a "tune table might help, but this will help mostly with SQL queries, by optimizing the use of (correct) indices.
There are other SQL tools on the portal (query plan, sql index usage etc.) that might help to see if the indices are correct.

2. Using the correct data types for your data (e.g. using %Date data type to store dates in $horolog format is much more effective than a %String (YYYY-MM-DD) on large scaled databases, especially with indices where cache memory can not hold most of the DB or with systems with huge number of transactions).
BTW, this is true to both persistent classes (that you access qwith SQL) and "raw" globals that you access with COS (or other language).

3. In a large scald DBs if some of your queries are "heavy" - code optimization might also be a consideration (e.g. replacing SQL with COS that does direct $Order on the index globals).