Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-12-01 : 17:23:22
|
| Hi,We had a PSoft consultant come in to identify the performance issues we were having from last so many months.One interesting thing he pointed out which I would like to share (esp with anyone who might have overdone db tuning to fix performance issues like me):-> SP3 contained a change to the SQL Engine to aggressively reuse sql access plans. One of our processes (cobol sql) which queries the database does so by using a stored procedure sp_execute which takes a sql statement and replaces the bind variables with the ones passed in from the cobol program. The sql statement access plan is reused by the sql engine instead of creating a new plan each time it runs. Normally, this dramatically improves performance but if the access plan is not appropriate for the new bind variables, the sql potentially becomes inefficient. (One of the variables is a paygroup which has the largest # of employees in it and others don't, most of which are processed before it hits the big paygroup).So, the fix was to force a MERGE join (use hint) in the cobol sql because it was always doing bunch of nested joins per query execution plan. - Ultimately, I saw great performance improvement.But this kind of stinks because we never wanted to touch Psoft vanilla cobol - now we have to maintain it esp during application of fixes and patches to application!!Thanks,Sarat.Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
|
|
SQL Server 2000 SP3 Optimization Issue!