PVI_COPY

11_1_5_SQLServer_ИНДЕКСЫ

Apr 12th, 2021
200
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. -- 11_1_5_SQLServer_ИНДЕКСЫ
  3.  
  4. --Набор скриптов для знакомства с SQL Server
  5. --https://infostart.ru/1c/articles/1128594/
  6.  
  7. SELECT  @@Servername AS ServerName ,
  8.         DB_NAME() AS DB_Name ,
  9.         o.Name AS TableName ,
  10.         i.Name AS IndexName
  11. FROM    sys.objects o
  12.         INNER JOIN sys.indexes i ON o.object_id = i.object_id
  13. WHERE   o.Type = 'U' -- User table
  14.         AND LEFT(i.Name, 1) <> '_' -- Remove hypothetical indexes
  15. ORDER BY o.NAME ,
  16.         i.name;
  17.  
  18.  
  19. -- статистика использования индексов.
  20. SELECT 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
  32. FROM sys.indexes IX
  33. INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
  34. INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
  35. WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
  36. GROUP 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.  
  43. SELECT 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
  49. INTO #TempMaintenanceCost
  50. FROM 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
  53. WHERE 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.
  61. EXEC sp_MSForEachDB    'USE [?];
  62. -- Table already exists.
  63. INSERT INTO #TempMaintenanceCost
  64. SELECT 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
  70. FROM   sys.dm_db_index_usage_stats s
  71. INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id]
  72.   AND s.index_id = i.index_id
  73. WHERE 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.
  77. ORDER BY [Maintenance cost]  DESC
  78. ;
  79. '
  80. -- Select records.
  81. SELECT TOP 10
  82.     *
  83. FROM #TempMaintenanceCost
  84. ORDER BY [Maintenance cost]  DESC
  85. -- Tidy up.
  86. DROP TABLE #TempMaintenanceCost
  87.  
  88.  
  89.  
  90. -- отсутствующих индексах
  91. SELECT
  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
  133. FROM 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
  139. WHERE   ddmid.database_id = DB_ID()
  140. ORDER BY est_impact DESC;
  141.  
  142. -- фрагментация индекса
  143.  
  144. SELECT
  145.     DB_NAME([IF].database_id) AS [Имя базы]
  146.     ,OBJECT_NAME(object_id) AS [Имя таблицы] 
  147.     ,OBJECT_NAME([IF].index_id) AS [Имя индкса]   
  148.     ,[IF].*
  149. FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) AS [IF]
  150. WHERE avg_fragmentation_in_percent > 30 -- процент фрагментации выше 30%.
  151. ORDER BY avg_fragmentation_in_percent
  152.  
  153.  
RAW Paste Data