如何将SQL Server表驻留内存和检测

将SQLServer数据表驻留内存是SQLServer提供的一项功能，在一般小型系统的开发过程中估计很少会涉及到。这里整理了相关文档资料，演示如何把SQLServer中一个表的所有数据都放入内存中，实现内存数据库，提高实时性。

1,DBCCPINTABLE

Marksatabletobepinned,whichmeansMicrosoftSQLServerdoesnotflushthepagesforthetablefrommemory.

Syntax
DBCCPINTABLE(database_id,table_id)

TodeterminethedatabaseID,usetheDB_IDfunction.

TodeterminethetableID,usetheOBJECT_IDfunction.

注释

DBCCPINTABLE不会导致将表读入到内存中。当表中的页由普通的Transact-SQL语句读入到高速缓存中时，这些页将标记为内存驻留页。当SQLServer需要空间以读入新页时，不会清空内存驻留页。SQLServer仍然记录对页的更新，并且如有必要，将更新的页写回到磁盘。然而，在使用DBCCUNPINTABLE语句使该表不驻留之前，SQLServer在高速缓存中一直保存可用页的复本。

DBCCPINTABLE最适用于将小的、经常引用的表保存在内存中。将小表的页一次性读入到内存中，将来对其数据的所有引用都不需要从磁盘读入。

注意DBCCPINTABLE可以提供性能改进，但是使用时务必小心。如果驻留大表，则该表在开始时会使用一大部分高速缓存，而不为系统中的其它表保留足够的高速缓存。如果所驻留的表比高速缓存大，则该表会填满整个高速缓存。sysadmin固定服务器角色的某个成员必须关闭而后重新启动SQLServer，然后使表不驻留。驻留太多的表和驻留比高速缓存大的表会产生同样的问题。

示例：

Declare@db_idint,@tbl_idint

UseDATABASE_NAME

Set@db_id=DB_ID('DATABASE_NAME')

Set@tbl_id=Object_ID('Department')

DBCCpintable(@db_id,@tbl_id)

可将表Department设置为驻留内存。

Declare@db_idint,@tbl_idint

UseDATABASE_NAME

Set@db_id=DB_ID('DATABASE_NAME')

Set@tbl_id=Object_ID('Department')

DBCCUNpintable(@db_id,@tbl_id)

可将表Department取消设置为驻留内存。

可以使用如下的SQL指令来检测执行情况：

SelectObjectProperty(Object_ID('Department'),'TableIsPinned')

如果返回结果为1：则表示该表已经设置为驻留内存；0：则表示没有设置为驻留内存。

2,SP_TableOption

Setsoptionvaluesforuser-definedtables.sp_tableoptionmaybeusedtoturnonthetextinrowfeatureontableswithtext,ntext,orimagecolumns.

Syntax
sp_tableoption[@TableNamePattern=]'table'
,[@OptionName=]'option_name'
,[@OptionValue=]'value'

其中，'option_name'有如下用法：

pintable--Whendisabled(thedefault),itmarksthetableasnolongerRAM-resident.Whenenabled,marksthetableasRAM-resident.（可将指定的表驻留内存）

另外，tablelockonbulkload,insertrowlock,textinrow等等可选值，因不涉及将表驻留内存，具体用法可以查询SQLServerBooksOnline.

Value有如下用法：

theoption_nameisenabled(true,on,or1)ordisabled(false,off,or0)

示例：

EXECsp_tableoption'Department','pintable','true'

将数据表Department驻留内存

EXECsp_tableoption'Department','pintable','false'

取消数据表Department驻留内存

可以使用如下的SQL指令来检测执行情况：

SelectObjectProperty(Object_ID('Department'),'TableIsPinned')

如果返回结果为1：则表示该表已经设置为驻留内存；0：则表示没有设置为驻留内存。

3.Conclusions

将数据表设置为驻留内存时，并没有实际将表读入内存中，直到该表从被检索。因此，可以使用如下SQL指令进一步将数据表Department驻留内存：

Select*FromDepartment

另外，可以使用如下SQL指令方便显示/检测数据库Database中所有设置为驻留内存的表：

SELECT*FROMINFORMATION_SCHEMA.Tables

WHERETABLE_TYPE='BASETABLE'

ANDOBJECTPROPERTY(object_id(TABLE_NAME),'TableIsPinned')>0