Escalation is usually straightforward, but the original question points out
one gotcha that many database administrators and developers fall into. Let's say
you've set all the LE parameters to their defaults (i.e., LE threshold maximum =
200). Escalation happens when one statement has locked more than 200 pages. The
focus on statement is important, because a transaction can lock more
than 200 pages of a table without escalating to a table lock. Consider the
following example:
DECLARE @LoopCounter int
SELECT @LoopCounter = 1
BEGIN Tran
WHILE @LoopCounter <= 300
BEGIN
UPDATE CustomerTable SET FakeColumn = @LoopCounter
SELECT @LoopCounter = @LoopCounter + 1
END
COMMIT Tran
This batch file updates 300 records in one transaction, so you might think
that the individual page locks will escalate to one table lock. But this batch
file will eat up the 300 individual page locks because each UPDATE statement
affects only one record and locks only one page. Lock escalation works exactly
as Microsoft documents it: Escalation occurs only when one command affects more
than the maximum number of pages.
Does the REVOKE command always revoke permissions?
Strange but true: The REVOKE command doesn't work when used against a table
with a certain number of columns. Explaining this phenomenon isn't easy because
the behavior changes between service packs. In SQL Server 6.5 Service Pack 4,
the REVOKE command doesn't remove all existing permissions if the binary
representation of the number of columns in the table ends in 111. The command
removes some columns but not all of them. You can hack the system tables
directly to remove the rogue permissions, but adding an extra column is a much
safer and easier method. Table 4, page 201, shows the decimal and binary
representation of numbers that cause this problem; the query in Listing 1 also
reveals which tables are affected.
The script in Listing 2 shows what happens when you change permissions on a
table and then run sp_helprotect to look at the configured permission. The
resulting output illustrates that you can't revoke permissions when the table
has seven columns, but you can after you add an eighth, dummy column.
This problem happens with all the numbers listed in Table 5 (i.e., numbers
that end with binary 111) when you're running SQL Server 6.5 with any service
pack. Some users have reported that the condition also occurs on tables in which
the number of columns is divisible by 7. Windows NT Magazine hasn't been
able to confirm the divisible-by-7 problem, but keep the possibility in mind if
you experience strange REVOKE problems. Don't fret, Microsoft has fixed this
problem in SQL Server 7.0 beta 2.
Can you handle the tempdb database in some way other than leaving
it on the master device? Users frequently ask me how to move tempdb
from the master device and put it on its own device, but I'm unsure how to move
it.
Richard Waymore provided a solution procedure in a SQL Professional article about a year ago. Here are the key steps:
- Move tempdb to RAM (this move takes tempdb off the master device).
- Use sp_diskdefault to unmark the master and any other device as a
default device.
- Create a new device for tempdb and mark the device as the default.
- Take tempdb out of RAM. Tempdb will now be on the new device.
- Expand the device as necessary and alter tempdb to use the expanded
space.
- Use sp_diskdefault to re-mark whatever default devices you want.
End of Article
Prev. page
1
2
3
[4]
next page -->
