Yesterday I worked on optimizing a database for a client... I had a pretty nice optimization of a stored proceedure in place... that's what I thought at least. Using a computed column with an index I could do direct lookups instead of searching large amounts of text data and when testing the procedure in management studio the execution plan was perfect and IO was reduced from 50k to less than 50 :). But server load didn't drop as expected so I fired up performance monitor and started a trace... guess what the procedure was still running with poor performance.
After some tinkering it was obvious that the application code failed to get the correct execution plan for reasons still unknown (maybe access rights or some weird ansi-option related to the computed column, a did a simple workaround and it worked like out fine.
Lesson learned: There is a difference between successful optimization when running the procedure in management studio and actually applying it to a running application.
When in doubt trust performance monitor.
Prenumerera på:
Kommentarer till inlägget (Atom)
Inga kommentarer:
Skicka en kommentar