·
Verify that the feature of the locked pages is
not ON because the start-up account for SQL Server 2012 does not have
the permission "lock pages in memory" by
default.
--The table is still in the old database. You need to delete it.
--Option 1 - locked_page_allocations_kb should be
0
SELECT * FROM sys.dm_os_memory_nodes
--Option 2: check the memory manager pane and
note the locked ages allocated is 0
DBCC memorystatus
·
As you may know, in SQL Server 2012, the default
start-up service account is a virtual account. In my case, it is NT
Service\MSSQLSERVER.
·
In SQL Server 2012, we can
enable "locked pages" by granting the "lock pages in
memory" user right for the startup account for the instance of SQL Server
as below:
o Run: gpedit.msc,
o Find the option:
Computer Configuration|Windows Settings|Security Settings|Local
Policies|User Right Assignments|Local Pages in Memory (the right panel), double
click, Add User or Group.
o Now is the trick part, you cannot find NT Service\MSSQLSERVER because it is not a user, a group, a
built-in security principal.
o Solution: You can either directly type NT Service\MSSQLSERVER
or copy the service account from SQL Server Configuration Manager to box, then
click OK, it will show several items. Select the first one: MSSQLServer. That's
it! It is NT Service\MSSQLSERVER.
o Restart SQL Server, verify the "locked page" feature is
ON now.
