Index of view isn't used on SQL Server

Question

I want to speed up the access to a view and have put an index over it. Unfortunately this is not used, although it seems logical and obvious to me that this should be used. Can someone tell me what I am doing wrong?

CREATE VIEW dbo.PickingInfoTEST WITH SCHEMABINDING AS
SELECT
    ccs.ID_ChargeCarrier,
    ccs.ID_Storage,
    sp.ID_PlantArea,
    pa.ID_Plant,
    p.CompanyNo,
    CAST(s.Description1 AS INT) AS PickingStorage,
    s.Description2 AS TargetCountry,
    COUNT_BIG(*) AS Commonness
FROM dbo.ChargeCarrier_Storage ccs
INNER JOIN dbo.Storage s ON s.ID = ccs.ID_Storage
INNER JOIN dbo.StoragePlace sp ON sp.ID = s.ID
INNER JOIN dbo.PlantArea pa ON pa.ID = sp.ID_PlantArea
INNER JOIN dbo.Plant p ON p.ID = pa.ID_Plant
WHERE s.ID_StorageType = 1
GROUP BY
    ccs.ID_ChargeCarrier,
    ccs.ID_Storage,
    sp.ID_PlantArea,
    pa.ID_Plant,
    p.CompanyNo,
    CAST(s.Description1 AS INT),
    s.Description2
GO

CREATE UNIQUE CLUSTERED INDEX UQX_OnePickingStoragePerCarrier  
    ON dbo.PickingInfoTEST (ID_ChargeCarrier)
GO

CREATE UNIQUE INDEX IX_ChargeCarrier_OriginalStorage
    ON dbo.PickingInfoTEST (ID_ChargeCarrier DESC) INCLUDE (ID_Storage)
GO

SELECT ID_ChargeCarrier
      ,ID_Storage
  FROM dbo.PickingInfoTEST
WHERE ID_ChargeCarrier between 1234 and 5678

I would expect the SELECT statement at the end of my code block to load the cached values from IX_ChargeCarrier_OriginalStorage. It runs the code of the view instead.

Never create an index on `DESC` values unless you actually insert them in that order (which is not the case here), it causes needless fragmentation. SQL Server can read indexes in reverse just fine, so this is not needed for selecting in a particular order. I don't think this would preclude use of the index, though. Try `WITH (NOEXPAND)` to attempt to force the use of an index to see if it's usable at all. — Jeroen Mostert, May 10 '23 at 12:24
With the addition of your clustered index, it seems likely the optimizer won't think your additional index is worth it, as it's not much smaller than the base table even if it got used. You can force the use of the index with `WITH (INDEX (..))` and compare runtime and execution plans; it's probably not going to do a lot. — Jeroen Mostert, May 10 '23 at 12:27
Unless you're running some enterprise versions of sql server, you need NOEXPAND hint that @JeroenMostert mentioned, otherwise sql server just unwraps the view and use the standard algorithm — siggemannen, May 10 '23 at 12:43
@siggemannen: this was true only up until SQL Server 2016 SP1, which made a lot of formerly Enterprise-only features available in all editions (mostly leaving only resource restrictions in place, and some online operations), including indexed view matching. Explicit hinting is still useful to see if the optimizer can make use of the index at all, but it should not be required for normal operations (at least not to make it behave the same as Enterprise). — Jeroen Mostert, May 10 '23 at 12:53
@siggemannen How can I check, if the server running SQL Server Enterprise? — André Reichelt, May 10 '23 at 13:11
@JeroenMostert Not sure where you got that from, pretty sure the restriction is still there https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022?view=sql-server-ver16#rdbms-manageability — Charlieface, May 10 '23 at 13:41
@Charlieface: Interesting, seems to be a case of the docs contradicting themselves. ["Prior to SQL Server 2016 (13.x) Service Pack 1, automatic use of an indexed view by the query optimizer is supported only in specific editions of SQL Server."](https://learn.microsoft.com/sql/t-sql/queries/hints-transact-sql-table#using-noexpand) -- implying that after that it's supported in all editions. But indeed, the feature list only has it for Enterprise (even for 2016), so this might have been planned but then reconsidered. — Jeroen Mostert, May 10 '23 at 13:47

score 0 · Answer 1 · answered May 10 '23 at 12:20

0

The first index on a view has to be a unique, clustered, index. If you don't have another index on there, pretty sure you're seeing that index get defaulted to the cluster. The optimizer won't always spot the index that is best for the query because of row estimates and statistics and other factors. Since this is now a clustered index, you could write a query to select directly from that index.

answered May 10 '23 at 12:20

Grant Fritchey

2,645
19
21

Sorry, I missed to add the clustered index to my code. It was there already. How can I write a query to select from that index directly? – André Reichelt May 10 '23 at 12:22
@AndréReichelt, try adding the `NOEXPAND` hint to see if the view index is used: `FROM dbo.PickingInfoTEST WITH(NOEXPAND)` – Dan Guzman May 10 '23 at 12:55
It's actually using the index with the `NOEXPAND` keyword. – André Reichelt May 10 '23 at 13:13
2

@AndréReichelt, the optimizer only considers view indexes automatically in Enterprise Edition per [the documentation](https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022?view=sql-server-ver16#rdbms-manageability). But even with EE, the hint is sometimes required. – Dan Guzman May 10 '23 at 13:27

score 0 · Answer 2 · answered May 10 '23 at 13:34

The problem is that by default SQL Server will expand a view to its definition within your query. Then it will try to match indexes, which may include your indexed view.

This can sometimes fail, especially with a view as complex as this. It also only matches indexed views in Enterprise, Evaluation and Developer editions.

To prevent this happening, and for other reasons too, you should almost always use WITH (NOEXPAND) hint on indexed views.

SELECT ID_ChargeCarrier
      ,ID_Storage
FROM dbo.PickingInfoTEST WITH (NOEXPAND)
WHERE ID_ChargeCarrier between 1234 and 5678;

There are rare occasions when you might not want to use this hint, such as if you might remove the index, in which case the query will fail. See also Can there be situations where using (noexpand) on an indexed view is undesirable?. But in most cases you should use it.

As mentioned by others, the descending ordered index is almost completely pointless.

Also, you should remove the CAST from the GROUP BY, as it can cause unnecessary sorting (the optimizer can work out that sorting is unnecessary if the primary key is already grouped).

...
GROUP BY
    ccs.ID_ChargeCarrier,
    ccs.ID_Storage,
    sp.ID_PlantArea,
    pa.ID_Plant,
    p.CompanyNo,
    s.Description1,
    s.Description2

Index of view isn't used on SQL Server

2 Answers2