.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Multiple databases on same instance - performance hit?

Posted By:      Posted Date: September 05, 2010    Points: 0   Category :Sql Server
Hi I am not a SQL person to please excuse me if my terminology is wrong :) We are running SQL 2005 at the moment on Windows 2003 SP2 servers. We have multiple SQL servers spread around in each of our data centres. We have an application we're going to be bringing in that needs it's own SQL database. We need to decide whether we build a dedicated SQL server for this or just create a new database on an exisiting SQL instance. The advantages of the former are that we don't max out any existing servers and affect the performance of other databases, whereas with the latter we save the cost of having to build a new server. For those of you that use SQL heavily, what's the general way of doing things? I know that SQL is memory, disk intensive etc so not sure.

View Complete Post

More Related Resource Links

Steps to performance tune multiple databases with multiple stored procedures

I have a sql2008R2 server with approximately 10 databases and 1400 stored procedures that will eventually support a website.  I currently have 3 sql2008 machines doing the same in production.  The goal of the new server is to remove all non-clustered indexes and performance tune all the stored procedures using the sql tuning adviser and then compare the performance to what we have in production.  I have been dragging my feet on this project as I dont really know where to start as many of the procs exec other procs and access many database objects.  I feel that there should be a specific order in how I start this project and I am looking for some advice from other users that may have had a similar project.   Thanks in advance.

Performance r/w xml and dealing with multiple access.



I've working on a chat type project at the moment. I was originally using a DB but decided to move to XML which has improved speed a lot and also made the control more portable. I have two related issues that I was hoping you guys could pass comment on.

The first is dealing with multiple users either reading or trying to write to the xml file when someone else already has it open. I've had to be an Exception loop around the write process to 'queue' the writes from different users. This means that sometimes there is now an issue with speed of response.

The second issue is to do with the speed at which my code read and writes xml. Speeding it up would help my app generally and but would also help with issue one.

Here is my code. Just so you are aware, when someone makes a post it does Savemessage and then Getmessages. Getmessages also runs every 3 seconds regardless:

public List<Chat.Message> Getmessages(int numberOfMessages, int lastMsgID)
        List<Chat.Message> res = new List<Chat.Message>();

        DataSet xmlDB = new DataSet("xmlMSG");        

        if (xmlDB.Tables.Count > 0)
            // I only want a maximum of the last

Running a single SQL update query on multiple foxpro databases

Hi, Env : SSIS 2008 My requirement is to run a update SQL statement on multiple foxpro databases. The fox database paths are available in a SQL server table.  I've created a for each loop control and assigned the database path to a variable named dbpath. Within the loop, I added an "execute sql task" control. Now what properties are to be set to get the requirement done. Appreciate any help. Regards. BH  Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

How to use single windows service to host multiple WCF Services instances (Each instance of WCF is i

How to use single windows service to host multiple WCF Services. I have a mutiple instance of same WCF service in different folder, each instance has unique service name and port. WCFService has reference to Reg-Free COM Reference, i have Native.WCFService.manifest in each folder. Ex: WCFService1 is in "C:\WCFService01\WCFService.dll, Native.OCWCFService.manifest, OCWCFService.dll.config WCFService2 is in "C:\WCFService02\WCFService.dll, Native.OCWCFService.manifest, OCWCFService.dll.config Now i need host above services from single Windows Service. I am trying to create AppDomain for each above WCFService Instance from Windows Service and then host. Its working if i register COM dll in the server using regsvr32, but when i un register COM dll, i am getting unable to load COM component .... This looks like AppDomain is not loading COM DLL and Manifest files. Its looks strange but we need it in this way because we have production and test environment in the same server. Everything works fine if create a seperate Windows Service for each WCFService Instance, but my manager wants single Windows Service :). Any help whould be really appreciated.  ravi kumar reddy

Rebooting one Instance on a SQL 2008 Server with multiple instances installed

Hello, Hoping someone can answer the following questions ... If I have SQL Server 2008 with multiple Named Instances configured on it, can I reboot one Named Instance without rebooting any of the other Named Instances, or without having to reboot the entire server ? For example, if I have an application that needs to be re-booted and it requires the Database to be re-booted. Regards, JD

Installing multiple instances of SQL Server R2 creates permissions issues - first instance won't st

After installing two named instances of SQL Server 2008 R2 DataCenter edition (R2D2 and C3P0) I found the first instance would no longer start.  The errorlog showed the reason to be an invalid path. 2010-09-14 18:15:39.12 Server      Error: 17113, Severity: 16, State: 1. 2010-09-14 18:15:39.12 Server      Error 3(The system cannot find the path specified.) occurred while opening file 'C:\MSSQL\SQLDAT\MSSQL10_50.R2D2\MSSQL\DATA\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary. Finding the files in the proper location I check the ACLs and found the Local Group created for the second instance on the ACL for the first instance.  That is to say for instance R2D2 I found MSP-JANHONJ7\SQLServerMSSQLUser$MSP-janhonj7$C3P0 instead of the MSP-JANHONJ7\SQLServerMSSQLUser$MSP-janhonj7$R2D2 that was originally there. C:\MSSQL\SQLDAT\MSSQL10_50.R2D2\MSSQL\DATA>icacls master.mdf master.mdf NT AUTHORITY\SYSTEM:(F)            BUILTIN\Administrators:(F)            MSP-JANHONJ7\SQLServerMSSQLUser$MSP-janhonj7$C3P0:(F) It seems that creating the second instance causes this problem.  I have been u

Multiple instance of User Control on Page - Button Click Event

