Automatic Database Space expansion feature in ASE 15.7

[lisa_sayre]

Has anyone used the new automatic database space expansion feature in ASE 15.7?  Within our software, I am looking to switch over from using the traditional device creation methods (creating devices large enough to house all of required database data) to creating small devices and setting up the necessary thresholds and expansion rules.

However, since this is fairly new feature within ASE 15.7, I was wondering if there are any issues.

Some of my initial concerns are the following:

1.  Disk fragmentation and how that effects database performance.  Over time, the device(s) will be fragmented physically as space is allocated.  Does this affect performance within the server/database?

2.  When expansion is detected by the server, does the expansion process of the device and allocating of space to the database affect database performance?

3.  Is there a limit to the size of the auto expanding device(s) - other than the amount of physical space available?

4.  Any known bugs/defects within this feature that are show stoppers?

I am in the process of setting up a database with auto expansion, and will play around with it, but thought I would ask the experts for any known issues.

Thank you!

Lisa Sayre

[lisa_sayre · Accepted solution]

One more question,

Does the owner of the database need to have the sa_role assigned to them?

I understand that you need to use the SA user (or a user with SA role privilege) to install the installdbextend script, and to issue the sp_dbextend commands to set up everything.

When the procedure sp_dbxt_extend_db is fired when it detects the threshold has been exceeded, I assume it executes using the owner of the database.  If the owner does not have sa_role assigned, will there be any failures?

Thanks

Lisa Sayre

Thanks

[marcin_najs · Accepted solution]

Hi Lisa.

1.

For installation (running installdbextend script) I would recommend SA user. For other users (with sa_role granted and exec granted on sp_dbextend to PUBLIC) installation failed in my case. It required sso_role and other exec permissios for subsequent procedures... (it is not mentioned in manual).

2.

" If the owner does not have sa_role assigned, will there be any failures? "

No, Database owner does not have to be granted sa_role to expand database automatically ... but you need sa_role to run some of sp_dbextend commands like: simulate, execute, check, reload defaults, trace.

Best Regards.

--

Marcin

[lisa_sayre · Accepted solution]

Thank you for the information!!

Lisa

[marcin_najs]

Hi .

In Sybase ASE you can use 'sp_dbexted' procedure to magage automatic database expantion.

See:

Expanding Databases Automatically

It is available from version 12.5.1 so it is not a new feature.

You must be aware that it cannot manage device creation  but it is very flexible because of threshold action procedures. You can combine your threshold procedures (where you can add your disk init commands) and sp_dbextend mechanism (for resizing devices and db segments).

1. Remember that you can always bind objects to specific segments/devices or/and use REORG command(s) to resolve fragmentation problems on table level.

IMHO: I think that creating multiple database devices is a better solution because you can spread them between physical disks and speed up database writes...You can also benefit from partitioning an parallel queries if partitions are physically separated.

2. Expansion should not affect performance if you extend your disks/databases with reasonable amount of MB/GB. Disk init / resize can sometimes slow down your IO response times (it depends on your physical I/O performance). You should calculate your own amount of extension portion based on performance tests in your env.

3. You can specify growby and maxsize parameters for devices/segments by sp_dbextend.

4. While using sp_dbextend proc. i came across 1 issue (bug?) concerning logsegment expansion (case is during processing by Sybase Tech. Supoprt). Procedure responsible for expanding logsegment sometimes is fired multiple times (which can lead to large logsegment size).

Best Regards

--

Marcin

[lisa_sayre]

Thank you for the information - greatly appreciated!

Lisa Sayre

[jayrijnbergen]

This feature is available on different RDBMS flavors like Sybase, MSSQL and Oracle.

All can support automatic db extension

Basically on all platforms you have the same issue with this feature:

- less control about disk allocation (increasing fragmentation, etc)

- extra free space monitoring on OS level required

Note that on Sybase the device allocation also influences other processes, e.g. dbcc checkstorage can use multiple worker processes, but is limited by the amount of devices used for a database.

For Sybase there might be another issue with reloading you backups on other servers. E.g. for user acceptance testing. The same disk layout is required for the restore.

Most companies I've seen prefer to manage the space allocation manually.

Not much answers to your questions, but I hope it helps

[lisa_sayre]

Thank you for the information - it is very helpful!  Our software has managed device and database space allocation manually for 15 years now.  So, I am hesitant to make the switch, but we are seeing requests coming from Sybase DBA's to manage the space automatically, and product management wishes to go this route.  I just hope it's worth the effort.  Thanks again!

Lisa Sayre