To list locks held by a current session

select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
object_name(rsc_objid) tablename,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type ='LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and rsc_dbid = db_id()
and req_spid = @@spid
order by spid

Example
BEGIN TRANSACTION
INSERT INTO TEST VALUES ( 1 )

Output
spid dbid ObjId tablename IndId Type Resource Mode Status
—— —— ———– ——————————— —— —- ————– ——– ——
52 5 0 NULL 0 DB S GRANT
52 5 2105058535 TEST 0 RID 1:143:0 X GRANT
52 5 2105058535 TEST 0 PAG 1:143 IX GRANT
52 5 2105058535 TEST 0 TAB IX GRANT

• SQL Server
• Tuning