This script will provide a list of indexes that are not used in a database. Indexes are overhead and any indexes not being used but are being updated cause additional load on a database. This looks at the following items: User seeks, scans and lookups. If all three of these are zero and the User Updates column is above zero this means we are using resources to keep these indexes up todate but we are not getting benefit from them. One thing to remember is index information comes from DM's and they are cleared whenever SQL is restarted. Make sure your SQL server has been running for at least a month to ensure you capture enough data. Always keep the drop and create index code in case you do need to create an index you dropped. Always test in a Dev environment whenever possible.
Missing index script
This script on missing indexes that SQL had found. SQL will track queries and provide suggestions on indexes it feels will provide improvements. You should not take this information and create all the indexes. Performance tuning can be an art and takes practice. This information should be reviewed and as a DBA implemented a little […]
2020-04-20 (first published: 2020-04-13)
9,736 reads