SQL Server Update Statistics Performance Impact

SQL Server Update Statistics Performance Impact

SQL Server Update Statistics Performance Impact

The post “SQL Server Update Statistics Performance Impact” shows some interesting points on how statistics impact the query performance in SQL Server. Now before look into the impact we’ll quickly go through the essential basics:

Index Statistics:

Index statistics contain information about the distribution of index key values. By distribution, I mean the number of rows associated with each key value. SQL Server uses this information to determine what kind of execution plan to use when processing a query.

Types of Statistics:

  • Statistics created by optimizer.
  • Statistics created due to index creation.
  • User defined statistics created from “CREATE STATISTICS”
  • Query statistics generated from SET options. i.e SET STATISTICS IO / TIME ON / OFF;

Updating Statistics:

Auto Update Statistics: If there is an incoming query but statistics are outdated then SQL Server first updates the statistics before building the execution plan.

Query Submitted Check and Update Statistics if required Prepare the execution plan Execute

Auto Update Statistics Asynchronous: If there is an incoming query but statistics are outdated then SQL Server executes query with the current statistics and a background process will start to update the statistics in a separate thread.

Query Submitted Prepare the execution plan using the current statistics Execute Update Statistics if required.

SQL Server Update Statistics Performance Impact:

We’ll see how statistics impact the query performance with a simple example. I am using the database “AdventureWorks2014” for demo in DEVELOPMENT environment.

  • Create a table “sales.OrderDetail” from “sales.SalesOrderDetail”
  • Switch off AUTO UPDATE STATISTICS
  • Apply some INSERT and DELETE operations on the table
  • Empty the cache (Buffer Cache and Procedure Cache)
  • Write a simple SELECT query and check the estimated execution plan
  • Run the query and check the actual execution plan
  • Update the Statistics
  • Check the estimated execution plan
  • Run the query and see the actual execution plan

1. Create a Table:

USE [AdventureWorks2014]

GO

–Created a table called [Sales].[OrderDetail] by taking the create script from [Sales].[SalesOrderDetail]

2. Switch OFF AUTO UPDATE STATISTICS

 

 

3. INSERT and DELETE records from the table

 

 

4. Clear the cache for the accurate results

 

 

When Statistics are Outdated

5. Check the Estimated and Actual Execution plans:

 

SQL Server Update Statistics Performance ImpactSQL Server Update Statistics Performance Impact

From the above picture we can clearly see the Actual Row Count: 541438 and Estimated Row Count: 183375. Difference is (541438 – 183375) = 358063. Which means the estimated row count is not even 50% of the actual row count. This is just a simple query against a clustered index. Now you can imagine what happens to the critical queries if incase we switch off the auto update statistics. While designing the execution plan optimizer consider the estimated row count and the histogram (how data has been distributed in given data range) and based on these values it chooses the appropriate operator and index.

When Statistics are updated:

6. Update the Statistics and clear the cache

 

 

7. We’ll check the estimated and actual execution plan for the same query again

SQL Server Update Statistics Performance Impact SQL Server Update Statistics Performance Impact

Now we can clearly see the difference between Actual and Estimated Row count = (541438 – 541124) = 314 rows. This can clearly shows that the updated statistics can give you the more accurate results.

Summary:

  • Do not switch off the Auto Create / Update statistics
  • Outdated statistics doesn’t help optimizer to design the optimized execution plan
  • If query optimizer creates an execution plan based on out dated statistics it might negatively impact the I/O, CPU and execution time.
  • Always switch on properties “AUTO_CREATE_STATISTICS” and “AUTO_UPDATE_STATISTICS”

There are very few cases where switching on Auto_Update_Statistics might causes an extra I/O, we’ll discuss it in a separate post.

Posted in Performance Tuning, SQL Development, SQL Server DBA | Tagged , , , , , | Leave a comment

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz