SQL Server, SharePoint, Azure, Security and more…
Saturday October 25th 2014

5Y in a ROW!

MVP


CO-AUTHOR

SQL Server MVP Deep Dives Volume Two

CONTRIBUTOR

BOOKLET__ExpoloringMDX_AshimMazumder
December 2012
M T W T F S S
« Nov   Jan »
 12
3456789
10111213141516
17181920212223
24252627282930
31  

[EN] How to do It? Using MAX MEMORY option in SQL Server {updated}

It’s nothing new, generally. You can find a lot of great posts abour MAX MEMORY option for SQL Server Environment from many experts, for example from my MVP’s Family Members: Glenn Berry {BLOG|TWITTER} and Jorge Segarra {BLOG|TWITTER}. You can albo find a lot of information about it on MSDN, TechNet and other media, such as books. But many times, many sql server administrators forgotting about this option. Option which should be a part of Your personal DBA’s list called: MUST DO IT FIRST.

I’m writing this post, because it could help You, and because it’s very helpfull option for me. OK not only helpfull, this option saved my speakers live few times. I have bad luck futh my standard laptop, The Old one have been damaged few times, The New One is not ready yet. And – with Murphy’s Law – as always crashed happened day before my session. Fortunatelly I’ve second one. Acer Aspire One. It’s small netbook, quadcore, 64-bits OS, and 320 GB HNN (unfortunatelly not SSD) and UNFORTUNATELLY with 2GB RAM only. Based on Glenn Berry’s post (hope he will not kill me of borrowing it) look for  this table:

image

Pict.1 | Max Memory Settings for 64-bit SQL Server 2008 Instances

imageI’ve five instances of SQL Server: 2005 Express, 2008 Express and Enterprise, 2008 R2 Express and 2012 Enterprise. I used they on my sessions and learning and sometimes training.  Memory on my laptop is a big problem. Especially that by default SQL Server Instance sizing memory to: 2147483647 MB (it’s about 2TB). But when my instances starting run, starting consuming memory too. As much as possible. Time of execution the query on this netbook is note Grand Goal, but execution is. So, I must limited each of my instances, for working on the same time and not kill my OS, and of course the instances.

What I can do? Setting Up MAX MEMORY option (LINK for MSDN). But remember about minimum limitations on right side. So what is the right way? We can use SQL Server Management Studio (SSMS) in two ways: via graphical user interface and clicking or via T-SQL options. For this case I preffer the second way. Lets do it quick:

Captura_SP_MAXMEMORY_01

Pict.2 | These are full T-SQL statement for configuration MX MEMORY option, but we can go step by step.

Captura_SP_MAXMEMORY_02

Pict. 3 | In phase one – first we must enabling advanced option (switching from “0” to “1”)

Captura_SP_MAXMEMORY_03

Pict. 4 | In phase two – secondly we must send RECONFIGURE statement to database engineCaptura_SP_MAXMEMORY_04

Pict. 5 | In phase two – first we shoould choos right value of memory (in MB). We received confirmation in Message Area.

Captura_SP_MAXMEMORY_05

Pict. 6 | In phase two – second part is the same, we must use RECONFIGURE option.

Captura_SP_MAXMEMORY_06

Pict.7 | Finally we can use it together with only one execution

As You see my MAX MEMORY option has been setting up to 196 MB for each instances. It’s not much, but all instances working pararelly, OS is working, internet and PowerPoint too. I’m ready for small show. There is no official information on MSDN for SQL Server 2012 MAX MEMORY, but they are for SQL Server 2005 and SQL Server 2008:

image

If You need more informationa about it, just go to very interesting, detailed posts:

 

UPDATE {January 2014}: if You preffer clickology than t-sql, You can use server properties like below:

Capturaupd1

Pict.8 | Right-Click on SQL Server and Properties.

Capturaupd2

Pict.9 | On first properties page we can look how much physical memory we have: here 5119 MB (available for SQL Server)

Capturaupd3

Pict.10 | On second page (if we never changed MAXMEMORY option) we will see default memoty: MIN: 128 MB and MAX 2147483647 MB

Capturaupd4

Pict.11 | Last step is changing memory for appropriate value: in this case MIN 256 MB and MAX 4096 MB

Be Sociable, Share!

Leave a Reply

You must be logged in to post a comment.