THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

You got questions? I got answers!

...Of course, the problem is my answers aren't always the best.

Anyway, as I travel around and speak to SQL Server user groups, I always get a handful of questions to follow up.  Here's a smattering of such questions and their answers.

Q: Does NOLOCK affect lock escalation?

A: Nope.  Lock escalation is now hard-coded into engine.  We used to be able to tune lock escalation ourselves back in the v7.0 and earlier days, but that's been taken away.  Incidentally, the lock escalation threshold is now just over 4000 (rows or pages, depending on the lock method originally chosen by the engine).  So, you can expect SQL Server to attempt to move to a table lock when more than 4000 rows/pages are locked by the engine.


Q: How can you PIN a stored procedure into memory as you can in Oracle?

A: No can do.  However, you might look at FIXPLAN, so that your preferred plan is always used.  Based on SQL Server's LRU (least-recently used) algorithms the lazywriter should not flush a procedure out of cache if it is either very sophisticated or used moderately often.


Q: Do changes in SET connection settings affect other connections?

A: The effects of SET are isolated per connection, unless you've set them on the database- or server-level.


Q: Is there a TSQL command sequence that will allow SQL DELETE statements to be issued without triggering any log growth?
Assume I can change the recovery model.

A:No.  DELETE statements are always recorded in the transaction log, even if the log is purged very often by using the simple recovery model.

On the other hand, if you want to remove ALL records from a table, then you can use the non-logged TRUNCATE statement.


Q: Many a time developers ask for profiler access in Production environment. Can we give profiler access without giving them SA access (SQL Server 2000) and is there a workaround for this. 

A: As far as I know, Profiler needs full rights to the system.  However, you can set up a server-side trace for the developers, run that, and hand over the trace files for the developers to evaluate on their own workstations.  It's not live, but it does avoid giving them inappropriate rights. 



Don't forget that you can ask me and the other SQL Server experts a question anytime by sending an email to  There's also a link to submit questions on the blog homepage of the wiki at  And if you'd like to start an extended discussion, consider the SQL Server forum at Kevin's Korner






Published Tuesday, December 16, 2008 3:50 PM by KKline
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Andy Irving said:

TRUNCATE is minimally logged, but logged none the less

December 17, 2008 4:14 AM

KKline said:

Very true, Andy.  The 64k extents are logged, if memory serves me right, not the individual records.  

This is why I use the term non-logged instead of unlogged.  An unlogged operation wouldn't show up in the transaction log at all.  I apologize that this was completely unclear.

Thanks and good catch!


December 17, 2008 11:16 AM

AaronBertrand said:

Kevin, "non-logged" is still misleading, IMHO.  I would change it to "minimally logged".

Note that TRUNCATE can be rolled back within a certain period of time, so it may not be intuitive for users to understand how that can happen without logging every piece of data.  There are also some severe limitations with TRUNCATE, e.g. it cannot be referenced by a foreign key or indexed view, IDENTITY values are reset to the initial seed, delete triggers are not fired, etc.  When TRUNCATE is not possible then another alternative to prevent blowing up the log is to delete rows in batches (say 10K or 100K rows at a time) and issue checkpoints in between.  If you cannot switch from full to simple then you can perform a log backup between each delete.

December 17, 2008 12:12 PM

Armando Prato said:

And delete triggers don't fire with TRUNCATE

December 17, 2008 12:19 PM

Denis Gobo said:

I agree with Aaron here since only the page deallocations are recorded in the transaction log.

I had that as a SQl Myth here:

December 17, 2008 12:21 PM

cr8nk said:

You can also give developers profile access on 2005 and 2008 without SA access using:


December 17, 2008 1:23 PM

Mike Suarez said:


nice post. a few questions/comments:

You say: "expect SQL Server to attempt to move to a table lock when more than 4000 rows/pages"

The way i am reading it, it sounds like it will move to a table lock after 4000 row locks? Is that what you meant? Or does it go to page locks after 4000 row locks? Then table after 4000 page locks?

Also, why does it do it based on the number 4000, and not based on a percentage? If a table has billions of rows, 4000 rows (or even pages) seems trivial.

You say: "The 64k extents are logged, if memory serves me right, not the individual records."

I believe, as others have mentioned, they log the pages, not the extents. Because, remember, you can have mixed extents.

One other comment on the deleting without too much log growth.

If you are doing a very large delete on a portion of a table, you can always do this:

declare @count int

select @count = 1

while @count > 0


 delete top (10000) from mike_test_delete where i < 100

 select @count = @@rowcount


or if it's ad hod, from SSMS, this:

delete top (10000) from mike_test_delete where i < 100

go 100

December 19, 2008 2:06 PM

jerryhung said:

I second cr8nk, have done that a few times for 2005 (thank god)


You can also give developers profile access on 2005 and 2008 without SA access using:


December 19, 2008 2:10 PM

jerryhung said:

Regarding lock, I seemed to recall (from 70-431 book?) that SQL engine escalates when it acquires 1/3 of the lock

so if it needs to acqure > 1/3 rows of the table, it will escalate into Table Lock, same for Table -> Page lock

4000 number is the first I have heard of, interesting...

December 19, 2008 2:15 PM

Armando Prato said:

I had this bookmarked about lock escalation.

Their blog states that there's a threshold at around 5,000 locks (intent locks included).

Kevin, I'm curious as to where you got your information from?  I just want to make sure I understand this correctly.

December 20, 2008 12:17 AM

Adi said:

Regarding set options.  When a stored procedure is created, the server will save the settings of SET QUOTED_IDENTIFIER and SET ANSI_NULLS options and will use the same settings whenever the procedure is executed.  One might say that the setting options for the session that created the procedure, will effect the set options of the sessions that run the procedure (even if it is just for the time that the procedure is executed).


December 21, 2008 10:37 AM

Leave a Comment


About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS,

This Blog



Privacy Statement