SQL Server Performance Tuning Part 5

                                                   LOCKS

Locks:

 SQL Server holds a specific object (tables, database, pages, rows, instance, extent, key……etc) by using this locking concept.

 Locks are very useful to provide consistence data or right data or correct data to the end user

 SQL Server cannot lock the resources.

Note: Lock internally managed by lock manager and takes the decision depend on the transaction what lock to be applied.

LOCK RESOURCES:

ROW LEVEL:  Row identifier used to lock a single row with in a table.

PAGE LEVEL:  8 kilo bytes (kb) data pages or index pages.

(The page is nothing but the fundamental unit of data storage in SQL Server where actual data present)

EXTENT LEVEL: Contiguous group of eight data pages or index pages.

( An extent is a collection of eight physical contiguous pages. )

TABLE LEVEL:  Entire table including all data index.

DATABASE LEVEL: Database

KEY LEVEL: row lock with in an index used to product key ranges in serializable transaction.

How to find locks:

SP_LOCK

       

OR

SELECT * FROM SYS.DM_TRAN_LOCKS

               

Output Columns:

Resource type [Database or Page or Object or Row or Extentor table]

Request mode [Lock type]

Request type

Request status [Grant or Wait]

Request Session id

How will u find out which session is doing what work?

SP_WHO2

TYPES OF LOCKS:

1. Shared lock[S]: Multiple users can able to read the data on specific resource. No transaction or query need to wait.

 When transaction starts internally lock manager applies shared lock and once reading completed lock revoked automatically.

2. Exclusive Lock[X]: When we perform any insert and delete operations then an exclusive lock (X) will be placed on the resource.

Note: Always lock manager gives the priority to DML operations compare to any select queries.

3. Update Lock [U]: Whenever we perform any update operations then update lock placed in SQL Server.

Update lock calls most of the time exclusive lock (X) by lock manager.

4. Schema Lock (SCH-L): When performing any locks at schema table then lock manager raise Schema level lock.

5. Bulk Update [BU]: Bulk update lock generally placed by lock manager when there are any bulk transactions.

Ex: Insert into, bulk into, select into

6. Intent lock: Indented to apply desired lock on a  particular lock.

3 Types:

1. Intent Shared [IS] -- Indented to read the data

2. Intent Exclusive [IX] -- Intended to write the data

3. Shared with Intent Exclusive [IS] --

Lock Escalation: Process of converting a lot of low level locks such as row level , page level locks into higher level locks such as table level of row level is called as Lock Escalation i.e .

 Instead of multiple row level locks better is table level lock. Which reduces number of locking types and improves the performance by escalating lock

 Instead of multiple page level of locks better is database level lock.

 This decision of Escalation is taken by SQL Server Engine.

 SQL Server supports escalating the locks to the table level. The locks can only be escalated from rows to the table or pages to the table level.

RID --> Pages --> Tables --> DB

Note: In SQL Server locks  can be maintain by lock manger Users or DBA does not have any consoling locking system .