New Question or Feedback

Wonderful, isn't it, what happens when the various performance components are integrated, into one performance whole ... the whole performance ensues ... naturally.

Comment on an Article
Post comments related to an article on the article itself, not here. They will be edited, so that only relevant comments remain with the article, and readers do not have to wade through fur and skin to get to the meat. Comment as you like, but if the article is your question or if you intend your comment to become part of the article, please assist by being concise (I get to Accept/Reject only, there is no drag or copy facility).

New Question
Post your new question as a comment to this article. If it is worthy, I will start an article, otherwise I will reply by email. (If your question is simple or not relevant to readers, you can email me instead, but please note, there are many sites that service simple requests.)

Feedback
Post any general feedback (not specific to a thread) or anything you would like me to change, as a comment here.

Submission
Post a synopsis of your intended article either here, or to my email address.

Let's get this blog rolling !

bxAv110 bxAv110 bxAv110

Tempdb Monitor

Rajasekhar Reddy asked:

Could you please let me know the best way to get alarms before tempdb gets full ?

A method of dealing with tempdb filling up, and analysing the causes (thus preventing them in the future) is provided here. This is a second generation method, useful to all ASE servers, with unknown, untamed tempdb content; the first generation rendition is quite adequate for tamed and controlled databases. This has been implemented and tested in large Production systems. The approach is important, and different from other approaches that have been evaluated, in two requirements:

Rajasekhar Reddy asked:

Could you please let me know the best way to get alarms before tempdb gets full ?

A method of dealing with tempdb filling up, and analysing the causes (thus preventing them in the future) is provided here. This is a second generation method, useful to all ASE servers, with unknown, untamed tempdb content; the first generation rendition is quite adequate for tamed and controlled databases. This has been implemented and tested in large Production systems. The approach is important, and different from other approaches that have been evaluated, in two requirements:
- monitoring and analysis of the conditions that lead to tempdb full (rather than merely reacting to a full tempdb)
- administering Sybase from external scripts (this should be quite ordinary for test and production systems; scripting for Sybase is not discussed here)

Cause & Prevention

Handling the symptom without understanding and addressing the cause is a never-ending story of fighting fires, quite unfulfilling. The two common causes and prevention are identified here, so that the firebugs has be hunted down. tempdb is the busiest database on any Sybase server. Any effort that is made to affect these two areas will substantially reduce tempdb usage, and therefore affect overall server performance.

1 A large tempdb, and the event of it filling up, is caused by the over-use of temp tables; particularly re-creating large result sets as an intermediate step in processing. As the main database grows, tempdb has to grow as well. This is caused by the lack of Normalisation; in fact a large and active tempdb is a classic hallmark of this condition.

2 For Normalised databases, the second cause is poor SQL coding that fails to take advantage of the power of Relational paradigm (joins, subqueries, set-processing, conditionals), and instead reverts to creating intermediate worktables and uses procedural processing (row-processing; cursors or cursor substitutes). That is, the coder has a Relational Database but has coded in non-relational style.

User tempdbs provide a containment factor, however, if the cause is not addressed, these simply spread the pain and present additional administrative tasks to the DBA.

Synopsis & Method

Two thresholds are identified:

1 A Report Threshold for space used by an object in tempdb, below which the object is of no concern, and above which the object is reported (eliminates the multitude of small objects from the report)

2 An Alarm Threshold, for space used by an object in tempdb, which when exceeded, triggers an alarm (e.g. mail being sent to the DBAs).

Both thresholds are expressed as a percentage of the allocated tempdb default segment space, with set defaults of e.g. 2% for objects worth reporting and 20% for objects which cause alarm. If tempdb default and logsegments are not separated, then the percentages should be slightly lower, to compensate for that.

The method consists of three components:

1 A table tempdb..sg_object (not a hash table) which holds a series of snapshots of objects which exceed the Report Threshold. This is placed in model, so that it is created in tempdb on server boot.

2 A stored proc sp_helptempdb_sg which can be executed from anywhere, on any [user] tempdb:
- both Report and Alarm Thresholds have defaults (e.g 1% and 20% respectively) which can be overridden with execution parameters
- identify the objects residing in tempdb, from sysobjects and sysindexes, which are above the Report Threshold
- store them in tempdb..sg_object
- report such objects for the current execution [unless executing Silently]
- report "ALARM" (an additional column) for objects that exceed the Alarm Threshold

3 A shell script sg_monitor_tempdb, within the structure for normal DBA scripts:
- invoked by crontab every minute or every five minutes
- execute sp_helptempdb_sg
- capture the output_report
- grep the output_report. If "ALARM" is found:
--- mail output_report to admin_mail_list (e.g. same list as for "dump failed" condition)
--- [if login exists in a kill_list, kill spid]

Analysis of Cause

Note that tempdb..sg_object remains for inspection of specific causes, via ordinary simple SQL, logins, objects, and applications can be readily identified. Over time, patterns and trends can be analysed. It is simple enough to connect a spreadsheet application to it and analyse the content. This provides the basis for identification and thus prevention of the cause of large objects in tempdb; the ability to approach the culprits with evidence; charge-back for resource usage on a user-pays basis; etc.

The table may be filtered and archived into a permanent table in a user database, where longer term trend analysis is relevant.

Related

Always set sp_dboption 'abort tran on log full' for [user] tempdbs. This eliminates the condition, where many users are hung in log suspend, and waiting for the DBA to clear it; they get aborted and cleared right away.

Considerations

1 User tempdbs may be monitored as well. For sanity reasons, all reports for all tempdbs monitored are placed in the main tempdb.

2 Once the structure is running, the thresholds have been modulated to suit the particular site and tempdb use, and confidence has been established, the enhancement to kill the relevant spid may be implemented.

3 Using resource limits is possible in some but not all circumstances, but the facility itself is limited, and therefore I do not provide recommendations for it.

4 MDA tables could be used. However there are many problems with them, not the least of which is inability to join tables and substantial overhead (as per documentation). In order to use them effectively, one must set up a capture-and-store mechanism. They are thus not worthy of consideration for code that must execute correctly under duress.

Limitations

1 This method captures all forms of tempdb objects except internal worktables (there is currently no method of identifying worktables in Sybase). In any case, worktables are proportionate to temp tables, thus considerate modulation of the thresholds will cover worktables. (Some might suggest that MDA tables provide information re worktables, but that is false, only the number of tempdb tables and worktables per process is provided.)


2 This method does not capture the condition where cartesian products which exceed tempdb size within any single iteration of the monitoring script (ie. one minute). Note that cartesian products s are unintended, and controlled by other means; they are not the same as large intermediate result sets that keep growing.

First Generation

In order to report any segment full, and hopefully do something meaningful if it is logsegment, sp_thresholdaction has to exist in any case. In the first implementation of this method, I modified the threshold proc, to populate tempdb..sg_object, if it were triggered for tempdb default segment at 70%, 80%, 90% and 95% full. That worked well for well-behaved databases and servers, where tempdb is not running riot, where the main issue is spikes in tempdb usage. Those events are usually the result of new code (by new coders, refer Cause [2]), or report tools running without configuration limits. That method stores a snapshot at those specific trigger events only, and is recommended for standard-compliant, quality databases.

For sub-standard databases and anti-social applications, where tempdb does run riot, something more, in terms of capturing the build-up to tempdb full, is required.

Code

Software Gems provides all relevant code for all objects identified here, at a commercial quality level, as a fully integrated part of a Sybase Server & Database Administration suite. That includes housekeeping tasks suh as trimming and archiving sg_object. The code required uses ordinary Sybase ASE facilities, it is simple enough to write (ask questions), and it is presented here for those who wish to write their own. The superiority of this method over others, lies in the approach.

Copyright © 2009 Software Gems Pty Ltd

[Read post] bxAv110 bxAv110

Normalisation Workshop 1 - Niksa & Others

Value
This post is best described as per the title. It is not a top-down formal education re the subject. It has substantial value because it deals with several problems that are common in the real world, that damage the quality and performance of Relational Databases. This provides the method for providing a high quality, high performance database, without breaching Standards and Rules.

There are several elements in the workshop that are of value to Developers (more) and Data Modellers (a bit less). Its value is in the example, so do not get too caught up in the edges and the tangents. The sequence/exchange is worth following because it is virtually a script, based on common myths, and deals with the blocks to learning. Depending on how carefully you inspect each step, there are many small but important items to be noted, in the context of the operating whole Relational Database, and resolving each new requirement between the Modeller, the Database, and the Developer, as they arise.

Warning Since this thread has the back-and-forth nature of a discussion, a workshop, and it goes into detail, it will be long. I will keep it trimmed and up-to-date, as the comments are integrated in to the post. When the discussion is closed, I will publish a concise and abbreviated Revision for posterity.

Value
This post is best described as per the title. It is not a top-down formal education re the subject. It has substantial value because it deals with several problems that are common in the real world, that damage the quality and performance of Relational Databases. This provides the method for providing a high quality, high performance database, without breaching Standards and Rules.

