MIN and MAX memory settings for SQL 2005 in multiple instances

Question

MIN and MAX memory settings for SQL 2005 in multiple instances

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 1

Best bet is to build the environment based on need.. share the instances if possible because each instance running can load the OS with as much as 1.5 GB memory just running SQL (that's not counting the buffer pool). another issue is that the more instances you have the more patching, etc. you'll do. Don't just do it to do it.

How much memory SQL uses apart from the B pool depends partly on how busy your Instance is.

Slava Oks has the definitive article on SQL memory use.

http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx

From experience, the most important things in a multi-instance environment are E and F.

Nothing kills a whole server faster than when the OS is memory starved. In your memory sizing, keep everything in memory.. don't let the OS use the pagefile.

Another thing to watch out for with multi instance machines is disk. give each instance its own disk.

Watch out too for scheduled tasks all kicking off at the same time and hogging resources (DBCC's and Reindexing are bad ones)

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Maybe I missed it, but can I ask WHY you are doing 5 instances on one server? There is some significant downside to that so I am curious about your justifications/requirements.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Rudy Panigas SSChampion Points: 10702 More actions · Answer 3

Good question. The reason people put more than 1 instance on a server is to consolidate and thus get ride of servers. Why would you need 5 servers with all could run very well on 1 larger server. Most companies are doing this when they are trying to reduce costs, get ride of older servers and/or ending their lease on servers.

We have been running several servers with more that 10 instances and all work very well.

Thanks,

Rudy

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

I am not saying that it won't work, nor that there aren't valid reasons for having multiple instances. But consolidation for consolidation's sake is not one of them. You can put all the databases from 10 different servers on ONE instance on a sufficiently sized server and they will work fine there too just like they do with your ten instances. Given many/most scenarios they can actually work much more efficiently on one rather than ten instances. There is quite a lot of 'wasted' overhead in ten instances of sql server, especially in the RAM and thread/context switching realms.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Rudy Panigas SSChampion Points: 10702 More actions · Answer 5

Yes I agree that consolidation for that sake of consolidation is not the way to go. But if, for example, you are planning to migrate from a SQL 200 to SQL 2005/8 consolidation may be the way to go.

Now we also have several servers that are stand alone for example our data ware house system. In this case since it is heavily used, it would not make sense to place it onto a consolidated server. I found that if you spend the time to learn the application and server needs (this includes collecting complete server and sql server stats over a long period of time) then you will be able to decide if stand alone, consolidation or even virtual is the best way to move your system. You may even decide to leave it as is.

Rudy