one instance mith multiple DB VS several instances

Last Post 24 Feb 2008 06:36 AM by TRACEYSQL. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
vhaulotte
New Member
New Member

--
08 Feb 2008 03:59 AM
hi,

We are running sql 2005 on serveral servers, each server has several databases (1 to 5).
Today, I wonder if I should or not setup one instance per database....

pros are: it would be easier to monitor the load by country, and we can restart services for one db only when needed...

question is: what about perfs? will serveral instances consume much more ressources than one with all DBs?
for exemple: mill MSDBs be concurent on the drive and lower perfs?
Who already tryied? and what are the resluts?

thanks
vhaulotte
New Member
New Member

--
08 Feb 2008 06:00 AM
hi, thank you for your post,

What I was trying to fix is that even if I can monitor the number of tran/ db, I can't split my CPU load by db (thus by website). When I have a high CPU load, I often wonder which of the database is the source of the problem.... then I have to check all queries to try to iddentify the load...

Of course I was aware of the multiplication of overhead etc... but I wanted some external feedback... and you gave it...
thanks

SQLUSA
New Member
New Member

--
09 Feb 2008 12:53 AM
You don't have to go multiple instances for load identification reasons.

As Andy pointed out, there are several reasons not to.

You just setup SQL profiler trace for slow running queries (that will show blockings also), save it to a table and run it 24/7 . The trace log has the db info as well, so you can identify which website has the load activity going.

Once you have the trace in a table, you can periodically examine it for slow running sprocs & queries. Generally you keep the logs for a month, but you can keep it for a year if you prefer.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!

tom27
New Member
New Member

--
14 Feb 2008 09:38 PM
It should be an instance per application if you have a high end database server, so that you can effectively use the processing power of your DB server.

Assigning specific DB server resource (example CPU) to an application is also possible.
SQLUSA
New Member
New Member

--
15 Feb 2008 08:09 AM
>And depending on the configuration the memory may be dynamic the memory sharing between instances of SQL Server is less than ideal to say the least.

Also sharing data (if necessary) among dbs on different instances is slower.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/ The Best SQL Server 2005 Training in the World!
tom27
New Member
New Member

--
17 Feb 2008 08:59 PM
When you are saying last resort, there should be some practical use for that feature, other wise Microsoft do not have implemented a not useful feature.





tom27
New Member
New Member

--
18 Feb 2008 04:43 AM
Gunney, Sorry I don't have time to read all this, thanks anyway.
TRACEYSQL
New Member
New Member

--
24 Feb 2008 06:36 AM
I would not split 5 databases into 5 instances.
I would follow what is mentioned and determine what is using up the memory and cpu.

I use Quest and keep this running 24 hours so i can go back after the days activities and see what was run that day and what caused the cpu to spike.

We had similar issues where our CPU would always go up to 100% during the busy end of month activities and i found it was always the same processes and it was the vendors problem to fix and this resulted them in sending us more updated indexes to put on our tables.

I also found out that the number of transactions being generated were just not enough for our SAN, i found this out using Quest the number of IOPS second. Then i performed some SAN tests and the amount of IOPS the disks could take was the same as what we were sending it and it was just max out.

After finding these results there was not much more i could do but get a larger SAN to handle the vast amount of volume of transactions that the organization was doing.

I also found out moving the data to RAID 10 was better as our data was on RAID 5.

If you do not have the budget to buy new equipment you could split up some of the databases to different instances so that your main database has more memory, and the others have fewer. But then there is the maintenance involved.

Before doing anything i would do some measuring and performance to determine what seems to be the problem. This is a big tasks so take it in a few steps first.




Acceptable Use Policy
---