Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 26, 1998

Memory Mgt./Concurrency - Page 11

By Sharon Dooley

25 Jan 98

Memory management

Memory is allocated dynamically. It is not possible to fix portions of memory for specific databases. The SQL Server process space does not change size. Only the buffer pool will grow and shrink.

Buffer management is being done by a clock algorithm rather than by a least recently used algorithm. This is reported to improve performance and increase scalability.


The scanning thread directs Read Ahead. There are no configuration parameters. The absence of separate read-ahead threads reduces context switching.

Index reads can be issued from intermediate nodes rather than needing to go to the leaf level in all cases.

Concurrency control

There is full row-level locking. It is not necessary to configure the number of locks. The lock escalation configuration options are gone.

Key range locking is used in indexes. This means that there is more concurrent update and also reduces the amount of locks required for page splits.

The lock granularity is determined at run time based on the lowest cost strategy. This is based on input from the Query Processor. Several different lock granularities may be used for a single query, for example, there may be page locks for index scans and row locks for reading from the table.


Many thanks to Sharon for providing these notes - drop her a note at sharond@compuserve.com and tell her thanks!


MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM