Performance: Demonstration of Outdated Statistics

Statistics are created when Query Optimizer generates a query plan for getting a better performance. Query Optimizer uses Statistics to estimate how many rows will be returned from a query plan. You can access the statistics under the indexes folder in SSMS.

You can create statistics in two ways. One of them is CREATE STATISTICS command and the other one is CREATE INDEX command. When you create a database, normally Auto Update Statistics, which allows to automatically update the statistics when they are deemed outdated, and Auto Create Statistics, which allows to automatically create stats for a single non-indexed column if they are missing when it is used in a where or join condition, options are set to ON by default. However, we will set them to OFF during the demonstration below. So, lets start with how outdated statistics can affect performance.

Firstly, we need to create a demonstration database with these two options are set to ON.

On the next step, we create and populate a test table (OrdersTest) with 1 million records. This type of CTE based tally table referred by Itzik Ben-Gan. It’s really fast!

Now, demonstration environment is ready to test! Don’t forget, as a first step,  Auto Create Statistics and Auto Update Statistics options are enabled. Now, we have to disable both of them and modify lots of records while these auto statistic options are disabled.

In order to compare Actual Execution Plan and Estimated Execution Plan, drop clean buffers and send our query.

Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics. <TechNet>

 

 

606.655 number of rows are listed totaly different on Actual and Estimated Execution Plans as a result of comparision. In that case, update the statistics and check the results again. We expect that Estimated Rows and Actual Rows will be much more close with updated statistics. The question is when to update statistics? There are three cases; it is highly recommended to update the statistics after the maintanence operations or when you apply bulk insert operations to modify larger amount of data or if your queries are executing slower then before.

 

 

This is much more close than the value we got with the outdated statistics! By the way, you can find the outdated statistics list with Transact-SQL shown as below.


Posted In:
2 Comments
Post Details
2 Comments
  • Gordon Feeney
    Apr 21, 2014

    Bit of a typo in the third block of code (database name). However a useful refresher. I would also add that updating the statistics with the FULLSCAN option in the penultimate code block would probably give you an even more accurate estimated/actual comparison.

    Reply
    • yigitaktan
      Apr 21, 2014

      Greetings. Thx for a typo! I think it happened when I was using Replace All function in my SSMS
      Btw, you are right! But, when you use UPDATE STATISTICS with FULLSCAN option on large tables (the size of the table should matter!) it may cause a significant usage of TempDB of workload instance. In my demo I inserted 1M records into the table. So, I did not want to show/use FULLSCAN while I was updating statistics.

      Reply
  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Time limit is exhausted. Please reload the CAPTCHA.