SQL Server2005 memory locking

Question

I've noticed that SQL Server 2005 x64 does not seem to lock pages into memory the same way SQL Server 2000 did. In 2000 I could easily see from task manager that SQL had locked 8GB of ram with AWE. I'm fairly certain I've got 2005 setup in an equivalent way.

Is this a normal x64 difference or am I forgetting a crucial setup option?

Answer 1

SQL Server 2005 x64 certainly doesn't need, or use, AWE; AWE is only to allow it to use > 4GB on 32 bit systems.

You can use the old lock-pages-in-memory trick, but as this KB shows (http://support.microsoft.com/kb/918483): "Note For 64-bit editions of SQL Server 2005, only SQL Server 2005 Enterprise Edition can use the Lock pages in memory user right."

Answer 2

Ask the perf team article

Other links:

Windows x64 config

KB article (same as above)

Answer 3

I've seen permissions problems crop up a lot too - if the account you're using for the SQL Server service doesn't have the right permissions, it can't lock pages in memory even if you're running Enterprise Edition. This blog entry by the PSS SQL Server Engineers is really helpful:

PSS Engineers: Do I have to assign the Lock Pages in Memory privilege for Local System?