SQL Server Agent jobs - performance tuning

• Kurt W. Zimmerman

  SSCrazy Eights

  Points: 8676

  More actions

  May 9, 2007 at 9:10 am

  #173587

  It is always challenging to take over a production system and to try to find ways to make things more efficient.

  I manage several medium size databases. Without going in and re-engineering the 2200+ stored procedures (really not practical) I've gone in and tuned the most frequently used and worse performing SProcs with great success. I've also put in an index manager to address the worse case index fragmentation. All of these have paid off big time and I am seeing great database response time with an increased volume of users.

  So my next area that I want to focus on for performance tuning are the jobs that run on the job queue (SQL Server Agent Jobs). Most of the jobs execute stored procedures. Some of them are SQL Server Integration Services Packages and some of them are embedded SQL running under TSQL.

  My gut feeling says that the embedded SQL jobs would benefit most if they were converted and executed as stored procedures.

  I would like some feedback on this.....

  Thanks in advance.

  Kurt Zimmerman

  DBA

  RHWI

  Poughkeepsie, NY

  Kurt W. Zimmerman
  SR DBA
  Lefrak Organization
  New York, NY

  http://www.linkedin.com/in/kurtwzimmerman

• Grant Fritchey

  SSC Guru

  Points: 398881

  More actions

  May 10, 2007 at 6:55 am

  #705472

  Hate to say this, but, it depends. There's no inherent advantage of a stored proc over a SQL statement. It's execution plan re-use that you're actually shooting for. However, Agent jobs are frequently run once a day, a week, a month, whatever, so the time that might be saved eliminating the regeneration of an execution plan is probably already lost since the plan has been reused. I'd look at the code being called. If any of it is hard coding values, generating code to incorporate certain parameters or flexibility or something, these may be opportunities to move the code into a stored proc. If it's straightforward code, there may be no benefit at all.

  One other thing to consider, apart from performance, is maintenance. I would suggest that it's easier to maintain code that is all in stored procedures as opposed to ad hoc queries stored god knows where. That may be a better reason to modify these jobs than any kind of performance issue.

  Hopefully that's helpful.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Kurt W. Zimmerman

  SSCrazy Eights

  Points: 8676

  More actions

  May 10, 2007 at 7:32 am

  #705486

  Thanks Grant;

  That does help me. I can see that there are certain jobs that could more than likely benefit to be moved to a SProc. However, for consistency, and as a long time Sr. developer friend of mine stated, it may be worth keeping everything all in the same place... has he put it "nice and tidy".

  Thanks again.

  Kurt

  Kurt W. Zimmerman
  SR DBA
  Lefrak Organization
  New York, NY

  http://www.linkedin.com/in/kurtwzimmerman