Hi, I have this Web User Control, with 3 controls:<asp:TextBox ID="txtSearch" runat="server" /> <asp:RequiredFieldValidator ID="rfvSearch" runat="server" ControlToValidate="txtSearch" Display="None" /> <asp:Button ID="btnSearch" runat="server" onclick="btnSearch_Click" /> In code behind: protected override void Render(HtmlTextWriter writer) { rfvSearch.ValidationGroup = this.ID + "_validation"; txtSearch.ValidationGroup = this.ID + "_validation"; btnSearch.ValidationGroup = this.ID + "_validation"; base.Render(writer); } protected void btnSearch_Click(object sender, EventArgs e) { Response.Redirect("ResultadoSearch.aspx?argumento=" + txtSearch.Text); } When I try to add two UserControl and I Click in btnSearch of second UserControl, txtSearch.txt returns value of first User control.How I can get the value of second txtSearch?

The SQL Server performance counter 'Page Splits/sec' (instance 'N/A') of object 'SQLServer:Acce

I am getting these page split errors every 5 minutes I have tried everything reindexing rebuilding working with different fill factor values But nothing seems to effect this problem. The threshold is now set to 100 (what the max threshold number i can set the alert to ?) Also please let me know how to get rid of the pagesplit alerts. thanks

ASP.NET Web Application connecting to multiple databases

Hi Experts,I am supposed to develop an enterprise-class ASP.NET Web Application that connects to multiple databases. For example when the web form loads, I should populate different ASP.NET controls (GridView, DataPager, TextBox, DropdownList etc) from multiple databases (SQL Server, Oracle, DB2, SAP Server etc) and also, edit the data in the controls and save the data to the respective databases on button submit.Ideally, what should be approach? Should I use Web Service or WCF for data integration, if so is there a limit on the size of the data?What should be the architecture considering the application should be scalable and easy to maintain?Pardon my ignorance.Thanks

MVVM - Multiple ViewModels of same type --> Singel instance of view


I have a ViewModel that contains a property WorkSpaces:

public ObservableCollection<ServiceViewModel> WorkSpaces{get; set;}

The WorkSpaces I bind to a ContentControl that contains as ItemTemplate a DataTemplate which contains a TabControl

<ContentControl Content="{

Multiple databases backup/restore in a transaction, if possible, into only one file


Hi dear,

I have three databases named XDW, XOLTP, XOLTPSchema. All of these are for only one application. In the application, user should do backup/restore using GUI.


1. How I should do backup/restore in one transaction? i.e. all of three actions should be success elsewhere all of them should fail.

2. Is it possible to backup them into only one file e.g. X.bak?

3. Or, What is the standard solution in this condition?

Thanks in advance.

How To: Joining multiple DataTable using “LINQ to DataSet”

Database snapshots do not work well with databases having multiple log files


It appears there is a bug in the implementation of database snapshots.

If you create a snapshot on a database with multiple log files:

Then restore from the snapshot:

then all of you log files, but one will disappear. As a result the backup command will fail with the following message:
Msg 3636, Level 16, State 2, Line 1
An error occurred while processing 'BackupMetadata' metadata for database id 12 file id 10.
Msg 3046, Level 16, State 2, Line 1
Inconsistent metadata has been encountered. The on

How would you handle this? Multiple instance of a custom class...


Forgive me if this isn't the appropriate place.

I have the following data.

10484	1	30	0.5011	0.4630	0.5254	0.0081	10.8362
10484	1	30	0.5061	0.4666	0.5363	0.0075	12.2901
10484	1	30	0.5051	0.4688	0.5383	0.0060	12.5718
10484	1	30	0.5064	0.4623	0.5352	0.0074	12.9344
10484	2	30	0.5035	0.4637	0.5371	0.0074	13.1082
10484	2	30	0.5092	0.4726	0.5397	0.0075	11.7046
10484	2	30	0.5051	0.4645	0.5368	0.0060	13.1261
10484	2	30	0.5062	0.4642	0.5392	0.0081	13.2161

 I am using the following class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace CvasAdmin.Classes
  public class invitro
    public class invitroDetail
      // pk of the foxpro Dbf
      public double sampleid;

get the databases names attached to specific SQL server instance


Hi guyz,

How can I get the databases names attached to specific SQL server instance?

How to improve performance on an instance with large I/O activity?


I have a mirrored instance that throws up 'unsent log' and 'unrecovered log' errors. Everytime it does the I/O activity shoots up. I was wondering what can be done to improve this?

Extra memory? More processing power?

What if I spread the filegroups across different disks? Would this help?

How to improve performance on an instance with large I/O activity?


I have a mirrored instance that throws up 'unsent log' and 'unrecovered log' errors. Everytime it does the I/O activity shoots up. I was wondering what can be done to improve this?

Extra memory? More processing power?

What if I spread the filegroups across different disks? Would this help?

session variable and multiple web application instance


 I have a web application using window authentication. There are a search page and a edit page in the application. 

In search page, user can enter some search criterias in the textboxes and the search result will be displayed in the gridview by clicking the "Search" button. User can select any row from the gridview and it will take him to the edit page. 

 In the edit page, there is a "Back" button to take the user back to the search page. I use session variables to remember what the search criterias the user entered in the textboxes and what page number the user was in the gridview. So when the user goes back to the search page, the textboxes are pre-populated with the search criterias and the gridview is displayed in the correct page number.

The problem is:

If user1 opened one browser window and did a search using search criterias1, then user1 opened a 2nd browser window and did a search using search criterias2. When the user1 went back to the search page from the edit page using the first browser window, all the textboxes and page numbers are from the 2nd browser window, instead of the 1st one.

Can anyone explain this problem to me and how to prevent it? It will be helpful too if you can give me some links about the similar problem.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend