When are SQL Server Views updated?
I have two tables, each with about 500k rows (and growing). Inserts / updates happen to them all the time, sometimes 100 per minute. The system is experiencing performance issues such as timeouts on basic inserts into these tables. We tuned our indexes and did the usual optimizations. But I'm wondering if the fact that these two tables refer to 5 kinds with a heavy join could be harmful. I've always thought, perhaps wrongly, that as the underlying tables change, the views that reference them change too. Therefore, if the tables change a lot, it is possible that our system becomes overloaded, constantly playing with catch-up view updates.
source to share
If they are not indexed views (you didn't mention this in your question), they are not "updated" at all.
Normal representations are like macros in C — just handy shorthand to hide some of the larger expression. They are expanded in the syntax tree of any statement referencing them, and the entire tree is then compiled and optimized — at the point of use.
For indexed views, you would be mostly correct - the views are maintained as part of the same transaction that makes changes to the underlying tables. However, the rules for indexed views were designed in such a way that this update should not result in too much penalty (they can be maintained without re-querying the entire base table).
source to share
It depends:
1) If the view is not indexed then the view is expanded
-- View definition
CREATE VIEW Sales.v_SalesOrderDetail
AS
SELECT h.SalesOrderID, h.SalesOrderNumber, h.OrderDate,
d.SalesOrderDetailID, d.OrderQty, d.UnitPrice, d.LineTotal,
p.ProductID, p.Name AS ProductName, p.Color AS ProductColor
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
INNER JOIN Production.Product p ON d.ProductID = p.ProductID
GO
-- View usage
SELECT v.SalesOrderDetailID, v.OrderQty, v.UnitPrice, v.ProductName
FROM Sales.v_SalesOrderDetail v
WHERE v.ProductColor='Red';
GO
If we look at the execution plan (SSMS: Ctrl + M),
we can see that the view ( FROM Sales.v_SalesOrderDetail v
) expands, and the server only requests two tables: Sales.SalesOrderDetail d
and Production.Product p
. Moreover, we see how the connection between Sales.SalesOrderHeader h
and Sales.SalesOrderDetail d
is removed because:
-
clause
SELECT
(SELECT v.SalesOrderDetailID, v.OrderQty, v.UnitPrice, v.ProductName
) does not include columns from the tableSales.SalesOrderHeader
, -
a foreign key constraint exists between this two tables and
-
this FK restriction is trusted.
2) If the view is indexed (which means the view is specified UNIQUE CLUSTERED INDEX
) and the SQL Server version is enterprise, then the view may or may not be extended . If the extended version is published in version <> enterprise, then it is indexed. We can tell the server not to expand the [indexed] view with a hint NOEXPAND
:
-- View definition
CREATE VIEW Sales.v_SalesOrderDetail2
WITH SCHEMABINDING
AS
SELECT h.SalesOrderID, h.SalesOrderNumber, h.OrderDate,
d.SalesOrderDetailID, d.OrderQty, d.UnitPrice, d.LineTotal,
p.ProductID, p.Name AS ProductName, p.Color AS ProductColor
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
INNER JOIN Production.Product p ON d.ProductID = p.ProductID
GO
-- Defining the UNIQUE CLUSTERED INDEX
CREATE UNIQUE CLUSTERED INDEX PK_v_SalesOrderDetail2
ON Sales.v_SalesOrderDetail2 (SalesOrderDetailID);
GO
-- View usage
SELECT v.SalesOrderDetailID, v.OrderQty, v.UnitPrice, v.ProductName
FROM Sales.v_SalesOrderDetail2 v WITH (NOEXPAND)
WHERE v.ProductColor='Red';
GO
In this case, we can see that the execution plan
includes a statement Clustered Index Scan PK_v_SalesOrderDetail2
. Thus, it uses the index defined on the second view.
Be aware: Error indexing SQL Server with index + MERGE .
source to share
Indexing indexes is good, but it might be worth thinking about how often and when you update statistics on those indexes. Also, using a buffer table to store inserts that can then be inserted as a bulk operation every 5 or 10 minutes, or something suitable for your system, might help (setting this on a separate physical disk would be a good idea.) would pick much faster 90% of the time and not much worse than the current 10% of the time.
source to share