CODEWITHSUNDEEP
sql-serversql-server-2008
Ben Thul
Ben Thul

Reputation: 32697

Stored prodcedure performance change

I had performance of a stored procedure tank last night. Looking at the query plan, the estimated row count from many of the operators is a lot higher than the actuals. This typically signals to me that statistics are out of date. However, after updating all statistics in all tables in the relevant database with the fullscan option, the problem persists. I've also tried recompiling the stored procedure along with any views that it uses to no avail. Does anyone have any ideas on how to improve the performance or why the performance changed? Thanks in advance.

Upvotes: 1

Views: 87

Answers (1)

MikeCov
MikeCov

Reputation: 136

Based upon what you have gone through so far, I would say take a look at your underlying data, and see if it has substantially changed in any way. Even with the most up to date statistics, queries are usually optimal for a specific range of table sizes / record return counts. Or possibly an index gone/changed somewhere in the underlying tables that would have a similar effect, drastically changing the (estimated) amount of records returned for a particular piece in the query plan.

Upvotes: 1

Related Questions