SQL Server Performance

SQL Server.exe (CPU usage is high)

Discussion in 'General DBA Questions' started by golden1237, Jan 14, 2010.

  1. golden1237 New Member

    Hi all,
    I have a db that hit a very high CPU usage this morning.
    Before that, the DB was performing well.
    However,since yesterday night, the performance become slow and CPU usage hit to almost 100 %.
    The last dbreindex was done last month, but today i check the fragmentation is still ok.
    I did run the profiler and notice some of the stored procedure is taking much time than ever.
    Please kindly advice.

  2. Luis Martin Moderator

    Welcome to the forums!.
    If nothing chance (like more users, some job running, etc.) I suggest to take a look of execution plan in those store procedures.
    May be you need some optimization.
    BTW: What do you use to check CPU usage?
  3. michaellutz New Member

    If you're running on SQL Server 2005 or greater there is a DMV that tracks worst performing SQL since the instance was started. This may help narrow down which SQL is most guilty in using CPU (the DMV tracks CPU utilization as well as duration and reads). http://msdn.microsoft.com/en-us/library/ms189741(SQL.90).aspx. There are other approaches e.g. summarizing a SQL trace but this technique requires more effort. Another approach if the problem is still occurring regularly is to run SQL profiler and trace on statements with higher CPU utilization. Remember thought that your problem may be relatively fast queries that are running very frequently. So it's not necessarily the single statement with the highest CPU utilization that's the problem.
  4. michaellutz New Member

    Woops sorry, this DMV only tracks cumulative stats for plans in cache. So if the guilty SQL has been dropped from cache you won't see it listed here.
  5. preethi Member

    You can also check for blocking issues...
  6. satya Moderator

  7. michaellutz New Member

    This is not a complete answer.
    The information you are sharing just shows how to identify SQL causing high CPU usage. Once the bad SQL is identified, it then needs to be tuned. Also there may be something else going on here - it is odd that 10 threads are being created so consistently for each SPID.
    It's a bit annoying to me when a problem is described and someone assumes that a trouble-shooting article holds the answer and simply emails out a link. Let's let the user run the script you suggest, identify the bad SQL (if that is in fact the problem), and then help them through the trouble-shooting process to the end!
    A script is just a script. Let's ask the user to run the script and share his findings and actually follow through with the solution.
    Also the user never told us they're on SQL2005 or higher. So if they're on SQL2000 the proposed solution won't work!
  8. Luis Martin Moderator

    [quote user="michaellutz"]
    This is not a complete answer.
    The information you are sharing just shows how to identify SQL causing high CPU usage. Once the bad SQL is identified, it then needs to be tuned. Also there may be something else going on here - it is odd that 10 threads are being created so consistently for each SPID.
    It's a bit annoying to me when a problem is described and someone assumes that a trouble-shooting article holds the answer and simply emails out a link. Let's let the user run the script you suggest, identify the bad SQL (if that is in fact the problem), and then help them through the trouble-shooting process to the end!
    A script is just a script. Let's ask the user to run the script and share his findings and actually follow through with the solution.
    Also the user never told us they're on SQL2005 or higher. So if they're on SQL2000 the proposed solution won't work!
    [/quote]
    Take it easy Michaell.
    May be Satya miss (also you) the original poster was in SQL 7,2000 section, and each of as try to help. No matter how.
  9. satya Moderator

    I willnot assume anything before replying any thread here, the reference I gave helps you to get the information (partial). I guess you haven't gone thru the complete information from the posts.
    When I have suggested a script means I have used the same on previous instances, I don't mean to simply throw things up in the air.
  10. golden1237 New Member

    Hi all,
    Sorry for the very late reply.
    We found the problem maybe due to some problem in linked server and caused some internal sql jobs to be pending for quite some time.
    Really appreciate the advice given.

Share This Page