Post reply

CPU utilization is 100%

  • July 27, 2011 at 3:30 am

    #241459

    HI All,

    Thanks in Advance .

    We have one preprod and production server . All the time the CPU Utilization is 100% on production server . We found that its causing the application server . When we stop the Application server the DB Utlization is very low.

    The preprod server is cloned from production server [After SQl instalation we moved all system and user databases from production]

    we checked the Both servers name by using select @@servername .

    Its showing both server names is same .Now the preprod is going to live .In preprod also the CPU Utilization is 100% constantly . The server names may be the reason the DB utilization is 100% on preprod .

    Thanks,

    Lavanya Sri

  • July 27, 2011 at 4:36 am

    #1359050

    Find out queries which are taking longer time and try to optimize them.

    Is the server capable enough to handle the load you're putting in?

    Are there fragmentation in indexes?

    Are the statistics upto date?


    Pradeep Singh

  • July 27, 2011 at 5:26 am

    #1359082

    Is this 64-bit SQL Server? Check the standard reports for top 5 queries by CPU usage and diagnose it.Look at perf mon for CPU counters and correlate with SQL profiler to track down what is causing it.

    http://qa.sqlservercentral.com/Forums/Topic443346-146-2.aspx#bm444775

    Here is what you can do to check:

    http://www.mssqltips.com/tip.asp?tip=1212

    Cheers,
    - Win.

    " Have a great day "

  • July 27, 2011 at 5:28 am

    #1359085

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Once you find the offending queries post back with the actual execution plans if you need extra help solving those.

  • July 27, 2011 at 5:35 am

    #1359091

    Ninja's_RGR'us (7/27/2011)

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    This set of article by Gail is one of the best for analyzing query performance and otimizing them.


    Pradeep Singh

  • July 27, 2011 at 9:13 pm

    #1359788

    Lavanyasri (7/27/2011)

    HI All,

    Thanks in Advance .

    We have one preprod and production server . All the time the CPU Utilization is 100% on production server . We found that its causing the application server . When we stop the Application server the DB Utlization is very low.

    The preprod server is cloned from production server [After SQl instalation we moved all system and user databases from production]

    we checked the Both servers name by using select @@servername .

    Its showing both server names is same .Now the preprod is going to live .In preprod also the CPU Utilization is 100% constantly . The server names may be the reason the DB utilization is 100% on preprod .

    Thanks,

    Lavanya Sri

    I believe you've correctly identified the problem. You might be able to resolve it somewhat by making sure that the IP addresses are different but having two identically named instances in the same domain is one of the worst things you can do to a server.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 28, 2011 at 4:19 am

    #1359909

    Thanks.

    The two Ip's are different and two machine names are different .

    You have any idea how to find these two servers in the same domain .

    SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName')

    I run the above query it shows different machine names and insatnce name column in NULL .

    We are planning to close the old server . As per my request is it compulsory to chanage the server name by using the below query

    sp_dropserver '[OldServerName]'

    go

    sp_addserver '[NewServerName]','local'

    go

    Please advise on this . after running what are the steps i have to take .

  • July 28, 2011 at 5:31 am

    #1359947

    Lavanyasri (7/28/2011)

    Thanks.

    The two Ip's are different and two machine names are different .

    You have any idea how to find these two servers in the same domain .

    SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName')

    I run the above query it shows different machine names and insatnce name column in NULL .

    We are planning to close the old server . As per my request is it compulsory to chanage the server name by using the below query

    sp_dropserver '[OldServerName]'

    go

    sp_addserver '[NewServerName]','local'

    go

    Please advise on this . after running what are the steps i have to take .

    The next step in that sequence is to restart the sql server service.

    Jeff I've had 2 servers with the same name (not in wondows, only in sql server) in prod before and I never had that issue. Was I just lucky?

  • July 28, 2011 at 6:09 am

    #1359989

    Luck may have something to do with it, Remi. I've never had any luck at running OSQL (old days) or SQLCMD when two SQL Server instances were named precisely the same on the same domain. It may work with WEB apps because of the IP address but it made it impossible for batch files and the like to do their thing correctly.

    Even if that ran perfectly (and it didn't), having identically named servers certainly does cause some confusion. To me, it would be like having two brothers named "Daryl".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 28, 2011 at 6:16 am

    #1359999

    Jeff Moden (7/28/2011)

    Luck may have something to do with it, Remi. I've never had any luck at running OSQL (old days) or SQLCMD when two SQL Server instances were named precisely the same on the same domain. It may work with WEB apps because of the IP address but it made it impossible for batch files and the like to do their thing correctly.

    Even if that ran perfectly (and it didn't), having identically named servers certainly does cause some confusion. To me, it would be like having two brothers named "Daryl".

    Thanks, we'll call this one luck :w00t:.

    Good news is I don't work for them anymore!

  • July 28, 2011 at 6:17 am

    #1360002

    Ninja's_RGR'us (7/28/2011)

    Lavanyasri (7/28/2011)

    Thanks.

    The two Ip's are different and two machine names are different .

    You have any idea how to find these two servers in the same domain .

    SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName')

    I run the above query it shows different machine names and insatnce name column in NULL .

    We are planning to close the old server . As per my request is it compulsory to chanage the server name by using the below query

    sp_dropserver '[OldServerName]'

    go

    sp_addserver '[NewServerName]','local'

    go

    Please advise on this . after running what are the steps i have to take .

    The next step in that sequence is to restart the sql server service.

    @Lavanyasri,

    Agreed with Remi's added suggestion.

    I also suggest that, once that's done, you see if the application server still flames the CPU. If it does, then you good folks need to read the links that Remi provided. They're some of the best links there are on the subject.

    In parallel with that (the above will likely help you identify the code causeing the problem), you might want to find out why simply connecting the Application Server drives the SQL Server nuts. If someone wrote some "are you there?" type of code, they're going to need to put a delay into it. If it's not that, there are several other reasons why it could be happening but I'll have to defer to Front-End programmers on what those other reasons may be.

    Of course, it may have nothing to do with code at all. I'm not sure what the cause could be in this area but it could be hardware related or other IP setting related. I've seen it where coffee pots plugged into the same power line as a router caused collision storms on the network(yeah... they actually did that at one company I worked for). THAT took a while to find. 😛 So, it could be anything and it may take a while to find.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 28, 2011 at 6:48 am

    #1360034

    HI Jeff ,

    You have any idea how to find two servers in the same domain . ??

  • July 28, 2011 at 10:21 am

    #1360291

    Lavanyasri (7/28/2011)

    HI Jeff ,

    You have any idea how to find two servers in the same domain . ??

    I sure don't... that's part of the problem. I normally work with the folks in OPS for such a thing.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 29, 2011 at 4:58 am

    #1360660

    Hi Jeff,

    So is it compulsory to change the server name ??

  • July 29, 2011 at 6:51 am

    #1360729

    Lavanyasri (7/29/2011)

    Hi Jeff,

    So is it compulsory to change the server name ??

    "Compulsory"? I don't actually know. According to Remi above, he's never had a problem with it. I just think that having two identically named SQL Server instances on the same domain is a really, really bad idea because so much is done by server instance name. You can't even have a conversation about one server or another because they have the same names never mind predictably log into one through the likes of SQLCMD... which one would you get when you logged in? I'm not even sure that SQLCMD would log into one or the other successfully especially if you used Windows Logins.

    I've not taken the time to look for one, but I'll just bet there's an MS white paper somewhere out there that explains all the possible problems you can have with identically named SQL Server instances on the same domain. I just never looked for it because I've never worked in a shop where anyone has ever done such (a crazy, IMHO) thing. There might be a good reason to do such a thing but I sure don't know what it is and. it seems to me, that the problems it would cause would override any possible benefit.

    Of course, ALL of that is 100% personal opinion and conjecture on my part because, like I said, I've never worked in an environment where such a thing has been done and I've not taken the time to research what would happen, either.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 15 posts - 1 through 15 (of 27 total)

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