May 9, 2007 at 9:10 am
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
May 10, 2007 at 6:55 am
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
May 10, 2007 at 7:32 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply