Created
October 21, 2015 18:40
-
-
Save robsonalves/db24b231d5696197e1be to your computer and use it in GitHub Desktop.
Returning Index Missing and List of Idexes
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
set transaction isolation level read uncommitted | |
select DB_NAME(database_id) as DataBaseName | |
, count(*) as [Missing Index Count] | |
from sys.dm_db_missing_index_details | |
group by db_name(database_id) | |
order by [Missing Index Count] desc | |
select | |
db_name() as DataBaseName | |
,SCHEMA_NAME(O.schema_id) as SchemaNAme | |
,OBJECT_NAME(I.object_id) as TableName | |
,I.Name as IndexName | |
from sys.indexes I | |
inner join sys.objects O on I.object_id = O.object_id | |
left outer join sys.dm_db_index_usage_stats S on S.object_id = I.object_id | |
and I.index_id = S.index_id and database_id = DB_ID() | |
where OBJECTPROPERTY(o.object_id,'IsMsShipped') = 0 | |
and i.name is not null | |
and s.object_id is null |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment