Introduction to Memory configuration

Yesterday I was discussion with one of my friend on memory management and internals to expertise in sql server. So started working more on it and found very fundamental information everyone should aware of it. Memory management for MS sql server is very important. We should have a basic knowledge of memory information, how it works and how to configure to effectively usage of it. Memory is a common resource of the system.

Memory stores everything about sql server.

For 32 Bits system(x86):-

Memory has a limit of 4GB x86 (32bit 2³²) of VAS (Virtual Address Space) which divides as 64KB for Null blocks which stores NULL Pointers, 2GB is for USER MODE and 2 GB for Kernel Mode. This is the default behavior of the memory configuration.

We can increase the USER mode to 3GB by adding /3GB option with /USERVA at BOOT.ini file till Windows server 2003 and earlier version, for Windows server 2008 is BCDEdit.exe. This will be good for USERMODE but restricting KERNEL mode to 1GB which may impact performance somewhere.

What if we wanted to use more than 4GB of memory? For Windows to recognize more than 4GB of memory we have to enable PAE option. PAE option will increase the system to recognize more than 4GB of memory, which is up to 64GB for x86.

Now our system has more than 4GB of memory, but for MS Sql server to use more than 3GB of memory requires to enable AWE configuration option. Using AWE option Sql server will recognize that extra memory enabled by PAE. This extra memory is only good for database cache pages not procedure cache.

USER Mode of Memory is divided into two parts:

1. MemToLeave (Stack Size  * Max Worker Thread ) + (-g startup option)

Default values

Stack size =512k

Max worker thread = 256k

-g option =256mb (we can change this)

1. Buffer Pool (USERMode (2GB) – MemToLeave).

For 64 Bits system:-

The system is having same partitions but here the system has huge capacity for the system. 2 64. so here USER MODE could be 2GB to 8TB which is very high and generally nobody uses this. And Kernel Mode uses up to 8TB, means for 64 Bit system there is no upper limit for memory. Hence no need for PAE option. But we can use AWE ability to increase the VAS is used.

WOW (Windows on windows)

System, which has 32 Bit Sql server on 64 Bit Windows system(WOW). We can have 4GB of USER Mode space. And still use AWE for extra USER Memory.

Additional information:

Buffer Pool Stores:

>>  Sql server connection requires 32 bits of Memory

>>  Lock requires 96 Bytes of memory.

>>  Data Pages

>>  Compile plan

>> Execution Plan

>> Workable

As this is my first blog exclusive on memory, will try to write some more blogs on Memory in future.

Thanx.

Vinay

http://vinay-thakur.spaces.live.com/blog/

http://rdbmsexperts.com/Blogs/

https://twitter/thakurvinay

Reference:

http://blogs.msdn.com/slavao/archive/category/9005.aspx

Bob Ward’s PASS Webcast.

Yesterday I though of doing an audio webcast what all I blog.

So started audio webcast, its a series of webcasts on Whats New blog category which I blog here and here please refer blog when listen.

Today I uploaded it on public folder. Hope you will enjoy it same as I did while recording.

Following are the audio webcasts and their link to it

1. History of MS Sql Server

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma

2. Whats New MS Sql Server 7

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma#resId/645E3FC14D5130F2!744

3.  Whats New in MS Sql Server 2000

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma#resId/645E3FC14D5130F2!746

4. Whats New in MS Sql Server 2005

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma#resId/645E3FC14D5130F2!747

5. Whats New in MS Sql Server 2008 & MS Sql Server 2008 R2

Audio Webcast:

http://cid-645e3fc14d5130f2.skydrive.live.com/self.aspx/.Public/History^_of%20^_MSSqlServer.wma#resId/645E3FC14D5130F2!748

Please let me know your feedback on this.

Please read the Blog as a reference for this Audio Webcast.

http://vinay-thakur.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=cat%3dWhats%2520New

and

http://rdbmsexperts.com/Blogs/?p=57

Thanks and Happy Learning.

Thanx.

Vinay

http://rdbmsexperts.com/Blogs

vinay-thakur.spaces.live.com/

twitter/thakurvinay

As discussed about Disaster Recovery and High Availbility topic here continue on this. today would like to discuss some more information about mirroring.

As stated in my ealier post about mirroring, today I would like to write about how to conifigure mirroring, mirroring is very simple to configure. before going for it we should deside what time of mirroring we want to go with

High Availability

High Protection

High Performance

depending upon that we have to configure Witness.

I would say there are only two steps for it.

1. Create End points

2. alter database “” set PARTNER

this is the simple procedure I want to discuss there are other several ways to setup the mirroring.

1. need to decide what is your “priceple” database server , “Mirror” database server and “Witness” database server. this can be seperate box or difference named instance on the same server, but should be on differenct instance as on one  sql server you can create multiple “endpoints” which is the way Mirroring works(interacts)

the simple command for creating endpoints could be

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT =5022)
FOR DATABASE_MIRRORING (ROLE = ALL )
if you are on same server(box) with different instances then we have to give different port number(eg. 5023, 5024)

* Principle and Mirrored database should be having same edition of sql server. witness can be express edition, and should be of “FULL RECOVERY MODEL”

Backup the Principle and restore it on Mirrored database with NORecovery
once you create the “Endpoints” then we have to alter the database PARTNER to listen to that endpoint port like.

at principle

alter database MirrorDB  set Partner=’TCP://MirroredServer:5022′

at mirror server

alter database MirrorDB  set Partner=’TCP://PricipleSrv:5024′

we can check the property of the principle database of option “Mirror” you will see the end point specified and other mirroring related info.

you are done. this is quick and short way of configuring mirror, once priciple is down it will rollover to mirror database server and Mirror server will become priciple and when Priciple is up that will become “mirror” server ie. reverse the setup.

*You cannot mirror the master, msdb, temp, or model system databases.

You can add on the securites and other configuration on it.

This is what I learned today.

Thanx.

Vinay

http://rdbmsexperts.com/Blogs/

http://vinay-thakur.spaces.live.com/blog

twitter\thakurvinay