create table dbo.TIdentity
(
    id int identity(1,1) not null primary key,
    dt datetime2 default(sysdatetime()),
    st varchar(7000)
)
go

create table dbo.TNewid
(
    id uniqueidentifier not null default (newid()) primary key,
    dt datetime2 default(sysdatetime()),
    st varchar(7000)
)
go

create table dbo.TNewsequentialid
(
    id uniqueidentifier not null default (newsequentialid()) primary key,
    dt datetime2 default(sysdatetime()),
    st varchar(7000)
)
go

create sequence dbo.CountBy as int start with 1 increment by 1;
go

create table dbo.TSequence
(
    id int not null default (next value for dbo.CountBy) primary key,
    dt datetime2 default(sysdatetime()),
    st varchar(7000)
)
go

create table dbo.TSimple
(
    id int identity(1,1) not null primary key,
    dt datetime2 default(sysdatetime()),
    st varchar(7000)
)
go

declare @i int = 0

while (@i < 1000)
begin
    
   insert into dbo.TSimple(st)
   select replicate('_', 7000)
   set @i += 1

end

-- {Статистика использования индексов}
select object_name(s.[object_id]) as objectname
       ,i.name as indexname
       ,i.index_id
       ,user_seeks + user_scans + user_lookups as reads
       ,user_updates as writes
       ,i.type_desc
       ,i.fill_factor
       ,user_seeks
       ,user_scans
       ,user_lookups
from sys.dm_db_index_usage_stats as s
join sys.indexes as i on s.[object_id] = i.[object_id]
where objectproperty(s.[object_id], 'IsUserTable') = 1 and i.index_id = s.index_id and s.database_id = db_id()
order by object_name(s.[object_id])
         ,writes desc
         ,reads desc

-- {Неиспользуемые индексы}
select object_name(i.[object_id]) as tablename
       ,i.name
from sys.indexes i
join sys.objects o on i.[object_id] = o.[object_id]
where i.index_id not in (
                            select s.index_id
                            from sys.dm_db_index_usage_stats as s
                            where s.[object_id] = i.[object_id]
                                  and i.index_id = s.index_id
                                  and database_id = db_id()
                        )
      and o.[type] = 'U'
order by object_name(i.[object_id]) asc

-- {Пропущенные индексы}
select user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) as [index_advantage]
       ,migs.last_user_seek
       ,mid.[statement] as [Database.Schema.Table]
       ,mid.equality_columns
       ,mid.inequality_columns
       ,mid.included_columns
       ,migs.unique_compiles
       ,migs.user_seeks
       ,migs.avg_total_user_cost
       ,migs.avg_user_impact
from sys.dm_db_missing_index_group_stats as migs with(nolock)
join sys.dm_db_missing_index_groups as mig with(nolock) on migs.group_handle = mig.index_group_handle
join sys.dm_db_missing_index_details as mid with(nolock) on mig.index_handle = mid.index_handle
where mid.database_id = db_id()
order by index_advantage desc

-- {Возможно "плохие" индексы writes > reads}
select db_name(s.database_id) as database_name
       ,object_name(s.[object_id]) as table_name
       ,i.name as index_name
       ,i.index_id
       ,user_updates as total_writes
       ,user_seeks + user_scans + user_lookups as total_reads
       ,user_updates - (user_seeks + user_scans + user_lookups) as diff
from sys.dm_db_index_usage_stats as s with(nolock)
join sys.indexes as i with(nolock) on s.[object_id] = i.[object_id] and i.index_id = s.index_id
where objectproperty(s.[object_id], 'IsUserTable') = 1
      and user_updates > (user_seeks + user_scans + user_lookups)
      and i.index_id > 1
      and s.database_id = db_id()
order by diff desc, total_writes desc, total_reads asc

-- {Статистика использования индексов}
select object_name(s.[object_id]) as objectname
       ,i.name as indexname
       ,i.index_id
       ,user_seeks + user_scans + user_lookups as reads
       ,user_updates as writes
       ,i.type_desc
       ,i.fill_factor
       ,user_seeks
       ,user_scans
       ,user_lookups
from sys.dm_db_index_usage_stats as s
join sys.indexes as i on s.[object_id] = i.[object_id]
where objectproperty(s.[object_id], 'IsUserTable') = 1 and i.index_id = s.index_id and s.database_id = db_id()
order by object_name(s.[object_id])
         ,writes desc
         ,reads desc