Clustered Indexes in ASE

1.  We have a table that is effectively a queue, all the IO is on the last
100 or so records added to the table.

Sustained rates of insert at 50 msgs a second occur.

From reading on sybase I have seen some conflicting points of view

On one hand it is suggested that columns of low cardinality should be chosen
for clustered indexes, but at the same time there must be a benifit in
having a clustered index that is firstly  unique and secondly non sequential
(to avoid landing all insert IO on several pages of table)

What is a reasonable guide to choosing an index for a table like this ?

2. The second question is Are there is downsides tro turning lock escalation
off on tables, assuming that all transaction are short and well behaved.






0
Bruce
5/17/2003 3:33:47 AM
sybase.ase.general 8655 articles. 0 followers. Follow

2 Replies
726 Views

Similar Articles

[PageSpeed] 24
Get it on Google Play
Get it on Apple App Store

"Bruce McDonald" <bruce.mcdonald@migasia.com> wrote:
> 1.  We have a table that is effectively a queue, all the IO is on the 
last
> 100 or so records added to the table.
>
> Sustained rates of insert at 50 msgs a second occur.
>
> From reading on sybase I have seen some conflicting points of view
>
> On one hand it is suggested that columns of low cardinality should be 
chosen
> for clustered indexes, but at the same time there must be a benifit in
> having a clustered index that is firstly  unique and secondly non 
sequential
> (to avoid landing all insert IO on several pages of table)
>
> What is a reasonable guide to choosing an index for a table like this ?

If inserts are made by different connections, I think
is vital to provide a high concurrency. An APL clustered
index should be disregarded, as it may either concentrate
inserts on one page or lead to frequent and costly page
splits.

Two better choices are:
- Partitioned APL heap table (no clustered index). It
 allows as many concurrent inserts as partitions
- Datarows locking schema with a clustered index. This
 kink of clustered index doesn't incur in page splits.

Regards,
Mariano Corral
0
Mariano
5/17/2003 1:06:12 PM
Hello, Bruce!
You wrote  on Sat, 17 May 2003 13:33:47 +1000:

 BM> On one hand it is suggested that columns of low cardinality should be chosen for clustered
 BM> indexes, but at the same time there must be a benifit in having a clustered index that is
 BM> firstly  unique and secondly non sequential (to avoid landing all insert IO on several pages
 BM> of table)

This is true only for APL tables. With new DOL tables, some old-manner techniques
such as elimination of contention for last data page become unnecessary.
I whould look at DOL tables, if I were you.

 BM> 2. The second question is Are there is downsides tro turning lock escalation off on tables,
 BM> assuming that all transaction are short and well behaved.

I don't know any, assuming you provide enough number of locks for your transactions.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


0
Ilya
5/19/2003 2:46:09 PM
Reply:

Similar Artilces:

ASE to ASE
Hi, Is anyone out there doing ASE to ASE replication using SQL remote? If you are have created your own version of the SSEXTRACT utility to set up a replicant database in ASE rather than ASA? Or does anyone know if SYBASE has created a version SSEXTRACT for use with ASE to ASE replication? Thanks in advance Doug Trainer Hi Douglas, I thought the SQL Remote Replication support in ASE11.5 was meant for a consolidate-database only (by design)! At least that was what Sybase said when they announced support for SQL Remote technology support in ASE11.5. I will be happy to...

ASE and ASE
Could it be possible to take scripts from ASA and load them in an ASE Any information is welcome jean-fran�ois ASA supports a fairly large sub-set of Transact-SQL, so if you write your stored procedures and triggers in ASA using T-SQL, you should be able to create scripts that will run against both ASE and ASA. If you're planning to do this though, I would suggest developing your database schema against ASA, since everything you write in T-SQL in ASA will be supported on ASE, but the reverse is not true. Check out the section in the ASA documentation entitled "Tra...

