Sunday, February 19, 2012

How can I get Actual Operation Cost from Actual Execution Plan?

I have a view in SQLServer 2005. It took 30 sec. to finish. Then I deleted 4500 records from one table that is used in view. It took 90 sec. to finish now. I did a comparison on Actual Execution Plan between before I deleted data and after I deleted data, they are almost same, only different is Actual Number Rows become less after deleted data. So, I wonder why data become less but time become more. When I look closely on the Actual Execution Plan, the ridiculous thing is, there are only Estimated Operation Cost on each step, no Actual Operation Cost. I guess there are something wrong with optimizer because reuse same Execution Plan, but how can I tell which step wrong without Actual Operation Cost.

Thanks!

Henry

There is only Estimated Operation Cost, since the actual operation cost will change from time to time. You may use DBCC to rearange the page allocation, to see if the performance can be improved. However, the view tooks 30 sec. to run does not look good. I would check the view first, to see if there is any thing wrong.|||

Thank you for the response. Because the view is using in production site, we are not able to do tuning on it. We need to figure out why there are big different after deleted records and explain to client. Do you know any way to show us which step or operation spent more time than before. Thanks!!

No comments:

Post a Comment