May 24, 2015 at 2:17 pm
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.
May 24, 2015 at 2:53 pm
That's definitely not normal.
I'd check into what that session is running and what isolation level it's running under..
Even with RCSI enabled, sessions can explicitly set other isolation levels, and DML will still take locks.
It could be either a long-running/uncommitted UPDATE/INSERT/DELETE, or a session that explicitly set the transaction isolation level to, say, serializable.
Hopefully that gives you some idea what to check.
Cheers!
May 25, 2015 at 2:37 am
Hi Jacob,
Thanks for the response!
I would like to highlight the bifurcation of count of locks in the SPID (or one transaction) :
resource_typerequest_modecount 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.
May 25, 2015 at 10:45 am
Thanks for the information!
What does running the following query show?
SELECT
last_request_end_time,
host_name,
login_name,
program_name,
open_transaction_count,
is_active=ISNULL((SELECT 1 FROM sys.dm_exec_requests req WHERE req.session_id=ses.session_id),0),
transaction_isolation_level,
text.text
FROM sys.dm_exec_sessions ses
INNER JOIN sys.dm_exec_connections con
ON ses.session_id=con.session_id
CROSS APPLY sys.dm_exec_sql_text(con.most_recent_sql_handle) text
WHERE ses.session_id=<put the offending spid here>
My hunch would be that it was a long running transaction that never got committed or rolled back, but the results of that query should help pinpoint what it is/was running, whether there's an open transaction, and whether it's active.
Cheers!
May 25, 2015 at 4:30 pm
Hi ,
Modified the script a bit as :
SELECT
last_request_end_time,
host_name,
login_name,
program_name,
sysr.open_transaction_count,
is_active=ISNULL((SELECT 1 FROM sys.dm_exec_requests req WHERE req.session_id=ses.session_id),0),
ses.transaction_isolation_level,
text.text
FROM sys.dm_exec_sessions ses
INNER JOIN sys.dm_exec_connections con
ON ses.session_id=con.session_id
inner join sys.dm_exec_requests sysr
on ses.session_id=sysr.session_id
CROSS APPLY sys.dm_exec_sql_text(con.most_recent_sql_handle) text
WHERE ses.session_id=SPID
Transposed the output for better readability :
last_request_end_time: 2015-05-26 00:13:41.967
open_transaction_count: 1
is_active: 1
transaction_isolation_level: 2
text: Select Statement
Also
DBCC OPENTRAN shows this SPID has Start time as May 25 2015 11:06:07:127PM .
I am also trying to capture Trace data for this specific SPID . Will share the analysis soon.
If you want to capture any specific counter do let me know.
Thanks.
May 25, 2015 at 4:50 pm
Steel83 (5/24/2015)
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.
Let's first ask the question of "Is the server experiencing any performance problems? If so, can they be traced back to the code this spid is running?"
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2015 at 5:01 pm
Ah, well, in that case it's probably going to be a sticky issue if the query is actually causing any issues.
If it's still an active transaction and has been running for over 24 hours, then even killing it could be horrendous because the rollback will be quite intensive.
Unless it's causing problems (causing excessive blocking for example), it's likely best to just let it run its course. Even if it is causing some problems, there may be no better option than letting it finish, if the rollback is going to be nightmarish.
With the amount of time it's been running, I'm curious what it's done to the log.
What's the following query show?
SELECT
session_id,
UsedLogMB=database_transaction_log_bytes_used/(1024*1024)
FROM sys.dm_tran_database_transactions dbtran
INNER JOIN sys.dm_tran_session_transactions sestran
ON dbtran.transaction_id=sestran.transaction_id
WHERE sestran.session_id=<your spid here>
If the used log is incredibly large (as I fear it might be), then you're likely stuck with letting it finish, since the rollback could be worse than letting it run.
Cheers!
May 26, 2015 at 12:27 am
Hi ,
Please find the below response :
I have addded DB name in to the output list :
Now the time for same SPID has been changed :
Start time : May 26 2015 6:53:48:313AM
and the transaction is having two entries :
SPID 110
DB NAME : tempdb and UsedLOgMB : 0
DB Name "USer DB Name" and UsedLOgMB : 110
Also regarding performance, we are experiencing sluggishness in system during working hours. The application becomes slow and the upload or retrieval of documents becomes slow. During that time the CPU utilization of DB server also shoots upto 70 to 80 %. To overcome CPU issue, I have applied few indexes which has given me good results but want to analyze this locks part.
Moreover in yesterday's trace I have figured out during the execution of above mentioned transaction SQL lazy writer was also acquiring locks on same database objects. Shall we direct our attention towards that side also.
Regards
May 26, 2015 at 2:09 am
Small correction to my earlier response :
It is Ghost cleanup process acquiring locks not the lazy writer.
Sorry for confusion.
Thanks.
May 26, 2015 at 8:33 am
Well, we probably have two separate things going on here then.
First, on the original subject of the session with all the locks, the fact that the log used for that session's transaction is relatively small, coupled with the fact that the start time has changed indicates that the previous transaction completed.
Is that session still showing as having about a million locks?
The sluggish performance might not be related to that session at all, so the second thing to do would be to approach that more systematically.
You'll definitely want to start capturing wait stats at intervals so that you can see what the waits look like during times of normal performance and times of sluggish performance, as well as be prepared to examine what is running on the server when the performance starts becoming unacceptable.
For immediate help with that, Brent Ozar's site has some material that could help you, perhaps starting with http://www.brentozar.com/responder/.
More long-term, there are also a couple good books for free here at http://www.sqlservercentral.com/Books/ that could help, notably Troubleshooting SQL Server: A Guide for the Accidental DBA (a useful refresher even for non-accidental DBAs) and Performance Tuning with SQL Server Dynamic Management Views.
I hope this helps!
May 28, 2015 at 2:29 pm
Hi Jacob,
Thanks for the help so far !!!
I have figured out the cause of such enormous locks. 🙂 🙂 There was a cleanup job scheduled on documentum server and was trying to delete approx 120 thousand records in one batch. Also the cause of ghost clean up to wait was the deleted records. Although the records were deleted but not committed and thus the locks from ghost clean up were ending in lock time out.
Told developers to remove the records in small batches.
Appreciating your help on this topic.
Thanks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply