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
ALTER DATABASE [AdventureWorks2014] SET AUTO_CREATE_STATISTICS OFF
GO
ALTER DATABASE [AdventureWorks2014] SET AUTO_UPDATE_STATISTICS OFF
GO
3. INSERT and DELETE records from the table
INSERT INTO [sales].[OrderDetail] (SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice,UnitPriceDiscount,rowguid,ModifiedDate)
SELECT SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate FROM [sales].[SalesOrderDetail]
GO 5
GO
DELETE FROM [sales].OrderDetail WHERE OrderDetailID BETWEEN 15000 AND 45000;
GO
DELETE FROM [sales].OrderDetail WHERE OrderDetailID BETWEEN 320000 AND 355000;
4. Clear the cache for the accurate results
USE [AdventureWorks2014]
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
When Statistics are Outdated
5. Check the Estimated and Actual Execution plans:
SELECT SalesOrderID, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal
FROM sales.OrderDetail
WHERE SalesOrderID > 43661;
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
ALTER DATABASE [AdventureWorks2014] SET AUTO_CREATE_STATISTICS ON;
GO
ALTER DATABASE [AdventureWorks2014] SET AUTO_UPDATE_STATISTICS ON;
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
7. We’ll check the estimated and actual execution plan for the same query again
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.
