Very huge count of rows in sys.dm_tran_locks

SQL Server Administration
1

Hi Experts,

I am having a QA system with SQL Server 2008 R2 , SP2 standard edition. The host is a VM with 8 VCPUs and 32 GB of RAM where in 28 GB has been allocated to SQL Server.

This configuration is hosting a database in snapshot isolation level = on and read committed snapshot = on.

While checking sys.dm_tran_locks, we have found that there are total 1010072 rows or sometimes even more than that. Also all these rows are associated with a single SPID.

Is it a normal SQL server behavior ?

Thanks.

2

All the same type of lock? What type(s) specifically?

3

Thanks for the response!

I would like to highlight the bifurcation of count of locks in the SPID (or one transaction) :

resource_type request_mode count of locks
OBJECT IX 17
PAGE IX 19297
DATABASE S 1
METADATA Sch-S 1
OBJECT Sch-S 1
KEY X 676185
RID X 12394

Also the database is running with Parameterization value= forced and the transaction is not causing any blockings.

Regarding the isolation level of statements, the value is always = 2 i.e. ReadCommitted .

This database belongs to application documentum and we are experiencing sluggishness during day time.

Thanks.

4

Given the huge number of Key (X) locks I wonder what's going on at that moment. Thousands of users inserting data? One huge job inserting tons of stuff in one batch?

5

This is a QA server and hardly few users are connected to it. I will check the possibility of a job inserting data into the database.

Thanks.