From the course: SQL Server 2016: Administer a Database Infrastructure

Unlock the full course today

Join today to access over 22,400 courses taught by industry experts or purchase this course individually.

Identify and drop underutilized indexes

Identify and drop underutilized indexes - SQL Server Tutorial

From the course: SQL Server 2016: Administer a Database Infrastructure

Start my 1-month free trial

Identify and drop underutilized indexes

- [Instructor] It's possible for your SQL server databases to be saddled with too many indexes. If indexes are created on tables, but never used, then there are resources being consumed for no benefit. Every time a record is added, dropped, or modified, the server is still spending CPU cycles maintaining them, and writing those changes to disc. To check up on how often our indexes are being used, you'll want to use a dynamic management view called sys.dm db index usage stats. Let's take a look at in the WideWorldimporters database. First, I'll just select everything out of that view. Then I'll return a column for the database ID and the object ID, and each of these records in the result set represent an individual index in our system. Here we can see the statistics for each index. The first couple of columns user seeks, user scans, user lookups, and user updates, will all provide a count of how many times each index was utilized by a user query. We also have columns that indicate the…

Contents