SQL Server Consultant – Page 67

New System Objects In SQL Server 2022

Posted on May 24, 2022May 24, 2022 by Erik Darling

Today!

SQL Server 2022 was released today, so I’m digging around looking for new stuff. I’ll provide what details I can as I go, but it’s brand new so 🤷‍♂️

Here’s a list of new system objects for you to speculate about!

+------------------------------------------------------------+----------------------------------+
|                            name                            |            type_desc             |
+------------------------------------------------------------+----------------------------------+
| backup_metadata_store                                      | INTERNAL_TABLE                   |
| database_ledger_blocks                                     | VIEW                             |
| database_ledger_digest_locations                           | VIEW                             |
| database_ledger_transactions                               | VIEW                             |
| database_query_store_internal_state                        | VIEW                             |
| db_ledger_blocks                                           | INTERNAL_TABLE                   |
| db_ledger_digest_locations                                 | INTERNAL_TABLE                   |
| db_ledger_transactions                                     | INTERNAL_TABLE                   |
| dm_change_feed_errors                                      | VIEW                             |
| dm_change_feed_log_scan_sessions                           | VIEW                             |
| dm_column_encryption_enclave_properties                    | VIEW                             |
| dm_database_backups                                        | VIEW                             |
| dm_database_external_policy_actions                        | VIEW                             |
| dm_database_external_policy_principal_assigned_actions     | VIEW                             |
| dm_database_external_policy_principals                     | VIEW                             |
| dm_database_external_policy_role_actions                   | VIEW                             |
| dm_database_external_policy_role_members                   | VIEW                             |
| dm_database_external_policy_roles                          | VIEW                             |
| dm_dist_requests                                           | VIEW                             |
| dm_dw_databases                                            | VIEW                             |
| dm_dw_locks                                                | VIEW                             |
| dm_dw_pit_databases                                        | VIEW                             |
| dm_dw_quality_clustering                                   | VIEW                             |
| dm_dw_quality_delta                                        | VIEW                             |
| dm_dw_quality_index                                        | VIEW                             |
| dm_dw_quality_row_group                                    | VIEW                             |
| dm_dw_resource_manager_abort_cache                         | VIEW                             |
| dm_dw_resource_manager_active_tran                         | VIEW                             |
| dm_dw_tran_manager_abort_cache                             | VIEW                             |
| dm_dw_tran_manager_active_cache                            | VIEW                             |
| dm_dw_tran_manager_commit_cache                            | VIEW                             |
| dm_exec_requests_history                                   | VIEW                             |
| dm_external_data_processed                                 | VIEW                             |
| dm_external_policy_cache                                   | VIEW                             |
| dm_os_out_of_memory_events                                 | VIEW                             |
| dm_request_phases                                          | VIEW                             |
| dm_request_phases_exec_task_stats                          | VIEW                             |
| dm_request_phases_task_group_stats                         | VIEW                             |
| dm_server_external_policy_actions                          | VIEW                             |
| dm_server_external_policy_principal_assigned_actions       | VIEW                             |
| dm_server_external_policy_principals                       | VIEW                             |
| dm_server_external_policy_role_actions                     | VIEW                             |
| dm_server_external_policy_role_members                     | VIEW                             |
| dm_server_external_policy_roles                            | VIEW                             |
| dm_server_hardware_offload_config                          | VIEW                             |
| dm_server_suspend_status                                   | VIEW                             |
| dm_toad_tuning_zones                                       | VIEW                             |
| dm_toad_work_item_handlers                                 | VIEW                             |
| dm_toad_work_items                                         | VIEW                             |
| dm_xcs_enumerate_blobdirectory                             | SQL_INLINE_TABLE_VALUED_FUNCTION |
| external_job_streams                                       | VIEW                             |
| external_libraries_installed_table                         | INTERNAL_TABLE                   |
| external_stream_columns                                    | VIEW                             |
| external_streaming_jobs                                    | VIEW                             |
| external_streams                                           | VIEW                             |
| external_table_partitioning_columns                        | VIEW                             |
| extgov_attribute_sync_state                                | VIEW                             |
| extgov_attribute_sync_tables_synchronizing                 | VIEW                             |
| fn_cdc_is_ddl_handling_enabled                             | SQL_SCALAR_FUNCTION              |
| fn_filelog                                                 | SQL_INLINE_TABLE_VALUED_FUNCTION |
| fn_ledger_retrieve_digests_from_url                        | SQL_INLINE_TABLE_VALUED_FUNCTION |
| fn_xcs_get_file_rowcount                                   | SQL_INLINE_TABLE_VALUED_FUNCTION |
| ledger_column_history                                      | VIEW                             |
| ledger_columns_history_internal                            | INTERNAL_TABLE                   |
| ledger_columns_history_internal_history                    | INTERNAL_TABLE                   |
| ledger_table_history                                       | VIEW                             |
| ledger_tables_history_internal                             | INTERNAL_TABLE                   |
| ledger_tables_history_internal_history                     | INTERNAL_TABLE                   |
| PK_Sql_Pools_Table_Id                                      | PRIMARY_KEY_CONSTRAINT           |
| plan_persist_plan_feedback                                 | INTERNAL_TABLE                   |
| plan_persist_plan_forcing_locations                        | INTERNAL_TABLE                   |
| plan_persist_query_variant                                 | INTERNAL_TABLE                   |
| plan_persist_replicas                                      | INTERNAL_TABLE                   |
| plan_persist_runtime_stats_v2                              | INTERNAL_TABLE                   |
| plan_persist_wait_stats_v2                                 | INTERNAL_TABLE                   |
| polaris_executed_requests_history                          | INTERNAL_TABLE                   |
| polaris_executed_requests_text                             | INTERNAL_TABLE                   |
| polaris_file_cache_entries                                 | INTERNAL_TABLE                   |
| polaris_file_cache_streams                                 | INTERNAL_TABLE                   |
| polaris_file_statistics                                    | INTERNAL_TABLE                   |
| query_store_plan_feedback                                  | VIEW                             |
| query_store_plan_forcing_locations                         | VIEW                             |
| query_store_query_hints                                    | VIEW                             |
| query_store_query_variant                                  | VIEW                             |
| query_store_replicas                                       | VIEW                             |
| sp_cdc_set_scheduler_job                                   | EXTENDED_STORED_PROCEDURE        |
| sp_change_feed_create_table_group                          | SQL_STORED_PROCEDURE             |
| sp_change_feed_disable_db                                  | SQL_STORED_PROCEDURE             |
| sp_change_feed_disable_table                               | SQL_STORED_PROCEDURE             |
| sp_change_feed_drop_table_group                            | SQL_STORED_PROCEDURE             |
| sp_change_feed_enable_db                                   | SQL_STORED_PROCEDURE             |
| sp_change_feed_enable_table                                | SQL_STORED_PROCEDURE             |
| sp_change_feed_vupgrade                                    | SQL_STORED_PROCEDURE             |
| sp_cleanup_all_average_column_length_statistics            | EXTENDED_STORED_PROCEDURE        |
| sp_cleanup_all_openrowset_statistics                       | EXTENDED_STORED_PROCEDURE        |
| sp_cleanup_all_user_data_in_master                         | EXTENDED_STORED_PROCEDURE        |
| sp_cleanup_data_retention                                  | SQL_STORED_PROCEDURE             |
| sp_collect_backend_plan                                    | EXTENDED_STORED_PROCEDURE        |
| sp_copy_data_in_batches                                    | EXTENDED_STORED_PROCEDURE        |
| sp_create_format_type                                      | EXTENDED_STORED_PROCEDURE        |
| sp_create_format_type_synonym                              | EXTENDED_STORED_PROCEDURE        |
| sp_create_openrowset_statistics                            | EXTENDED_STORED_PROCEDURE        |
| sp_create_parser_version                                   | EXTENDED_STORED_PROCEDURE        |
| sp_create_streaming_job                                    | SQL_STORED_PROCEDURE             |
| sp_delete_database_engine_configuration_internal           | EXTENDED_STORED_PROCEDURE        |
| sp_discover_trident_table                                  | SQL_STORED_PROCEDURE             |
| sp_drop_format_type                                        | EXTENDED_STORED_PROCEDURE        |
| sp_drop_openrowset_statistics                              | EXTENDED_STORED_PROCEDURE        |
| sp_drop_parser_version                                     | EXTENDED_STORED_PROCEDURE        |
| sp_drop_storage_location                                   | EXTENDED_STORED_PROCEDURE        |
| sp_drop_streaming_job                                      | SQL_STORED_PROCEDURE             |
| sp_drop_trident_data_location                              | SQL_STORED_PROCEDURE             |
| sp_execute_flight_query                                    | EXTENDED_STORED_PROCEDURE        |
| sp_executesql_metrics                                      | EXTENDED_STORED_PROCEDURE        |
| sp_external_policy_refresh                                 | EXTENDED_STORED_PROCEDURE        |
| sp_fido_build_basic_histogram                              | EXTENDED_STORED_PROCEDURE        |
| sp_fido_build_histogram                                    | EXTENDED_STORED_PROCEDURE        |
| sp_fido_execute_graph_request                              | EXTENDED_STORED_PROCEDURE        |
| sp_fido_get_CS_rowset_row_count                            | EXTENDED_STORED_PROCEDURE        |
| sp_fido_get_remote_storage_size                            | EXTENDED_STORED_PROCEDURE        |
| sp_fido_glm_server_execute_batch                           | EXTENDED_STORED_PROCEDURE        |
| sp_fido_glms_get_storage_containers                        | EXTENDED_STORED_PROCEDURE        |
| sp_fido_glms_set_storage_containers                        | EXTENDED_STORED_PROCEDURE        |
| sp_fido_glms_unregister_appname                            | EXTENDED_STORED_PROCEDURE        |
| sp_fido_indexstore_update_topology                         | EXTENDED_STORED_PROCEDURE        |
| sp_fido_indexstore_upgrade_node                            | EXTENDED_STORED_PROCEDURE        |
| sp_fido_remove_retention_policy                            | EXTENDED_STORED_PROCEDURE        |
| sp_fido_set_ddl_step                                       | EXTENDED_STORED_PROCEDURE        |
| sp_fido_set_retention_policy                               | EXTENDED_STORED_PROCEDURE        |
| sp_fido_setup_endpoints                                    | EXTENDED_STORED_PROCEDURE        |
| sp_fido_spaceused                                          | SQL_STORED_PROCEDURE             |
| sp_fido_tran_abort                                         | EXTENDED_STORED_PROCEDURE        |
| sp_fido_tran_begin                                         | EXTENDED_STORED_PROCEDURE        |
| sp_fido_tran_commit                                        | EXTENDED_STORED_PROCEDURE        |
| sp_fido_tran_get_state                                     | EXTENDED_STORED_PROCEDURE        |
| sp_fido_tran_set_token                                     | EXTENDED_STORED_PROCEDURE        |
| sp_generate_database_ledger_digest                         | SQL_STORED_PROCEDURE             |
| sp_generate_external_table_statistics_description_and_hash | EXTENDED_STORED_PROCEDURE        |
| sp_generate_openrowset_statistics_props                    | EXTENDED_STORED_PROCEDURE        |
| sp_generate_trident_table_manifest                         | EXTENDED_STORED_PROCEDURE        |
| sp_get_dmv_collector_views                                 | EXTENDED_STORED_PROCEDURE        |
| sp_get_external_table_cardinality                          | EXTENDED_STORED_PROCEDURE        |
| sp_get_fido_lock                                           | EXTENDED_STORED_PROCEDURE        |
| sp_get_fido_lock_batch                                     | EXTENDED_STORED_PROCEDURE        |
| sp_get_file_splits                                         | EXTENDED_STORED_PROCEDURE        |
| sp_get_migration_vlf_state                                 | EXTENDED_STORED_PROCEDURE        |
| sp_get_openrowset_statistics_additional_props              | EXTENDED_STORED_PROCEDURE        |
| sp_get_openrowset_statistics_cardinality                   | EXTENDED_STORED_PROCEDURE        |
| sp_get_streaming_job                                       | SQL_STORED_PROCEDURE             |
| sp_get_total_openrowset_statistics_count                   | EXTENDED_STORED_PROCEDURE        |
| sp_get_trident_data_location                               | SQL_STORED_PROCEDURE             |
| sp_help_change_feed                                        | SQL_STORED_PROCEDURE             |
| sp_invoke_external_rest_endpoint                           | SQL_STORED_PROCEDURE             |
| sp_ldw_apply_file_updates_for_ext_table                    | SQL_STORED_PROCEDURE             |
| sp_ldw_get_file_updates_for_ext_table                      | SQL_STORED_PROCEDURE             |
| sp_ldw_normalize_ext_tab_name                              | SQL_STORED_PROCEDURE             |
| sp_ldw_update_stats_for_ext_table                          | SQL_STORED_PROCEDURE             |
| sp_manage_msdtc_transaction                                | EXTENDED_STORED_PROCEDURE        |
| sp_memory_leak_detection                                   | SQL_STORED_PROCEDURE             |
| sp_metadata_sync_connector_add                             | SQL_STORED_PROCEDURE             |
| sp_metadata_sync_connector_drop                            | SQL_STORED_PROCEDURE             |
| sp_metadata_sync_connectors_status                         | SQL_STORED_PROCEDURE             |
| sp_MSchange_feed_ddl_event                                 | SQL_STORED_PROCEDURE             |
| sp_process_memory_leak_record                              | EXTENDED_STORED_PROCEDURE        |
| sp_publish_database_to_syms                                | EXTENDED_STORED_PROCEDURE        |
| sp_query_store_clear_hints                                 | EXTENDED_STORED_PROCEDURE        |
| sp_query_store_clear_message_queues                        | EXTENDED_STORED_PROCEDURE        |
| sp_query_store_set_hints                                   | EXTENDED_STORED_PROCEDURE        |
| sp_release_all_fido_locks                                  | EXTENDED_STORED_PROCEDURE        |
| sp_release_fido_lock                                       | EXTENDED_STORED_PROCEDURE        |
| sp_reset_inactive_duration_flag                            | EXTENDED_STORED_PROCEDURE        |
| sp_reset_msdtc_log                                         | EXTENDED_STORED_PROCEDURE        |
| sp_set_data_processed_limit                                | EXTENDED_STORED_PROCEDURE        |
| sp_set_database_engine_configuration_internal              | EXTENDED_STORED_PROCEDURE        |
| sp_set_def_format_type_default_target                      | EXTENDED_STORED_PROCEDURE        |
| sp_set_def_format_type_extractor                           | EXTENDED_STORED_PROCEDURE        |
| sp_set_def_format_type_md_preprocessor                     | EXTENDED_STORED_PROCEDURE        |
| sp_set_distributed_feedback_context                        | EXTENDED_STORED_PROCEDURE        |
| sp_set_format_type_ls_syntax                               | EXTENDED_STORED_PROCEDURE        |
| sp_set_msdtc_network                                       | EXTENDED_STORED_PROCEDURE        |
| sp_set_parser_version_default_target                       | EXTENDED_STORED_PROCEDURE        |
| sp_set_parser_version_extractor                            | EXTENDED_STORED_PROCEDURE        |
| sp_set_parser_version_md_preprocessor                      | EXTENDED_STORED_PROCEDURE        |
| sp_set_trident_data_location                               | SQL_STORED_PROCEDURE             |
| sp_show_external_table_average_column_length_statistics    | EXTENDED_STORED_PROCEDURE        |
| sp_show_openrowset_statistics                              | EXTENDED_STORED_PROCEDURE        |
| sp_shutdown_feedback_client_connection                     | EXTENDED_STORED_PROCEDURE        |
| sp_start_fixed_vlf                                         | EXTENDED_STORED_PROCEDURE        |
| sp_start_flight_server                                     | EXTENDED_STORED_PROCEDURE        |
| sp_start_glm_server                                        | EXTENDED_STORED_PROCEDURE        |
| sp_start_streaming_job                                     | SQL_STORED_PROCEDURE             |
| sp_stop_flight_server                                      | EXTENDED_STORED_PROCEDURE        |
| sp_stop_streaming_job                                      | SQL_STORED_PROCEDURE             |
| sp_update_logical_pause_deactivation_params                | EXTENDED_STORED_PROCEDURE        |
| sp_update_logical_pause_flag                               | EXTENDED_STORED_PROCEDURE        |
| sp_update_streaming_job                                    | SQL_STORED_PROCEDURE             |
| sp_upgrade_vdw_configuration_parameters                    | EXTENDED_STORED_PROCEDURE        |
| sp_verify_database_ledger                                  | EXTENDED_STORED_PROCEDURE        |
| sp_verify_database_ledger_from_digest_storage              | EXTENDED_STORED_PROCEDURE        |
| sp_xcs_mark_column_relation                                | EXTENDED_STORED_PROCEDURE        |
| sql_pools_table                                            | INTERNAL_TABLE                   |
+------------------------------------------------------------+----------------------------------+

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

SQL Server IF Branches And Query Performance Part 2: Trying To Fix Parameters Doesn’t Work

Posted on May 24, 2022May 16, 2022 by Erik Darling

Jerked

Everyone thinks they’ve outsmarted the optimizer. All the time.

Like it’s a bumbling video game security guard that walks in the same circle and can’t see you if you just hold real still.

In reality, the optimizer is more like a dutiful parent playing along with your childish ruses.

One thing I see developers do quite a bit is try to “fix” a parameter in an IF branch.

Maybe it’s to protect against bad search values, but more often it’s to nix NULLs.

I know that the stored procedure I’m showing only has one branch in it where a query is executed, and the series is supposed to be about if branching with multiple queries.

I’m only doing that to simplify the point of this post, which is that “fixing” supplied values does not correct performance and cardinality estimation issues with queries in IF branches.

Sometimes that’s easier to demonstrate without additional noise.

The Thing

Here’s close to what I normally see someone trying:

CREATE OR ALTER PROCEDURE
    dbo.counter_if
(
    @PostTypeId int = NULL,
    @CreationDate datetime = NULL
)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN

    IF @CreationDate IS NULL
    BEGIN
        SET @CreationDate = '20080101';
    END;
    
    IF @PostTypeId IS NOT NULL
    BEGIN
    
        SELECT
            c = COUNT_BIG(*)
        FROM dbo.Posts AS p
        JOIN dbo.Users AS u
            ON p.OwnerUserId = u.Id
        WHERE p.PostTypeId = @PostTypeId
        AND   p.CreationDate >= @CreationDate;
    
    END;

END;
GO

The problem here is that by the time we hit the point where @CreationDate gets set to another value, we’ve already got a query plan.

You might get a search for the value you assign there, but the plan gets optimized for NULL.

Puddings

If you execute the proc like so, and get the query plan for it, here’s what happens:

EXEC dbo.counter_if
    @PostTypeId = 2;

We get a real bad cardinality estimate there, and I’ll show you that it’s because of the NULL we passed in, even though we set it to 2008-1-01-01 later.

SQL Server Query Plan Parameters — video

Digging into the operator properties of the select, here’s what the execution plan shows us about the parameters:

@PostTypeId is compiled and executed with 2 for both
@CreationDate is compiled with NULL, but executed with 2008-01-01 00:00:00.000

Different World

If we clear out the procedure cache — and I’m allowed to do that because I am a doctor (in Minecraft) — and re-run the proc with 2008-01-01, we get accurate cardinality estimation.

EXEC dbo.counter_if
    @PostTypeId = 2, 
    @CreationDate = '20080101';

We no longer get a one row estimate. Look at us. Look at how smart we are.

I’m starting to understand why so many people hate NULLs.

But Is It Null?

For brevity, I’m going to list out a bunch of similar patterns that also end up poorly:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
JOIN dbo.Users AS u
    ON p.OwnerUserId = u.Id
WHERE p.PostTypeId = @PostTypeId
AND   p.CreationDate >= ISNULL(@CreationDate, '20080101')
AND   p.CreationDate >= COALESCE(@CreationDate, '20080101')
AND   (p.CreationDate >= @CreationDate OR @CreationDate IS NULL)
AND   p.CreationDate >= CASE WHEN @CreationDate IS NULL THEN p.CreationDate ELSE @CreationDate END

None of these patterns or similar permutations yield desirable results in most cases.

You may find an edge case where they’re acceptable, but most folks I end up talking to aren’t calling me because what they’ve done is working out well.

More or less, they all results in this estimate/plan:

See? You’re still not clever, and I still got your nose. Go play outside, slugger.

S Dot

Hopefully by now you can see why this technique doesn’t necessarily give you good results.

In tomorrow’s post, we’ll look at another anti-pattern I see a lot with local variables.

If you’re looking for working solutions, you’re gonna have to hang on until the end of the week.

That’s just how culminations work. They Culm and then they Inate.

Duh.

Thanks for reading!

Going Further

SQL Server IF Branches And Query Performance Part 1: The Problem

Posted on May 23, 2022September 15, 2022 by Erik Darling

Manifesto

This is a problem I deal with quite a bit when helping people track down performance problems and, you know, solve them.

The basic scenario is something like this:

CREATE PROCEDURE
    dbo.iffy_kid
(
    @p1 int,
    @p2 int,
    @decider varchar(10)
)
AS
SET NOCOUNT, XACT_ABORT ON;

IF @decider = 'this_table'
BEGIN

    SELECT
        this.*
    FROM dbo.this_table AS this
    WHERE this.this_column = @p1;

END;

IF @decider = 'that_table'
BEGIN

    SELECT
        that.*
    FROM dbo.that_table AS that
    WHERE that.that_column = @p2;

END;

ELSE
BEGIN

    /*Do something else*/

END;

You have some parameter that decides which logical execution path that a query will take, and different queries that run based on that path.

What this does not control is query optimization paths, or cardinality estimation paths, at least not written in this manner.

First Blood

When this stored procedure is executed for the first time, or when some recompilation event happens, both queries will get a query plan generated and cached.

For simplicity, let’s say that when a query plan is cached, it it’s compiled and executed with

@p1 = 100
@p2 = NULL
@decider = ‘this_table’

SQL Server’s query optimizer will generate a query plan for the entire stored procedure based on cardinality estimation for:

@p1 = 100 as a predicate on this_table
@p2 = NULL as a predicate on that_table

On future executions, if the runtime execution parameters change to:

