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 also check out the discussion forums on SQLMag.com any time, including the Tool Time forum, where I discuss many of the popular free tools covered in my Tool Time column each month in SQL Server Magazine. 

Thanks!

-Kevin