Unused SQL Server Indexes Script - MSSQLTips Monday Mission

[MSSQLTips.com logo]( [View email in your browser]( TODAY'S NEWSÂ FROM MSSQLTips Learn something new every day! [mssqltips image]( [Time Series Forecasting using Python Prophet]( Learn how to use the Python library Prophet for time series forecasting to predict a target variable over a period.... [Read Now]( [mssqltips image]( [Cloud Costs Getting Overwhelming?]( In this webinar, we delve into High Availability (HA) and Disaster Recovery (DR) for SQL Server deployments in Azure, focusing on cost-efficient methodologies.... [Read Now]( [mssqltips image]( [SQL Replication Error - row not found at the Subscriber]( Learn how to fix SQL replication error - the row was not found at the Subscriber when applying the replicated UPDATE command for Table with Primary Key(s): (Source: MSSQLServer, Error number:20598). ... [Read Now]( TODAY'S SCRIPT ON MSSQLTips MSSQLTips Monday Mission - Unused SQL Server Indexes On MSSQLTips, our goal is to help you best manage your Microsoft Data Platform - on-prem, in a data center or in the cloud. Today we are turning to indexes, which at times can radically improve stored procedures and scripts. Unfortunately, workloads change over time and indexes are not free. The SQL Server database engine still needs to support the indexes, indexes consume storage and need to be maintained regularly. Today's mission, should you choose to accept it, is to find unused SQL Server indexes then determine your next steps - testing disabling or dropping in a Dev and Test environment before Production. -- Purpose: Query to Find Unused SQL Server Indexes -- URL: SELECT DB_NAME() AS [database_name], DB_ID() AS database_id, OBJECT_SCHEMA_NAME(i.[object_id]) AS [schema_name], OBJECT_NAME(i.[object_id]) AS [object_name], iu.[object_id], i.[name], i.index_id, i.[type_desc], i.is_primary_key, i.is_unique, i.is_unique_constraint, iu.user_seeks, iu.user_scans, iu.user_lookups, iu.user_updates, iu.user_seeks + iu.user_scans + iu.user_lookups AS total_uses, CASE WHEN (iu.user_seeks + iu.user_scans + iu.user_lookups) > 0 THEN iu.user_updates/( iu.user_seeks + iu.user_scans + iu.user_lookups ) ELSE iu.user_updates END AS update_to_use_ratio FROM sys.dm_db_index_usage_stats iu RIGHT JOIN sys.indexes i ON iu.index_id = i.index_id AND iu.[object_id] = i.[object_id] WHERE OBJECTPROPERTY(iu.[object_id], 'IsUserTable') = 1 AND iu.database_id = DB_ID() ORDER BY CASE WHEN (iu.user_seeks + iu.user_scans + iu.user_lookups) > 0 THEN iu.user_updates/( iu.user_seeks + iu.user_scans + iu.user_lookups ) ELSE iu.user_updates END DESC GO Learn more and share your feedback: [Safely Dropping Unused SQL Server Indexes]( RECOMMENDED READINGÂ ON MSSQLTips [mssqltips image]( [SQL Server Monitoring and Troubleshooting Simplified]( Learn how SQL Sentry enables SQL Server DBAs to pinpoint performance problems in minutes to minimize the impact to organizations. ... [Read Now]( [mssqltips image]( [SQL Quick Reference Guide]( This guide outlines various aspects of working with SQL along with several links to resources to help you better understand how to use the SQL language.... [Read Now]( [mssqltips image]( [Know Your Business (KYB) Data Verification Solution]( Learn how to meet Know Your Business Data Verification rules with Melissa Personator Identity Global to prevent fraud or money-laundering and meet legal regulations.... [Read Now]( [Manage MSSQLTips Subscriptions | Unsubscribe]( Edgewood Solutions LLCÂ PO Box 682 | Wilton, NH 03086Â [Privacy Statement]( Sent to: {EMAIL}Â [tag]