March 25, 2008 at 2:06 am
Hi,
Does anyone know if there's a way to have Management Studio display the locks acquired as part of an Estimated or Actual Execution Plan?
March 25, 2008 at 4:05 am
AFAIK: No.
Locking is also dependant on the type of connection isolation level or command isolation level you are using. Hence, no info regarding execution plan.
However, you can launch sql profiler and execute it an see what it locks at that run. (Keep in mind profiler may have a performance impact to your instance)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 25, 2008 at 4:15 am
Hi
Not in SSMS, but u can use sys.dm_tran_locks or system monitor.
"Keep Trying"
March 25, 2008 at 5:28 am
Thanks to all for your suggestions.
Specifically, I'm curious about tables with non-clustered indexes. When I do a select, the table and the index need to locked. Likewise an update or insert will do the same, although in a different order, which can lead to deadlocks.
If anyone knows of documentation and/or a good book, which describes the order of locking in such scenarios, I'd be interested.
Thanks,
Jamie
March 25, 2008 at 6:52 am
The order of locking depends on the query being run. That's why two different queries can lock in different order leading to deadlocks. You can usually spec it out in broad terms by yourself. If you have a read followed by an update, you'll see a shared lock followed by an update lock. For details on locking, I'd look at Inside SQL Server 2005 "The Storage Engine."
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 25, 2008 at 3:32 pm
Grant Fritchey (3/25/2008)
...For details on locking, I'd look at Inside SQL Server 2005 "The Storage Engine."
...pages 345 ff 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 26, 2008 at 5:26 am
And the place you want to go to look at these locks as they occur is sys.dm_tran_locks (p. 346 😛 ).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 26, 2008 at 10:17 am
allough this is a sql 2000 script it works in sql 2005 too and gives a more readable output of locks - you'd have to suspend your query by using a transaction I suspect, you may find it useful.
http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/02/13/viewing-object-locks-in-sql-2000.aspx
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply