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 ?