Post reply

SQL Low performance only in afternoons

  • July 5, 2013 at 7:20 am

    #274935

    In one of our database, there is a stat about the whole day

    : SQL CPU Usage spikes b/w 8AM & 6PM is 5 spikes - highest is 30% @ 3:52PM; longest b/w 29 to 30% b/w 3:52PM to 3:56PM – spike occurs same time every day

    so i want to find out how to check on whats going wroing during after noon when its slowest?

  • July 5, 2013 at 8:45 am

    #1630106

    Have you got SQL Agent jobs running on the server at those times?

  • July 5, 2013 at 9:01 am

    #1630114

    Yes i do have many of them running,

    what would be the fastest way to get the stats which jobs are most expensive?

    Regards

  • July 5, 2013 at 9:39 am

    #1630128

    If you've got SQL Agent jobs running at a certain time, it is to be expected that there will be a spike in memory usage then, so nothing's necessarily wrong.

    If there is a problem, you may be able to spread the jobs out to even the load, or possibly run them overnight.

    Looking for a simple solution - If you identify a problem time & there's only 1 job running, then that is the problem.

    SQL Server doesn't keep stats about run durations etc by default - you need to generate them yourself. A good way is using Profiler. If you create a trace & run it for the period of interest, this can tell you which queries are slow. There's a lot of info about this on the web. Bear in mind that running a trace will add to the load on the server, so only run it when needed. A good source of information is "Mastering SQL Server Profiler" by Brad McGehee - see chapter 4 "How to identify slow-running queries".

    You can download this book for free.

    Try this:

    download.red-gate.com/ebooks/SQL/Mastering_Profiler_eBook.pdf

  • July 5, 2013 at 2:04 pm

    #1630189

    is a spike of 30% actually causing you a problem?

    If you really want to know the SQL causing the issue run a server side profiler trace (google that) and a perfmon trace over the same time frame.

    when done you can run the profiler gui to display the trace and call the perfmon trace into the profiler gui, they will be displayed in tandem and you will be able to see exactly what was running when the CPU spiked.

    ---------------------------------------------------------------------

  • July 5, 2013 at 4:30 pm

    #1630196

    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/

    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

  • July 5, 2013 at 4:32 pm

    #1630197

    Just to add to what Laurie posted...

    The dbo.sysjobhistory system table in MSDB does keep track of duration and both the job and the level. It certainly won't give you the other goodies that an SQL Provile run will give you but it give you a good place to start on long running jobs.

    You can also get some pretty good information, a lot of which is also contained in SQL Profiler, from the sys.dm_exec_requests without actually running SQL Profiler. Of course, you do have to pay attention to recompiles and the times they occur but you can also isolate both the (for example) stored procedure and the part of the stored procedure using the most resources from that view.

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

  • July 8, 2013 at 7:07 am

    #1630421

    Thanks That helped

Viewing 8 posts - 1 through 7 (of 7 total)

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