Reprint address: https://segmentfault.com/a/1190000018022330

To view the index of a table:

use [Database name]
sp_helpindex Table name;

To view index usage:

user_seeks and user_ If the scans field is 0, consider whether it is a garbage index

In addition, last_ user_ seek,last_ user_ If scan is a very early time, consider whether the index will not be used due to the application changes

SELECT i.name indexname,
       user_seeks,
       user_scans,
       last_user_seek,
       last_user_scan
  FROM sys.dm_db_index_usage_stats s
 INNER JOIN sys.indexes i
    ON s.object_id = i.object_id
   AND s.index_id = i.index_id
 WHERE database_id = db_id('ClntMgr')
   AND s.object_id = object_id('IDVerifyTbl');

Returns fragments of the specified database, table, and index

When the index type is HEAP, the fragment ratio is generally large
reason:

1. a table without a clustered index is called a heap, which means that the data stored in it has no specific order.
2. when the index is rebuilt or reorganized, the clustered index is sorted according to the clustered key and the data pages it reorders.
3. however, the heap will not be regenerated during index reconstruction or reorganization, so it will grow out of control and occupy a lot more data pages than necessary.

SELECT OBJECT_NAME(f.object_id) Table name,
       i.name Index name,
       f.index_type_desc Index type,
       f.avg_fragmentation_in_percent Fragment ratio
  FROM sys.dm_db_index_physical_stats(DB_ID('Library name'),
                                      OBJECT_ID('Table name'),
                                      NULL,
                                      NULL,
                                      'limited') f
 INNER JOIN sys.indexes i
    ON i.object_id = f.object_id
   AND i.index_id = f.index_id
 ORDER BY f.avg_fragmentation_in_percent DESC;

Rebuild all indexes of the table Online

alter index all on Library name.dbo.Table name rebuild with (online = on);

Reorganize all indexes of the table

alter index all on Library name.dbo.Table name reorganize;

View disk space occupied by tables and indexes

SELECT name 'Table name',
       convert(char(11), row_Count) as 'Number of data pieces',
       (reservedpages * 8) 'used space(KB)',
       (pages * 8) 'Space occupied by data(KB)',
       (CASE
         WHEN usedpages > pages THEN
          (usedpages - pages)
         ELSE
          0
       END) * 8 'Index footprint(KB)',
       (CASE
         WHEN reservedpages > usedpages THEN
          (reservedpages - usedpages)
         ELSE
          0
       END) * 8 'unused space(KB)',
       LTRIM(STR(reservedpages * 8 / 1024 / 1024, 15, 0) + ' GB') as 'used space(GB)'
  from (SELECT name,
               SUM(reserved_page_count) as reservedpages,
               SUM(used_page_count) as usedpages,
               SUM(CASE
                     WHEN (index_id < 2) THEN
                      (in_row_data_page_count + lob_used_page_count +
                      row_overflow_used_page_count)
                     ELSE
                      lob_used_page_count + row_overflow_used_page_count
                   END) as pages,
               SUM(CASE
                     WHEN (index_id < 2) THEN
                      row_count
                     ELSE
                      0
                   END) as row_Count
          FROM sys.dm_db_partition_stats
         inner join sys.objects
            on sys.dm_db_partition_stats.object_id = sys.objects.object_id
         where type = 'U'
         group by sys.objects.name
        union
        SELECT sys.objects.name,
               sum(reserved_page_count) as reservedpages,
               sum(used_page_count) as usedpages,
               0 as pages,
               0 as row_count
          from sys.objects
         inner join sys.internal_tables
            on sys.objects.object_id = sys.internal_tables.parent_id
         inner join sys.dm_db_partition_stats
            on sys.dm_db_partition_stats.object_id =
               sys.internal_tables.object_id
         where sys.internal_tables.internal_type IN
               (202, 204, 211, 212, 213, 214, 215, 216)
         group by sys.objects.name) t
 order by 'used space(KB)' desc

View table missing index information

SELECT DatabaseName = DB_NAME(database_id),
       [ Number Indexes Missing ] = count(*)
  FROM sys.dm_db_missing_index_details
 GROUP BY DB_NAME(database_id)
 ORDER BY [ Number Indexes Missing ] DESC;

Identify the most expensive missing index
column_ The usage values are as follows:
1.EqualityUsage means that equality operation has been performed on this column;
2.InequalityUsage represents that unequal operations have been performed on this column;
3. Include columns represents inclusive columns
The results of this query, sorted by total cost, show the cost of the most important missing indexes and information about the required columns in the database / schema / tables and missing indexes. In particular, this script determines which columns are used in equal and unequal SQL statements. In addition, it reports which other columns should be used as inclusive columns in the missing index.
Inclusion columns can be used to satisfy more coverage queries without getting data from the underlying page, thus using fewer I/O operations and improving performance.

SELECT TOP 100 [ Total Cost ] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), 0),
       avg_user_impact,
       TableName = statement,
       [ EqualityUsage ] = equality_columns,
       [ InequalityUsage ] = inequality_columns,
       [ Include Cloumns ] = included_columns
  FROM sys.dm_db_missing_index_groups g
 INNER JOIN sys.dm_db_missing_index_group_stats s
    ON s.group_handle = g.index_group_handle
 INNER JOIN sys.dm_db_missing_index_details d
    ON d.index_handle = g.index_handle
 ORDER BY [ Total Cost ] DESC;