SQL Server Performance

row locks and page locks set to off on primary key,what will it do

Discussion in 'ALL SQL SERVER QUESTIONS' started by javed rehman, Oct 16, 2012.

  1. javed rehman New Member

    I created some scripts using one of the dba tools that will generate create table scipts for me, the default of these scripts is setting allow page locks and row level locks to off on primary key. Does this mean that each write operation will be done via a table lock? also since these row and page locks are turned off basically on primary key does this mean that table lock will be acquired only if sql has a primary key in it, which will be the case probably 99% of the time. just some questions? thanks

    Javed
  2. FrankKalis Moderator

    Welcome to the forums!
    If both row and page locks are turned off on the index that enforces the Primary key constraint, then SQL Server will take locks at the table level to maintain the index. If there is no Primary key constraint, well, obviously there is no reason why SQL Server will ever decide to take a lock on that index. :)
    Anyway, I'm inclined to think that there has to be a VERY good reason to deviate here from the default ON setting. Off top of my head, I can't think of such a reason, so I would test these scripts and their effect extensively before you run them against a production database.
  3. javed rehman New Member

    Thank you very much frank, I knew all along that row level and page level locks being off on primary key since having a table without a primary key is possible but I have never seen it in my years of IT is a bad idea and you have confirmed it and I confirmed it as well by creating the schema as generated by these scripts and guess what thousands of deadlocks, so yes it should be turned on and that is exactly what I am doing. thanks

Share This Page