How to resident and detect SQL Server tables

How to resident and detect SQL Server tables

Resident memory of SQL Server Data Tables is a function provided by SQL Server. It is estimated that it is rarely involved in the development of small systems. This document demonstrates how to put all the data in a table in SQL Server into the memory to improve the 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.

Note

Dbcc pintable does not read the table into the memory. When pages in a table are read from normal Transact-SQL statements to the cache, these pages are marked as memory resident pages. When SQL Server needs space to read New pages, the memory resident page is not cleared. SQL Server still records page updates and writes the updated pages back to the disk if necessary. However, before using the dbcc unpintable statement to make the table do not reside, SQL Server keeps saving copies of available pages in the cache.

Dbcc pintable is most suitable for saving small and frequently referenced tables in memory. Read the page of a small table into the memory at one time. In the future, all references to the table data do not need to be read from the disk.

Note that dbcc pintable can provide performance improvement, but be careful when using it. If a large table is resident, a large portion of the high-speed cache is used at the beginning, instead of retaining sufficient high-speed cache for other tables in the system. If the resident table is larger than the high-speed cache, the table fills up the whole high-speed cache. A member of the sysadmin fixed Server role must be shut down and then restart SQL Server so that the table does not reside. Tables that reside too many tables and tables that reside larger than the cache will 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)

You can set the table Department to resident memory.

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 unset the table Department to resident memory.

You can use the following SQL command to check the execution:

Select ObjectProperty (Object_ID ('department '), 'tableispinned ')

If the returned result is 1, the table has been set to resident memory; 0: The table is not set to resident 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'

'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 reside in the memory)

In addition, table lock on bulk load, insert row lock, text in row, and other optional values, because the table does not need to reside in the memory, the specific usage can query SQL Server Books Online.

Value 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'

Resident data table Department in memory

EXEC sp_tableoption 'department ', 'pintable', 'false'

Cancel data table Department resident memory

You can use the following SQL command to check the execution:

Select ObjectProperty (Object_ID ('department '), 'tableispinned ')

If the returned result is 1, the table has been set to resident memory; 0: The table is not set to resident memory.

3. Conclusions

When a data table is set to resident memory, the table is not actually read into the memory until the table is retrieved. Therefore, you can use the following SQL command to further resident the data table Department in the memory:

Select * From Department

In addition, you can use the following SQL command to conveniently display/detect all tables in the Database that are set to resident memory:

SELECT * FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_TYPE = 'base table'

And objectproperty (object_id (TABLE_NAME), 'tableispinned')> 0