We finally fixed our SQL 7 and Radius 2.5 configuration, and I wanted to
share some technical notes about SQL 7 that differ from 6.5 in handling of
triggers.
In the transition from 6.5 to 7.0 M$ changed the way the "inserted"
attribute appears to work in triggers. It would appear that when referring
to "inserted" in SQL 7, the database engine actually seeks out the primary
key to obtain the data from what was inserted, not from memory of what was
inserted (as 6.5 I think did). The result.. if AcctSessionId is not
indexed in the Calls table, the calls_insert trigger takes a *lot* longer
to complete it's task, and all other accounting records getting inserted
into the database become deadlock victims. This makes sense since the
database engine has to hash the data on the fly from millions of calls
records. I checked a backup of our 6.5 database from last month, and we
did not have AcctSessionId indexed in 6.5. This is clearly a difference in
how M$ is handling triggers/tables (either 6.5 automatically indexed the
primary key, or caught the inserted data from memory and not the table..
not sure which one)
Anyone upgrading to 7.0 be sure to run this in Query Analyzer:
CREATE INDEX index_AcctSessionId ON Calls(AcctSessionId)
That will create an index on the AcctSessionId column of the Calls table
and eliminate CPU spike problems and save you the hassle we've been through.
Dale,
That additional index has *completely* removed the CPU spikes. We have not
seen CPU usage exceed 10% with that new trigger built.
--==================================================== /\ Mike A. Miller == /--\ \/ Abraxis Networks ==/ \ B R A /\ I S == / == N E T W O R K S michael@abraxis.com====================================================
For more information about this list, including removal, pleasesee this URL: http://www.iea-software.com/maillist.html