[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:


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:


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


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


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.


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


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:


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:


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


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


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


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

[PL] Pierwsze przecieki na temat certyfikacji z SQL Server “Denali”

Na blogu Jamesa Serry (Senior Business Intelligence Architect) pojawił się wpis mówiący o planowanych ścieżkach egzaminacyjnych z SQL Server code name “Denali”, który ma być następcą Microsoft SQL Server 2008 R2. Na podstawie odzewu społeczności na post umieszczony na blogu Born To Learn zatytułowanym: SQL Server “Denali” Experts–We Need You! James opisał prawdopodobny (dodam od z własnych źródeł, że bardzo prawdopodobny) kierunek certyfikacji z SQL Server “Denali”.

W skład całego procesu ma wchodzić siedem egzaminów, podzielonych na dwie ścieżki:

  • SQL Server “Denali” Business Intelligence
  • Writing queries with Transact-SQL (T-SQL)
  • ETL Developer or Data Warehouse Developer.
  • BI Developer.
  • BI Architect
  • SQL Server “Denali” Database Professional
    • Writing queries with Transact-SQL (T-SQL)
    • Implementing, Configuring, and Maintaining  Microsoft® SQL Server®
    • Building, Supporting, and Developing Databases for Microsoft® SQL Server®
    • Designing Database Solutions for Microsoft® SQL Server®

    Jest to zasadnicza zmiana w porównaniu do obecnej ścieżki obejmującej trzy kierunki certyfikacji:

    • Database Administrator
    • Database Developer
    • Business Intelligence Developer

    Dodatkowo na stronie SolidQ pojawiła się informacja iz magicy z tej firmy będą pracować nad trzema nowymi kursami szkoleniowymi z zakresu SQL Server “Denali”.

    Oj będzie się działo, będzie…