@p1 = NULL
@p2 = 200
@decider = ‘that_table’

The query plan with cardinality estimation for @p2 = NULL will be reused.

You’ve essentially multiplied any parameter sensitivity issue by:

The number of separate IF branched queries
The number of parameters fed into the stored procedure

Exploration

Over the rest of the week, I’m going to cover this topic from a few different angles to show you what works and what doesn’t work for fixing the problem.

Clients that I work with are often very surprised by the gotchas, intricacies, and weird details that crop up when writing queries like this.

Thanks for reading!

Going Further

A Week In Other Databases: IO in PostgreSQL: Past, Present, Future

Posted on May 20, 2022May 5, 2022 by Erik Darling

Cool Cool Cool

Grand Bonne

I know what you’re thinking: Who cares about that free database?

Well, it’s not necessarily the Postgres part that you might care about, but more the fact that a third party is developing software to do what major vendors aren’t doing.

This sort of thing might come to SQL Server someday, and it probably should. The self-tuning features in Azure are ass.

Thanks for reading.

Going Further

Indexed Views In SQL Server: No Filtered Indexes Or Filtered Statistics

Posted on May 12, 2022May 16, 2022 by Erik Darling

Half Baked

In my quest to love indexed views more, I’m always trying new things with them to solve problems.

Occasionally, I am pleasantly surprised by what can be accomplished with them. Occasionally.

Today was not an occasion. Let’s take an unfortunate look.

CREATE TABLE
    dbo.IndexedViewMe
(
    id int PRIMARY KEY CLUSTERED
);
GO 

CREATE VIEW 
    dbo.TheIndexedView
WITH SCHEMABINDING
AS
SELECT
    ivm.id
FROM dbo.IndexedViewMe AS ivm;
GO 

CREATE UNIQUE CLUSTERED INDEX
    uqi
ON dbo.TheIndexedView
    (id);

INSERT 
    dbo.IndexedViewMe
(
    id
)
SELECT
    x.c
FROM 
(
    SELECT 1 
      UNION ALL 
    SELECT 2
) AS x(c);

This gives us a tiny little table and indexed view. If we try to do either of these things, it doesn’t go well:

CREATE INDEX 
    i
ON dbo.TheIndexedView
    (id)
WHERE 
    id = 2;

Msg 10610, Level 16, State 1, Line 40

Filtered index ‘i’ cannot be created on object ‘dbo.TheIndexedView’ because it is not a user table. Filtered indexes are only supported on tables.

If you are trying to create a filtered index on a view, consider creating an indexed view with the filter expression incorporated in the view definition.

CREATE STATISTICS 
    s
ON dbo.TheIndexedView
    (id)
WHERE 
    id = 2;

Msg 10623, Level 16, State 1, Line 47

Filtered statistics ‘s’ cannot be created on object ‘dbo.TheIndexedView’ because it is not a user table. Filtered statistics are only supported on user tables.

Sort of a bummer, that. And it strikes me that it’s an odd limitation — especially for the statistics — but what can you do?

Indexed views haven’t changed aside from bug fixes in forever and a day. I doubt there’ll be any real investment in enhancing them anytime soon.

Thanks for reading!