- Docs
- /
14 Apr 2021 32809 views 0 minutes to read Contributors
SQL Server registers every missing index. Here is how you retrieve that information from the missing index DMV's
T-SQLTransact-SQL
1 select db_name(t1.database_id) as database_name, t1.inequality_columns,t1.included_columns,t1.statement as table_name,(select max(rowcnt) from sysindexes where id=t1.object_id and status in (0, 2066) ) as [rowcount] , t2.user_seeks,t2.last_user_seek,t2.last_user_scan,t2.avg_total_user_cost,t2.avg_user_impact, 'CREATE INDEX idx_DBO_'+REPLACE(REPLACE(REPLACE(statement,'[',''),']',''),'.','_')+CONVERT(VARCHAR,t1.index_handle)+'E ON '+statement+'('+equality_columns+') '+ CASE WHEN included_columns IS NOT NULL THEN 'INCLUDE( '+ISNULL(included_columns,'')+') ' ELSE '' END as [Equality] , 'CREATE INDEX idx_DBO_'+REPLACE(REPLACE(REPLACE(statement,'[',''),']',''),'.','_')+CONVERT(VARCHAR,t1.index_handle)+'I ON '+statement+'('+inequality_columns+') '+ CASE WHEN included_columns IS NOT NULL THEN 'INCLUDE( '+ISNULL(included_columns,'')+') ' ELSE '' END as [InEquality] from sys.dm_db_missing_index_details t1 WITH (NOLOCK) inner join sys.dm_db_missing_index_groups t0 WITH (NOLOCK) on t1.index_handle=t0.index_handle INNER JOIN sys.dm_db_missing_index_group_stats t2 WITH (NOLOCK) ON t0.index_group_handle=t2.group_handle where database_id = Db_id() and user_seeks > 10 order by avg_user_impact desc, user_seeks desc, user_scans desc, last_user_seek desc
The WHERE clause contains a filter that only shows the missing indexes for the current databases and only for indexes that are missing for more then 10 times. You can change that how ever you want.
The result is a list of CREATE statements that you can copy and execute in your database. The names of the indexes are somehow prefabricated, you can adjust that formula.
We especially pay attention to the data in the yellow marked area. For example If the amount of rows is very high and the user seeks is very low and the last user seek was a week ago, we wouldn't create that index of course. But if the rowcount was for example 200000 and the user seeks above 1000 and last user seek actual with current date then yes.
Over the years, SQLTreeo gained precious know-how which we transformed into customer support, managed database services and database solutions. In order to express our knowledge and experiences, we tend to work with anything less than the best-trained staff and that is the key to success and the biggest asset of our company. Thus, all our database management services are controlled by high level skilled and Senior database administrators. We can help you in every aspect of SQL Server.
In this article
Please choose a monitoring environment to connect to:
Enter your email address to recover your password.
Download link successfully sent. Check your email for details.
Reset password link successfully sent to . Check your email for details.
An email with a link for creating your password is sent to
Share link successfully sent to .
Your reply is send.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
We received your request. We will contact you shortly