SQL Server Memory Calculation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL Server Memory Calculation

SQL Server Memory Calculation

Rate Topic

Display Mode

Topic Options

Author

Message

leon2486

Posted Thursday, April 01, 2010 8:12 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:10 AM
Points: 10, Visits: 318

Hi All,

first, let me introduce my self. my name is Leon, i'm a DBA production. i want ask about SQL Server memory sizing best practice.how to calculate the size of memory for a few user in a system? how to obtain the rasio between size of memory and count of user in a system ? is this ratio can be used as a baseline for memory sizing ??

thank you for help.

best regards,
Leon

Post #894804

Steve Jones - SSC Editor

Posted Thursday, April 01, 2010 8:38 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:20 AM
Points: 31,437, Visits: 13,752

Is this a dedicated SQL Server machine? Typically you don't size on user count since any user can run any query. One user can use all resources on a server with a query. It's not based on user count, but rather the load of the queries being run.

On a dedicated SQL Server machine, usually I leave 1GB or so for the OS and allow SQL to use the rest.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #894844

Toby White

Posted Thursday, April 01, 2010 8:42 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, November 09, 2012 2:48 PM
Points: 493, Visits: 636

While user connections do take up a small amount of memory in SQL Server instance, it is for the most part insignificant. A better way to calculate how much memory you might need to allocate to a SQL Server instance is to think about what kind of queries will execute and how large the tables/indexes will be. The buffer pool will be the largest necessary container for memory. It will contain the indexes and heaps of the table structures. Figuring out what size you expect these objects to be is a good place to start.

Another, easier approach is to get as much memory as you can on a 64x machine without having to pay too much. The best price point is usually 32GB or 64GB depending on your vendor and what model server(s) you are going with.

Yet another alternative is to figure out what size you expect your largest database to grow and have enough memory for a percentage of that size. Somewhere between 10 and 30 percent should do depending on your query pattern and usage type (OLTP/OLAP).

Regards,

Toby

Post #894855

leon2486

Posted Thursday, April 01, 2010 6:20 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:10 AM
Points: 10, Visits: 318

Hi Steve,,

Nice to meet you and thank you for your reply.. yes,, this is dedicated to SQL Server Machine.. if we can calculate memory by users memory consumed , do you have best practice to get baseline for memory sizing in database server ??

Thank you for your help..

best regards,
Leon

Post #895409

leon2486

Posted Thursday, April 01, 2010 6:26 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:10 AM
Points: 10, Visits: 318

Hi Toby,

Nice to meet you..

Thank you for your reply...

so you mean is to get baseline for memory sizing, we do stress test to our database server ,, and parallel we calculate how much memory consume by query and user ... it's true ??

best regards,
Leon

Post #895412

SQLRNNR

Posted Thursday, April 01, 2010 6:26 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

Leon, I would recommend you follow the advice by Toby. Though User connections will take a nominal amount of memory, you need to scale for the application and code being executed on your database first.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #895413

leon2486

Posted Monday, April 05, 2010 12:40 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:10 AM
Points: 10, Visits: 318

Dear Jason,

thank you for your reply..

like you said before

CirquedeSQLeil (4/1/2010)

how to calculate size of memory that consumed by application and code ?? and what tools that can we use for calculate that..

thank you for your help..

best regards,
Leon

Post #896550

SQLRNNR

Posted Monday, April 05, 2010 10:05 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

Start with finding what is being cached.

A look at DBCC ProcCache would be useful for procedural entries in cache.

Post #896823

« Prev Topic | Next Topic »

Permissions