SQL Server Memory Basics

Posted on December 30, 2009 by Amit Banerjee

I will give a brief account of how SQL Server memory is organized (in a nutshell). There is much more to it under the covers! 🙂

SQL Server Memory space is organized in the following manner:

Each instance of Microsoft SQL Server 2000 has an address space with two main components, each of which has several subcomponents:

There are two main areas of memory within SQL Server’s address space, the buffer pool (BPool) and a second memory pool sometimes called the “MemToLeave” area. The contents of the SQL Server buffer pool include cached table data, workspace memory used during query execution for in-memory sorts or hashes, most cached stored procedure and query plans, memory for locks and other internal structures, and the majority of other miscellaneous memory needs of the SQL Server. The Bpool handles memory allocations for 8K and below. All allocation requirements above 8K is satisfied from the MTL region.

Executable code

The number and size of the executable files and dynamic link libraries (DLLs) used by an instance of SQL Server varies over time. In addition to the executable files and DLLs used by Open Data Services, the SQL Server engine, and server Net-Libraries, the following components load in their own DLLs, and these DLLs can allocate memory themselves:
Distributed queries can load an OLE DB Provider DLL on the server running the instance of SQL Server.
Extended stored procedures are implemented as DLLs that are loaded into the address space of the instance of SQL Server.
The OLE Automation system stored procedures are used to create instances of OLE Automation objects. Each class of OLE Automation object loads its own code into the address space of the instance of SQL Server.

Memory pool

The memory pool is the main unit of memory for an instance of SQL Server. Almost all data structures that use memory in an instance of SQL Server are allocated in the memory pool. The main types of objects allocated in the memory pool are:

System-level data structures

These are data structures that hold data global to the instance, such as database descriptors and the lock table.

Buffer cache

This is the pool of buffer pages into which data pages are read.

Procedure cache

This is a pool of pages containing the execution plans for all Transact-SQL statements currently executing in the instance.

Log caches

Each log has a cache of buffer pages used to read and write log pages. The log caches are managed separately from the buffer cache to reduce the synchronization between log and data buffers. This results in fast, robust code.

Connection context

Each connection has a set of data structures that record the current state of the connection. These data structures hold items such as parameter values for queries and stored procedures, cursor positioning information, and tables currently being referenced.

The high level memory architecture remains the same in SQL Server 2005 and 2008. The difference is that we introduced a change called Memory Clerks which were responsible for memory allocation requests for requirements for a particular object/cache store. The allocators changed to Single Page Allocator (any allocation required below 8KB) and Multi Page Allocator (any allocation above 8KB). With the help of SPA and MPA, we are now able to track Buffer Pool and non-Buffer Pool memory requests in a more structured manner when components within SQL Server are requesting for memory. However, if your DLL/CLR code hosted within SQL Server memory is calling VirtualAlloc() directly, then tracking those requires extensive data collection and analysis.

Useful Articles

How to track SQL Server 2000 memory usage using DBCC Memorystatus

How to track SQL Server 2005 memory usage using DBCC Memorystatus

How to insert BLOB data into a SQL Server database table using T-SQL

Posted on December 30, 2009 by Amit Banerjee

One of the methods that you can use to insert BLOB data into a SQL Server database table is:

CREATE TABLE BLOB_TABLE (BLOBName varchar(100),BLOBData varbinary(MAX))

GO

INSERT INTO BLOB_TABLE (BLOBName, BLOBData)

SELECT 'First test file', BulkColumn

FROM OPENROWSET(Bulk 'C:\temp\picture1.jpg', SINGLE_BLOB) AS BLOB

GO

OPENROWSET has the functionality of letting you import BLOB data by returning the contents of the Binary Data as a single rowset varbinary(max) output.

SQL Server 2005 setup failing with 29538 error

Posted on December 30, 2009 by Amit Banerjee

There are numerous occasions when we find that the SQL Server 2005 setup has failed due to the following error:

