In simple recovery checkpoints are run when the log becomes 70% full or based on the recovery interval setting, which ever comes first.
Recovery Interval � the checkpoint interval which is the time between the start of consecutive checkpoints is determined by the recovery interval and the number of records in the transaction log. The recovery interval is set for an entire instance of SQL Server. The value represents the number of minutes that you choose to allow SQL server for automatic recovery. SQL server uses an algorithm to determine when to perform the next check point. The algorithm will create checkpoint to ensure that in the event of system failure the recovery will not take longer than the time specified in your recovery interval.
SQL Server Memory Allocation
Memory management in SL Server 2005 requires very little, if any, user intervention and by default memory is allocated and de-allocated dynamically by SQL Server as needed for optimal performance according to the amount of physical memory available. Although this dynamic behaviours can be overridden if you so wish.
Dynamic Memory Allocation
In SQL Server 2005 memory allocation is by default dynamic, even when AWE is enabled. AWE is applicable on 32 bit operating systems The AWE (Advanced Windowing Extensions) API allows applications to access more than 4GB of RAM. The exception to this is when running SQL Server 2005 on windows 2000 32 bit operating system with AWE enabled; in this case memory allocation is static. AWE support is only available for STANDARD, ENTERPRISE and DEVELOPER editions of SQL Server.
SQL server dynamically manages memory based on either the default memory setting or a setting that you specify. Dynamic memory management mean SQL Server automatically acquires and released memory as necessary. At start-up SQL Server only acquires the memory it needs at that point, as users connect and workload hits the server SQL Server will acquire more memory as necessary. SQL Server can also de-allocate memory freeing it for other applications to use however if no other application is requesting memory SQL Server will maintain its memory pool even if there are unused pages. Memory is only de-allocated if needed by another process.
SQL server maintains it virtual memory space so it is always less than the physical memory space. This prevents excessive paging, some minimal paging is normal. By maintaining this situation SQL Server can have the largest memory available while preventing SQL Server pages from swapping to the page file on disk. The page file should not be used for SQL Server pages.
With dynamic memory allocation, if additional applications running on the same machine as SQL Server require memory, SQL Server will release memory for them from its memory pool. Thus other applications might attempt to �Steal� memory from SQL Server. It is best practice to dedicate a system to SQL Server and not run other applications alongside it.
Static Memory Allocation
There are two cases in which SQL Server maintains a static amount of memory:
With Static memory allocation once the maximum amount of memory allocated to SQL Server is reached it will never release the memory. Even if another process needs it. If there is not enough memory to support these other applications then they are likely to experience excessive paging. This highlights the point made above regarding the need for a dedicated system for your SQL Server.
- As mentioned earlier when SQL Server 2005 32 bit, Enterprise, Standard or Developer is run on Windows 2000 32 bit operating system with AWE enabled.
- The other occurs when the memory settings (Min and Max values) are set to the same value.
Min and Max Server Memory
There are three options in SQL Server that allow you to configure memory settings:
1. Min Server Memory
2. Max Server memory
These are all advanced options, to view them using SP_CONFIGURE you must set SHOW ADVANCED OPTIONS to and reconfigure
Sp_configure �show advanced options�, 1;RECONFIGURE;
To enable the AWE Enabled setting you must the awe-enabled option to 1 (1 = enabled, 0 = disabled, 0 is the default)
SP_CONFIGURE �awe enabled�, 1;
You will need to restart SQL Server for the awe-enabled option to take affect.
To achieve dynamic memory allocation you can leave the default settings for both min and max server memory, this will allow SQL Server to acquire as much memory as it needs or you can change the default values to other limited. SQL Server dynamically acquires and releases memory between then amount specified in the Min and Max settings.
To ensure that SQL Server always has 512 megabytes if memory set the min server memory to 512:
SP_CONFIGURE �min server memory�, 512;RECONFIGURE;
The min option will ensure that SQL Server always has a certain amount of memory to use, thus preventing other applications from �stealing� all the memory. The Max setting can be used to ensure that memory is available for other applications and they don�t have to wait for SQL Server to release it. As an example you may want to limit SQL Server to use 1GB of memory you can configure this setting as follows:
SP_CONFIGURE �Max Server Memory�, 1024;RECONFIGURE;
The min and max options only require you to RECONFIGURE you do not need to restart SQL Server.
Microsoft recommends that you allow SQL Server to configure it memory usage dynamically by leaving the min and max setting as defaults, which is ideal if you have dedicated server to SQL Server but not if you budgets are tight and you have utilise all your hardware. Just ensure you don�t cause excessive paging by configuring these values incorrectly.
To force SQL Server to use a fixed amount of memory set both the Min and Max values to be the same. Again avoid excessive paging by not setting the fixed memory size too large for your system.
This article has discussed how SQL Server utilises memory to enhance performance and also discussed some of the options a DBA may need to adjust so SQL Server can work in relative harmony with other applications. Microsoft�s best practice suggests a dedicated a server for your SQL Server system and I tend to agree with them but if this is not an option for you configure the SQL Server memory allocation with utmost care to minimise the impact on performance.
Article copyrighted by www.sqldruid.com, source: Memory Management in SQL Server 2005