ASA To ASE - VS
What are the differents in terms of configuring SQL Remote Between ASA to ASE and ASE To ASE ? You should start with the Help file and then ask specific questions: Data Replication with SQL Remote PART 5. Appendix APPENDIX A. Enterprise and Anywhere: Differences There are many differences, so begin there. -- David Fishburn Sybase Please only post to the newsgroup BH Ong <bhong@tm.net.my> wrote in message news:01bf4b98$33183580$7ccdc8c8@virtual-branch... > What are the differents in terms of configuring SQL Remote Between ASA to > ASE and ASE To ASE ...

Adding ASE to existing ASE-to-ASE Installation
I am using all ASE's in my SQLRemote setup. My current installation consists of a Consolidated ASE & 2 Remote ASE's. My attempt to add another Remote ASE to the existing installation consists of these steps: - Install the sqlremote system objects in the new Remote - Install the stable queues in the new Remote - Manually extract a bcp file of the shared table from the Cons server - Configure the Cons server for another subscriber - Configure the new Remote's shared table, publications, & subscriptions. - Manually b...

Dump/Load (from ASE 12.5.4 to ASE 15.x) - VS.
Hi All, We are in the process of planning the upgrade of our ASE 12.5.4 dataservers to ASE 15.x. What is the most recommended way to upgrade ASE dataservers from ASE 12.5.4 to ASE 15.x? Potential options: 1) Install new instances of ASE 15.x and load database dumps from our ASE 12.5.4 dataservers to the newly installed ASE 15.x dataservers. - OR - 2) Use the ASE upgrade utility (sqlupgrade) to upgrade our existing ASE 12.5.4 dataservers to ASE 15.x. What are the Pros vs. Cons for loading a 12.5.4 database into a 15.x ASE dataserver versus performing an ASE dataserv...

Poor index page cluster ratio; ASE 12.0
I have created a fresh clustered index but dbcc is reporting index page cluster ratio of 0.00. What can I do to improve this? Thanks. In article <EQKn#4KrCHA.287@forums.sybase.com>, Vikas- ramrakhiani@deshaw.com says... > I have created a fresh clustered index but dbcc is reporting index page > cluster ratio of 0.00. > What can I do to improve this? > Thanks. > > Hi, Take a look in the Performance & Tuning Guide (http://sybooks.sybase.com) for "update statistics". You will want to pay special attention to "update statistic...

ASE and Clusters
Hi, Anyone know if exists any ASE version for clusters? (HP Intel Clusters on SCO UnixWare) Or I can use the same ASE version that I have for a common server, for a cluster? We work with ASE 11.9.2.2 for SCO UnixWare 7.1.1 Where can I find any paper about that? Any experience? TIA Gabriel Mautino. ...

Clustered ASE?
Hi, How do you know if a ASE server is part of a cluster? Thx, rick_806 On 18 Nov 2009 15:02:32 -0800, rick_806 wrote: What kind of cluster are you talking about? ASE has SMP and CE (Clustered Edition) products in the current 15.0.3 offerings. Also ASE has an HA option which requires cluster hardware. the sql command "select @@version" is a good start as others can tell you capabilities based upon the product version. You may need more command to see what is configured? "select @@clustermode" -> "shared disk cluster" for Cluster Editio...

sql-remote between ASE and ASE
hello! sql-remote document don't give us help with replication with ASE and ASE. i need detail how to do it. i use ASE12 + NT4.0 + SQL Remote6.0.2 . i test it in the list 1�� define messagetype : smtp 2�� define use_name :one is publisher,the other is remote user_name 3�� define publisher,publication,subscription 4��execute ssremote.exe to receive or send message I failed at the step of extract database .i don't extract the database . please help me ,tell me the datail of sql-remote confige. Thanks a lot , ...

ASE 11.9.2 Error 625 with clustered index
I am using ASE 11.9.2 in HP-UX 10.20. Asynchronous i/o is configured in Sybase and HP-UX. The database devices are created as raw devices in a RAID 5 System. After upgrading from 11.0.3.3 to 11.9.2 I am getting the error 625 when a specific table is accessed via a clustered index on this table. When this index is dropped the error does not come up. I dropped already the index and also the table and created it again without any success. Is there anyone out there who had similar problems and solved it ? Thanks Keith billnia@hyperia.com ...

