Performance Tuning part -9 – Views

Summary:

We should use view if and only if it is really necessary. Use of the view causes performance problem because it still fetches the data from the underlying tables. However, we can still achieve better performance by using schemabinding views.

Index can not be created on views if it is not schemabinding view and in this case views directly use underlying tables.

Demo:

--Let's create drop the views if already exist

IF EXISTS (
SELECT 1
FROM sys.objects
WHERE name = 'vw_test1'
) --DROP the view if exists
DROP VIEW vw_test1
GO

IF EXISTS (
SELECT 1
FROM sys.objects
WHERE name = 'vw_test2'
) --DROP the view if exists
DROP VIEW vw_test2
GO

--Create simple view first

CREATE VIEW vw_test1
AS
SELECT SalesOrderID
,OrderDate
,DueDate
,STATUS
,ShipDate
,OnlineOrderFlag
,SalesOrderNumber
,PurchaseOrderNumber
,AccountNumber
,CustomerID
,SalesPersonID
FROM Sales.SalesOrderHeader SOH
GO

--Clean buffer and execute select on view

DBCC DROPCLEANBUFFERS --Clean buffer

DBCC FREEPROCCACHE --drop procedure cache
GO
SET STATISTICS IO ON
GO
SELECT SalesOrderID
,ShipDate
,STATUS
FROM vw_test1
WHERE SalesOrderID > 43668
GO
SET STATISTICS IO OFF
GO

Now check the execution plan and IO stats on query

Execution plan

As you can see, The select is directly fetching the records from underlying table SalesOrderHeader.

IO Stats

(1080033 rows affected)
Table ‘SalesOrderHeader’. Scan count 1, logical reads 26305, physical reads 1, read-ahead reads 26315, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Logical reads: 26305

--Now create Schemabinding view

CREATE VIEW vw_test2
WITH SCHEMABINDING
AS
SELECT SalesOrderID
,OrderDate
,DueDate
,STATUS
,ShipDate
,OnlineOrderFlag
,SalesOrderNumber
,PurchaseOrderNumber
,AccountNumber
,CustomerID
,SalesPersonID
FROM Sales.SalesOrderHeader SOH
GO

--Create cluster Index
CREATE UNIQUE CLUSTERED INDEX IX_vw_test2 ON vw_test2 (SalesOrderID)
GO

--Create non-cluster Index
CREATE NONCLUSTERED INDEX NIX_vw_test2 ON vw_test2 (SalesOrderID) INCLUDE (
ShipDate
,STATUS
)
GO

--Clean buffer and execute same select on scemabinding view
DBCC DROPCLEANBUFFERS --Clean buffer

DBCC FREEPROCCACHE --drop procedure cache
GO
SET STATISTICS IO ON
GO

SELECT SalesOrderID
,ShipDate
,STATUS
FROM vw_test2
WHERE SalesOrderID > 43668
GO
SET STATISTICS IO OFF
GO

Let’s check the execution plan and IO stats

Execution Plan

As you can see, This time the index on view has been used and it did not query the underlying table

IO Stats

(1080033 rows affected)
Table ‘vw_test2’. Scan count 1, logical reads 2545, physical reads 0, read-ahead reads 19, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The simple view performed 26305 logical reads while schemabinding view performed only 2545 logicals reads.

We should avoid using views but if it is truly necessary then use schemabinding view and create adequate indexes on view.

Pros of Schema binding view

  • Good Performance
  • A good way to provide the access of limited data
  • Easy to use in comparison to joins

Cons of Schemabinding views

  • Increase disk space use as schemabinding views holds the actual data
  • Decrease write performance because of extra writes.

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 9+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.