How to Find Unused SQL Server Indexed Views [2 Methods]

What are indexed views

Indexed views, also known as materialized views, are a feature of SQL Server that allow you to create precomputed result sets, effectively storing the results of a query as a physical table in the database. These views are not just ordinary views; they are enhanced with indexes, making them particularly adept at improving query performance.

• Views: A view is a virtual table derived from one or more base tables. It does not store data on its own but rather presents a logical representation of the data from underlying tables. Views are used to simplify complex queries, provide a security layer, and present data in a way that suits specific requirements.
• Indexes: An index is a database structure that enhances the speed of data retrieval operations on a table. It works like the index of a book, providing a quick reference to the physical location of data in a table, thus speeding up query performance.

How indexed views improve performance

As databases grow in complexity and scale, executing queries efficiently becomes a challenging task. This is where indexed views in SQL Server come into play as a powerful tool to enhance query performance and streamline data retrieval. And indexed views offer a range of performance benefits that contribute to more efficient query execution:

• Reduced CPU Load: With the precomputed results stored in the indexed view, the database engine doesn't need to perform extensive calculations during query execution. This leads to a lower CPU load and frees up processing power for other tasks.
• Minimized I/O Operations: Indexed views reduce the need for I/O operations, as the required data is readily available in the indexed structure. This can dramatically reduce the time it takes to fetch the results of complex queries.
• Enhanced Join Performance: Queries involving multiple joins can benefit significantly from indexed views. By storing pre-joined data, the engine can avoid repetitive join operations, resulting in faster data retrieval.
• Accelerated Aggregations: Aggregation queries, such as summing up values or calculating averages, can be resource-intensive. Indexed views with precomputed aggregates streamline these operations, leading to quicker query response times.
• Consistent Performance: Since indexed views store precomputed results, their performance remains consistent even as the database grows in size. This ensures that query response times don't degrade over time.

How to create SQL Server indexed views

SQL Server indexed views provide a potent means of optimizing query performance by precomputing and indexing query results. The process of creating indexed views involves careful planning and consideration of your database structure, query patterns, and indexing strategies. Follow these steps to perform SQL Server create index view effectively:

1. Identify suitable candidates and design the view:

• Verify the SET options are correct for all existing tables that will be referenced in the view
• Verify that the SET options for the session are set correctly before you create any tables and the view
• Verify that the view definition is deterministic
• Verify that the base table has the same owner as the view

2. Apply schema binding: To create an indexed view, you can use the WITH SCHEMABINDING option. This enforces a dependency between the view and the underlying tables, ensuring that the structure of the tables remains consistent.

CREATE VIEW dbo.MyIndexedView

WITH SCHEMABINDING

AS

-- Your query here

3. Create the unique clustered index on the view:

CREATE UNIQUE CLUSTERED INDEX

    ucidx_product_id

ON production.product_master(product_id);

4. It is also possible to append a non-clustered index to the product_name column within the view.

CREATE NONCLUSTERED INDEX

>    ucidx_product_name

ON production.product_master(product_name);

5. Upon querying data through the view, an observable shift in statistics will become apparent.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'product_master'. Scan count 1, logical reads 6, physical reads 1, read-ahead reads 11, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Instead of engaging with data extraction from three separate tables, SQL Server now accesses data directly from the materialized view labeled as product_master.

Note: This functionality is exclusively accessible within the SQL Server Enterprise Edition. For those utilizing the SQL Server Standard or Developer Edition, incorporation of the WITH (NOEXPAND) table hint is essential, inserted directly within the query's FROM clause when utilizing the view, as demonstrated in the subsequent example query.

SELECT *

FROM production.product_master

   WITH (NOEXPAND)

ORDER BY product_name;

How to find unused indexes in SQL Server

Unused or redundant indexes in a SQL Server database can consume valuable storage space and impact performance. It's essential to identify and remove these unused indexes to maintain a well-optimized database. SQL Server provides tools and techniques to help you find and manage unused indexes effectively. Here's how you can go about it:

