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;