How Statistics impact the performance of SQL Query

In this article I will run through a simple demo which explains how STATISTICS impact the performance. During this Demo, I will use the Number of Logical Reads as performance indicator (More Logical Reads indicates Performance Degrade)

  • Created a new table demo_SalesOrderDetail on test database using AdventureWorks2012.sales.SalesOrderDetail.

use AdminDB

select * into demo_SalesOrderDetail from AdventureWorks2012.sales.SalesOrderDetail

-- Create Clustered Index
ALTER table demo_SalesOrderDetail ADD PRIMARY KEY (SalesOrderDetailID)
GO

-- Create Nonclustered Index
CREATE NONCLUSTERED INDEX [IX_NonClustered_demo_SalesOrderDetail] ON [dbo].[demo_SalesOrderDetail]
([SalesOrderID] ASC)
GO

UPDATE STATISTICS [demo_SalesOrderDetail]  WITH FULLSCAN;

 

  • Include Actual Execution Plan , Enable Statistics on SQL Server Management Studio. Did you see different execution plans for these 2 similar queries? (We will also learn about tipping point)
-- Enable the option to Include Actual Execution Plan, Enable Statistics
SET STATISTICS IO ON;
GO

select * from [demo_SalesOrderDetail] where  SalesOrderID >= 74950  -- 74930 (non-clustered Index is used)
select * from [demo_SalesOrderDetail] where  SalesOrderID >= 74909  -- 74929 (non-clustered Index is not used)

-- -- RESULTS
--(413 row(s) affected)
--Table 'demo_SalesOrderDetail'. Scan count 1, logical reads 1277, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--(514 row(s) affected)
--Table 'demo_SalesOrderDetail'. Scan count 1, logical reads 1502, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Statistics_demo_1

  • Tipping point is the parameter value range where SQL Engine changes its Execution plan. In above example first select statement expected to return 413 records and using non-clustered index is considered as optimal solution has makes 1277 logical reads. Second query returned 514 rows and not using non-clustered index is optimal solution which has 1502 logical reads.
  • SQL not using available non-clustered index, why do you think it is good execution plan? Check this…
select * from [demo_SalesOrderDetail] 
where  SalesOrderID >= 74123  

select * from [demo_SalesOrderDetail] WITH (INDEX([IX_NonClustered_demo_SalesOrderDetail]))
where  SalesOrderID >= 74123  


---- RESULTS 
--(2282 row(s) affected)
--Table 'demo_SalesOrderDetail'. Scan count 1, logical reads 1502, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--(2282 row(s) affected)
--Table 'demo_SalesOrderDetail'. Scan count 1, logical reads 7004, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Statistics_demo_2

  • In above example, both select statements returned same results but number of logical reads went up from 1502 to 7004 due to non-cluster index usage which uses Lookup 2282 times.

 

  • How Statistics impacts the performance? Statistics are used by SQL Engine to create optimal query execution plan, stale STATISTICS will create non optimal execution plan impacting performance.
  •  In below example we will demonstrate simple scenario but in real world when table has millions of rows the impact will be significant.
    • Disable AUTO_UPDATE_STATISTICS on database, update few records on table, Check performance as baseline
    • Update STATISTICS of the table, Check performance to compare
ALTER DATABASE AdminDB SET AUTO_UPDATE_STATISTICS OFF
GO

update [demo_SalesOrderDetail]
set SalesOrderID = SalesOrderID + 100000
where SalesOrderID >= 50000

select * from [demo_SalesOrderDetail] where  SalesOrderID >= 75120

UPDATE STATISTICS [demo_SalesOrderDetail]  WITH FULLSCAN;
GO

select * from [demo_SalesOrderDetail] where  SalesOrderID >= 75120

---- RESULTS 
--(90609 row(s) affected)
--Table 'demo_SalesOrderDetail'. Scan count 1, logical reads 271987, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--(90609 row(s) affected)
--Table 'demo_SalesOrderDetail'. Scan count 1, logical reads 1502, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  • In above example, number of logical Reads went down from 272K to 1.5 just by updating the STATISTICS.

Clean up:

ALTER DATABASE AdminDB SET AUTO_UPDATE_STATISTICS ON
drop table [demo_SalesOrderDetail]
Advertisements
This entry was posted in Performance, Script, SQL and tagged , , , , , , . Bookmark the permalink.

One Response to How Statistics impact the performance of SQL Query

  1. Pingback: SQL Execution plan deep dive (03-Scan, Key Lookup) | Blog for reference – Vijred

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s