Method 1. SQL Server Management Studio (SSMS)

SQL Server Management Studio provides a built-in feature that can help you identify potentially unused indexes. Follow these steps:

1. Open SQL Server Management Studio.

2. Connect to your database server.

3. Right-click on your database, then select Reports >> Standard Reports >> Index Usage Statistics.

This report will display information about index usage, including the number of seeks, scans, and updates. You can use this information to identify indexes that have a low number of seeks and scans, suggesting they might be unused.

Method 2. Querying Dynamic Management Views (DMVs)

You can query SQL Server's dynamic management views to get more detailed insights into index usage. Here's an example query:

SELECT
    OBJECT_NAME(s.[object_id]) AS [Table Name],
    i.name AS [Index Name],
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM
    sys.dm_db_index_usage_stats AS s
INNER JOIN
    sys.indexes AS i ON i.[object_id] = s.[object_id]
                    AND i.index_id = s.index_id
WHERE
    OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
    AND s.database_id = DB_ID()
ORDER BY
[Table Name], [Index Name];

This query retrieves information about the usage of indexes in your database. Indexes with low user seeks, scans, and lookups might be candidates for removal.

Protect your database by regular SQL Server backup

As you've explored the remarkable benefits of SQL Server indexed views in optimizing query performance and streamlining data retrieval, it's equally imperative to ensure the security and resilience of your valuable database.

Just as indexed views enhance performance, a robust SQL database backup software like AOMEI Cyber Backup plays a pivotal role in fortifying your database's defenses and guaranteeing the recoverability of your critical data. It supports SQL 2005-2022, and provides a comprehensive solution for safeguarding your SQL database, ensuring data integrity, and enabling swift recovery.

To get started, you need to download and install AOMEI Cyber Backup on the server. Have a try on the 30-day free trial:

✍ Before you perform a database backup, please make sure:
1. Your computer with both AOMEI Cyber Backup Agent and Microsoft SQL Server installed.
2. The local disk or network shared to store backup files.

✤ Create SQL Server database backup task

1. Access to Source Device >> Add Microsoft SQL. If the database is present and the version is supported, it will be automatically detected. Otherwise, you can click ​​​Add Microsoft SQL >> Download proxy program. Install the program on the device where SQL Server is installed. Afterwards, select Already installed proxy and designate the desired proxies for addition.

2. Click … >> Authentication to verify the database instance. You can make a choice between Windows Authentication or SQL Authentication. Input the credentials and click Verify.

3. Click Backup Task >> Create New Task, and select Microsoft SQL Backup as your backup type.

4. Device Name: Click Device Name to specify the SQL instance and the database that you intend to back up. Depending on your requirements, you can select the number of databases for backup.

5. Backup Target: Define the Backup Target by indicating a local path or a network path. If your intention is to perform an SQL database backup to a remote location, you can click on Add a new network storage. The newly added path will be saved within the Add storage section.

6. Schedule (optional): Set up a schedule to run the SQL database backup daily/weekly/monthly. Additionally, you can specify the backup method as Full/Incremental/Differential.

7. Start Backup: You can choose to Add the schedule and start backup now or Add the schedule only and click Start Backup to execute SQL server automatic backup.

✤ Restore SQL Server database from backup

1. Navigate to Backup Task section on the page, locate the task associated with the database restoration, and click … >> Restore.

2. Choose the precise database backup version that you intend to restore. Afterward, click OK.

3. Indicate the destination where you wish to execute the restoration. By default, it will Restore to original location.

Conclusion

SQL Server indexed views offer a powerful means to elevate query performance by precomputing and indexing query results. By incorporating these views into your database design, you can unlock significant performance gains, especially in scenarios involving complex calculations and aggregations.

Additionally, complementing your performance optimization efforts with regular database backups using AOMEI Cyber Backup ensures that your data remains safe and recoverable in the face of any adversity.