{"id":79,"date":"2019-01-26T23:02:06","date_gmt":"2019-01-26T20:02:06","guid":{"rendered":"http:\/\/andrewg.ru\/?p=79"},"modified":"2019-01-28T05:57:37","modified_gmt":"2019-01-28T02:57:37","slug":"%d1%83%d1%81%d0%ba%d0%be%d1%80%d0%b5%d0%bd%d0%b8%d0%b5-%d1%80%d0%b0%d0%b1%d0%be%d1%82%d1%8b-%d0%b1%d0%b0%d0%b7%d1%8b-%d0%b4%d0%b0%d0%bd%d0%bd%d1%8b%d1%85-%d1%81-%d0%bf%d0%be%d0%bc%d0%be%d1%89%d1%8c","status":"publish","type":"post","link":"https:\/\/andrewg.ru\/?p=79","title":{"rendered":"\u0423\u0441\u043a\u043e\u0440\u0435\u043d\u0438\u0435 \u0440\u0430\u0431\u043e\u0442\u044b \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u0434\u0435\u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432"},"content":{"rendered":"<p>\u0421\u043e \u0432\u0440\u0435\u043c\u0435\u043d\u0435\u043c \u0438 \u043d\u0430\u043a\u043e\u043f\u043b\u0435\u043d\u0438\u0435\u043c \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 \u0431\u0430\u0437\u0435 \u043c\u043e\u0436\u0435\u0442 \u043d\u0430\u043a\u0430\u043f\u043b\u0438\u0432\u0430\u0442\u044c\u0441\u044f \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432, \u0438 \u044d\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u0441\u0438\u043b\u044c\u043d\u043e \u0441\u043a\u0430\u0437\u0430\u0442\u044c\u0441\u044f \u043d\u0430 \u0441\u043a\u043e\u0440\u043e\u0441\u0442\u0438 \u0440\u0430\u0431\u043e\u0442\u044b \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445. \u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u0435\u043d\u043e \u043a\u0430\u043a \u0432\u044b\u044f\u0432\u0438\u0442\u044c \u043d\u0430\u043b\u0438\u0447\u0438\u0435 \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u044b \u0438 \u043a\u0430\u043a \u0435\u0451 \u0443\u0441\u0442\u0440\u0430\u043d\u0438\u0442\u044c.<\/p>\n<p><!--more--><\/p>\n<p>\u0414\u043b\u044f \u043d\u0430\u0447\u0430\u043b\u0430 \u043c\u043e\u0436\u043d\u043e \u043f\u0440\u043e\u0432\u0435\u0440\u0438\u0442\u044c \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u044e<\/p>\n<pre>DECLARE @db_name varchar(50) = N'DbName',\r\n@table_name varchar(250) = N'TableName'\r\n\r\nSELECT IndStat.database_id,\r\nIndStat.object_id,\r\nQUOTENAME(s.name) + '.' + QUOTENAME(o.name) AS [object_name],\r\nIndStat.index_id,\r\nQUOTENAME(i.name) AS index_name,\r\nIndStat.avg_fragmentation_in_percent,\r\nIndStat.partition_number,\r\n(SELECT count (*) FROM sys.partitions p\r\nWHERE p.object_id = IndStat.object_id AND p.index_id = IndStat.index_id) AS partition_count\r\nFROM sys.dm_db_index_physical_stats\r\n(DB_ID(@db_name), OBJECT_ID(@table_name), NULL, NULL , 'LIMITED') AS IndStat\r\nINNER JOIN sys.objects AS o ON (IndStat.object_id = o.object_id)\r\nINNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id\r\nINNER JOIN sys.indexes i ON (i.object_id = IndStat.object_id AND i.index_id = IndStat.index_id)\r\nWHERE IndStat.avg_fragmentation_in_percent &gt; 10 AND IndStat.index_id &gt; 0<\/pre>\n<p>\u0412 db_name \u0438\/\u0438\u043b\u0438 table_name \u043c\u043e\u0436\u043d\u043e \u043f\u0435\u0440\u0435\u0434\u0430\u0442\u044c Null, \u0442\u043e\u0433\u0434\u0430 \u0432\u044b\u0432\u0435\u0434\u0443\u0442\u0441\u044f \u0434\u0430\u043d\u043d\u044b\u0435 \u043f\u043e \u0432\u0441\u0435\u043c \u0431\u0430\u0437\u0430\u043c \u0438\/\u0438\u043b\u0438 \u0442\u0430\u0431\u043b\u0438\u0446\u0430\u043c.<\/p>\n<p>\u0412 \u0432\u044b\u0432\u043e\u0434\u0435 \u0441\u043b\u0435\u0434\u0443\u0435\u0442 \u043e\u0431\u0440\u0430\u0442\u0438\u0442\u044c \u0432\u043d\u0438\u043c\u0430\u043d\u0438\u0435 \u043d\u0430 \u0441\u0442\u043e\u043b\u0431\u0435\u0446 avg_fragmentation_in_percent, \u0447\u0435\u043c \u043e\u043d \u0432\u044b\u0448\u0435, \u0442\u0435\u043c \u0441\u0438\u043b\u044c\u043d\u0435\u0435 \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0438\u0440\u043e\u0432\u0430\u043d \u0438\u043d\u0434\u0435\u043a\u0441, \u0438 \u0442\u0435\u043c \u0431\u043e\u043b\u044c\u0448\u0435 \u0431\u0443\u0434\u0435\u0442 \u0432\u044b\u0438\u0433\u0440\u044b\u0448 \u043e\u0442 \u0435\u0433\u043e \u0434\u0435\u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438.<\/p>\n<p>\u0414\u043b\u044f \u0434\u0435\u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u043c\u043e\u0436\u043d\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0439 \u043a\u043e\u0434<\/p>\n<pre><code class=\"1c hljs\">DECLARE\r\n      @PageCount INT = <span class=\"hljs-number\">128<\/span>\r\n    , @RebuildPercent INT = <span class=\"hljs-number\">30<\/span>\r\n    , @ReorganizePercent INT = <span class=\"hljs-number\">10<\/span>\r\n    , @IsOnlineRebuild BIT = <span class=\"hljs-number\">0<\/span>\r\n    , @IsVersion2012Plus BIT =\r\n        CASE WHEN CAST(SERVERPROPERTY('productversion') AS CHAR(<span class=\"hljs-number\">2<\/span>)) NOT IN ('8.', '9.', '10')\r\n            THEN <span class=\"hljs-number\">1<\/span>\r\n            ELSE <span class=\"hljs-number\">0<\/span>\r\n        END\r\n    , @IsEntEdition BIT =\r\n        CASE WHEN SERVERPROPERTY('EditionID') IN (<span class=\"hljs-number\">1804890536<\/span>, -<span class=\"hljs-number\">2117995310<\/span>)\r\n            THEN <span class=\"hljs-number\">1<\/span>\r\n            ELSE <span class=\"hljs-number\">0<\/span>\r\n        END\r\n    , @SQL NVARCHAR(MAX)\r\n\r\nSELECT @SQL = (\r\n    SELECT\r\n'\r\nALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s2.name) + '.' + QUOTENAME(o.name) + ' ' +\r\n        CASE WHEN s.avg_fragmentation_in_percent &gt;= @RebuildPercent\r\n            THEN 'REBUILD'\r\n            ELSE 'REORGANIZE'\r\n        END + ' PARTITION = ' +\r\n        CASE WHEN ds.[type] != 'PS'\r\n            THEN 'ALL'\r\n            ELSE CAST(s.partition_number AS NVARCHAR(<span class=\"hljs-number\">10<\/span>))\r\n        END + ' WITH (' + \r\n        CASE WHEN s.avg_fragmentation_in_percent &gt;= @RebuildPercent\r\n            THEN 'SORT_IN_TEMPDB = ON' + \r\n                CASE WHEN @IsEntEdition = <span class=\"hljs-number\">1<\/span>\r\n                        AND @IsOnlineRebuild = <span class=\"hljs-number\">1<\/span> \r\n                        AND ISNULL(lob.is_lob_legacy, <span class=\"hljs-number\">0<\/span>) = <span class=\"hljs-number\">0<\/span>\r\n                        AND (\r\n                                ISNULL(lob.is_lob, <span class=\"hljs-number\">0<\/span>) = <span class=\"hljs-number\">0<\/span>\r\n                            OR\r\n                                (lob.is_lob = <span class=\"hljs-number\">1<\/span> AND @IsVersion2012Plus = <span class=\"hljs-number\">1<\/span>)\r\n                        )\r\n                    THEN ', ONLINE = ON'\r\n                    ELSE ''\r\n                END\r\n            ELSE 'LOB_COMPACTION = ON'\r\n        END + ')'\r\n    FROM sys.dm_db_index_physical_stats(DB_ID(), <span class=\"hljs-literal\">NULL<\/span>, <span class=\"hljs-literal\">NULL<\/span>, <span class=\"hljs-literal\">NULL<\/span>, <span class=\"hljs-literal\">NULL<\/span>) s\r\n    JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id\r\n    LEFT JOIN (\r\n        SELECT\r\n              c.[object_id]\r\n            , index_id = ISNULL(i.index_id, <span class=\"hljs-number\">1<\/span>)\r\n            , is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (<span class=\"hljs-number\">34<\/span>, <span class=\"hljs-number\">35<\/span>, <span class=\"hljs-number\">99<\/span>) THEN <span class=\"hljs-number\">1<\/span> END)\r\n            , is_lob = MAX(CASE WHEN c.max_length = -<span class=\"hljs-number\">1<\/span> THEN <span class=\"hljs-number\">1<\/span> END)\r\n        FROM sys.columns c\r\n        LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id]\r\n            AND c.column_id = i.column_id AND i.index_id &gt; <span class=\"hljs-number\">0<\/span>\r\n        WHERE c.system_type_id IN (<span class=\"hljs-number\">34<\/span>, <span class=\"hljs-number\">35<\/span>, <span class=\"hljs-number\">99<\/span>)\r\n            OR c.max_length = -<span class=\"hljs-number\">1<\/span>\r\n        GROUP BY c.[object_id], i.index_id\r\n    ) lob ON lob.[object_id] = i.[object_id] AND lob.index_id = i.index_id\r\n    JOIN sys.objects o ON o.[object_id] = i.[object_id]\r\n    JOIN sys.schemas s2 ON o.[schema_id] = s2.[schema_id]\r\n    JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id\r\n    WHERE i.[type] IN (<span class=\"hljs-number\">1<\/span>, <span class=\"hljs-number\">2<\/span>)\r\n        AND i.is_disabled = <span class=\"hljs-number\">0<\/span>\r\n        AND i.is_hypothetical = <span class=\"hljs-number\">0<\/span>\r\n        AND s.index_level = <span class=\"hljs-number\">0<\/span>\r\n        AND s.page_count &gt; @PageCount\r\n        AND s.alloc_unit_type_desc = 'IN_ROW_DATA'\r\n        AND o.[type] IN ('U', 'V')\r\n        AND s.avg_fragmentation_in_percent &gt; @ReorganizePercent\r\n    FOR XML PATH(''), TYPE\r\n).value('.', 'NVARCHAR(MAX)')\r\n\r\nPRINT @SQL<\/code><\/pre>\n<p>\u041e\u043d \u0430\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043f\u043e\u043b\u0443\u0447\u0438\u0442 \u0441\u043f\u0438\u0441\u043e\u043a \u0432\u0441\u0435\u0445 \u0434\u0435\u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0445 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0438 \u0441\u0433\u0435\u043d\u0435\u0440\u0438\u0440\u0443\u0435\u0442 \u043a\u043e\u0434, \u0437\u0430\u043f\u0443\u0441\u0442\u0438\u0432 \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u043c\u043e\u0436\u043d\u043e \u0431\u0443\u0434\u0435\u0442 \u0434\u0435\u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u0438\u043d\u0434\u0435\u043a\u0441\u044b.<\/p>\n<p><span style=\"color: #ff0000;\">\u0412\u043d\u0438\u043c\u0430\u043d\u0438\u0435!<\/span> \u041d\u0435 \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u0439\u0442\u0435 \u043a\u043e\u0434 \u0431\u0435\u0437\u0434\u0443\u043c\u043d\u043e \u043d\u0430 \u0431\u043e\u0435\u0432\u043e\u0439 \u0441\u0438\u0441\u0442\u0435\u043c\u0435 \u043d\u0435 \u043e\u043f\u0440\u043e\u0431\u043e\u0432\u0430\u0432 \u0435\u0433\u043e \u043d\u0430 \u0442\u0435\u0441\u0442\u0435. \u0410\u0432\u0442\u043e\u0440 \u0441\u0442\u0430\u0442\u044c\u0438 \u043d\u0435 \u043d\u0435\u0441\u0451\u0442 \u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0435\u043d\u043d\u043e\u0441\u0442\u0438 \u0437\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u0441\u0442\u0432\u0438\u044f.<\/p>\n<p>\u0418\u0441\u0442\u043e\u0447\u043d\u0438\u043a\u0438:<br \/>\n<a href=\"https:\/\/community.terrasoft.ru\/articles\/optimiziruem-bazu-na-mssql-opredelaem-fragmentaciu-indeksov\">\u041e\u043f\u0442\u0438\u043c\u0438\u0437\u0438\u0440\u0443\u0435\u043c \u0431\u0430\u0437\u0443 \u043d\u0430 MSSQL: \u041e\u043f\u0440\u0435\u0434\u0435\u043b\u044f\u0435\u043c \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u044e \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 <\/a><br \/>\n<a href=\"https:\/\/habr.com\/ru\/post\/209698\/\">\u041f\u043b\u0430\u043d \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u044f &#171;\u043d\u0430 \u043a\u0430\u0436\u0434\u044b\u0439 \u0434\u0435\u043d\u044c&#187; &#8212; \u0427\u0430\u0441\u0442\u044c 1: \u0410\u0432\u0442\u043e\u043c\u0430\u0442\u0438\u0447\u0435\u0441\u043a\u0430\u044f \u0434\u0435\u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u0421\u043e \u0432\u0440\u0435\u043c\u0435\u043d\u0435\u043c \u0438 \u043d\u0430\u043a\u043e\u043f\u043b\u0435\u043d\u0438\u0435\u043c \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 \u0431\u0430\u0437\u0435 \u043c\u043e\u0436\u0435\u0442 \u043d\u0430\u043a\u0430\u043f\u043b\u0438\u0432\u0430\u0442\u044c\u0441\u044f \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432, \u0438 \u044d\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u0441\u0438\u043b\u044c\u043d\u043e \u0441\u043a\u0430\u0437\u0430\u0442\u044c\u0441\u044f \u043d\u0430 \u0441\u043a\u043e\u0440\u043e\u0441\u0442\u0438 \u0440\u0430\u0431\u043e\u0442\u044b \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445. \u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u0435\u043d\u043e \u043a\u0430\u043a \u0432\u044b\u044f\u0432\u0438\u0442\u044c \u043d\u0430\u043b\u0438\u0447\u0438\u0435 \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u044b \u0438 \u043a\u0430\u043a \u0435\u0451 \u0443\u0441\u0442\u0440\u0430\u043d\u0438\u0442\u044c.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[12,11],"class_list":["post-79","post","type-post","status-publish","format-standard","hentry","category-1","tag-ms-sql","tag-11"],"_links":{"self":[{"href":"https:\/\/andrewg.ru\/index.php?rest_route=\/wp\/v2\/posts\/79","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andrewg.ru\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andrewg.ru\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andrewg.ru\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/andrewg.ru\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=79"}],"version-history":[{"count":3,"href":"https:\/\/andrewg.ru\/index.php?rest_route=\/wp\/v2\/posts\/79\/revisions"}],"predecessor-version":[{"id":85,"href":"https:\/\/andrewg.ru\/index.php?rest_route=\/wp\/v2\/posts\/79\/revisions\/85"}],"wp:attachment":[{"href":"https:\/\/andrewg.ru\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=79"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andrewg.ru\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=79"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andrewg.ru\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=79"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}