Replication between ASE and ASE URGENT
HI everybody Can you help me please with the following question. We need to syncronize ASE to ASE ( bidirectional) Initially, it was planned to implement with SQL remote, but it isn't available. With replication server, it isn't a solution beacuse, the customer doesn=B4t have a comunntication channel all time. We think use Mobiliink Server to replictase ASE to ASE, but using an ASA in the middle, so that, first it replicate ASE to ASA, and later, ASA to ASE, is it this feasible ??? Thanks four your answer "Marco" <mbarrion@uniplex.com.ec> wr...

sql remote ase to ase
can I use sql remote ro replicate data form ase to ase or only from asa to ase and asa to asa ? I would also recommend that you read over the following tech doc; Implementing ASE to ASE Server SQL Remote Replication http://my.sybase.com/detail?id=1009730 Ian Bater iAnywhere Solutions Reg Domaratzki wrote: > Just be aware of the limitations pointed out in the documentation when using ASE as a remote database. > > Replication and Synchronization Guide > PART 4. Appendixes > APPENDIX A. Enterprise and Anywhere: Differences > > Limitations ...

ASE workgroup
hi, what the diference between ASE workgroup and ASE Enterprise, to which systems apply workgroup or enterprise. is an AlphaServer 4000 (DIGITAL SERVER 7305) workgroup and unix and ASE 12.0 will be avaliable for true unix (DEC UNIX). thanks in advance ...

SQL Remote ASE
hi did anybody know when SQL Remote support blob (image, text) replicacion for ASE - ASE? our company need this feature. joan caceres electro sur este s.a.a. Hello Joan, Support for image and text replication with SQL Remote went in as of ASA 6 mainline, 6.0.01.1256, and SA maintenance 5.5.04 build 1938. Regards, Bob Bauer Sybase Technical Support jmcd wrote: > hi > > did anybody know when SQL Remote support blob (image, text) replicacion for > ASE - ASE? > > our company need this feature. > > joan caceres > electro sur este ...

Web resources about - Clustered Indexes in ASE - sybase.ase.general

Clustered file system - Wikipedia, the free encyclopedia
A clustered file system is a file system which is shared by being simultaneously mounted on multiple servers . There are several approaches to ...

Even storage that isn't clustered wants to be
Soon it will seem like every storage vendor with a name will have a clustered storage box. Why? In a word: Web 2.0. It would appear that your ...

MegaDroid: 300,000 Androids clustered together to study network havoc
Sandia researcher David Fritz standing in front of a cluster of Android virtual machines, holding two Galaxy Nexus phones. Sandia National Laboratories ...

Sales incentive growth clustered around brands with few CUVs, trucks
Filed under: Honda , Kia , Toyota , Volkswagen , Earnings/Financials While it's arguably been around the longest, the dominance of the four-door ...

Hubble Space Telescope captures 4 galaxies clustered together illuminating brightly in deep space
Deep in space a group of 4 galaxies including a galactic cannibal are illuminating brightly. The image of the quartet of galactic neighbors has ...

Kids with no vaccinations clustered in some schools
Some Ohio schools might as well have a target painted on the side of the building as far as public-health experts are concerned. In some schools ...

NetApp Releases Clustered ONTAP New Software Technology For Big Data and Cloud Infrastructure
Big data and cloud infrastructure is creating an innovation boom and massive business opportunity. Anytime a market is going under radical reformation ...

Clustered Spires Golf Course
We offer deals from local businesses, including our popular Dining Deals, where you can save 30 to 50 percent on your next meal. Special for ...

Sponsored post: NetApp uses clustered data ONTAP to offer open approach to the cloud
As the unabated adoption of cloud technology continues at a truly explosive rate, more and more CIOs and IT leaders are embracing a critical ...

MusicStore fails to run against sql azure database unless clustered indexes are explicitly added for ...
... worked with older builds To fix this error, I had to add this statement to every entity builder.Entity ().Index("Id").ForSqlServer().Clustered(); ...

Resources last updated: 1/5/2016 9:09:07 AM