SQL Server is not releasing memory?

I got few questions to answer quickly in last week just like a test. Then I suddenly answered then after that I tried to find correct answers to them. I found answers for most of them. And one of them was there as it was.

A Windows server has 32 GB of memory and dedicated for SQL Server database. Every time you start the server the memory utilization of SQL Server gradually increases, until it takes almost all the memory and remains there for days even when there is no database activity. What will you do ?

Actually I answered

We can set maximum memory limit. Some how gradually SQL Server uses available memory.

I knew that SQL Server uses and usually doesn’t release memory, although he don’t need to use it. But I didn’t knew why is that. Then I got to know that Not releasing memory is a feature of SQL Server. On servers we don’t run our day to day applications and most of the times we have allocated separate box for SQL Server. Then nobody will use that memory. If nobody uses that why should we release and allocate again and again. When we are discussing about this problem our production server had reached its maximum memory too Winking smile. And this is not our production servers Smile.

sql server memory leak

Preethi told that this happens often. People who don’t know about SQL Server behavior when saw “task manager” like above they are suggesting to double the memory of server. Then until it takes few days they are happy. Again they are having same issue. Sad smile. Actually it’s not a problem with SQL Server.

If you really want to release that memory you can easily restart SQL Server. Then it begins everything from the beginning. Other wise you can execute those commands.

But even in the TechNet they haven’t mentioned that it is recommended running those commands against production servers. Somehow as I think theirs no need to flush memory manually. Because if you are using SQL Server on your laptop, every time that you restart your laptop it will flush again and again. In production servers we don’t need to flush it manually. Let SQL Server to use that memory as he want.

If you are using one box to install SQL Server and Application both you can set maximum memory limit to SQL Server. Then it will not exceed that limit. But I found this thread from SQLServerCentral.

http://www.sqlservercentral.com/Forums/Topic982342-1550-1.aspx

In that thread he is saying that SQL Server exceeds that maximum memory limit. But technically it should not happen. And I haven’t tested it also. But there may be a reason for that too. If you are setting maximum memory limit you should restart SQL Server. If you don’t restart it, it uses previous memory limit. Somehow below link says another thing. I have to learn about it further. Smile

Yup. Perfectly normal.
Max server memory is the max size of the buffer pool, the memory area that contains the data cache, plan cache and a whole bunch of other caches. SQL also uses memory outside the buffer pool for things like backup buffers, thread stack, linked server drivers, CLR and a few other things. This is outside of the buffer pool, so it’s not part of ‘max server memory’
On 32-bit SQL, that’s referred to as MemToLeave (memory to leave unallocated when assigning the buffer pool). On 64 bit that term has no meaning.

http://www.sqlservercentral.com/Forums/Topic1197388-1550-1.aspx

Picture: http://piglings.blogspot.com/2009/10/sql-server-2008-memory-leak.html

How varchar data is stored?

In SQL Server we use varchar, varbinary to save disk space in our tables. But its not saving data always as it sounds. When the data stores it takes few extra bytes to describe the data length. Because of that there is no gain using varchar(2). If you use varchar(2) it will take 4 or 3 bytes to store data. varchar(8000) or below should have 2Byte offset to store the variability of character count. If you are using varchar(max) is different. It should have 4 Bytes offset. But I’m still looking for that to confirm it takes 4 Bytes. And it is stored in Variable Column Offset Array.

062911_1840_Themysteryo1

Picture: http://www.sqlservercentral.com/blogs/aschenbrenner/2011/06/29/the-mystery-of-the-null-bitmap-mask/

Are you going to save space using Bit Data Type? – Part 2

Yesterday I wrote a post with same title. In that post I mentioned using char(1) is also same like using one bit column in table. But it is from the perspective of storage space. But using numbers is less burden to Database engine. It means using 1 and 0 is better than using ‘Y’ and ‘N’. Also in comparing data or in sorting data it is easier.

If you are storing numbers as a text its not a good thing although you don’t do any mathematical operation with that text. First thing it takes more space. It takes 1 byte for every character, while integers take very less. And second thing if you store them as string it will have extra cost to manipulate, compare, sort, whatever you are going to do with them.

Are you going to save space using Bit Data Type?

If you have Yes or No data to store in DB, what will you select as the data type? Most of the times you will select “Bit”. Because it only takes 1 bit to store data. And it is the best data type for this situation. But it takes more than bit in SQL Server. If you have only one bit column in your table it will allocate 7 more extra bits to store your bit data.

And if you have two bit typed fields in your table it will waste 6 more bytes. But if you are using 8 bit typed fields in your table, it will not waste any space. And if you have only one such a binary values column in your table. Otherwise it will take space same as char(1) or tinyint. Although its name is bit it is not small like bit always.

Data Type details from sphelp

I wanted to learn about data types. Then I went to technet some how most of the important things were there in technet documentations. But I found another way to get figures from SQL Server. We can have details of data types from “sphelp”. You just have to give your data type as a parameter like below.

EXEC sp_help int

Then I executed this to every data type except few system data types. And I listed figures on categories which was in technet.

Exact Numerics

Type Name Length Precision Scale Nullable
bigint 8 19 0 Y
bit 1 1 Null Y
decimal 17 38 38 Y
int 4 10 0 Y
money 8 19 4 Y
numeric 17 38 38 Y
smallint 2 5 0 Y
smallmoney 4 10 4 Y
tinyint 1 3 0 Y

Approximate Numerics

Type Name Length Precision Scale Nullable
float 8 53 Null Y
real 4 24 Null Y

Date and Time

Type Name Length Precision Scale Nullable
date 3 10 0 Y
datetime2 8 27 7 Y
datetime 8 23 3 Y
datetimeoffset 10 34 7 Y
smalldatetime 4 16 0 Y
time 5 16 7 Y
Binary Strings
 
Type Name Length Precision Scale Nullable
binary 8000 8000 Null Y
image 16 2147483647 Null Y
varbinary 8000 8000 Null Y

Character Strings

Type Name Length Precision
char 8000 8000
varchar 8000 8000
text 16 2147483647

Unicode Character Strings

Type Name Length Precision
nchar 8000 4000
nvarchar 8000 4000
ntext 16 1073741823

Other Data Types

Type Name Length Precision Nullable
hierarchyid 892 892 Y
sql_variant 8016 0 Y
timestamp 8 8 N
uniqueidentifier 16 16 Y
xml -1 -1 Y

If I haven’t specified any attribute of those data types usually they have their default values. One special thing was there were only one data type which was not nullable. It is “timestamp”. And I was unable to take data of cursor and table from this method.