What caused a Private Bytes on SQL Server 2005?

Question

My Server is Windows 2003 and SQL Server 2005 Standard. 3 GB of RAM. After run my client program for a while a Private Bytes up to 1.7 GB and never come down.

only process 51 with status "runnable". Click details.

create table #tmpDBCCinputbuffer (
[Event Type] nvarchar(512), 
[Parameters] int,
[EventInfo] nvarchar(512)) 
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(51)') 
select [Event Info] from #tmpDBCCinputbuffer

Call connections have been closed.

Any clue?

Answer 1

SQL Server manages it's own memory pool, and will release memory back to the OS under memory pressure (down to the minimum set in SQL's configured values.

So you do not need to be concerned.

Answer 2

1.7GB is the 2GB (default) user mode address space less 384MB. SQL Server leaves the 384 MB by default.

If you had /3GB set and 3GB RAM, it would use 2.7GB.

See this article, KB 316749 which applies to SQL 7 through to SQL 2005. Oddly, I've not found this information anywhere else, even with SQL Server memory whitepapers. It's easily reproducable though.