September 8, 2006 at 9:48 am
Is it generally considered good practice to keep data in one database if possible to avoid cross database joins? This has been proposed as a short term solution to soem performance problems my company's application is having. Currently all our client data is spreads across about 8 databases. Merging them into one would be a fairly major task and I would like to know if it is worth the effort.
September 8, 2006 at 6:21 pm
There isn't a performance hit (or a small one) to have multiple databases. This changes when they are on separate servers. In that case sql server might decide to pull the whole table across the network.
Moving them in one allows you to use direct relational integrity (DRI), reducing the number of checks (triggers) to keep the data intact. Also you also have to set less permissions on the data (no extra-database ownership chaining). Also all your data is in one backupset.
If it is really for performance I would look for the problem elsewhere unless the databases are spread out amongst several servers. (excluding partioned views)
Has there been some query plan monitoring or performance counter monitoring?
September 11, 2006 at 2:38 am
Thanks for the reply Jo. We have done some query plan monitoring and as a result we have changed some long running sp's to use temp tables instead of derived tables, this seems to have produced good results in some cases. It's just a question of time and resource, we have too many developers and not enuff dbas (i.e. none). I'm sure we're not alone.
Do you have any experience of SQL2005? Is it a worthwhile upgrade?
September 11, 2006 at 11:10 am
I don't have experience with sql 2005. Have you tried the sql server best practices analyzer? http://www.microsoft.com/downloads/details.aspx?FamilyId=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
September 11, 2006 at 1:36 pm
No I haven't I'll give it a go. Many thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply