August 28, 2006 at 12:46 pm
What are the factors one can check to see if a query has a clean execution plan. I know we should look into if there are any table scans high i/o cost& cpu cost . Anything else that i need to cehck which can possibly eliminate any performance issues.
TIA
August 29, 2006 at 9:50 am
Appropriate indexing. If you see Bookmark Lookup in the plan, you may want to re-examine the indexes on the table.
Clustered index scans = table scan. You already know about these.
Stored procedure recompilation can utterly destroy performance.
One of our procs was recompiling 70+ times / execution (used a few #temp tables). You need to use Profiler to diagnose these.
August 29, 2006 at 9:59 am
Hi,
also check that the estimated and actual row counts are approximately in agreement. If they are very different, then it indicates that your stats are out of date. since the cost-based optimiser uses the stats to decide how to execute your query, poor stats will lead to sub-optimal query plans for the actual data volume returned, and consequently poor performance.
I puzzled why my development server was running my new version of the financial report so slowly (apart from my code tinkering being the reason). When I checked the query plan, there was a big discrepancy in the estimated and actual row counts. Updating the stats (you can do this from the execution plan) solved the problem. Auto-update statistics is recommended for most databases, but I usually do a manual update once in a while as well.
http://www.sql-server-performance.com/nb_execution_plan_statistics.asp
David
If it ain't broke, don't fix it...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply