Post reply

New SQL Instance to boost performance?

  • May 10, 2005 at 8:22 am

    #109764

    Im having the following problem:

    I have an SQL server 2000 with 2 xeon 3.0Ghz processors and 4GB of memory and about 50 users on it.

    The problem is that i have about 20 applications running on it and sometimes the processors peak up to 85 and 100% of usage.

    All the system get's very slow...and it hard to work like this.

    What i would like to know is, if i create an second instance of my SQL Server and pass some of the Databases to the new instance, this will improve the memory and processor management or will i increase my problems??

    What my best thing to do to boost my SQL performance and take advantage of all the processing and memory?

  • May 10, 2005 at 12:44 pm

    #557868

    Is it a dedicated SQL Server or other applications also run in the same server?

    You have to figure which processes are taking away your resources (CPUs, Memory etc), It may not be SQL Serevr.

    If you can comfirm it is caued by SQL Server, the next step is try to figure which queries are running too long and comsuming too much resources within the SQL Server.

  • May 10, 2005 at 12:53 pm

    #557871

    My Sql server is dedicated only to my own app, but there are several of them running at the same time.

    I already confirmed that it's my SQL server that is pushing up my CPU up to 80%~100%.

    i already tryed to figure out what are the queries doing it, but there are several of them and i already tryed to put them in best performance, but still get the cpu to go very high.

    As i told you, i would like to try to create a new instance of my SQL server and try to separate some Databases into the new instance.

    The question is:

    Doing so will i get better performance and get more of the dual Xeon processor and the 4 GB memory??

    Best regards and thanks in advanced.

  • May 10, 2005 at 12:59 pm

    #557874

    I don't think multiple instances will help. Instaed, I think it may become even worst since the workloads remain same.

  • May 10, 2005 at 2:29 pm

    #557890

    Regarding a new instance, i think it does give you some flexibility in managing your resources but at the cost of overheads. I would concur with Allen that with the nature of the issue which you are having, multiple instances won't be a good place.

    Also, before going that far, i understand that you have identified some of the intesive queries. Is it possible to restrict the resources being used by the intensive queries e.g by restricting the max degree of parallelism and see if that helps ? Also, is there any processing which can be moved at a different time ?

  • May 10, 2005 at 11:26 pm

    #557934

    It is possible that you need more memory. If your server is swapping a lot right now to the disk. That can cause a lot of overhead.

    Try to find out if that is going on.

    Personally I think you need more memory... But it is just a hunch, because I have not enough information.

    Try to post some more performance counters and database sizes etc.

  • May 11, 2005 at 2:25 am

    #557972

    You say that you would like to 'separate some Databases into the new instance'.  This implies that each instance of your applications accesses a different database on this single server.  If that is the case then you would undoubtedly get a performance increase by introducing a second machine and pointing the different instances at the different servers.

  • May 11, 2005 at 5:56 am

    #558022

    As you can see by the image i created a new instance of current SQL Server, and i will transfer some databases from one instance to the other to see if doing so, each instance will take the most of the 4GB of memory in the server.

    The machine is the same, but what i dont know is if the first instance is using all the 4GB or only 2GB.

    Any ideas if this will make it work better?

    Best regards.

  • May 11, 2005 at 6:50 am

    #558042

    I'll repeat what Allen, SA23 and Tony have already said. The problem is not sql server, it can handle 600 000 transactions / minute if the server is strong enough. The problem is the machine, if you don't change that you don't correct the problem. Now you either get a more powerfull server, another server or more ram at first to see if it helps.

  • May 11, 2005 at 11:34 am

    #558182

    Pleae verify if you have the /3GB switch in the boot.ini file. this would atleast help you in using the available memory optimally.

  • May 11, 2005 at 2:14 pm

    #558215

    Also, check your queries to see if they are using indexes or if you need to redesign them.

     

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply