Keeping SQL Server data tables in memory is a function provided by SQL Server, which is rarely involved in the development process of general small systems. Related documents are compiled here to demonstrate how to put all the data of a table in SQL Server into memory to implement an in-memory database and improve real-time performance.
1, DBCC PINTABLE
Marks a table to be pinned, which means Microsoft SQL Server does not flush the pages for the 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.
Commenting
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, they are marked as memory-resident pages. Memory-resident pages are not cleared when SQL Server needs space to read in new pages. SQL Server still records updates to the page and, if necessary, writes the updated page back to disk. However, SQL Server keeps a copy of the available pages in the cache until the table is made non-resident using the DBCC UNPINTABLE statement.
DBCC PINTABLE is best used for keeping small, frequently referenced tables in memory. Read the pages of the small table into memory at once, and all future references to its data do not need to be read from disk.
NOTE DBCC PINTABLE can provide performance improvements, but must be used with caution. If a large table resides, the table initially uses a large portion of the cache without leaving enough cache for other tables in the system. If the hosted table is larger than the cache, the table fills the entire cache. A member of the sysadmin fixed server role must shut down and restart SQL Server and then make the table non-resident. Hosting too many tables creates the same problem as hosting tables that are larger than the cache.
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)
can set the table Department to be memory-resident .
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)
can unset the table Department as memory-resident.
You can use the following SQL command to detect the execution:
Select ObjectProperty(Object_ID('Department'),'TableIsPinned')
If the return result is 1: it means that the table has been set to reside in memory; 0: it means that it has not been set to reside in memory. Reserve memory.
2, SP_TableOption
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.
Syntax
sp_tableoption [ @TableNamePattern = ] 'table'
, [ @OptionName = ] 'option_name'
, [ @OptionValue = ] 'value'
where 'option_name' has the following usage:
pintable -- When disabled (the default), it marks the table as no longer RAM-resident. When enabled, marks the table as RAM-resident. (The specified table can be resident in memory)
In addition, table lock on bulk load, insert row lock, text in row and other optional values do not involve resident table in memory. For specific usage, you can query SQL Server Books Online.
Value It has the following usage:
the option_name is enabled (true, on, or 1) or disabled (false, off, or 0)
Example:
EXEC sp_tableoption 'Department','pintable', 'true'
will make the data table Department reside in the memory.
EXEC sp_tableoption 'Department','pintable', 'false'
will cancel the data table Department being resident in the memory.
You can use the following SQL command to detect the execution:
Select ObjectProperty(Object_ID('Department'),'TableIsPinned')
If the return result is 1: it means that the table has been set to reside in memory; 0: it means that it has not been set to reside in memory. Reserve memory.
3. ConclusionsWhen
setting a data table to be memory-resident, the table is not actually read into memory until the table is retrieved. Therefore, you can use the following SQL instructions to further reside the data table Department in memory:
Select * From Department
In addition, you can use the following SQL instructions to easily display/detect all tables set to reside in memory in the database:
SELECT * FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(object_id(TABLE_NAME), 'TableIsPinned') > 0