• Bouke Bruinsma (4/21/2014)


    Essentially, when you install a second instance of SQL, it's (almost) completely stand-alone. Both instances will have their own TEMPDB, Master, MSDB, and Model databases, each instance will be running as separate Windows services, etc. So, no contention between them for TEMPDB resources (except possibly for disk, if both instances TEMPDB are on the same disks) The reason for the "almost" is, both instances will share some things, if they're installed, such as SSMS.

    I realize this. But then I fail to understand how these dedicated databases on the same hardware could relieve performance issues. If e.g. we have nr of tempdb datafiles at the nr of physical cores, how would an extra instance help even more?

    I grant that an extra instance would help if also dedicated disks for this instance were available.

    Quite simply, really, they don't "relieve performance issues," they work around the performance issues. If I read what you posted correctly, if you have multiple copies of your application being hosted by a single instance of SQL, there (may) be issues with contention over TEMPDB. All putting them on separate SQL instances does is work around the underlying problem, it doesn't resolve any performance issues.

    From what you've posted so far, it sounds like you might be on the right track. Try to find out what's causing the problems when there's more than one install of the application sharing a SQL instance, then work on resolving that.