Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a reasonable server connected to a SAN which will be running SQL servers for multiples of the same application.

There are no security issues with one application being able to read anothers database.

We are unfortunately in 32 bit windows as well.

I'm of the opinion that it would be better to use one instance on the server, enable AWE so that the server instance can use almost all of the ram we have and then run each of the databases in the one instance.

However I've been overruled by the gods of the IT department on this one, so I'm really curious to hear your thoughts on this. From a performance point of view, am I incorrect that one instance of SQL is better than two?

I know that we could do some failover stuff, but doing that on one blade only seems like overkill to me..

share|improve this question
add comment

6 Answers

up vote 3 down vote accepted

SQL Server 2000 and 2005 Workgroup and Standard (32 bit, I'm not sure about 64 bit) will only use a maximum of 2GB memory so having two instances would allow you to use the full 4GB. This would be true even if you're running 32 bit SQL Standard on x64 Windows, more so in fact since you'd want an instance per 2GB of memory.

In principle using a single instance with two databases is faster than two instances with one database each, though I'm unconvinced it's a huge difference. Having separate instances can be useful for management. For example if you use SQL logins, and there are different sets of logins for different databases, using separate instances can make it easier to keep track of logins.

So the answer to your question is "It depends" :-)

JR

Re Spence's comment: SQL Server Standard on 32 bit Windows can use a maximum of 2GB memory. SQL Server Enterprise can use 3GB if you use the /3GB switch. On Windows 2003 Enterprise with AWE up to at least 16GB memory can be used (possibly more) so you can get better value out of your memory by running more than one instance of SQL Server.

I'm afraid the answer is still "it depends". If you have lots of memory, i.e. 8GB or 16GB, then you want to put the biggest databases in separate instances so they can have 2GB (or 3GB with /3GB) each. If you only have 4GB then I'd probably use a single instance and the /3GB switch as the small amount of extra memory used by having two instances wouldn't be worth the overhead.

As others have commented below there can be other considerations. If you reference two databases at the same time, e.g. in a select query or if you insert from one database into another, then you want them in the same instance for speed.

share|improve this answer
    
Win 2k3 enterprise and Server 2k5 enterprise. Could you update your answer? –  Spence Jul 11 '09 at 16:25
    
Duly updated, see above! –  John Rennie Jul 11 '09 at 16:45
    
Lets say I have 8 Gig. So AWE doesn't give you access in one instance to more than 2 Gig, it just means that your applications in total can use the other 4 gig of memory? –  Spence Jul 11 '09 at 17:06
    
Exactly. With 8GB you could have 4 instances and each could use up to 2GB at the same time. However any one instance could only use a maximum of 2GB, or 3GB with the boot.ini /3GB switch. –  John Rennie Jul 11 '09 at 17:11
    
Don't forget that /3GB reduces the memory available to the kernel to 1GB and this has some consequences, priamrily not being able to manage through PEA more than 16GB RAM (down from 64GB). Also is possible to see degradation of network and disk performance (smaller kernel pools). –  Remus Rusanu Jul 12 '09 at 18:30
show 1 more comment

32 bit is a dead end.

In SQL2K5 the memory consumption for things like plan compilation, the plan size itself and hence the proc cache have increased significantly over 2k. Also other components like the permission token cache tend to grow if you have many users (ie. large org and mid tier impersonates). Since all these cannot benefit from AWE you have a hard time fitting a busy system into a 32 bit memory space. If you have complex queries and plans, aggregating the instances may result in a very high percent of the buffer pool being stolen to these caches leave a small buffer pool to data, resulting in constant cache eviction of compiled plans and low page lifetime expectancy.

On the other hand multiple instances of SQL on the same box have a tendency to step on each other toes, triggering memory pressure in one another. Since the memory managers of the instances don't communicate with one another, is much harder to find an equilibrium compared with a single instance. Also the processor scheduling of multiple instances can suffer from similar issues as the OS will preempt the SQL schedulers amongst the instances, resulting in CPU cache trashing.

share|improve this answer
    
Oh I argued even harder for 64bit believe me. –  Spence Jul 12 '09 at 1:46
    
I trust you Spence. I'm just iterating the obvious for any future eyes that come to stumble upon this after a google search. –  Remus Rusanu Jul 12 '09 at 18:27
add comment

IMHO, I'd say you gods of IT are wrong on this one. As long as there is no security concern to address with having several databases running in the same instance, that's the way to go. Multi-instances always introduce some overhead, which will effectively give your server suboptimal performance. Admin-wise it's also better to stick to one instance.

share|improve this answer
    
You claim to be humble and question the Gods at the same time? ;) –  Sam Jul 12 '09 at 3:10
    
oops - I guess i did... :-) –  Trondh Jul 12 '09 at 14:36
add comment

One advantage of multiple instances is that it effectively scales your tempdb - which can be a performance benefit if your app uses tempdb heavily, and you have each instance's tempdb on different spindles.

share|improve this answer
    
+1 I didn't even think of the temp table. I guess that is a point of shared state between instances. –  Spence Jul 11 '09 at 16:27
    
(-1) - this is a non issue as nothing stops you to distribute tempdb over the same number of spindles without multiple instances. Just reconfigure tempdb - standard in any setup. –  TomTom Mar 27 '10 at 20:32
add comment

As always, it depends: Will the databases ever need to "talk" to one another?

If it's for the same application, you will often encounter times when you want one database to read from or write to the other. If that is the case, two DBs on a single instance is preferable. (No linked servers, shared logins, shared tempdb are all advantages here.)

If, however, the two databases are completely isolated and will never communicate with one another and in fact have nothing remotely to do with one another (i.e SharePoint and SAP), then two instances may be preferable. (The ability to run at different Service Pack levels, or limit the memory used by one instance are two advantages off the top of my head.)

share|improve this answer
    
Could you clarify why instancing is preferred if they don't speak to each other? –  Spence Jul 11 '09 at 16:27
add comment

We have an application that uses large and complex stored procedures which encapsulate most of our business logic.

Our machines operate on 32-bit windows. Previously our backend consisted of a sql instance with multiple databases.

We now have moved to an architecture where we have multiple instances on the server.

The performance of our application dipped terribly the same day we tested it on the new framework and most of our forms (especially those that draw their data from large, complex stored procs) timed out.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.