Can You Use “LIKE” In a Filtered Index

I was playing around with filtered indexes the other day on a test system, and it got me wondering: “what operators can you use in the predicates to filter the index?” For example,  thinking out loud, in the interest of curiousity, could you use the LIKE operator? For this demo I am using a table I have in a utility database which gathered perfmon counters on a regular basis. Suppose I want to create a filter to find all counters with a name that includes the word “pages”. In the table, there are 7 distinct counters out of 23 that include the word “page”. So can we create a filter with “LIKE”?

2015-03-09 15_47_46

That little red squiggle there suggests you cannot. If you wanted to use LIKE as a filter, you would want to create a view.


CREATE VIEW LookUpView
AS
SELECT [CollectionDateTime], [CounterName], [CounterValue]
FROM [dbo].[MemoryHistory]
WHERE CounterName LIKE '%lookups%';

But in this instance, what is the actual benefit here? Let’s query the underlying table with the LIKE predicate and check the IO statistics.

SET STATISTICS IO ON
SET STATISTICS TIME ON 

select [CollectionDateTime], [CounterName], [CounterValue]
FROM [dbo].[MemoryHistory]
WHERE CounterName LIKE '%Pages%';

and the resulting statistics:


(242662 row(s) affected)
Table 'MemoryHistory'. Scan count 1, logical reads 7217, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
 CPU time = 11422 ms, elapsed time = 11949 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

Now let’s use the view:


SELECT * FROM PageView

And the stats?


(242662 row(s) affected)
Table 'MemoryHistory'. Scan count 1, logical reads 7217, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
 CPU time = 11360 ms, elapsed time = 11903 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

Exactly the same! Even digging down into the execution plan xmls, there is no difference. This is because of View Resolution, which dictates the logic of the query optimiser when using views, be they indexed or unindexed.

2015-03-09 15_54_04

Clearly creating a view this way gives us no benefits, and nor should we expect it to. This is a bad way of using views. But what if we added an index to the view? To do this, we need to drop and recreate the view, specifying WITH SCHEMABINDING. This is a requirement before we can create an index on the view. So we cannot make changes to the underlying table without dropping the view first.


DROP VIEW [dbo].[PageView]
GO

CREATE VIEW PageView
WITH SCHEMABINDING
AS
SELECT [CollectionDateTime], [CounterName], [CounterValue]
FROM [dbo].[MemoryHistory]
WHERE CounterName LIKE '%Pages%';
GO
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON PageView ([CollectionDateTime], [CounterName], [CounterValue]);


SELECT * FROM PageView

And what about those stats?


(242662 row(s) affected)
Table 'PageView'. Scan count 1, logical reads 8980, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
 CPU time = 375 ms, elapsed time = 4182 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

That is much improved! And we can check the execution plan to verify what object the query is using.

2015-03-09 14_34_47

The keen eyed amongst you will notice that the performance of the query improved despite an increase in the number of pages being read. The simple reason for this is that we did not specify page compression; the table itself was created with page compression, whereas the view itself was not. The good news is that you can use page compression, or indeed any compression, on an indexed view. Run below to get a page-compressed index. Running the query again shows a minor performance boost, but the real win here is the space savings.


ALTER INDEX IDX_V1 ON PageView
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);
GO

2015-03-09 19_49_39

We can also run DBCC SHOW_STATISTICS on the clustered index to see the query optimisation statistics on the index. This also includes the histogram, which shows that the ranges of each distinct “PAGES” is stored separately, despite the view looking for strings that are LIKE “%pages%”. Other statistics can be created for the view, but this is beyond the scope of this post. If you want to learn more about this, then Paul White’s Indexed Views and Statistics will demonstrate.

2015-03-10 14_10_10

But hold on a minute, we’ve created an index view here, which look suspiciously like a non clustered index on the original table, just with some filtering. Without testing the efficiency of a non clustered index on the table, there’s not really a baseline for query performance. Maybe we should try to create a non-clustered index on the original table first. After all, the filtered index serves only one predicate; the non clustered index might be a better general fit?


CREATE NONCLUSTERED INDEX NCI_MH
 ON [dbo].[MemoryHistory] ([CollectionDateTime], [CounterName], [CounterValue])
GO

select [CollectionDateTime], [CounterName], [CounterValue]
from dbo.MemoryHistory M
where M.CounterName LIKE '%Pages%'

The stats below show an interesting story:


(242662 row(s) affected)
Table 'MemoryHistory'. Scan count 1, logical reads 1797, physical reads 0, read-ahead reads 35, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
 CPU time = 11469 ms, elapsed time = 11964 ms.
SQL Server parse and compile time:
 CPU time = 0 ms, elapsed time = 0 ms.

There is a considerable discrepancy with the time executed, so the indexed view is clearly the way to go to get faster results.

2015-03-09 15_00_22

Using a query straight from an excellent post by Tim Chapman titled The Joys of Filtered Indexes we can see the size of the two indexes we have created and their fragmentation, page count, and index type.


SELECT
i.name, d.index_type_desc,
d.index_depth, d.avg_fragmentation_in_percent,
d.page_count,
IndexSizeMB = (page_count * 8192)/1024.0/1024.0
FROM sys.dm_db_index_physical_stats
( DB_ID(),
OBJECT_ID('dbo.PageView'),
NULL,NULL, NULL
) d
JOIN sys.indexes i ON
d.index_id = i.index_id AND
d.object_id = i.object_id

SELECT
i.name, d.index_type_desc,
d.index_depth, d.avg_fragmentation_in_percent,
d.page_count,
IndexSizeMB = (page_count * 8192)/1024.0/1024.0
FROM sys.dm_db_index_physical_stats
( DB_ID(),
OBJECT_ID('[dbo].[MemoryHistory]'),
NULL,NULL, NULL
) d
JOIN sys.indexes i ON
d.index_id = i.index_id AND
d.object_id = i.object_id
WHERE
i.index_id > 1

2015-03-10 14_24_29

But wait, there’s more! If we were to drop the non clustered index on the table, and execute the SQL again, what would you expect to happen?

2015-03-09 19_55_11

Wow, we have replicated the stats of using the view! How so? Switching over to the execution plan reveals that the query engine has figured out that by it can use the indexed view! This handy feature is only available on SQL Server Enterprise edition, though it is available through table hints. Again, Paul White’s Indexed Views and Statistics will demonstrate.

2015-03-09 16_02_58

If you’re interested in wanting to know more about indexed views, because there is much I have not covered, like non-clustered indexes on views and how and when statistics are created, as well as the risks with using indexed filters, then Jes Borland’s Introduction to SQL Server Flitered Indexes is a great place to start.

References:

Books Online Indexed Views Limitations and Restrictions

Tim Chapman’s The Joys of Filtered Indexes

 

 

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

2 thoughts on “Can You Use “LIKE” In a Filtered Index”

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