Product : Database Services (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB955706_sqlrun_sql.msp.log
Error Number : 29538
Error Description : MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again.

This can happen when we are trying to install SQL Server Service Packs, Hotfixes, Cumulative Updates or GDRs. Now what you need to do is find if any of the following are true:

1. The SQL Server service account doesn’t have sufficient privileges on the DATA folder of SQL Server

2. It is a recommendation that the MSSQLSYSTEMRESOURCE files (MDF and LDF) be in the same location as the master database. This issue is documented under KB947989.

3. Another point that you would want to check is if you have any services to re-start the SQL service if it is stopped. SQL Server Setup performs setup in two phases: the first phase being replacement of binaries and the second phase being running the configuration scripts for updating the metadata. During the first phase, the SQL Server service is stopped. In case, you have a service which restarts the SQL service at that point, then you would run into this issue.

The easiest way to verify this would be to check the status of the SQL service from the Services snap-in right after the failure and it should show up as started. Or if you are command prompt savvy Smile

, then you could use "sc query mssqlserver" (provided it’s a default instance) to check the state. If it’s a named instance, then the service name would change from mssqlserver to mssql$<instance name>

Sample output:

SERVICE_NAME: mssqlserver
        TYPE               : 10 WIN32_OWN_PROCESS
        STATE              : 4 RUNNING
                                (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0 (0x0)
        SERVICE_EXIT_CODE : 0 (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

Two of the known service restart rules applied by other non-Microsoft services are:

1. Cisco security agent has a Service Restart rule which causes the SQL server service to restart once it is stopped.

2. Altiris has a similar rule.

The job of these services is to start services that were stopped. So when SQL setup stopped SQL Server service these services started SQL service which ended up holding a handle on this mssqlsystemresource1.ldf.

Customizing the TokenPerm in Yukon SP3!!

Posted on December 30, 2009 by Amit Banerjee

I had written about the TokenPerm cache store in a previous blog post of mine. The trace flags mentioned in the KB Articles helped in throttling the cache to prevent the cache size from growing out-of-proportions. Recently, a new Trace Flag (-T4621) was added SQL Server 2005 Service Pack 3 (KB959823) to customize the size of the Token Perm cache. Instead of hard coding the quota for Token Perm with the earlier trace flags, you can use the formula to specify the Registry value:

Quota = 1,024 * 2 * ( <Number of Distinct Logins> + <Total Number of Users in each database> )

Remember to turn off the other trace flags for Token Perm issues that you had activated on your systems previously. You cannot enable trace flag 4621 together with trace flag 4618. When trace flag 4621 and trace flag 4618 are enabled together, trace flag 4618 takes precedence. The reason this trace flag was introduced was to prevent detrimental effect to systems where constant flushing of the Token Perm cache would affect the performance rather than helping it.

Script for printing out ASCII characters

Posted on December 30, 2009 by Amit Banerjee

I had recently written a script to convert a String to it’s equivalent in ASCII using the Sample code given in Books Online. Thought I would put it up so that other people could use it as well.

<<SCRIPT>>

SET TEXTSIZE 0 

SET NOCOUNT ON 

--Declare the Local Variables 

DECLARE @position int, @string varchar(256),@count int,@rownum int,@output varchar(8000) 

-- Initialize the Local Variables used to keep track of the current position and offsets 

SET @position = 1 

SET @count = 1 

-- Get the data that you want to convert to ASCII 

select [name],ROW_NUMBER() OVER(ORDER BY [name]) as ROWNUM 

into #tmp_tbl 

from sys.syslogins 

select @rownum = max(rownum) from #tmp_tbl 

-- Begin WHILE loop to convert the String to ASCII 

while (@rownum >= @count) 

begin 

select @string = [name] from #tmp_tbl where rownum = @count 

print 'Converting:'+@string 

WHILE @position <= DATALENGTH(@string) 

BEGIN 

SET @output = @output+space(1)+CAST(ASCII(SUBSTRING(@string, @position, 1)) as CHAR(4)) + '|' + CHAR(ASCII(SUBSTRING(@string, @position, 1))) 

SET @position = @position + 1 

END 

set @position = 1 

set @count = @count + 1 

print @output 

set @output = '' 

end 

-- Drop the Temporary Table created above 

drop table #tmp_tbl 

SET NOCOUNT OFF

<</SCRIPT>

A sample output of the above script would be:

Converting: tester

116 |t 101 |e 115 |s 116 |t 101 |e 114 |r