- Serious performance issues when changing roles
- Posted by Dave Carr on March 2nd, 2004
Just a heads-up: don't change roles during times of heavy
user activity or you may have severe performance issues.
When you change a role, the MSCRMSEcurityService
evidently kicks off a massive job that updates the
securitydescriptor field in every record of every major
object. Last time I did this, it attempted to obtain a
table lock, and as a result, brought my system to its
knees.
The process was trying to update 46000+ rows from the
annotationbase table (you can see this by looking at the
last SQL command for the process that is blocking other
processes). Evidently because it had so many rows to
work on, it was trying to do it 'effeciently', and grab
the whole table, so anything that accessed Notes (i.e.
practically everything) started timing out.
Has anybody run across this, and found some setting in
SQL Server that will stop CRM from taking the system to
its knees? Thanks,
Dave
davidlcarr_removebetweenunderscores_@earthlink.net
- Posted by GreaterThanTwo Media on March 2nd, 2004
Not much you can do Dave, it's the way it's designed.
Mike
greaterthantwo media
Printable Quotes, Orders and Invoices, with line-item details, now available
for MSCRM.
http://www.greaterthantwo.com
"Dave Carr" <anonymous@discussions.microsoft.com> wrote in message
news:4e5801c3fff3$dd640000$a501280a@phx.gbl...
- Posted by Matt Parks on March 2nd, 2004
Sad, but true. It really highlights the need to try to configure the
security as best as you can early in the process. At least then,
there aren't that many rows in the tables when you make changes.
Matt
On Tue, 2 Mar 2004 08:51:14 -0800, "GreaterThanTwo Media"
<nospam_info@greaterthantwo.com> wrote:
Not much you can do Dave, it's the way it's designed.
Mike
greaterthantwo media
Printable Quotes, Orders and Invoices, with line-item details, now
available
for MSCRM.
http://www.greaterthantwo.com
"Dave Carr" <anonymous@discussions.microsoft.com> wrote in message
news:4e5801c3fff3$dd640000$a501280a@phx.gbl...
- Posted by Dave Carr on March 2nd, 2004
Unbelievable. Warning to everyone - do NOT make massive
changes to roles in production!!! MS Support is
supposedly working on a private hotfix for this as we
speak, and let me tell you - you do NOT want to suffer
this performance hit...
Some additional info: when you change a role, it inserts
a row (each circle I believe creates one role) into the
RolePrivileges table and the triggers in there kick off
an incredible number of update statements.
It goes through EVERY table (that has a
securitydescriptor field in it) BU by BU, and, evidently
in a cursor loop, does one update for every single
record. Talk about a monstrosity.
___FULL RANT ON___
When will people - even the sometimes brilliant MS
developers - learn: when you violate the rules of data
normalization in a transaction system, it will come back
to bite you EVERY SINGLE TIME!!!
This securitydescriptor has got to go. I know it was
done for performance reason, but it doesn't seem like
it's working too well to me right now! They sacrificed
some small performance hit for each transaction for a
HUGE, SYSTEM KILLING hit when you do something as crazy
as change a role!
I know the securitydescriptor contains some brilliant
Phd's encryption algorithm that encapsulates all of the
role and privilege stuff, but I have to tell ya, it was
the wrong design.
I have to believe if they used the relational database
the way it was supposed to be used, and created a view
that encapsulated all of the joins to get the security
descriptor information as it exists now, you wouldn't
need this monstrosity that updates each and every row of
every freakin' table each time you make a role change!!!!
In my dreams, everybody who signed off on this killer of
a design has to write this 1000 times on the blackboard:
DO _not_ _EVER_ DENORMALIZE TRANSACTION SYSTEM TABLES
DO _not_ _EVER_ DENORMALIZE TRANSACTION SYSTEM TABLES
DO _not_ _EVER_ DENORMALIZE TRANSACTION SYSTEM TABLES
DO _not_ _EVER_ DENORMALIZE TRANSACTION SYSTEM TABLES
DO _not_ _EVER_ DENORMALIZE TRANSACTION SYSTEM TABLES
DO _not_ _EVER_ DENORMALIZE TRANSACTION SYSTEM TABLES
Arghhh!!!!!!
-- Rant mode off ---
- Posted by Dave Carr on March 2nd, 2004
By the way, the support rep I talked to said one other
customer had this issue, and it took a DAY and a HALF for
the process to complete...
- Posted by GreaterThanTwo Media on March 3rd, 2004
Good rant Dave 
Mike
"Dave Carr" <anonymous@discussions.microsoft.com> wrote in message
news:5a6f01c400ae$428a83e0$a301280a@phx.gbl...
- Posted by John O'Donnell on March 3rd, 2004
:-)
Like everyone else I thought that you could fine tune security over time to
get it right. I decided to go through each of my roles and make one
change...of course after changing 3 roles i noticed the server was a bit
busy :-)
A call into Microsoft confirmed the issue and a support doc has been written
highlighting it but if i had gone through and made all the changes we were
intending to do we would have been in trouble.
"Dave Carr" <anonymous@discussions.microsoft.com> wrote in message
news:5a7101c400ae$7bd654d0$a301280a@phx.gbl...
