At 02:50 PM 3/17/99 , you wrote:
>Hello,
>
>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, please
>see this URL: http://www.iea-software.com/maillist.html
Thomas
..........................................................................
Inlet Corporation http:\\www.InletCorp.com
800-34-INLET
With knowledge comes sorrow.
The Bible
Every choice we make, determines the life we lead.
For more information about this list, including removal, please
see this URL: http://www.iea-software.com/maillist.html