SQL Server Performance Forum – Threads Archive

Performance impact in keeping multiple databases

Hi, Will there be any performace impact in selecting data from multiple databases in the same server and from a single database? Both the databases have reasonable data in them.
Please tell me, how sql server executes a query which refers multiple databases, like referring system tables from master database and things like that. Within a database we can keep multiple filegroups, each file group will have it’s own .ndf file, what difference this will make from keeping multiple databases? What technology is used to connect a local server and a remote server? Regards,
Deva

It makes no difference until you have permissions on those databases.
Can you give more details on what you are trying to do? Madhivanan Failing to plan is Planning to fail

I believe you need to thorough understanding about the SQL Server processess, I suggest to refer to Inside SQL Server 2000 book written by Kalen Delaney which will explain and clarify all your doubts with regard to SQL server. http://www.sqlteam.com/item.asp?ItemID=1452 for your understanding. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi, First of all you should clear about what you wants to do ! <blockquote id="quote">quote:<hr height="1" noshade id="quote">What technology is used to connect a local server and a remote server?<hr height="1" noshade id="quote"></blockquote id="quote"> are you looking for a solutions for Linked Server , then read linked server in BOL <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Will there be any performace impact in selecting data from multiple databases in the same server and from a single database? Both the databases have reasonable data in them. Please tell me, how sql server executes a query which refers multiple databases, like referring system tables from master database and things like that. <hr height="1" noshade id="quote"></blockquote id="quote"> This is not at all clear , what are u going to do with this ! please be more specific ? <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Within a database we can keep multiple filegroups, each file group will have it’s own .ndf file, what difference this will make from keeping multiple databases? <hr height="1" noshade id="quote"></blockquote id="quote"> please search forum for "File Groups" , its already been discussed several times. please be more spesific so that you can get good answers ! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

Hi, First of all, I want to thank all of you who answered for this query, basically we are in the process of spliting a large database into a few small databases, hence I wanted to know the pros and cons of doing so. We have carried out a few exercises to make a decision, but still I thought your suggestions would be greatly helpful, that is why I asked. I was keen to learn how sql server executes a query which refers multiple databases and it will also be helpful in arriving a decision, that is the reason why I asked that. I will go through BOL to collect details about linked server and forum messages regarding file groups, thanks again. Regards,
Deva

What problem you face by keeping a large database then to split it.
You can split the data file by keeping large tables in diffrent filegroup for fast access. It’s not good practise to split the whole large DB.

What kind of activity is on that database?
How frequently the data is getting manipulated on that large database?
What is the userbase connecting to that database? http://www.sql-server-performance.com/sql_2000.asp
http://www.sqlteam.com/item.asp?ItemID=751
http://www.sqlteam.com/item.asp?ItemID=1452 …links for your information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Deva, –Will there be any performace impact in selecting data from multiple databases in the same server and from a single database? Normally, we segrigate the related-data in one database according to the user access. In Single database, its managable in creating related objects with in that database. Like creating views/triggers/procedures etc.
Better to be in single database in this case. Yes you can have different different databases. This will effectively be done if you place your data files in different drives.If you are using 2 different databases which are placed different drives, obviously, access will be faster. The splitting of files can be managed by, spreading files in a filegroup using limiting the filegrowth and adding required files.
— what difference this will make from keeping multiple databases?
We keep different databases according to our needs like, if you have some control database which has objects related to application, nothing related to production, you can segrigate this into seperate database. Or if any database related to developement, which has not any direct impact on production, you can maintain it seperatly. Another advantage is, you can assign seperate owners for seperate database and you can manage easily. -Johnson

]]>