There are several elements in the workshop that are of value to Developers (more) and Data Modellers (a bit less). Its value is in the example, so do not get too caught up in the edges and the tangents. The sequence/exchange is worth following because it is virtually a script, based on common myths, and deals with the blocks to learning. Depending on how carefully you inspect each step, there are many small but important items to be noted, in the context of the operating whole Relational Database, and resolving each new requirement between the Modeller, the Database, and the Developer, as they arise.

Warning Since this thread has the back-and-forth nature of a discussion, a workshop, and it goes into detail, it will be long. I will keep it trimmed and up-to-date, as the comments are integrated in to the post. When the discussion is closed, I will publish a concise and abbreviated Revision for posterity.

Chronology
1 This table was submitted to me for implementation in the database

create table Transaction (
____CustomerId int, -- PK.1
____Date date, -- PK.2
____SequenceNo smallint, -- PK.3
____DebitAmount money,
____CreditAmount money
____)

Along with a report that was required by the user:
result.pdf

It is an excellent example of the common problem I have dealt with hundreds of time, on scores of full implementations, in my role as Modeller/DBA working with a Development team, which is why I am using it in this workshop.

This “table” is easily recognised as a brilliant scratchpad to supply a single user’s requirement. Unacceptable for the existing Relational Database. Unnormalised. Two facts in one place. No 1::1 ratio for PK::Attribute. The code required to deal with this would be as ugly as sin. If implemented, it would form a hard fixed structure, an island in a standard-compliant sea of database, and require special maintenance. (Ask questions if you do not understand any of these points.)

Context
A In order to avoid confusion of roles, at least in this thread, I am the Modeller/DBA, and the people requesting (a) assistance and (b) that their objects be accepted and placed in the database, are Developers.

B I administer Relational Databases, not blobs or data heaps, to Relational and design/implementation standards. That means they are fully Normalised; all objects are integrated with all other objects; Declarative Referential Integrity is defined and maintained by the server. This provides the highest level of performance and Relational power.

C Developers need to understand the application problem and the data; to develop prototype code, perhaps using a scratchpad. As DBA, I can guarantee you that you can do what you want to do in the ANSI SQL RDBMS that I am administering. But it may not be the way that was done on the scratchpad. Both parties have to conform to standards, so that we all speak the same language, and adhere to the same goals and rules for achieving them. These standards are not "mine", they were created for the express purpose to nullifying arguments, by nationally recognised standards bodies. In order to do a professional job, we must understand them and apply them:
- Structured Query Language, IBM 1970
- American National Standards Institute
- Relational Database Management Systems (Normalisation which pre-dates it) E F Codd & C J Date
- And finally an implementation platform such as Sybase ASE, with its extensions

D As Modeller/DBA, guardian of the Related Database as my identified job description in the organisation, to ensure that all users needs are met, within budget and hardware/software constraints. This means I do not allow new requirements which contradict with existing, implemented requirements; and I keep the organisations long-term goals (Data Model) clearly in mind. Developers have less power than users: there is no way some developer's unnormalised, single-user, fast-in-the-isolation-of-their-mind structure will be added to the Relational Database.

E We need to put aside all pre-conceived notions (fixed ideas) re what is "slow"/"fast", etc., and find out honestly what is slow/fast. What may have been slow/fast on the scratchpad in isolation is quite different in the context of a real Relational Database running live with hundreds of users. In order for the learning process to progress as quickly as possible, if at all, it is best that the Developer understands the Normalised article, and codes for that (as opposed to demanding that the unnormalised scratchpad be placed in the database or squealing that the code will be "slow".) Essentially, we need to maintain the identified roles. Slow or fast is irrelevant in isolation, we are working with a corporate Database which is used by the entire organisation; slow or fast will be honestly determined in that context before the end of the exercise.

What is required here uses simple SQL and Sybase, on Relational tables: no "clever" code, with its maintenance problems (ensuring a job for life for the um developer); or "new features"; or blaming others ("we can't do it until Sybase gives us what Oracle has"), are required. What is called for is to make the best use of the facilities and Data that exists.

Back to Chronology

2 In order for the user's requirements to be realised, since the “table” [1] was not Normalised, and since I am the Modeller/DBA, the Developer requested this table to be Normalised. So I did my job, Normalised the table, brought it up to other standards (not discussed here), and implemented it as a Relational Table in the Database, as an integrated part of it, related to existing tables, for use by all the users in the organisation (and not one particular user). Refer file:
DDL.pdf

(Ask questions if you do not clearly understand the change from the unnormalised submission to the Normalised table.)

