Statistics and the effect on query performance
Posted by rahmanagoro on October 25, 2012
I have always known about issues with table statistics on a SQL server database, but actually seeing it cause performance problems is a bit of a different experience. I got called from the support teams saying that a report which they normally run on the database has been running for 3 hours now, and shows no sign of completing.
I then logged onto the system, ran some diagnostic queries to pull up the query plan for the query running and then suddenly, something didn’t quite make sense to me. One of the tables which I’m familiar with seems to be returning an estimated number of rows of one. At this stage, I knew this wasn’t right as I’m quite familiar with the table and I know that it contains millions and millions of rows.
Even looking at a section of the query plan, I just knew it wasn’t right at all.
Straight away all the tables which had an estimate of one, I updated the statistics with full scan, and re-ran the query again, and this time it completed in around 6 minutes. One thing to learn in this post is that when you see estimated number of rows of 1, and you know that the table certainly has more than one row, its usually a pointer to show that the statistics are out of date. After updating the statistics we can see from the screenshot below that the pipes which moves from object to object is a lot more bigger in size, this means that the number of rows being worked on it significantly more.
· Always check statistics on the database.
· Ensure that auto update statistics is on for the database, unless you choose to manually run this yourself or the database is very large and manual statistics update is essential.
· Watch out for one row estimates on the query plan especially for large tables when you know the number of rows that ought to be returned is more than one.
· Update statistics full scan may not be suitable for every environment, normally a sampling rate would also work, but tests will need to be carried out to establish which sample rate is suitable.