Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Low performance only in afternoons Expand / Collapse
Author
Message
Posted Friday, July 05, 2013 7:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:40 PM
Points: 39, Visits: 146
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?
Post #1470726
Posted Friday, July 05, 2013 8:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
Have you got SQL Agent jobs running on the server at those times?
Post #1470773
Posted Friday, July 05, 2013 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:40 PM
Points: 39, Visits: 146
Yes i do have many of them running,

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

Regards
Post #1470783
Posted Friday, July 05, 2013 9:39 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
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
Post #1470798
Posted Friday, July 05, 2013 2:04 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 5,849, Visits: 12,585
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.


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

Post #1470873
Posted Friday, July 05, 2013 4:30 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
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

Post #1470881
Posted Friday, July 05, 2013 4:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 35,967, Visits: 30,258
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1470882
Posted Monday, July 08, 2013 7:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:40 PM
Points: 39, Visits: 146
Thanks That helped
Post #1471168
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse