April 1, 2010 at 8:12 am
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
April 1, 2010 at 8:38 am
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.
April 1, 2010 at 8:42 am
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
April 1, 2010 at 6:20 pm
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
April 1, 2010 at 6:26 pm
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
April 1, 2010 at 6:26 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 12:40 am
Dear Jason,
thank you for your reply..
like you said before
CirquedeSQLeil (4/1/2010)
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.
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
April 5, 2010 at 10:05 am
Start with finding what is being cached.
A look at DBCC ProcCache would be useful for procedural entries in cache.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply