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

将SQL Server数据表驻留内存是SQL Server提供的一项功能，在一般小型系统的开发过程中估计很少会涉及到。这里整理了相关文档资料，演示如何把SQL Server中一个表的所有数据都放入内存中，实现内存数据库，提高实时性。 Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
1, DBCC PINTABLE Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Marks a table to be pinned, which means Microsoft SQL ServerEï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
does not flush the pages for the table from memory. Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Syntax DBCC PINTABLE ( database_id , table_id ) Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
To determine the database ID, use the DB_ID function. Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
To determine the table ID, use the OBJECT_ID function. Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
注释 Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
DBCC PINTABLE 不会导致将表读入到内存中。当表中的页由普通的 Transact-SQL 语句读入到高速缓存中时，这些页将标记为内存驻留页。当 SQL Server 需要空间以读入新页时，不会清空内存驻留页。SQL Server 仍然记录对页的更新，并且如有必要，将更新的页写回到磁盘。然而，在使用 DBCC UNPINTABLE 语句使该表不驻留之前，SQL Server 在高速缓存中一直保存可用页的复本。 Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
DBCC PINTABLE 最适用于将小的、经常引用的表保存在内存中。将小表的页一次性读入到内存中，将来对其数据的所有引用都不需要从磁盘读入。 Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
注意Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
DBCC PINTABLE 可以提供性能改进，但是使用时务必小心。如果驻留大表，则该表在开始时会使用一大部分高速缓存，而不为系统中的其它表保留足够的高速缓存。如果所驻留的表比高速缓存大，则该表会填满整个高速缓存。sysadmin 固定服务器角色的某个成员必须关闭而后重新启动 SQL Server，然后使表不驻留。驻留太多的表和驻留比高速缓存大的表会产生同样的问题。 Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
示例： Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Declare @db_id int, @tbl_id int Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Use DATABASE_NAME Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Set @db_id = DB_ID('DATABASE_NAME') Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Set @tbl_id = Object_ID('Department') Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
DBCC pintable (@db_id, @tbl_id) Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
可将表Department设置为驻留内存。 Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Declare @db_id int, @tbl_id int Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Use DATABASE_NAME Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Set @db_id = DB_ID('DATABASE_NAME') Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Set @tbl_id = Object_ID('Department') Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
DBCC UNpintable (@db_id, @tbl_id) Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
可将表Department取消设置为驻留内存。 Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
可以使用如下的SQL指令来检测执行情况： Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Select ObjectProperty(Object_ID('Department'),'TableIsPinned') Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
如果返回结果为1：则表示该表已经设置为驻留内存；0：则表示没有设置为驻留内存。 Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
2, SP_TableOption Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Sets option values for user-defined tables. sp_tableoption may be used to turn on the text in row feature on tables with text, ntext, or image columns. Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Syntax sp_tableoption [ @TableNamePattern = ] 'table' Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
, [ @OptionName = ] 'option_name' Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
, [ @OptionValue = ] 'value' Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
其中，'option_name' 有如下用法： Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
pintableEï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
-- When disabled (the default), it marks the table as no longer RAM-resident. When enabled, marks the table as RAM-resident.Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
（可将指定的表驻留内存） Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
另外，table lock on bulk load, insert row lock, text in row等等可选值，因不涉及将表驻留内存，具体用法可以查询SQL Server Books Online. Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Value有如下用法： Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
the option_name is enabled (true, on, or 1) or disabled (false, off, or 0) Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
示例： Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
EXEC sp_tableoption 'Department','pintable', 'true' Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
将数据表Department驻留内存 Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
EXEC sp_tableoption 'Department','pintable', 'false' Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
取消数据表Department驻留内存 Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
可以使用如下的SQL指令来检测执行情况： Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Select ObjectProperty(Object_ID('Department'),'TableIsPinned') Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
如果返回结果为1：则表示该表已经设置为驻留内存；0：则表示没有设置为驻留内存。 Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
3. Conclusions Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
将数据表设置为驻留内存时，并没有实际将表读入内存中，直到该表从被检索。因此，可以使用如下SQL指令进一步将数据表Department驻留内存： Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Select * From Department Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
另外，可以使用如下SQL指令方便显示/检测数据库Database中所有设置为驻留内存的表： Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
SELECT * FROM INFORMATION_SCHEMA.Tables Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
WHERE TABLE_TYPE = 'BASE TABLE' Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í
AND OBJECTPROPERTY(object_id(TABLE_NAME), 'TableIsPinned') > 0 Eï~U××;áÿwww.netcsharp.cnGG©ÓõÔ«7Í