Performance issues due to bad Statistics?

Download PDF – Performance issues due to bad statistics?

Notes only

1. Statistics can be out of date – means we don’t have enough changes that have triggered Auto update stats.

2. In this case we check plan cache for queries where CPU usage is very high. E.g. Heavy index scan, Index seek with heavy key lookup.

3. Under execution plan check estimated and actual number of rows. If there is a huge different between these two then possibly the problem is there. E.g. Actual no of rows – 10000 and Estimated No of rows is 1. Like in table variable case.

4. Missing Statistics in execution plan – This we will get when we check the execution plan. Warnings can be seen in green colour over the graphical execution plan. There we will get a warning about the missing statistics. We normally find this with Indexed views. In this case we can create user defined statistics.

5. Statistics can be corrupted but very rare. In this case we have to rebuild the Index.

6. Statistics are very important for excellent query performance. Always try to keep them up to date.

7. Enable Auto create statistics on.

8. On larger tables we should create a schedule to update stats.

9. Also check queries which are suffering from out of date statistics issue. We can easily check this by comparing estimated and actual number of rows.