· 4 years ago · Apr 12, 2021, 05:54 PM
1
2-- 11_1_5_SQLServer_ИНДЕКСЫ
3
4--Набор скриптов для знакомства с SQL Server
5--https://infostart.ru/1c/articles/1128594/
6
7SELECT @@Servername AS ServerName ,
8 DB_NAME() AS DB_Name ,
9 o.Name AS TableName ,
10 i.Name AS IndexName
11FROM sys.objects o
12 INNER JOIN sys.indexes i ON o.object_id = i.object_id
13WHERE o.Type = 'U' -- User table
14 AND LEFT(i.Name, 1) <> '_' -- Remove hypothetical indexes
15ORDER BY o.NAME ,
16 i.name;
17
18
19-- статистика использования индексов.
20SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
21 ,IX.name AS Index_Name
22 ,IX.type_desc Index_Type
23 ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
24 ,IXUS.user_seeks AS NumOfSeeks
25 ,IXUS.user_scans AS NumOfScans
26 ,IXUS.user_lookups AS NumOfLookups
27 ,IXUS.user_updates AS NumOfUpdates
28 ,IXUS.last_user_seek AS LastSeek
29 ,IXUS.last_user_scan AS LastScan
30 ,IXUS.last_user_lookup AS LastLookup
31 ,IXUS.last_user_update AS LastUpdate
32FROM sys.indexes IX
33INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
34INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
35WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
36GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update
37-- Этим скриптом Вы можете получить информацию о количестве операций поиска, сканирования и некоторых других операций на индексах. В итоге можно составить список тех объектов, которых из базы можно удалить.
38
39
40-- Кроме этого, можно составить список индексов, которые имеют высокие издержки при использовании.
41-- Возможно, это "тяжелые" индексы, которые созданы на часто обновляемых таблицах или др. варианты.
42
43SELECT TOP 1
44 [Maintenance cost] = (user_updates + system_updates)
45 , [Retrieval usage] = (user_seeks + user_scans + user_lookups)
46 , DatabaseName = DB_NAME()
47 , TableName = OBJECT_NAME(s.[object_id])
48 , IndexName = i.name
49INTO #TempMaintenanceCost
50FROM sys.dm_db_index_usage_stats s
51 INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
52 AND s.index_id = i.index_id
53WHERE s.database_id = DB_ID()
54 AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
55 AND (user_updates + system_updates) > 0 -- Only report on active rows.
56 AND s.[object_id] = -999
57-- Dummy value to get table structure.
58;
59
60-- Loop around all the databases on the server.
61EXEC sp_MSForEachDB 'USE [?];
62-- Table already exists.
63INSERT INTO #TempMaintenanceCost
64SELECT TOP 10
65 [Maintenance cost] = (user_updates + system_updates)
66 ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
67 ,DatabaseName = DB_NAME()
68 ,TableName = OBJECT_NAME(s.[object_id])
69 ,IndexName = i.name
70FROM sys.dm_db_index_usage_stats s
71INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
72 AND s.index_id = i.index_id
73WHERE s.database_id = DB_ID()
74 AND i.name IS NOT NULL -- Ignore HEAP indexes.
75 AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
76 AND (user_updates + system_updates) > 0 -- Only report on active rows.
77ORDER BY [Maintenance cost] DESC
78;
79'
80-- Select records.
81SELECT TOP 10
82 *
83FROM #TempMaintenanceCost
84ORDER BY [Maintenance cost] DESC
85-- Tidy up.
86DROP TABLE #TempMaintenanceCost
87
88
89
90-- отсутствующих индексах
91SELECT
92 @@ServerName AS ServerName, -- Имя сервера
93 DB_NAME() AS DBName, -- Имя базы
94 t.name AS 'Affected_table', -- Имя таблицы
95 (LEN(ISNULL(ddmid.equality_columns, N'')
96 + CASE WHEN ddmid.equality_columns IS NOT NULL
97 AND ddmid.inequality_columns IS NOT NULL THEN ','
98 ELSE ''
99 END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'')
100 + CASE WHEN ddmid.equality_columns
101 IS NOT NULL
102 AND ddmid.inequality_columns
103 IS NOT NULL
104 THEN ','
105 ELSE ''
106 END, ',', '')) ) + 1 AS K, -- Количество ключей в индексе
107 COALESCE(ddmid.equality_columns, '')
108 + CASE WHEN ddmid.equality_columns IS NOT NULL
109 AND ddmid.inequality_columns IS NOT NULL THEN ','
110 ELSE ''
111 END + COALESCE(ddmid.inequality_columns, '') AS Keys, -- Ключевые столбцы индекса
112 COALESCE(ddmid.included_columns, '') AS [include], -- Неключевые столбцы индекса
113 'Create NonClustered Index IX_' + t.name + '_missing_'
114 + CAST(ddmid.index_handle AS VARCHAR(20))
115 + ' On ' + ddmid.[statement] COLLATE database_default
116 + ' (' + ISNULL(ddmid.equality_columns, '')
117 + CASE WHEN ddmid.equality_columns IS NOT NULL
118 AND ddmid.inequality_columns IS NOT NULL THEN ','
119 ELSE ''
120 END + ISNULL(ddmid.inequality_columns, '') + ')'
121 + ISNULL(' Include (' + ddmid.included_columns + ');', ';')
122 AS sql_statement, -- Команда для создания индекса
123 ddmigs.user_seeks, -- Количество операций поиска
124 ddmigs.user_scans, -- Количество операций сканирования
125 CAST(( ddmigs.user_seeks + ddmigs.user_scans)
126 * ddmigs.avg_user_impact AS BIGINT) AS 'est_impact',
127 avg_user_impact, -- Средний процент выигрыша
128 ddmigs.last_user_seek, -- Последняя операция поиска
129 ( SELECT DATEDIFF(Second, create_date, GETDATE()) Seconds
130 FROM sys.databases
131 WHERE name = 'tempdb'
132 ) SecondsUptime
133FROM sys.dm_db_missing_index_groups ddmig
134 INNER JOIN sys.dm_db_missing_index_group_stats ddmigs
135 ON ddmigs.group_handle = ddmig.index_group_handle
136 INNER JOIN sys.dm_db_missing_index_details ddmid
137 ON ddmig.index_handle = ddmid.index_handle
138 INNER JOIN sys.tables t ON ddmid.OBJECT_ID = t.OBJECT_ID
139WHERE ddmid.database_id = DB_ID()
140ORDER BY est_impact DESC;
141
142-- фрагментация индекса
143
144SELECT
145 DB_NAME([IF].database_id) AS [Имя базы]
146 ,OBJECT_NAME(object_id) AS [Имя таблицы]
147 ,OBJECT_NAME([IF].index_id) AS [Имя индкса]
148 ,[IF].*
149FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) AS [IF]
150WHERE avg_fragmentation_in_percent > 30 -- процент фрагментации выше 30%.
151ORDER BY avg_fragmentation_in_percent
152
153