How to host memory and detect SQL Server tables

The presence of SQL Server data tables in memory is a feature provided by SQL Server that is rarely expected to be involved in the development of a general small system. This paper collates the relevant documentation to show how to put all the data of a table in SQL Server into memory, realize the memory database, improve the real-time performance.

1, DBCC pintable

Marks a table to is pinned, which means Microsoft SQL Server does not flush the "pages for" table from memory.

Syntax
DBCC PINTABLE ( database_id , table_id )
To determine the database ID, use the DB_ID function.
To determine the table ID, use the OBJECT_ID function.

Comments

DBCC pintable does not cause the table to be read into memory. When pages in a table are read into the cache by ordinary Transact-SQL statements, the pages are marked as memory-resident pages. When SQL Server requires space to read a new page, the memory-resident page is not emptied. SQL Server still records updates to the page and, if necessary, writes the updated pages back to disk. However, SQL Server keeps a copy of the available pages in the cache until you use the DBCC unpintable statement to make the table not reside.

DBCC pintable is best used to keep small, frequently referenced tables in memory. Reads a page of a small table into memory at once, and any future references to its data do not need to be read from disk.

Note DBCC Pintable can provide performance improvements, but be careful when you use them. If you reside in a large table, the table uses a large portion of the cache at the beginning, not enough cache for other tables in the system. If the table that resides is larger than the cache, the table fills the entire cache. A member of the sysadmin fixed server role must shut down and then restart SQL Server, and then make the table not reside. Hosting too many tables and hosting tables that are larger than the cache can have the same problem.

Example:

Declare @db_id int, @tbl_id int
Use DATABASE_NAME
Set @db_id = DB_ID('DATABASE_NAME')
Set @tbl_id = Object_ID('Department')
DBCC pintable (@db_id, @tbl_id)
可将表Department设置为驻留内存。
<CENTER><ccid_nobr>
<table width="400" border="1" cellspacing="0" cellpadding="2" bordercolorlight = "black" bordercolordark = "#FFFFFF" align="center">
<tr>
　　<td bgcolor="e6e6e6" class="code" style="font-size:9pt">
　　<pre><ccid_code>　
Declare @db_id int, @tbl_id int
Use DATABASE_NAME
Set @db_id = DB_ID('DATABASE_NAME')
Set @tbl_id = Object_ID('Department')
DBCC UNpintable (@db_id, @tbl_id)

You can set the table department to host memory.

You can use the following SQL directives to detect execution:

Select ObjectProperty(Object_ID('Department'),'TableIsPinned')

If the result is 1: The table is already set to host memory; 0: It means it is not set to host memory.