Post reply

Memory

  • June 15, 2011 at 9:53 pm

    #1339439

    Hello

    Yes, I understand that it's the top CPU queries but it's just 700ms cpu time max. It can very well be only 10% of the total CPU usage.. Unless CPU goes beyond say 60% I won't try to tune queries/system for CPU.

    SQLDiag is a diagnostic tool that comes with SQL installation and is present in installation directory. It can capture all the details including system resources (perfmon and other system details) and profile sql queries. You can configure all the information that you need. However, to analyze the output, you need some free third party tools like PAL (to analyze perfmon counters) and SQLNexus (to analyze the profiler trace). Running SQLDiag will consume say 10% of system resources.

  • June 16, 2011 at 4:32 am

    #1339548

    sqlfriends (6/15/2011)

    I pull a report from SQl standard report just now around 1:00 pm, the report is high CPU top queries, and found the database called Discovery which is the our network managing software database that are on the top list.

    I attached it, can someone help to analyze a little bit. I see it has big number of reads, but I cannot see actual queries, and the important part I catch is the compilation time is at 6:03 am,

    Compilation time of what? The report? Stored procedures? A program?

    FYI: The server side trace or Profiler can give you the text of the queries.

    the report is generated is at 1:00 pm. that is the time that we were experiencing slow reponse time and that also delayed our other transaction backup log to ten minutes later.

    So change the time of the report to run after business hours. See if that changes the response times and the timing of the transaction log backup. If it doesn't, then the report isn't your issue.

    Does that mean the compilation makes the slow down?

    Never assume that something is causing a problem until you've prove it's causing a problem. The best way to find out is to test it (see my above comments). Because if you are stuck on "this is the problem" without double-checking, the problem will always be something else and you're just digging a hole for yourself.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 2 posts - 31 through 31 (of 31 total)

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