Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Troubles using sp_indexoption to disallow page and row locks Expand / Collapse
Author
Message
Posted Friday, July 23, 2004 7:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 10, 2014 6:38 AM
Points: 143, Visits: 50

Are there any trouble or negative hints when you use the stored procedure "sp_indexoption" to disallow page locks? I am using them to disallow rowlocks in some tables and no problem had been detected so far, but I found several comments about being carefully when using this option. I experiment many "deadlock" errors. Some SPs uses "insert" in batch, firing triggers that uses "updates" in same inserts, locking the index processes and causing the "deadlocks" when in concurrency with an others "spids". I think that better way is disallow index locks. In my tests this option are full functional. Does any problem may occurs in the future?

Post #127904
Posted Friday, July 23, 2004 8:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 2, 2005 11:49 AM
Points: 2,611, Visits: 1
I see some ERP application does that to some of tables. But you really have to resolve the deadloacks from application/database re-design.


Post #127958
Posted Friday, July 23, 2004 8:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 10, 2014 6:38 AM
Points: 143, Visits: 50

Thanks Allen,

I agree with you but my work is in government organization and there are more than 2000 users and several SPs running with this method. It's amazing since many tables should be change triggers by check constraints. Unhappily I no have a choice in this moment to do this (but I really will do an re-design in future). You see any trouble in apply this solution or I really try to fight to change my mind. SOS. I start in my job less than a month for SQL tuning in an institution where everyone has been done what want. Thanks again and if you have any other suggestion, please help me.

P.S. Sorry for my poor english.

 

 

Post #127967
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse