Below query will be helpful in finding all missing indexes in a database. I got this query from one of my friend. I am not sure from where it was actually taken. Since I found this useful, I am just making note on the same.
SELECT sys.objects.name,
( avg_total_user_cost * avg_user_impact ) * ( user_seeks + user_scans )
AS
Impact,
'CREATE NONCLUSTERED INDEX ix_IndexName ON '
+ sys.objects.name COLLATE database_default + ' ( ' +
Isnull(mid.equality_columns, '') + CASE
WHEN mid.inequality_columns IS NULL
THEN ''
ELSE
CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END +
mid.inequality_columns
END + ' ) ' + CASE
WHEN
mid.included_columns IS NULL THEN ''
ELSE
'INCLUDE (' + mid.included_columns + ')'
END + ';'
AS
CreateIndexStatement,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
AND mid.database_id = Db_id()
INNER JOIN sys.objects WITH (nolock)
ON mid.object_id = sys.objects.object_id
WHERE ( migs.group_handle IN (SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (
nolock)
ORDER BY ( avg_total_user_cost * avg_user_impact
) *
( user_seeks + user_scans ) DESC) )
AND Objectproperty(sys.objects.object_id, 'isusertable') = 1
ORDER BY 2 DESC,
3 DESC
Technology enthusiast Sherbaz is a Microsoft certified SQL Server Subject Matter Expert. He had fun automating day to day task to achieve management goals. He has extensive hands-on experience in high availability deployments and support with several years of industry experience performing SQL Server installations, configuration, maintenance, backups, performance tuning, Transactional & P2P replication, migration, high availability solutions (Always on, Failover Clustering, Logshipping and Mirroring) and Disaster recovery.
Sherbaz also has basic hands-on experience in other DBMSs like MongoDB, Oracle 9i, 10g, Sybase and MySQL. 1 year on Mongodb, Oracle 9i & 10g. Completed M102 & M202 courses in MongoDB.
Sherbaz spends his spare time with automation in electronics, embedded system circuits and IOT in his private lab at home.
Also, he supports his customers and users at www.SplitExpense.in for issues with app, website or telegram chat bot @SplitExpenseBot https://t.me/SplitExpenseBot. @TheSherBot https://t.me/TheSherBot is another telegram chat bot, partially artificial intelligent, programmed and maintained by him on python and backend Mongodb database.
Sherbaz always showed interest learning new technologies and to think out of the box bringing innovation in work. Some of his work is documented @ www.sherbaz.com.
He is now busy learning 3D modeling software on his roadmap towards building his next hobby build, A 3D PRINTER.
Subscribe to his youtube channel
https://www.youtube.com/c/SherbazMohamed/
View all posts by Sherbaz
