January 2008 - Posts

Professional SQL Server 2005 Performance Tuning released
27 January 08 03:36 AM | Christian | with no comments


I've had a couple of e-mails from people in the US that have had their copies delivered today so Wiley/Wrox seem to be ahead of schedule:)  It was due for release in the US on 29th Jan and 8th Feb in the UK.

I'm particularly pleased with the chapter on Tuning and Hardware and OS in which I cover things like PAE, AWE, 64-bit, Hyper-Threading and disk sector alignment in-depth but in a way that I hope will be accessible to a wide audience and the chapter on SQL Server Wait Types which I'll be using as the basis of a session at the SQLBits conference in Birmingham, UK on 1st March.  More details here: http://www.sqlbits.com.  I'll hopefully have some copies of the book from Wiley to give away so come along if you're able to!


Christian Bolton
Database Architect
http://coeo.com - The SQL Server Experts




Filed under:
SQL Server memtoleave, VAS and 64-bit
07 January 08 11:39 AM | Christian | 14 comment(s)

GrumpyOldDBA started a nice thread here: http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/01/06/configuring-windows-2003-x64-for-sql-server.aspx on 64-bit which I began to reply to but it got big enough for a full post.

This is a great topic to start a dialog on, I'm a great supporter of x64 as well having worked through lots of problems with PAE and AWE on the 32-bit platform in the past.

One of the common issues we used to see was with "MemToLeave" being too small or fragmented.  "MemToLeave" is virtual address space (VAS) that's left un-used when SQL Server starts so that external components called by SQL Server are saved some address space.  In the cases I saw, these components were always extended stored procs or linked servers.  The reason we need memtoleave is because the virtual address space on 32-bit is only 2GB which would all be taken by the buffer pool if we let it.  I think the memtoleave value settled at about 340MB after a few service packs of SQL 2000 although it could still be 256MB or a dynamic value in between.  The point is that its only a few hundred MB so it not unusual to run out although you can increase it if you get desperate by using the -g startup parameter.

In SQL 2000 you have to run the VMSTAT utility to get an accurate view of MemToLeave which is quite intrusive but in SQL Server 2005 you can get the information from DMV's.  This script will show you the largest available block of virtual address space (memtoleave) outside the buffer pool:

With VASummary(Size,Reserved,Free) AS
    Size = VaDump.Size,
    Reserved =  SUM(CASE(CONVERT(INT, VaDump.Base)^0)
    WHEN 0 THEN 0 ELSE 1 END),
    Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
    SELECT  CONVERT(VARBINARY, SUM(region_size_in_bytes))
    AS Size, region_allocation_base_address AS Base
    FROM sys.dm_os_virtual_address_dump 
    WHERE region_allocation_base_address <> 0x0
    GROUP BY region_allocation_base_address 
    SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
    FROM sys.dm_os_virtual_address_dump
    WHERE region_allocation_base_address  = 0x0
AS VaDump

SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB] ,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB] 
FROM VASummary 
WHERE Free <> 0

Whats really interesting is comparing the results between 32-bit and 64-bit:

My 32-bit laptop with SQL Server 2005 (32-bit obvously) and 2GB RAM:
Total avail mem, KB
max free size, KB

A 64-bit server with SQL Server 2005 x64 with 8GB RAM:
Total avail mem, KB
max free size, KB

My laptop has memtoleave of 120MB and the server has over 6TB. Beautiful.

Its not entirely glorious as it sounds though because you can still run out of VAS even on 64-bit if you run low on physical memory.  This is why its always recommend to have max server memory set on 64-bit SQL Server to prevent SQL Server taking too much.


Christian Bolton
Database Architect
http://coeo.com - The SQL Server Experts