Configuring Memory in SQL Server

Having been frequently confused by all the options in boot.ini and SQL Server for tuning the memory usage, I gathered together all the resources I could find, did my best to understand them, and came up with this flowchart which explains the various choices that lead to the right combination of settings.  I *think* this is all correct, but I would definitely like input from anyone that has greater insight into the mysteries of locked pages, AWE, /3GB and /PAE …

SQL Server Memory Configuration Flowchart

(Click the image for the full size version.)

Forcing a table lock in SQL Server

A client wanted to debug their application, there seemed to be an issue that caused incorrect records being inserted by an automated process in some cases.  It was eventually tracked down to a locked table causing a time-out in the application, but how could we prove this by locking the table instantly at any point, then unlocking it?

Quite easily, after a minute’s thought.  Begin a transaction, execute a statement including the TABLOCKX hint, then don’t commit the transaction.  So:

begin transaction;

select * from Sales with (tablockx, holdlock) where OrderID = 1;

Then you can unlock the table with a ROLLBACK.

ALTERing Objects in a Replicated Database

There’s a limit to what changes you can make to tables and other objects in a database which is being replicated.  Fortunately the restrictions aren’t that great; here’s a quick reference to what is and is not supported.

– Supported syntax
alter table Test1 alter column RowValue varchar(20) — Changing column definition
alter table Test1 add NewColumn int null — Adding a nullable column to the end of the table
alter table Test1 drop column NewColumn — Dropping a column
alter table Test1 add NewColumn int not null default 0 — Adding a non-nullable column with a default
alter table Test1 drop constraint DF__Test1__NewColumn__5DCAEF64 — Dropping a default constraint
alter table Test1 drop column NewColumn — Dropping a column after dropping its default constraint
alter view vwTest1 as select RowID from Test1 — Changing a view definition
alter procedure uspTest1 as select RowID, RowValue from Test1 — Changing a stored procedure definition
– alter function and alter trigger are also supported

– Unsupported syntax
alter table Test1 add NewColumn int not null — Adding a not-null column with no default
alter table Test1 add NewColumn int identity — Adding an identity column (Also an identity column cannot be dropped)
alter table Test1 drop constraint PK__Test1__FFEE74517F60ED59 — Dropping a primary key
alter table Test1 add constraint PK_Test1 primary key clustered (RowID) — Adding a primary key

Some things to remember:

• Writing your own ALTER statement will allow you to do more than generating a script from the table designer (which often likes to create a temp table, move the data into it, drop the table then rename the temp).
• Always execute your code on the publisher, not the subscriber!