About me: My name is Maria Zakourdaev. I have more than 10 years experience with SQL Server (starting from SQL Server 6.5). The last five years have been spent mostly on benchmarking different SQL Server features and flows, like data import, indexes impact on DML flows, star transformations in RDBMS, Hierarchic queries and custom OLAP-like aggregations. I was a speaker in the Microsoft Teched (Israel) on the SQL Server track. I am also an active member of the Israel SQL Server Group.

Tuesday, June 1, 2010

See all locks that were issued by your query

If you ever wondedered what king of locks your selct query ever issues and for how long, run it in QA environment with isolation level Repeatable Read, which holds all locks you select issues till the end of transaction. Default isolation level Read Committed keeps query locks only till select query has finished running so in most cases you are not able to catch them in sp_lock if your select works fast enough.

So, add"SET TRANSACTION ISOLATION LEVEL REPEATABLE READ" inside transaction, then open another window and check out EXEC sp_lock and in some cases you will be surprized how many locks sql server places and removes during simple queries.

Labels: , , , , , , ,


Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home