Multiple Databases or Multiple Instances?
Let’s say you have 2 different
applications, A and B, and only have one physical server. What is a best way to
share that server between these 2 applications? You have several options:
- Create different databases on the same SQL Server instance
- Install 2 separate instances of SQL Server on the same physical
- Install 2 VMware machines
As you may have learned from one of my previous
articles, VMware and SQL Server do not mix; so it shouldn’t even be considered.
So you’re left with 2 choices: 2 separate databases
or 2 different instances. But which is better?
In some cases, you may not have a choice
but to install 2 different instances. The following are some reasons why you
might want to follow this approach:
- Applications have hard-coded and common database names. For
example, if both applications needed to create a database named Customers.
A good example of such an application is BizTalk which, by the way, I
believe that this is not the right way to do it – in many projects,
different databases play roles of ‘namespaces’. But, at first, namespaces
are introduced in MS SQL 2005, but even on SQL 2000 using databases as
namespaces should be considered as a bad practice.
- The second reason why you might need to install separate
instances is because of different requirements in server-wide settings. If
they are not compatible, then you have no choice. These settings may include:
- Server default collation
- Security settings
- Linked servers
- And any other objects in the ‘server’ namespace.
Fortunately, in many cases, an ordinary
application just follows the connection string you provide: login, server name,
databases. So you can create any number of databases on your server and share
it between such applications. It’s true that in 90% of cases, simple
applications do not create logins, do not reboot the server, do not drop or create
databases. So, using separate databases provides them safe sandboxes, right?
First I create 2 databases, A and B, on one
instance of SQL server (2000). Please set recovery model to Simple, and pre-allocate 100Mb for data
and 50Mb for log.
Then, set the instance’s memory fixed at 70Mb:
Of course, it is a ridiculously small
amount of memory for a production server; but we are making a small-scale
experiment. Instead of 70MB, think about 8GB, and instead of 90MB tables, we’re
going to create them using the following script:
Docs (n int identity, k int not null, v varchar(128))
Big (n int identity, k int not null, v varchar(128))
Docs (k,v) select 1, 'this is a test'
declare @t datetime, @n int
insert into Docs (k,v) select k+n,v from Docs
index PP on Big
declare @n int
insert into Big (k,v) select k,v from Docs
Think about huge tables. What is real here
is that a table size (90MB, >2M rows) is bigger, than the server memory
(70MB). That script must be executed on both databases: A and B.
Now, we can perform a full table scan from
both databases. Execute the following scripts several times:
-- Scan A
declare @t datetime
select max(v) from A..Big
-- Scan B
declare @t datetime
select max(v) from B..Big
Ignore the duration of the first
executions; we are interested in last:
- ScanA 9693 6050
- ScanB 2913 2933 2903
For some reason, database B works much
faster than database A. I don’t have a good explanation; I guess it’s because
of the fragmentation of the disk or maybe it’s created on different sectors of
Anyway, both scans represent some heavy
read activity, usual for the OLAP/Reporting system. Now for an OLTP test, we
randomly read 1000 records from different places:
-- Random A
declare @t datetime
declare @k bigint, @n int, @v varchar(128)
select @v=v from A..Big where n=@k%2000000
Make sure you clear the SQL Server cache
before execution, using:
The first execution time was 9193ms, while the
second execution time was only 20ms! It reminds us about two facts, well known
before but are easily overseen today, because their effects are more and more
masked by RAIDs and powerful disk arrays, making them less evident. However,
these facts remain to be true:
1: A hard drive is not a random access
device (for databases). As you see, it took the same time (9 seconds for the
‘cold’ server) to read 2’000’000 records using a sequential read (table scan)
and to read 1000 records in random order. Sequential read was 2000 times
is one of the reasons, why object-oriented databases (even when they are more
logical and closer to the commonly-accepted OO ideology) could not win over relational
databases. OO databases processed data object by object, generating random read
patterns, and could not compete with relational ones
2: A ‘cold’ execution (SQL Server has been just
started, or is accessing data for the very first time) can be more than 450
times (9193/20) slower, than subsequent executions, when data is cached.
That reminds me something that I experienced. In one company, timeout for the
IIS .Net applications was 30 seconds (default). Because of the effect mentioned
above, the execution time of some complicated queries, which normally took 1-2
seconds to execute, took 40-50 seconds (on a ‘cold’ server) which caused a
course, testers did not know it. So, every time QA server had been rebooted, I
had 5-10 reopened issues in bug tracker about timeouts. Everything was ok the
second time. Interestingly, many people could not believe that execution time
can even vary by as much as two times, so I had to make a small demo to prove
Is it applicable for the modern, powerful
disk arrays and RAID controllers with a big cache? Yes, but in such
configurations DBCC DROPCLEANBUFFERS is not enough – it flushes the cache of
SQL Server, but not the controllers’. On a powerful production system, I had
the following results:
- ‘Cold’ SQL Server, ‘cold’ controller – 800ms
- ‘Cold’ SQL Server, ‘hot’ controller – 280ms
- ‘Hot’ SQL server (no access to controller, everything is in
cache) – 11ms
But let’s return to our main subject. Our
query on database A is ‘hot’. But then as time passes, there is activity in the
other database, and the data in cache is gone:
So we run script Random A
(RA) several times, and get the same execution time – 20ms. Then we run Scan on
database B (SB):
RA=20, RA=20, SB=2983, RA=20 – no effect!
But when we do it twice
or more times:
RA=20, RA=20, SB=
2863, SB=3086, RA=7963 (!!!)
SQL Server 2005 has another caching
strategy, and the same effect is much more difficult to reproduce.
On the contrary, nothing like this happens
when you use 2 instances. With 2 instances (each having a half of a memory,
35Mb) RA execution time is always 20ms, no matter how many scans are executed
on another instance. However, with less memory (less caching and worse cache
hits ratio), the Scan time degrades. For Scan A, it degraded from about 4200 ms
to 7600 ms.
So, when multiple databases share the same
server, high activity in one of them rebalances the SQL Server cache so the
most active database benefits from it, while the less active can suffer from lack
of resources – similar to a “DOS” attack.
So, should we install 2 instances instead?
Is it the solution? There are many cons for having 2 instances:
- You should still consider ‘cold server’ issues, because it’s
not an excuse for the customers who get timeouts.
- If you use 2 instances with statically-allocated memory, you
are not using server resources efficiently. If you allow a dynamic memory
configuration, then you get the same “re-balancing” problems, but in a much
- If both instances are accessing the same RAID/external disk
array, they compete for the same controller cache memory anyway, with the
same time of “balancing” issues, not inside SQL Server, but in the
- Finally, SQL 2005 handles such balancing issues more
efficiently than SQL Server 2000.
So, unless you have some fatal issues that
do not allow you to put everything on the same server, the best thing to do is
to use the rule of “Occam’s razor” - entities should not be
multiplied beyond necessity.