Post reply

System Performance - High CPU utilization--> SQLSRV.exe

  • October 16, 2007 at 1:32 pm

    #375906

    Hi all,

    I have users complaining about slow performance. I have sql server 2000 installed on windows server 2003. 32 gb of ram.

    the cpu utilization on this particular server shows around 99%.

    and the process whic utilizes the most is SQLSRV.EXE about 98%.

    I have checked all possible solutions for these but still get complaints from users about the slowness.

    THIS IS MY 6TH POST RELATED TO THIS TOPIC. MOST OF MY QUESTIONS WERE UNANSWERED.

    Please can somebody help me with this issue.

    I will answer any questions you have to help me solve this problem.

    Thanks,

    Sathya.

  • October 16, 2007 at 1:50 pm

    #742490

    Do you already know which statements are running during the peak time? (from profiler)

    Best Regards,

    Chris Büttner

  • October 16, 2007 at 1:52 pm

    #742492

    Does you SQL service always take up that much CPU time? Is this all day long or just at certain times throughout the day? Have you begun to run profiler or any other types of troubleshooting so that you might be able to determine if it's a particular process? What is the load like after a restart? Does it immediately go to 98-99% or does it progress to that point only after a certain number of concurrent connections or some such limit? How is your disk utilization? What's your disk Time like? What about locks? Are you seeing a lot of them that take a while to resolve? Are you seeing a bunch of deadlocks? How about procedure cache? What service pack are you using for the SQL instance and OS? How is your 32GB of memory allocated? All to SQL, some to SQL some to OS? Any and all of these may be causing some sort of problem. We really will need a bit more information. Anything you can tell us will help you try to narrow down your issue.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • October 16, 2007 at 2:07 pm

    #742497

    I have used Profiler as well. I have checked all counters available on the server.

    there are a lot of full scans going on. every day the avareage full scan is about 200.

    the cpu usage is about 98-99% all day. even after a reboot of the server.

    but there are less number of complaints after the reboot and then afetr a couple of days they all come back.

    I have also monityored the growth of the tempdb files. ensured they dont autogrow.

    also checked for disk space they stand good as well.

    -Sathya.

  • October 16, 2007 at 2:13 pm

    #742502

    >there are a lot of full scans going on

    Then why are you surprised that CPU is so high?

    There is no silver bullet or settings like

    SET CPU_UTILISATION LOW

    SET SQL_SERVER FAST

    🙂

    You need to optimize your queries.

  • October 16, 2007 at 2:13 pm

    #742504

    Yelling at a messageboard community is not likely to get you answers. At best we might provide some area to look at. If you need more/faster - I'd suggest HIRING a DBA as a consultant to come in and help you figure it out.

    Also - starting out by stating - "Ive tried everything and have gotten no answers to my questions" without the questions or the everything you tried just means we get to start over.

    Define "slow performance" - what's being done? what do the user see? How fast is it when it's fast? how slow is it when it's slow?

    Are you sure it's a SQL problem? What is your front-end application?

    What version of SQL2000 are you running? 32- or 64-bit?

    How much RAM are you using for SQL?

    How many processors?

    What is causing the slowness?

    How many volumes/databases/files/filegroups are involved? are they configured correct? are they configured optimally?

    Is there maintenance required?

    Would any new indexes help the problem?

    Is it bad code/bad design?

    Is something paging to file?

    Are the locks and blocking going on?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 17, 2007 at 10:23 am

    #742801

    You say you've used profiler. Have you identified the queries with high CPU time? If not, analyse the trace (I'd suggest load it into a table and use queries) and identify the queries with the highest CPU time. Those are the ones you need to look at fixing first.

    If you have identified the high CPU queries, have you tried to optimise them, either by rewriting or by modifying indexes?

    If you need help with that, post the individual queries, the schema and if possible the execution plan (obtained by running the query with SHOWPLAN_ALL set on) and we'll see what can be done.

    As for not answering, I looked over all your previous posts. I see one question relating to performance. You got some very good advice from Ninja and Kenneth, which you never replied to. We're all volunteers on this board, we help because we like helping. However I don't like getting shouted at.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 17, 2007 at 3:34 pm

    #742902

    Totally second others here.

    If you need help from us you should "POLITELY" ask, we are all voluntiers and you don't do that when you are trying to get other people's help or do your actual "job".

    An advice:

    1. use Profiler and check for High READS and High CPU queries.

    2. Check Indexes Fragmentation / Statistics (you do have maintenace setup ,right? )

    3. BE POLITE WITH OTHERS.


    * Noel

  • October 18, 2007 at 11:19 am

    #743227

    Performance analysis is a VERY deep and complex subject. The odds of you getting satisfaction for a wide-spread issue like you have is very small. I STRONGLY recommend simply hiring a professional to give your company a performance review and mentor you at the same time so you can be more prepared the next time a problem arises.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • October 19, 2007 at 3:56 am

    #743491

    TRY : select * from sysprocesses where status='running'

    check for the cpu column utilization and check which process is being run by the spid.

  • October 19, 2007 at 9:53 am

    #743688

    You may want to download a 30-day trial of Quest Software's Spotlight on SQL Server:

    http://www.quest.com/2_0/registration.aspx?requestdefid=69

    Provides a visual of various potential bottlenecks in one view and allows you to drill down into various components for more in-depth analysis.

  • October 19, 2007 at 6:30 pm

    #743813

    Here's my 'short list' for tuning:

    Round I

    DBCC UPDATEUSAGE

    UPDATE STATISTICS (with FULL scan) for all tables

    exec sp_recompile for all tables

    exec sp_refreshview for all views

    Round II

    DBCC DBREINDEX

    UPDATE STATISTICS (with FULL scan) for all tables

    exec sp_recompile for all tables

    exec sp_refreshview for all views

    Round III

    Profiler

    Query Hints

    Hint: Write scalable code to start with !

    "gots" list courtesy of Jeff Moden.

    Got cursors?

    Got WHILE loops?

    Got views with aggragate functions?

    Got views on views?

    Got non-setbased triggers?

    Got user defined functions (not all are bad but a lot of folks just don't know

    how to write good ones).

    Processing anything one row at a time?

    Got mixed datatypes?

    Got tables without primary keys?

    Got clustered primary keys on big transactional tables?

    Got dates stored as VARCHAR?

    Got temp DB set to autogrow at 10%?

    Etc, etc...

    Those are ALL different things to look at that can cause performance problems!

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • October 19, 2007 at 7:05 pm

    #743816

    Heh... can't believe you saved that list, Rudy 😀

    --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)

  • October 19, 2007 at 7:26 pm

    #743820

    I have checked all possible solutions for these but still get complaints from users about the slowness.

    I'm thinking you haven't checked ALL possible solutions if you still have a problem or you'd have found the problem 😛

    It could be a server problem... but more likely, it's a code problem. You might have a scheduled job that "listens" for files coming in by looping in a very tight loop. Or, it may be external application code that has a tight listening loop in some vain attempt to do pessimistic row locking. Could be a batch job that's "listening" for something, again, in a tight loop.

    Could also be folks leaving connections open or crap code that has memory leaks.

    Whatever it is, YOU need to look at and study SysProcesses and find out who's using all the CPU time. Track the SPID down to the computer and then to the "user", be it a human, an app, or listener code. If not that, YOU need to check the Windows Server the SQL Server is on and find out who's hogging the CPU. Could even be a worm, virus, or bad code. Might be that someone is using the server as a "stream" for bloody music downloads. Whatever... Nobody on THIS end can help you do that. It's not going to be easy but YOU are the only one that can solve this problem... put your DBA hat on, roll up you sleeves, and get to work. If you spent as much time doing that as you have complaining about how this is the 6th time you posted and still haven't gotten a silver spoon answer, you'd have found the problem by now. 😀

    --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)

  • October 20, 2007 at 2:36 am

    #743846

    Hi

    Good posts, especially with a 'ToDo' list (even it is not complete)

    But the problem is that if one can understand that type of list and what to do in each item, then that person usually does not ask that sort of questions.

    For the persons asking 'My server is too slow.' without even trying to understand WHY that type of list is useless.

    So, in most cases the best response is 'Hire a consultant!' 🙂

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

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