August 27, 2002 at 6:46 am
I have an application/database migrated from Access. The database is highly
Hierarchical with Accounts, then Policies then Policy Items and Sub Policy
Items.
(i.e. 1 to many relationship from Accounts to policies, 1 to many from
policy to multiple Policy Item tables, 1 to many relationship from some
Policy Item tables to Sub Policy Item Tables) All enforced via Direct
Referential Integrity (DRI).
When I attempt to delete an account with no dependent polices in a single
user environment through Query Analyzer it takes 10 minutes. During this
time I can see exclusive TABLE locks (and intent for exclusive locks) being
placed on some policy Item tables and Sub Policy Item Tables. So far via
the VB/ADO application the Delete appears to work, but the delete gets
rolled back on connection close and the account "reappears" on next login.
I also locked up the production database (no users could even login and do a
select on the account table) in attempting to delete 50 (out of 2000)
accounts.
This database is planned for an OLTP environment with 100-150 concurrent
users. Exclusive table locks are unacceptable. Any suggestions on why this
may be occurring or how to fix it.
This database and database design has been working in Access for over a year
so this excessive locking and slow response by SQL Server has me puzzled.
Brian Freeman
Carnegie Technologies/Bluewave Computing
http://www.carnegie.com and http://www.bluewave-computing.com
August 27, 2002 at 8:02 am
How do you enforce the RI? Do you use the cascading delete options in SQLServer, do you have a bunch of triggers on the tables or is your app making sure everything works?
Did you try executing the delete statement in Query Analyzer?
August 27, 2002 at 8:04 am
RI is enforced via cascade deletes. Delete in Query Analyzer takes 10 minutes. Unable to delete via application (never? completes prior to disconnecting).
August 27, 2002 at 9:57 am
I can imagine that the application time-outs on the statement if it really takes that long.
Have you looked at the execution plan? And are you running the latest SP?
If you can't pin down any problem, try removing the cascading deletes one by one (from the lowest level upwards, of course). Maybe you can identify if there is a problem in one table.
Can you post (simplified) DDL for the tables involved?
August 30, 2002 at 12:55 pm
do you have the appropriate indexes defined? an index on Policy(AccountID) seems appropriate here.
August 30, 2002 at 6:22 pm
Gotta second what omen said. Remember that DELETEs have WHEREs, so they benefit from indexes just like SELECTs do. Make sure that whatever WHERE is being used in your delete is properly indexed. If you just have the WHERE on the parent table, and the others are deleted via cascade, make sure the key fields that determine the FK/cascade relationship are properly indexed.
- Troy King
- Troy King
August 30, 2002 at 11:24 pm
I think this topic is replicated as i did replied earlier.......
Cheers,
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply