The more memory available for SQLSERVER, the more resources SQLSERVER has to use in the buffer zone, which can reduce the number of times the server reads data from the disk to obtain static information or compile the process plan. To adjust the memory occupied by SQLSERVER, you can set the totalmemory parameter in the server configuration file. The value of this parameter specifies the total memory required by SQLSERVER at startup. If the value is 10000 pages, at startup, SQLSERVER will try to obtain 10000*2 K = 19.5M memory. If not, the startup will fail.
Note: If the operating system supports dynamic memory allocation throughout the lifecycle of a process, you can allocate additional memory to it after SQL Server is started.
At startup, SQLSERVER allocates the memory to the executable code of SQLSERVER. The static memory used by SQLSERVER. You can configure the memory occupied by parameters and the data structure that does not reside in the cache, the remaining memory is allocated to two SQLSERVER buffers: data buffer and process buffer. The size of the two buffers has a great impact on the performance of the entire system. In a development system, you may need to increase the dedicated memory of too many client buffers, in a production system, the size of the Process buffer must be reduced to obtain a larger data buffer.
1. Determine the buffer size
In the total space of totalmemory configuration, we must first remove the space occupied by cute Execution Code. The size varies with the platform and version. You can use sp_configure to query the executablecodesize parameter, generally, it is 3-4 MB. Besides the space occupied by the internal structure, the internal structure is divided into the kernel structure and server structure. This part of memory can also be viewed as static overhead and user configuration parameter occupation, the size of the former is not affected by configurable parameters, generally 2-3 MB. The latter means that the size of configurable parameters depends on the type and value of the parameter, including numberofuserconnections, numberofopendatabases, numberofdevices, numberofopenobjects, numberoflocks, and so on, you need to get a precise estimation of the memory size used by these user configuration parameters, you can call sp_configure without any option to display the memory usage of all parameters and each parameter.
In addition to the above occupation, the remaining memory will be allocated to the process buffer and data buffer proportionally based on the value of the procedurecachepercent configuration parameter. For example, if the value is 20, that is, 20% of the remaining memory is allocated to the process buffer and 80% to the data buffer.
Another way to determine the buffer size is to check the memory information written to the SQL Server Error Log at SQLSERVE startup, which accurately describes the amount of data allocated to the process buffer and data buffer, and how many processes or other compilation objects can reside in the buffer at the same time. For example, the related information is:
Server: Numberofprocbuffersassocated: 556
Servers: Numberofblocksleftforprocheaders: 629
Server: memoryallocatedforthedefadatdatacache: 4144kb
The buffer information of the first two actions. The total size of the Process buffer is the memory allocated to the process buffer (the first line) and allocated to the process header (that is, the place where the compiled object, such as the stored procedure, is stored, according to the size of the stored object, one or more process headers may be required.) the sum of memory (second row, compilation objects that can be stored in the process buffer are limited by smaller ones. In the above information, the first line indicates that the number of buffers allocated to the process buffer zone is 556, and the size of each buffer zone is 76B. Therefore, the size of the Process buffer zone is 42256B on 21 pages; the second row indicates that the space allocated to the process header is 629 pages. Therefore, the total size of the Process buffer is 629 + 21 = 650 pages, which is 1.27 MB.
The third row indicates the size of the buffer space allocated to the default database. In addition, if a named buffer is configured, the related information will also contain information about each named buffer, and the memory size occupied by these named buffers will be the total data buffer size. For details about the data buffer size, you can use the sp_helpcache process to obtain detailed information about each buffer, the objects bound to these buffers, and the overhead related to different buffer sizes.
2. Buffer Management
The data buffer stores the data pages, index pages, and log pages currently used and recently used by SQLSERVER. when SQL Server is first installed, a default data buffer is generated. SQLSERVER allows the system administrator to divide the data buffer into an independent named data buffer, and then bind the database or database object to the named buffer to organize the buffer space more reasonably, controls the memory resident of databases, tables, and indexes. in addition, the system administrator can create a buffer pool in the buffer zone to execute large disk I/O, improve data query performance, and reduce disk I/O. commands related to database buffers include:
Sp_cacheconfig creates or deletes a named buffer and changes the buffer size or type.
Sp_poolconfig creates or deletes an I/O buffer pool and changes its size.
Sp_bindeache binds a database or database object to a buffer zone
Sp_unbindcache unbinds a specified object or database from a buffer zone
Sp_unbindcache_all unbinds all objects from a buffer zone
Sp_helpcache Reports Summary information about the data buffer and displays the databases and objects bound to the buffer.
Sp_cachestrategy reports on the buffer policies set for tables and indexes. Disable or re-allow the first-mentioned or MRU policies.
Sp_logiosize: Change the default I/O size of logs.
Sp_spaceused estimates the table and index size or information about the space used by the database
Sp_estspace is used to estimate the table and index size, and the number of rows contained in the table is given.
Buffer to which the sp_help report table is bound
Sp_helpindex: buffer to which the report index is bound
Sp_helpdb report the buffer to which the database is bound
Setshowplanon Reports application policies on the I/O size and buffer zone used for queries
Setstatisticsioon report the number of read operations performed for the query
Setprefetchon/off allows or disables pre-extraction of a session
(1) query database buffer Information
To view the size of the default data buffer, run the command sp_cacheconfig "defaultdatacache". The result is as follows, which consists of two parts, the previous section reports the memory pool configuration information in each buffer and totals the size of all configured buffers. The next section provides other details in each buffer.
The output columns in the first part indicate that cachename is the buffer name, and status indicates whether the buffer zone is activated (when the value is pend/act, it indicates that the buffer zone has just been created and will be activated after restart; when the value is active, it indicates that the buffer is currently active. If the value is pend/del, it indicates that the buffer is active, but will be deleted after the server is restarted ); type indicates whether the buffer can store data ("mixed") and log pages ("logonly"). Only the default buffer has the "default" type, and the default data buffer type cannot be changed, other buffer types cannot be changed to "default"; configvalue displays the buffer size after the next SQLSERVER restart. In this case, the default buffer cannot be explicitly configured, so the size of the information is 0; runvalue indicates the size currently used by SQLSERVER. For the default data buffer, this value is used as all data buffer spaces not displayed in other buffers.
The output column in the second part shows the cache size in the buffer pool. By default, all buffer pools are allocated 2 K space (the valid space is 2 K, 4 K, 8 K, 16 K ); the fill size indicates the size of the flushing area of the buffer pool. The configsize and runsize indicate the configuration size and the current size. The two values are different in the 2 K buffer pool (because the size cannot be explicitly configured ), for other buffer pools, if you try to move space between the buffer pools and some space cannot be released, the above two values are also different.
|
(2) configure the Data Buffer
There are two ways to configure the data buffer: one is to use the process sp_cacheconfig and sp_poolconfig for interactive configuration, and the other is to edit the totalmemory parameter in the configuration file and restart it to make it take effect.
A. Interactive Configuration
Create a named buffer (the newly created named buffer allocates space from the default defadatacache buffer). Each time you execute sp_cacheconfig or sp_poolconfig, SQLSERVER writes the new buffer zone or buffer pool information to the configuration file and copies the old version of the file to a backup file. The backup file name information is sent to the error log.
Create a new 10 M buffer command named pubs_cache. The size unit can be P pages, K, G, and (K by default) in addition to M ). this command changes the system table and writes the new value to the configuration file but does not activate the buffer temporarily. To make it take effect, restart SQLSERVER.
Sp_cacheconfigpubs_cache, "10 M"
You can use sp_cacheconfig "pubs_cache" to view the configuration information before restarting SQLSERVER to make the new value take effect. The value of the status column is pend/act, which indicates that the configuration of the buffer is suspended and takes effect after the restart. Therefore, Configvalue (configured value) it is different from Runvalue (a value in use.
CacheNameStatusTypeConfigvalueRunvalue |
After the restart, sp_cacheconfig without the buffer name parameter is allowed. The information is changed as follows. It can be seen that pubs_cache has been activated, and the default buffer zone is allocated 10 MB to the buffer. you can use the sp_cacheconfig process to set the minimum value of the default data buffer. This part of the space will be locked to the default data buffer. for example, if the default data buffer size is set to at least 25 MB, the command is sp_cacheconfig "defaultdatacache" "25 MB ".
|
To retain a buffer type that is only used for transaction logs, you can set the buffer type to "logonly". For example, you can create a pubs_log buffer of the "logonly" type as sp_cacheconfigpubs_log, "7 M", "logonly", which indicates the status of the buffer before the restart.
CacheNameStatusTypeConfigvalueRunvalue
---------------------------------------------------------
Pubs_cachepend/actlogonly7.00MB0.00MB
-------------------------------
Total7.00MB0.00MB
After a named buffer is created, some objects must be bound to the buffer. If no object is bound to the named buffer after a named buffer is created, the memory occupied by the named buffer is wasted, because any object without a specified buffer will use the default data buffer defaultdatacache by default.
>;
After dividing data buffer zones into buffer pools, you can divide them into buffer pools. Different buffer pools have different I/O sizes. When SQLSERVER performs large I/O operations, it can read many pages into the buffer at a time, thus improving I/O efficiency. these pages are always processed as a unit. They coexist in the buffer and are written to the disk as a unit.
The page size of the buffer pool can be 2 K, 4 K, 8 K, 16 K. The total size of the buffer pool cannot be smaller than K. all buffer pool configurations are dynamic and take effect without restarting SQLSERVER. the number of 2 k page buffer pool in SQLSERVER must be a reasonable value; otherwise, SQLSERVER performance will be affected, because some commands can only use 2 k I/O blocks such as diskinit and some dbcc commands.
In the pubs_cache data buffer area, create a buffer pool with a size of 16 KB and a total space of 7 mb. The command is as follows. generally, when you create a named data buffer zone, the space is always divided into two K buffer pools per page by default. When you create a buffer pool of other pages, this 2 k page buffer pool space is partially allocated to the new buffer pool. in the following example, a buffer pool of 2 k pages has a total space of 10 MB. After 7 m is allocated to a new buffer pool of 16 K pages, 3 M is left in the buffer pool of 2 k pages.
|
You can also create a buffer pool in the default data buffer zone. For example, if the size of a 16 K buffer pool is 8 Mb, the command is sp_poolconfig "defaultdatacache", "8 M", "16 K ".
To create a buffer for transaction logs of a database, you should configure a large part of the buffer space to match the log I/O size. the default value is 4 K. If no 4 K buffer pool is available, SQLSERVER uses 2 k I/O for logs.
The log I/O size can be changed through sp_logiosize in the system process. The log I/O size of each database is reported in the error log after SQL Server is started, you can also use the database and execute the process sp_logiosize without parameters to check the log I/O size of a database. for example, if the buffer pool command for configuring 4 K page size for the pubs_log buffer is sp_poolconfigpubs_log, "3 M", "4 K", you can also create a 4 K page buffer pool in the default data buffer zone, to use transaction logs of any database not bound to other buffers: sp_poolconfig "defaultdatacache", "2.5 M", "4 K ".
In addition, you can modify the buffer pool size. For example, you can extract 1 MB of space from the 16 K page I/O buffer pool and add it to the 4 K page I/O Buffer Pool: sp_poolconifgpub_cache, "1 M ", "4 K", "16 K"
Bind a buffer zone
After dividing the data buffer into an independent named data buffer, the system administrator can bind database objects to these buffers to control the memory resident of the database, tables, and indexes. if no object is bound to the named buffer after a named buffer is created, the memory occupied by the named buffer is wasted, because any object without a specified buffer will use the default data buffer defaultdatacache by default. to bind any system table, including transaction logs syslogs, to the buffer, the database must be in single-user mode. the object takes effect immediately after it is bound without restarting SQLSERVER. note: When binding or deleting a binding operation, SQLSERVER needs to lock the corresponding object, so other operations on the corresponding object may be delayed. in addition, you can rebind the existing bundle instead of deleting the existing bundle.
Object. In addition, when the bound object has a dirty read activity or an open cursor, the bundling or deleting bundling operation cannot be performed.
1>; sp_dboptionpubs2, single, true set pubs2 database to single user status
2>; usepubs2
3>; checkpoint
4>; go is ready to bind database objects. Note that the bound object must be in the library where the object is located.
>; Sp_bindcachepubs_cache, pubs2, titles
Bind the table titles in the pubs2 database to the buffer zone pubs_cache.
>; Sp_bindcachepubs_cache, pubs2, titles. titleind
Bind indexes on titles and bind the table titles in the pubs2 database to the pubs_cache buffer.
>; Sp_bindcachepubs_cache, pubs2, "hj. sale_east"
Bind the user hj table sale_east in the pubs2 database to the buffer zone pubs_cache.
>; Sp_bindcachepubs_log, pubs2, syslogs
Bind the transaction log syslogs of the pubs2 database to the buffer zone pubs_log.
>; Sp_bindcachepubs_cache, pubs2, au_pix, "textonly"
The text and image columns of a table are stored in a separate data structure. If you bind the table to a buffer, you need to add the "text" parameter.
1>; sp_dboptionpubs3, single, true set pubs3 database to single user status
2>; usepubs3
3>; checkpoiot
4>; usemaster
5>; go prepares to bind the database. Note that the bundled database must be in the master database.
>; Sp_bindcachetempdb_cache, tempdb
Bind the database tempdb to the buffer zone tempdb_cache. Note: The objects bound to the log buffer can only be syslogs tables. You can use the sp_helpcache process to query the binding information of all or specified buffers and bound objects. the status column reports that the binding of the buffer zone is ("V") No ("I") Valid. if the database or object is bound to the buffer pool and the buffer zone has been deleted, the binding information is retained in the system table, but the buffer bundle is marked as invalid, all invalid bundled objects use the default database buffer. if another buffer is created with the same name as the deleted buffer, the above binding becomes valid when the buffer is activated by restarting SQLSERVER.
Two procedures can be used to delete the buffer Bundle: sp_unbindcache is used to delete the bundle of an entity in the buffer; sp_unbindcache_all is used to delete the bundle of all objects in the buffer, but if the number of databases bound to the buffer exceeds 8, if the number of database objects exceeds 8, p_unbindcache_all cannot be used. In this case, sp_unbindcache must be used to delete a single database or object, so that no more than 8 bound databases can be used. in addition, when you delete the binding of a buffer to an object, all the current pages in the memory are cleared from the buffer.
>; Sp_unbindcachepubs2
Delete the binding to database pubs2
>; Sp_unbindcachepubs2, titles
Delete the titles table bound to the pubs2 Database
>; Sp_unbindcachepubs2, titles, titleidind
Unbind the titleidind index from the titles table in the pubs2 database.
>; Change the name data buffer size
You can use sp_cacheconfig to specify a new space to increase or decrease the size of the specified buffer, all new spaces will be added to the 2 k page buffer pool in the specified buffer zone from the default data buffer zone defaultdatacache, all reduced space is also released from the 2 k page buffer pool in the specified buffer zone to the 2 K buffer pool in the default database buffer zone defaultdatacache.
|
>;sp_cacheconfigpubs_cache |
|
|
To completely delete a data buffer and reset its size to 0, use sp_cacheconfigpubs_log, "0 ". the buffer state is changed to "pend/del". After SQLSERVER is restarted, this change takes effect. before performing this operation, the buffer zone must be activated. All objects bound to the buffer zone still use this buffer zone for I/O operations. if an object is bound to the data buffer to be deleted, after is restarted, the buffer bundle is marked as invalid. All objects bound to the invalid buffer zone use the default data buffer. when the binding is marked as invalid, the warning information is entered into the error log. note: The default data buffer cannot be deleted.
(
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