2.1 In order to ensure that we come up with the intended results correctly, a set of prepared data (known values) is included in "DDL.pdf".

2.2 I have checked, to ensure that the "result.pdf" report is entirely possible; all data can be derived directly from this table, without any fuss or complexity (it may be complex in other platforms, and marvellous new features may be required there, but it is dead simple and very fast in Sybase, now that the data is Normalised and placed in a Relational Database). The code I wrote is merely for the purpose of verification before I make such assurances to developers, users, and bosses. It is "not my job" to produce this code, it may not have all the structures and standards for code, as defined by the organisation. It is the developer's job to provide the code for the user.

2.3 The "result.pdf" shows the correct report as per user requirement has been produced. I have included both the required output report, and the statistics, on one page in the "result.pdf". The statistics are merely to prove that the results were in fact produced, that the SQL required is possible. The statistics can be safely ignored for now, do not get caught up in the tangents, tiny code often executes in 0 ms. Discussion of this before discussing the code, would be premature. Likewise the efficiency (which code is better than which other code) is best left until after submissions have been made, and evaluated in the full context.

2.4 Since ORDER BY Date and GROUP BY Date are required to produce the derived data in the report, a worktable is demanded and cannot be avoided. At no stage have I suggested that submissions should not use worktables. Derived data is ordinary and normal, the consequent worktables are ordinary and normal. The ability to write code to derive columns from data is normal.

2.5 Duplicate data is abnormal. Inability to work with Databases, and write code to produce derived columns is abnormal. Inability to give up attachment to personal, non-corporate data is a severe impediment.

2.6 The code should rely on the data only, and expect to perform Relational functions only. It should not rely on the current Clustered Index, the physical ordering of rows, or the price of fish.

2.7 The stats produced by non-compliant code that relies on 100% duplicate data or other substandard structures, are irrelevant to this thread.

3 The ball is in the Developer's court now, to develop SQL using the existing Database objects (forget about past objects, they were rejected) to produce said "result.pdf".

3.1 Any attachment to obsolete structures and substandard code will hinder the learning process. Better to understand the tables as they are and develop new code. The data may not be where it was, but I can assure that data is all there, possibly in different locations; and completely accessible using ordinary SQL. As per [E], do not worry about joins and subqueries; these are the ordinary constructs required to access and produce Derived Data from a Relational Database; we'll make sure the joins, subqueries and worktables aren't too "costly" before the day is out.

3.2 Do avoid cursors and temp tables.

3.3 The code will be reviewed and upgraded to standard if required, so do not worry about cutting perfect code the first time. This is about learning and understanding, not about perfection or competition.

3.4 If the code-cutter cannot cut code, there are many resources (books, manuals, courses, reading the code of others), and ultimately many people from whom to ask for help. I know a Modeller who knows a thing or two about SQL, who would be happy to help. But asking for help means submitting code (working or not) first, not asking me to code from scratch (that is doing the Developers job for them, and nothing is learned).

3.5 Some Developers have mental blocks, that hinder them from using the existing Database tables, due to their attachment to the spreadsheet-style contents on their C drives. This prevents them performing their duties effectively. The thread expects to address that, as part of the learning process, and overcome that, but that is limited by other person's ability to change.

Go for it

Now code, and submit it. An hour or so should suffice.

Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2009 Software Gems Pty Ltd

Psuedo-relational is a dog's breakfast
True Relational is simpler than you think

Submission 1

Niksa:

I thought the [implemented] table was irrelevant. Sorry.

See, that is exactly the problem. The table has the basis, the seed of the Law, and you did not even notice.

The reason you did not notice, is because all tables are irrelevant to you (only code is relevant to you). To you “tables” come and go, they can be changed to fit the code. Or worse, another “table” can be added, creating a duplicate or triplicate.

In the corporate world, Data is the identified asset, it changes little if implemented properly (gets replaced otherwise); programs, code, come and go, and are cheap to replace.

If coders are going to last in the organisation, they need to learn respect for data (without giving up their respect and ability with code). Real Database data is easy [a] [b] but expensive to change, it affects everyone and many code segments; that is exactly why developers often demand that their own personal or private data "table" is implemented. That leads to massive duplication and maintenance problems, which lead to complete replacement, which we have to avoid. It is better to learn to work with the corporate data; to implement smaller, correct, controlled, standard-compliant changes to the Databases [which I had already done last week]; and to code for that.

a. it took me all of 10 minutes to implement the users requirement in the existing Database, with full standards.

b. it is hard to change, and affects more people, if there are no standards implemented.

Submission 2

Niksa:

Here the output produced by running my own code written in the spirit of [person's] algorithm.
[link]
Does this qualify me to enter the classroom?

Almost, but unfortunately, no.

This is your best attempt so far, though. At least now you are using a Database table. But not the existing Clustered Index. Refer [2.6].

If you can read and follow instructions, which everyone else has to follow, you will get in.

Oh, and post the code, it probably wipes the floor with my code.

Submission 3

Niksa:

... The SequenceNo and the Date in CustomerTransaction table may not be "in sync" ...

There is no relation between SequenceNo and Date. As a Relational standards-compliant DBA/Modeller, even if there were an implied relation, I have to state that it is not identified, determined, or supported. Your code should not rely on it. Use CustomerTransaction.Date only to drive your code. (Note the required report is driven by Use CustomerTransaction.Date, anyway.)

I am open to changing it but that is a physical, low level aspect of the Database. As a Developer, you are required to work with the logical Database only. The physical aspects of the database are "none of your business". If a certain query (not this one) is slow, and needs an additional index, I am happy to look at it.

(As detailed in [2.6], I will not be changing the CI for one developer).

The only relevant order in the report is the order of transactions occurrences (the Date in the Database Table).

The physical order of the rows in the Database Table is not relevant to you, should not be relevant to you, and should not be relied upon by any code segment.

Submission 4

Niksa:

... [not relevant] ...

A couple of Definitions, to assist everyone:

Database: Normalised set of Tables with Referential and Data Integrity:

Report: A projection, a view, a list of data from the Database, produced by Joins and Queries (includes subqueries) using SQL. This projection, by definition, is Denormalised.

Some people get them mixed up.

And a couple of reminders:

a. This is not an accounting class.

b. This is a Relational Database class. It uses an accounting example. Whether the example is applicable to the real world or not, is irrelevant to the class.

Submission 5

Niksa:

... [not relevant] ...

The strong belief that you know the problem and therefore the solution, is actually the block itself. In order to learn, we must take the position that we do not know. Otherwise we sit at the back of the class, making comments about how much we know more than the professor in order to feel better, disrupting the class ... and failing. It is a well-known way of taking the focus off the problem (your inability to write the required code) and blaming someone else (the professor is clueless about the real world, here let me show you how they do it in the real world).

Your focus on the relative value of the exercise (wrong place) is hindering you from performing the exercise (focus on that only). It is like the fat kid who says the wooden horse in the gymnasium that everyone has to jump over, is taller than a real horse. Who cares, just jump over it. Everyone who wants to qualify has to.

If you write physical code, you will be breaking the Logical Rule and your code will be glued at the hip to the physical ordering in the table, which will change, and your code will break. Which is one crucial reason why you have not qualified (and note that qualified people can [a] write logical code, that [b] does not break every time the physical aspects of the Database change.) Physical code will be rejected, as stated from the outset.

Submission 6

Niksa:

OK. I've just corrected myself: UCLI (CustomerID, SequenceNo).
[link]

That is great, excellent progress !

a. The code produces the required result. Using the existing Database Table. Regardless of the existing Clustered Index.

b. It appears to use just one worktable, and one scan of the Database table. In that regard, it appears to be much better than my “proofing” code, but then you are probably a better SQL coder than I. (I did predict that you would probably wipe the floor with my code.)

c. You have submitted evidence that the required report has been produced, but not the code.

d. The problem with your submission, however, is that you identify and emblazon your work as “Unnormalised”. By Definition (your initial request for the new “table” to be implemented; which was determined to be Unnormalised; which means you do not understand Normalisation; this is a Normalisation class), and by Role (I am the Modeller; you are the Developer; you are trying to obtain qualification), you are not qualified to make that statement.

e. By making statements which are contradictory to the exact lesson, you have sabotaged your own submission. In order to qualify (for the class, or in the context of a Developer working with a Modeller and a Relational Database), you will need to refrain from making unqualified and self-contradicting statements.

f. The second problem is that you identify the existing CI as “flawed”. This identifies that you are focused on the physical, an aspect of the Database that is outside your job description, that should be irrelevant to you; that is none of your business as Developer, as per [2.6] and Submission 2. This further determines that you do not yet qualify. You will need to make a statement that the code works regardless of the physical aspects of the Database, specifically that it relies on the Logical Data only, and that it is immune to the current Clustered Index and changes to it.

Heads Up
Typical of an ongoing development project, another user from another country (not the user you work for) has submitted a late requirement, the implementation of which will cause (i) the CustomerTransaction Table and (ii) its Clustered Index, to change. I will not waste your time with the details. Rest assured: as long as your code is standard-compliant, which will be declared as per [f], and verified when you submit it, it will be unaffected by such changes.

[Read post] bxAv110 bxAv110

Normalisation Workshop 2 - Tartampion & Others

tartampion asked:

I would like to ask you to explain the normalization steps in a simple case; for example "Student who have courses with different professors in different rooms of a college", please. We are the development DBAs, We all either understand the importance of normalization or try to understand it. You know it, so help us; give us a hint.

Context
This is not a tutorial (there are good books and courses elsewhere), this is a workshop for DBAs and Developers who have an interest in modelling (Normalising) Relational Databases. Since you are asked me, you will get high performance, and a few 'built-in' standards as well.

tartampion asked:

I would like to ask you to explain the normalization steps in a simple case; for example "Student who have courses with different professors in different rooms of a college", please. We are the development DBAs, We all either understand the importance of normalization or try to understand it. You know it, so help us; give us a hint.

Context
This is not a tutorial (there are good books and courses elsewhere), this is a workshop for DBAs and Developers who have an interest in modelling (Normalising) Relational Databases. Since you are asked me, you will get high performance, and a few 'built-in' standards as well.

In order for the discussion to move quickly, and for everyone to understand what is right and wrong, we need a standard against which to judge ourselves. The standards I apply (without defining them here), in the correct order, and each being a smaller sub-set (or stricter set of rules) within the preceding standard, are:
- Normalisation
- Relational Model (Codd's Twelve Rules)
- IDEF1X

I have done this a few times, so let us run this as a class. The value is in the interaction. I will put something up on the board; you post comments (questions and problems); I will update the board; etc. At any point in time, the Board consists of the following documents which will be kept up-to-date:
- Entity Relation Diagram
- Data Definition Language
(At this stage, a full Data Model does not seem necessary.)

Roles
I am the Modeller, you guys and dolls are the Developers, all working for the same college; we are working together on a new project to produce an Enrollment (and Schedule ?) system. As is usual these days:
- there is no formal systems analysis
- you deal with the users and gather requirements
- you identify app components, GUIs, reports ... and thus identify data requirements
- then we sit down in a workshop we work out the database
- as the project progresses, you identify additional or changed data requirements
- then we sit down in smaller workshops and work out the changes

It will start out loose, and get verified, tightened up, as we progress. I have no problem adding and changing, but I do not like deleting. Which is why I adhere to standards. The data columns and keys are the important part, so you need to supply them, and supply problematic items.

Alright, I have no idea what you need, so the Board is very much a first cut, with the first few obvious entities. I do not know much you know or don't know, you need to ask questions. Your turn.

[Read post] bxAv110 bxAv110

'Reverse' Key (Relational Key) - John Flynn

John Flynn wrote:

It's a known performance problem in Sybase when your primary key has monotonically increasing values and you assign a clustered index to it. Apparently all row inserts go to the hotspot at the end of the table. I guess this is true whether your key is an identity-type thing or a natural key.

Not quite. A Natural or Relational Key will not be a monotonically increasing value. A monotonically increasing value (system or code generated) is a Surrogate key, by definition (the meaning of the word), it is the key that you have when you do not have a Key.

John Flynn wrote:

It's a known performance problem in Sybase when your primary key has monotonically increasing values and you assign a clustered index to it. Apparently all row inserts go to the hotspot at the end of the table. I guess this is true whether your key is an identity-type thing or a natural key.

Not quite. A Natural or Relational Key will not be a monotonically increasing value. A monotonically increasing value (system or code generated) is a Surrogate key, by definition (the meaning of the word), it is the key that you have when you do not have a Key.

1 Apart from responding to your question below, the best advice I can give, after many years experience and many assignments is, there is no substitute for a Relational Key:

1.1 Remember, the Surrogate Key is an additional column, and an additional index. Therefore a guaranteed performance hit for many reasons, in addition the above.

1.2 work harder to identify a relational key that remains logical. Even a contrived key (eg. removal of vowels and double consonants from a name), producing a short name code, is far better than a surrogate.

1.3 See if you can contrive something, even something not "logical" such as CustomerName or State plus SmallDatetime.

2 An Identity is a Surrogate, but a Surrogate need not be Identity.

2.1 Many people use a NextSequentialKey table, which contains one row pre real table, containing the last used key, to generate the next key for each table. Although the Surrogate is a substitute for a Natural Key, this does not break the Logical Rule. This method (or other similar methods) is superior to Identity, in many ways.

2.2 Identity is physical, it breaks The Logical Rule. One of the main issues with an Identity is, the user is not aware of the columns when they insert the row. The code has to insert it, then read it back. Unless performed correctly, duplicate rows are easy to create. The whole idea is to ensure the row does not exist: this requires the clear determination of the rows by using only the columns that the user knows. In which case, the Identity is simply not required.

3 Never use a monotonically increasing value (Identity, next-sequential, datetime) for the APL Clustered index. This is easy to overcome: simple use the monotonically increasing value for an Unique Nonclustered index, and use a column that distributed the data for the APL CI.

(By the way, we have a technique that avoids all these issues altogether and guarantees an unique logical Relational Key, but it is commercial and thus I cannot disclose it. The combination of this and a correct Clustered Index distributes the data beautifully, with or without partitions.)

Anyway, in the specific case when you're using a surrogate key like identity, it occurred to me that isn't there a simple concept to let the values spread near-randomly across the possible range of values? That would be to take the data (depending on its particular binary data format) and "reverse" the bits or bytes? Conceptually, suppose you had a monotonically increasing key that was a three-byte numeric string, so the values went like 001, 002, 003, etc., up to 999. What if each time you generated a key, you reversed the bytes, so the sequence written into the database is 100, 200, 300, etc.? Those keys would be very well distributed across the whole range, yet they'd also be guaranteed unique because they come from the same generating system as before. Thus would that key not become appropriate for a clustered index? The same concept could apply to other datatypes, like a 32-bit binary integer, just reverse all the 32 bits. It's still an integer, but now it naturally distributes itself across the whole range.

This seems so simple, and something Sybase could have built into ASE, like as a new flavor of identity thingy. The fact that they have not done so leads me to think that there must be something wrong with the idea. What is wrong with the idea? I know that if your primary key is a natural key then this concept doesn't help. But what if it's a surrogate key?

Great minds think alike. A certain person (who shall remain nameless) has been trying to influence Sybase to do exactly that for some time. The progress is as follows. First a few items to tighten up the design, which may demonstrate its maturity.

1 Your idea is great but your example is not (a pattern forms after a while, and “random” distribution is not achieved), but that is easily overcome by a well known technique called Hashing the Key, common to non-relational systems, and in a different class of performance, where each of the bytes are hashed separately and then "added" together, which guarantees even distribution. Give the system a key and it will immediately return its computed location.

2 This technique has one limitation: the range of distribution must remain fixed as it is a component in the hashing algorithm. That is not such a big deal in that (a) one must use a value that one expects never to exceed (b) in the event that it is exceeded, simply unload/reload the table. The single read degrades to 1.1 when the table is full.

3 However, the physical space must remain fixed, and reserved in a new way, unlike any current space-allocation-for-table method.

4 It also increases the speed of single-row access phenomenally: the computational hash method eliminates the index altogether (eg. avoid working through a B-Tree height of 7 levels), but you do need the index for other purposes.

5 Upon submission of Hashed Key Tables to Sybase Engineering (not via the hilarious "ISUG Enhancement Request Process" with all of 12 voting members), the person was informed that Sybase had already thought of that; implemented it; that it should be tested and feedback was invited. It was called Virtually Hashed Tables.

6 Further investigation revealed that it was only available on one platform: IBM P-Series Linux, both 12.5.4 and ASE 15.0.2. There is no information re availability on other platforms (there was some talk, but no evidence, that Sybase was planning on releasing it on all platforms; in the enhancement game that means no plans).

7 I do not have that equipment, and thus I have not tested it, but I reviewed the doco carefully and exchanged correspondence on the subject.

8 The implementation has a gross limitation as I have reviewed it: each VHT requires its own private segment. With the 29-segment limit, that is a joke. I can understand the need for a discrete segment, much like logsegment, for any/all VHTs only, and I have no complaint about that, but I baulk at one segment per VHT. The very people who want VHT (performance types) would be using segments, and they can ill-afford to lose one segment, never mid one segment per table.

9 That has been the situation unchanged for two years.

10 The Sybase ASE product manager is not identified, and that is the only person I have not pressed this issue with.

11 Separate to that, I have lodged a public ER through the identified process, to eliminate the 29 segment limit. That is required for other reasons, such as providing the level of high performance available to small and medium-sized databases, to VLDB. The reason I am mentioning it is, this will eliminate [8] as a problem, and deem VHT as useable.

Since the ISUG process is a joke, I would not recommend that you spend money just to vote on the established ERs or push VHT; I recommend you write to Sybase formally, and keep going up the chain.

Finally ...

Back to your general idea, and Rob’s. I recommend against it, because there is a translation barrier: the user does not know the real key (this disallows access to database via tools other than the app; one of the classic results of breaking the Logical Rule) and it has to be translated back and forth at every interaction step. The main point here, again, is to emphasise the value of the Twelve Rules, and to use a logical relational key that is meaningful to the user. For which there is no substitute.

Separately, Rob’s idea of inserting into a surrogate (that word again) Identity table just to obtain the Identity value, while reducing some of the side effects of Identity columns, poses an active contradiction to performance.

Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright © 2009 Software Gems Pty Ltd

[Read post] bxAv110 bxAv110

Data Distribution - Mario Ascione

Distribution of data over different disks for better disk utilization

Without posting the entire thread, which has a somewhat different context, I have picked it up at the point where the unanswered part starts. Mario Ascione posted:

The table has a unique clustered index on the primary key "test_id" [identity] with lock datarows and some non-clustered index.

That is not a relational key, it is a physical, non-relational key which will cause a problem everywhere it is used. Let's look at one instance ...

Distribution of data over different disks for better disk utilization

Without posting the entire thread, which has a somewhat different context, I have picked it up at the point where the unanswered part starts. Mario Ascione posted:

The table has a unique clustered index on the primary key "test_id" [identity] with lock datarows and some non-clustered index.

That is not a relational key, it is a physical, non-relational key which will cause a problem everywhere it is used. Let's look at one instance ...

Filling the table happens what [person] said (first reply):
"Note: Once you place a clustered index on your roundrobin partitioned table, future inserts will be placed sequentially (not randomly) on the various segments on which the partitions reside; in other words, the partitions will become unevenly populated with new inserts until the clustered index is re-created (which will equally redistribute the data, in index order, over the partitions/segments)."

I do not agree with the person's wording, but in the main ... with a view to continuing this post without going back and forth ... ok.

So only the first partition is populated while the others remain empty.

Well, that is what you have told ASE to do. The "clustered" index tells ASE to PLACE the rows in that physical order [it doesn't really but that is a different point, outside the scope of your problem]. The partitioning tells ASE to use RR, to place the rows in the heap (not the "clustered" index) in the four partitions in RR sequence. The Identity "key" is always going to be next-sequential: that means the "clustered" index remains a B-tree with true next-sequential leaf ordering (the space usage is probably not visible to use); but the data rows are RR across 4 partitions. BUT you ran your test from one session, one spid: that does not provide RR; the RR is for each sequential insert from DIFFERENT spids; all inserts from one spid go into one partition.

Basically you have a few contradictions, and ASE it doing "what you told it to do".

There is another issue, it is hard to learn by yourself, making mistakes and correcting them one by one. It is easier to learn from examining good implementations.

I have to drop and recreate the clustered index in order to distribute data evently over the four partitions.

Yes. In this type of situation only. In other situations, definitely not.

AND this is invoking the characteristics of the "clustered" index. But right afterward, as you insert new rows, it goes back to uneven distribution.

Some questions:
How can I avoid drop/create clustered index?

You can't in this case. If the test was more real (multiple inserting spids) the data would be distributed ... but that conflicts with the CI definition you have given it, so "suck it and see".

I've read that It's wrong use CI on a datarows table.

It is not "wrong" to use a CI on DPL/DRL. First, the CI is not a CI ala APL, so do not expect that operation. Second it is PLACEment only; it remains a separate detached B-Tree, but on the same segment as the data heap. Third an Identity or next-sequential number or datetime is the wrong key for a CI. Basically, 90% of the time, people choose the wrong key for the CI on DPL/DRL, and they have incorrect ideas re placement/distribution. Therefore I suggest not having a CI on DPL/DRL, just have NCIs, you lose nothing except the confusion.

Should I use a non-clustered index instead of clustered one?

In this case, that would be a simple way of overcoming the confusion and getting what you intend. Drop the CI; create an NCI on test_id; run your inserts from four spids, and you will get even distribution of data across the partitions.

Thanks Derek for the clarification.....

just one doubt:
what do you mean with "four spids"?
number of worker process? I suppose not (the worker process is an ASE internal thread; I've configured 12 wp).
multiple threads ("session") performing insert operation? yes means that partition table is good "only" in multithread environment.
Others?

Correct. Worker processes are internal to ASE and support parallelism, I wouldn't express it as "only good for multi-threaded environments", the server is beautifully multi-threaded; parallelism needs a multi-processor environment. Each spid/connection will have many WPs.

By "four spids" I mean four sessions, four separate invocations of isql, which will simulate four separate user/client connections in a live system. The inserts of each separate spid/connection will "Round Robin" across the heap partitions in a DPL/DRL table.

[Read post] bxAv110 bxAv110