SaveText.Ru

Untitled
  1. IF OBJECT_ID('dbwork_log') IS NULL
  2. BEGIN
  3.   CREATE TABLE dbwork_log
  4.       (id INT IDENTITY(1, 1) PRIMARY KEY
  5.      , work_date SMALLDATETIME NOT NULL
  6.      , work_action INT NOT NULL
  7.      , info VARCHAR(MAX) NOT NULL
  8.       )
  9. END
  10. GO
  11.  
  12. SET ANSI_NULLS ON
  13. GO
  14.  
  15. SET QUOTED_IDENTIFIER ON
  16. GO
  17.  
  18. IF OBJECT_ID('Adm_reindex_V2', 'P') IS NOT NULL
  19. BEGIN
  20.   DROP PROCEDURE dbo.Adm_reindex_V2
  21. END
  22. GO
  23.  
  24. /**********************************************************************************
  25. Andrey 11.04.2018
  26. Поменял алгоритм. Восстанавливаем индексы по мере необходимости.
  27. Режим восстановления зависит от степени фрагментации.
  28. При регулярном использовании снижает время на обслуживание.
  29. Andrey 12.10.2018
  30. убрал курсор
  31.  
  32. RAD 25.07.2019
  33. Добавлен параметр, который позволяет не изменять индексы,
  34. а посмотреть, какие нуждаются в реорганизации или перестроении
  35. @isRebuild = 0 - показать только таблицы и индексы
  36. @isRebuild = 1 - показать таблицы и индексы, перестроить
  37. или реорганизовать индексы у таблиц (статус в сообщениях будет выводиться)
  38.  
  39. Также были добавлены сообщения о том, сколько осталось индексов изменить
  40.  
  41. #todo времянку сделать, чтобы не было копипаста
  42. **********************************************************************************/
  43.  
  44. CREATE PROCEDURE dbo.Adm_reindex_V2 @isRebuild BIT
  45. AS
  46. BEGIN
  47.  
  48.   DECLARE @dbid AS INT
  49.   SET @dbid = DB_ID()
  50.  
  51.   DECLARE @count_frag_index AS INT
  52.   SET @count_frag_index = 0
  53.  
  54.   DECLARE @code AS NVARCHAR(MAX)
  55.   SET @code = ''
  56.  
  57.   SELECT CASE @isRebuild
  58.            WHEN 1
  59.              THEN 'Запущен в режиме перестроения и реорганизации следующих индексов. '
  60.            WHEN 0
  61.              THEN 'Запущен в режиме просмотра индексов, которые нуждаются перестроения и реорганизации. '
  62.          END
  63.  
  64.   SELECT 'Всего индексов ' + CAST(COUNT(*) AS VARCHAR(10)) + ' (в том числе и не нуждающиеся)'
  65.   FROM sys.tables AS o
  66.     JOIN sys.indexes AS i ON o.object_id = i.object_id
  67.     JOIN sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, NULL) AS st ON st.object_id = i.object_id
  68.                                                                                 AND st.index_id = i.index_id
  69.  
  70.   SELECT o.name AS 'Table'
  71.        , p.rows AS 'RowCount'
  72.        , i.name AS 'Index'
  73.        , CASE
  74.            WHEN i.fill_factor < 90
  75.                 AND i.type_desc <> 'CLUSTERED'
  76.                 OR i.fill_factor <> 100
  77.                 AND i.type_desc = 'CLUSTERED'
  78.              THEN N' REBUILD WITH (FILLFACTOR = ' + CASE
  79.                                                       WHEN i.type_desc = 'CLUSTERED'
  80.                                                         THEN '100'
  81.                                                     ELSE '90'
  82.                                                     END + ')'
  83.          ELSE CASE
  84.                 WHEN st.avg_fragmentation_in_percent > 30
  85.                   THEN N' REBUILD'
  86.               ELSE N' REORGANIZE'
  87.               END
  88.          END AS 'Action'
  89.        , i.type_desc
  90.        , i.fill_factor
  91.        , st.avg_fragmentation_in_percent
  92.   FROM sys.tables AS o
  93.     CROSS APPLY (
  94.                  SELECT SUM(p.rows) AS rows
  95.                  FROM sys.partitions AS p
  96.                  WHERE o.object_id = p.object_id
  97.                 ) AS p
  98.     JOIN sys.indexes AS i ON o.object_id = i.object_id
  99.     JOIN sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, NULL) AS st ON st.object_id = i.object_id
  100.                                                                                 AND st.index_id = i.index_id
  101.   WHERE st.page_count > 8 --количество страниц в индексе
  102.         AND st.index_id > 0
  103.         AND (st.avg_fragmentation_in_percent > 5
  104.              OR i.fill_factor < 90)--процент фрагментации
  105.   ORDER BY 'Table'
  106.  
  107.   IF @isRebuild = 1
  108.   BEGIN
  109.  
  110.     SELECT @count_frag_index = @count_frag_index + 1
  111.          , @code = ISNULL(@code, '') + N'
  112.     ALTER INDEX [' + i.name + N'] ON [' + o.name + N'] ' + CASE
  113.                                                              WHEN i.fill_factor < 90
  114.                                                                   AND i.type_desc <> 'CLUSTERED'
  115.                                                                   OR i.fill_factor <> 100
  116.                                                                   AND i.type_desc = 'CLUSTERED'
  117.                                                                THEN N' REBUILD WITH (FILLFACTOR = ' + CASE
  118.                                                                                                         WHEN i.type_desc =
  119.                                                                                                         'CLUSTERED'
  120.                                                                                                           THEN '100'
  121.                                                                                                       ELSE '90'
  122.                                                                                                       END + ')'
  123.                                                            ELSE CASE
  124.                                                                   WHEN st.avg_fragmentation_in_percent > 30
  125.                                                                     THEN N' REBUILD'
  126.                                                                 ELSE N' REORGANIZE'
  127.                                                                 END
  128.                                                            END + '
  129.       PRINT ''Altering table: ' + o.name + ' Index: ' + i.name + '; был перестроен индекс ' + CAST(@count_frag_index AS VARCHAR(
  130.                                                            10)) + ' из нуждающихся в этом ' + '[COUNTREPLACE]'''
  131.     FROM sys.tables AS o
  132.       JOIN sys.indexes AS i ON o.object_id = i.object_id
  133.       JOIN sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, NULL) AS st ON st.object_id = i.object_id
  134.                                                                                   AND st.index_id = i.index_id
  135.     WHERE st.page_count > 8 --количество страниц в индексе
  136.           AND st.index_id > 0
  137.           AND (st.avg_fragmentation_in_percent > 5
  138.                OR i.fill_factor < 90)--процент фрагментации
  139.     ORDER BY i.object_id
  140.            , i.index_id
  141.  
  142.     SET @code = REPLACE(@code, '[COUNTREPLACE]', @count_frag_index)
  143.  
  144.     EXEC (@code)
  145.   END
  146.  
  147.   IF OBJECT_ID('dbwork_log') IS NOT NULL
  148.   BEGIN
  149.     INSERT INTO dbwork_log
  150.            (work_date
  151.           , work_action
  152.           , info
  153.            )
  154.     SELECT GETDATE()
  155.          , 1
  156.          , @code
  157.   END
  158. END
  159.  
  160. GO
  161.  
  162. SET ANSI_NULLS ON
  163. GO
  164.  
  165. SET QUOTED_IDENTIFIER ON
  166. GO
  167.  
  168. IF OBJECT_ID('Adm_reStatistics', 'P') IS NOT NULL
  169. BEGIN
  170.   DROP PROCEDURE dbo.Adm_reStatistics
  171. END
  172. GO
  173.  
  174. CREATE PROCEDURE dbo.Adm_reStatistics
  175. AS
  176. DECLARE @ret AS INT
  177. EXEC @ret = sp_updatestats
  178.  
  179. IF OBJECT_ID('dbwork_log') IS NOT NULL
  180. BEGIN
  181.   INSERT INTO dbwork_log
  182.          (work_date
  183.         , work_action
  184.         , info
  185.          )
  186.   SELECT GETDATE()
  187.        , 2
  188.        , 'Statistics Update' + CHAR(13) + CHAR(10) + 'return code: ' + CAST(@ret AS VARCHAR(2))
  189. END
  190. GO
  191.  
  192. PRINT '***UPDATE INDEX***'
  193. EXEC Adm_reindex_V2 1
  194. GO
  195.  
  196. PRINT '***UPDATE STATISTICS***'
  197. EXEC adm_restatistics
  198. GO

Share with your friends:

Print