Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #1 
If hard-nosed techie-talk is not your thing, please ignore this post. I have noticed that the Primary Key indexes on the eEvent and eAlert tables were very badly fragmented. Both were between 90% and 100% fragmented. I rebuilt them on my system a few days ago, and they are back at 15% to 20% fragmented already.

Why is this? Well, the main reason is they are very badly designed. They are both clustered. Now it may seem like a nice idea to have a clustered Primary Key index. It may be, but only if the data is inserted in that order. If it is not, you are going to fragment the table very fast.

Why? Because a clustered index keeps the actual physical table structure in the order of the index. If I insert a record in the ‘middle’ of this index, the remaining records below it need to be moved down. In a non-clustered index, it can be added to the end, and the index gets updated (in many clever ways) to minimise the disruption to the index itself.

Why is this such a problem? Because these are the two tables that get added to, and indeed removed from (for eAlert) the most. Every eEvent and eAlert entry made, and there are lots, causes the entire table for every Folder created after that folder to be moved down a row, Of course, SQL Server does not do this a row at a time, but in a block, so there will always be empty spaces to use. It still seriously degrades performance and space usage, however.

With the eAlert table, once you set ‘delete deletion alerts’ to true, records will be continually deleted. As new records are added, the table will suffer the same problems as eEvent, however.

The solution:

Make the eEvent and eAlert Primary Key indexes  non-clustered.

Note that SQL Server creates Primary Keys as clustered by default (almost certainly the root cause of the problem, and it was just propagated from there). See the following article for way to prevent that:
http://strangenut.com/blogs/dacrowlah/archive/2009/01/18/changing-a-clustered-primary-key-to-non-clustered-in-sql-server-2000-2005-2008.aspx

   1. Open the table in design view in SQL Server Management Studio (right click on the table in the object explorer, click 'Modify')
   2. Right Click anywhere over the column definitions, from the context menu click 'Indexes/Keys'
   3. Select your primary keys index by name in the right listbox
   4. In the right pane, under the section titled 'Table Designer' find the item labeled 'Create as Clustered' and change the 'Yes' to 'No'.
   5. Click close, then close the design view of the table, and choose the option to save.

Or use the following script:
ALTER TABLE eEvent DROP CONSTRAINT ePKU_eEvent

GO
ALTER TABLE eEvent ADD CONSTRAINT ePKU_eEvent PRIMARY KEY nonclustered
( eFolderID, eEventID )
GO

ALTER TABLE eAlert DROP CONSTRAINT ePKU_eAlert
GO
ALTER TABLE eAlert ADD CONSTRAINT ePKU_eAlert PRIMARY KEY NONCLUSTERED
( eFolderID, eUserName )
GO

Note that you should test this on a development or test database first to make sure no functionality is affected or performance impaired. We have tested it in 9.0.3 and seen no ill effects.

While you are at it, you may possibly improve the use of the eAlert table a little with the following adjustment. I have not proved that the SQL will use this, but it certainly should, as the Watch or To Do list is selected, then the Process:
DROP INDEX eIX_eAlert1 ON eAlert

GO
CREATE NONCLUSTERED INDEX eIX_eAlert1 ON eAlert
(
eUserName,
eAlertType,
eMapName,
eStageName
)


Now with Oracle? I have not tried it, but I do know that I have to rebuild the indexes regularly. I may well do the Oracle scripts sometime soon.

__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #2 
Well, in 9.3 this has still not been properly addressed, and the index is STILL clustered in SQL Server. The real problem is that the index added is almost useless:
Quote:
CREATE INDEX eIX_eAlert1
    ON eAlert ( eUserName,eMapName,eStageName )


Because the client always show either the To Do or Watch list, and so the search order is:
eUserName
eAlertType

The possibly eMapName,
then possibly eStageName

Seriously, is it so hard? This has a small but significant effect on performance for each user. When there are many users, or any user has a large To do or (more commonly) a large Watch list, it can be very great.

It seems so very simple to fix, but it just never has been.

__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #3 
Astounding:

Looking at the SQL Server scripts (not in the Oracle ones AFAIK), there is a second index created that is valid, but only if 'eDeleteDeletionAlerts' is set to zero (ie if deletion alerts are left in the database).

It should still be created, even if this is set to zero! It is still required because we look at either To Do or Watch list alerts, and that means looking at the eAlertType value.

So, 7/10 for effort, but 0/10 for execution, there....

__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!