{"id":94,"date":"2015-10-01T19:40:21","date_gmt":"2015-10-01T17:40:21","guid":{"rendered":"http:\/\/vmarkovsky.org.ua\/wordpress\/?p=94"},"modified":"2015-10-01T20:09:12","modified_gmt":"2015-10-01T18:09:12","slug":"defrag-index-ms-sql-server","status":"publish","type":"post","link":"https:\/\/vmarkovsky.org.ua\/wordpress\/index.php\/2015\/10\/01\/defrag-index-ms-sql-server\/","title":{"rendered":"\u0414\u0435\u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0411\u0414 \u0432 MS SQL Server"},"content":{"rendered":"<p>\u041e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0411\u0414 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u0432\u0441\u0442\u0440\u043e\u0435\u043d\u043d\u043e\u0433\u043e \u0438\u043d\u0441\u0442\u0440\u0443\u043c\u0435\u043d\u0442\u0430 &#8220;Maintenance Plans&#8221; \u043d\u0435 \u0432\u0441\u0435\u0433\u0434\u0430 \u043e\u043f\u0440\u0430\u0432\u0434\u0430\u043d\u043e, \u043e\u0441\u043e\u0431\u0435\u043d\u043d\u043e \u0434\u043b\u044f \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u0431\u0430\u0437. \u041a\u0440\u0430\u0441\u0438\u0432\u043e \u0441\u0444\u043e\u0440\u043c\u0443\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u043e <a href=\"https:\/\/technet.microsoft.com\/ru-ru\/magazine\/2008.08.database.aspx\">\u0442\u0443\u0442<\/a>:<\/p>\n<blockquote><p>\u041d\u0435\u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0438 \u043f\u0440\u043e\u0441\u0442\u043e \u0440\u0435\u0448\u0430\u044e\u0442 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u0430\u0432\u043b\u0438\u0432\u0430\u0442\u044c \u0438\u043b\u0438 \u043f\u0435\u0440\u0435\u0441\u0442\u0440\u0430\u0438\u0432\u0430\u0442\u044c \u0432\u0441\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u043a\u0430\u0436\u0434\u0443\u044e \u043d\u043e\u0447\u044c \u0438\u043b\u0438 \u043a\u0430\u0436\u0434\u0443\u044e \u043d\u0435\u0434\u0435\u043b\u044e (\u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f, \u043d\u0430\u043f\u0440\u0438\u043c\u0435\u0440, \u0432\u0430\u0440\u0438\u0430\u043d\u0442 \u0441 \u043f\u043b\u0430\u043d\u043e\u043c \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u044f) \u0432\u043c\u0435\u0441\u0442\u043e \u0442\u043e\u0433\u043e, \u0447\u0442\u043e\u0431\u044b \u0432\u044b\u044f\u0441\u043d\u044f\u0442\u044c, \u043a\u0430\u043a\u0438\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u044b \u0438 \u043a\u0430\u043a\u043e\u0435 \u043f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 \u0434\u0430\u0441\u0442 \u0443\u0441\u0442\u0440\u0430\u043d\u0435\u043d\u0438\u0435 \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438. \u0425\u043e\u0442\u044f \u044d\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u0445\u043e\u0440\u043e\u0448\u0438\u043c \u0440\u0435\u0448\u0435\u043d\u0438\u0435\u043c \u0434\u043b\u044f \u043d\u0435\u0432\u043e\u043b\u044c\u043d\u043e\u0433\u043e \u0430\u0434\u043c\u0438\u043d\u0438\u0441\u0442\u0440\u0430\u0442\u043e\u0440\u0430 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u043f\u0440\u043e\u0441\u0442\u043e \u0436\u0435\u043b\u0430\u0435\u0442 \u043f\u0440\u0438\u043c\u0435\u043d\u0438\u0442\u044c \u043a\u0430\u043a\u043e\u0435-\u0442\u043e \u0440\u0435\u0448\u0435\u043d\u0438\u0435 \u0441 \u043c\u0438\u043d\u0438\u043c\u0430\u043b\u044c\u043d\u044b\u043c\u0438 \u0443\u0441\u0438\u043b\u0438\u044f\u043c\u0438, \u044d\u0442\u043e \u043c\u043e\u0436\u0435\u0442 \u043e\u043a\u0430\u0437\u0430\u0442\u044c\u0441\u044f \u043e\u0447\u0435\u043d\u044c \u043f\u043b\u043e\u0445\u0438\u043c \u0432\u044b\u0431\u043e\u0440\u043e\u043c \u0434\u043b\u044f \u0431\u043e\u043b\u0435\u0435 \u043a\u0440\u0443\u043f\u043d\u044b\u0445 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 \u0438 \u0441\u0438\u0441\u0442\u0435\u043c, \u0433\u0434\u0435 \u0440\u0435\u0441\u0443\u0440\u0441\u044b \u0432 \u0434\u0435\u0444\u0438\u0446\u0438\u0442\u0435.<!--more--><\/p><\/blockquote>\n<p>\u0412 \u043a\u043e\u043d\u0442\u0435\u043a\u0441\u0442\u0435 1\u0421 \u0413\u0438\u043b\u0435\u0432 \u0440\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0443\u0435\u0442 \u0441\u043a\u0440\u0438\u043f\u0442: <a href=\"http:\/\/www.gilev.ru\/dbreindex\/\">http:\/\/www.gilev.ru\/dbreindex\/<\/a>\u00a0(\u0440\u0430\u043d\u044c\u0448\u0435 \u0431\u044b\u043b\u043e \u0442\u0443\u0442:\u00a0http:\/\/www.gilev.ru\/1c\/mssql\/dbreindex.htm ).<\/p>\n<p>\u0415\u0441\u043b\u0438 \u0431\u0430\u0437 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e, \u043b\u0443\u0447\u0448\u0435 \u0432\u043e\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c\u0441\u044f \u0441\u043a\u0440\u0438\u043f\u0442\u043e\u043c, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u043e\u0431\u0440\u0430\u0431\u0430\u0442\u044b\u0432\u0430\u0435\u0442 \u0432\u0441\u0435 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u0441\u043a\u0438\u0435 \u0431\u0430\u0437\u044b:\u00a0<a href=\"http:\/\/blogs.msmvps.com\/gladchenko\/2008\/03\/30\/tips-for-dba-using-sys-dm_db_index_physical_stats-in-a-script-to-rebuild-or-reorganize-indexes-no-partitions-sql-server-2005\/\">http:\/\/blogs.msmvps.com\/gladchenko\/2008\/03\/30\/tips-for-dba-using-sys-dm_db_index_physical_stats-in-a-script-to-rebuild-or-reorganize-indexes-no-partitions-sql-server-2005\/<\/a>\u00a0 \u00a0(\u0440\u0430\u043d\u044c\u0448\u0435 \u0431\u044b\u043b\u043e \u0442\u0443\u0442:\u00a0http:\/\/msmvps.com\/blogs\/gladchenko\/archive\/2008\/03\/31\/1563721.aspx ). \u0412 \u043e\u0441\u043d\u043e\u0432\u043d\u043e\u043c \u043f\u043e\u0441\u0442\u0435 \u0441\u043a\u0440\u0438\u043f\u0442\u044b \u0434\u043b\u044f \u043e\u0434\u043d\u043e\u0439 \u0411\u0414, \u0430 \u0432 <a href=\"http:\/\/blogs.msmvps.com\/gladchenko\/2008\/03\/30\/tips-for-dba-using-sys-dm_db_index_physical_stats-in-a-script-to-rebuild-or-reorganize-indexes-no-partitions-sql-server-2005\/#comment-39\" target=\"_blank\">\u044d\u0442\u043e\u043c\u00a0\u043a\u043e\u043c\u043c\u0435\u043d\u0442\u0430\u0440\u0438\u0438<\/a>\u00a0&#8211; \u0434\u043b\u044f \u0432\u0441\u0435\u0445 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u0441\u043a\u0438\u0445 \u0431\u0430\u0437. \u041d\u043e \u0442\u0430\u043c \u043e\u043d \u043a\u0430\u043a-\u0442\u043e \u043a\u0440\u0438\u0432\u043e \u043e\u043f\u0443\u0431\u043b\u0438\u043a\u043e\u0432\u0430\u043d, \u0441\u043e \u0441\u0442\u0440\u0430\u043d\u043d\u044b\u043c\u0438 \u043a\u0430\u0432\u044b\u0447\u043a\u0430\u043c\u0438 \u0438 \u0442\u0438\u0440\u0435. \u041a\u0440\u043e\u043c\u0435 \u0442\u043e\u0433\u043e, \u0435\u0441\u043b\u0438 \u043e\u0434\u043d\u0430 \u0438\u0437 \u0431\u0430\u0437 &#8211; \u0432 \u0440\u0435\u0436\u0438\u043c\u0435 offline &#8211; \u0432\u044b\u0432\u0430\u043b\u0438\u0432\u0430\u0435\u0442\u0441\u044f \u0441 \u043e\u0448\u0438\u0431\u043a\u043e\u0439. \u041d\u0438\u0436\u0435 \u0438\u0441\u043f\u0440\u0430\u0432\u043b\u0435\u043d\u043d\u044b\u0439 \u0432\u0430\u0440\u0438\u0430\u043d\u0442:<\/p>\n<pre>SET NOCOUNT ON\r\n SET DEADLOCK_PRIORITY LOW\r\n DECLARE @SQL nvarchar(2048), @db int, @@SQL2 nvarchar(max), @ErrorID int\r\n SET @@SQL2 = 'DECLARE @SQL nvarchar(2048);\r\n '\r\n DECLARE usersdatabases CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR\r\n SELECT database_id FROM sys.databases\r\n WHERE (CAST(case when name in ('master','model','msdb','tempdb') then 1 else is_distributor end AS bit)=0\r\n AND CAST(isnull(source_database_id, 0) AS bit)=0\r\n AND state=0)\r\n ORDER BY [name]<\/pre>\n<pre>OPEN GLOBAL usersdatabases\r\n WHILE 1 = 1\r\n BEGIN\r\n FETCH usersdatabases INTO @db\r\n IF @@fetch_status &lt;&gt; 0 BREAK\r\n SET @@SQL2 = @@SQL2 + '\r\n USE ' + DB_NAME(@db) + ';\r\n DECLARE reindex' + CAST(@db AS nvarchar) + ' CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR\r\n SELECT ''ALTER INDEX ALL ON ['' + DB_NAME(' + CAST(@db AS nvarchar) + ')+ '']..['' + OBJECT_NAME(afp.[OBJECT_ID]) + ''] REBUILD WITH (SORT_IN_TEMPDB = ON);'' AS [\u0418\u043d\u0441\u0442\u0440\u0443\u043a\u0446\u0438\u044f T-SQL]\r\n FROM ' + DB_NAME(@db) + '.sys.dm_db_index_physical_stats (' + CAST(@db AS nvarchar) + ', NULL, NULL, NULL, ''SAMPLED'') AS afp\r\n WHERE afp.database_id = ' + CAST(@db AS nvarchar) + '\r\n AND afp.index_type_desc IN (''CLUSTERED INDEX'')\r\n AND (afp.avg_fragmentation_in_percent &gt;= 15 OR afp.avg_page_space_used_in_percent &lt;= 60)\r\n AND afp.page_count &gt; 12\r\n UNION ALL\r\n SELECT [\u0418\u043d\u0441\u0442\u0440\u0443\u043a\u0446\u0438\u044f T-SQL] =\r\n CASE\r\n WHEN afp.avg_fragmentation_in_percent &gt;= 15\r\n OR afp.avg_page_space_used_in_percent &lt;= 60\r\n THEN ''ALTER INDEX ['' + i.name + ''] ON ['' + DB_NAME(' + CAST(@db AS nvarchar) + ') + '']..['' + OBJECT_NAME(afp.[OBJECT_ID]) + ''] REBUILD WITH (SORT_IN_TEMPDB = ON);''\r\n WHEN (afp.avg_fragmentation_in_percent &lt; 15 AND afp.avg_fragmentation_in_percent &gt;= 10)\r\n OR (afp.avg_page_space_used_in_percent &gt; 60 AND afp.avg_page_space_used_in_percent &lt; 75)\r\n THEN ''ALTER INDEX ['' + i.name + ''] ON ['' + DB_NAME(' + CAST(@db AS nvarchar) + ') + '']..['' + OBJECT_NAME(afp.[OBJECT_ID]) + ''] REORGANIZE;''\r\n END\r\n FROM ' + DB_NAME(@db) + '.sys.dm_db_index_physical_stats (' + CAST(@db AS nvarchar) + ', NULL, NULL, NULL, ''SAMPLED'') AS afp\r\n JOIN ' + DB_NAME(@db) + '.sys.indexes AS i\r\n ON (afp.[OBJECT_ID] = i.[OBJECT_ID] AND afp.index_id = i.index_id)\r\n AND afp.database_id = ' + CAST(@db AS nvarchar) + '\r\n AND afp.index_type_desc IN (''NONCLUSTERED INDEX'')\r\n AND (\r\n (afp.avg_fragmentation_in_percent &gt;= 10 AND afp.avg_fragmentation_in_percent &lt; 15)\r\n OR (afp.avg_page_space_used_in_percent &gt; 60 AND afp.avg_page_space_used_in_percent &lt; 75)\r\n )\r\n AND afp.page_count &gt; 12\r\n AND afp.[OBJECT_ID] NOT IN (\r\n SELECT [OBJECT_ID]\r\n FROM ' + DB_NAME(@db) + '.sys.dm_db_index_physical_stats (' + CAST(@db AS nvarchar) + ', NULL, NULL, NULL, ''SAMPLED'')\r\n WHERE database_id = ' + CAST(@db AS nvarchar) + '\r\n AND index_type_desc IN (''CLUSTERED INDEX'')\r\n AND (avg_fragmentation_in_percent &gt;= 15 OR avg_page_space_used_in_percent &lt; 60)\r\n AND page_count &gt; 1\r\n )\r\n ORDER BY [\u0418\u043d\u0441\u0442\u0440\u0443\u043a\u0446\u0438\u044f T-SQL]'<\/pre>\n<pre>SET @@SQL2 = @@SQL2 + '\r\n OPEN GLOBAL reindex' + CAST(@db AS nvarchar) + '\r\n WHILE 1 = 1\r\n BEGIN\r\n FETCH reindex' + CAST(@db AS nvarchar) + ' INTO @SQL\r\n IF @@fetch_status &lt;&gt; 0 BREAK\r\n EXEC(@SQL)\r\n PRINT @SQL\r\n END\r\n CLOSE GLOBAL reindex' + CAST(@db AS nvarchar) + '\r\n DEALLOCATE GLOBAL reindex' + CAST(@db AS nvarchar) + ''<\/pre>\n<pre>--PRINT @db\r\n END\r\n CLOSE GLOBAL usersdatabases\r\n DEALLOCATE usersdatabases\r\n DECLARE @SQL1 nvarchar(500)\r\n --PRINT @@SQL2\r\n EXECUTE master..sp_executesql\r\n @@SQL2\r\n ,N'@SQL1 nvarchar(2048) OUTPUT'\r\n ,@SQL1 = @SQL1 OUTPUT\r\n --PRINT('\u2014\u2014\u2014\u2014\u2014\u2014\u2014-')\r\n --PRINT @SQL1\r\n GO<\/pre>\n<p><span style=\"text-align: justify;\">\u041a\u0440\u043e\u043c\u0435 \u044d\u0442\u043e\u0433\u043e \u0432\u0430\u0440\u0438\u0430\u043d\u0442\u0430, \u0435\u0441\u0442\u044c \u0447\u0443\u0434\u0435\u0441\u043d\u044b\u0439 \u043d\u0430\u0431\u043e\u0440 \u0441\u043a\u0440\u0438\u043f\u0442\u043e\u0432 \u043e\u0442\u00a0ola.hallengren.com . \u041e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0442\u0443\u0442:\u00a0<\/span><a style=\"text-align: justify;\" href=\"https:\/\/ola.hallengren.com\/sql-server-index-and-statistics-maintenance.html\" target=\"_blank\">https:\/\/ola.hallengren.com\/sql-server-index-and-statistics-maintenance.html<\/a><br \/>\n&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u041e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0411\u0414 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u0432\u0441\u0442\u0440\u043e\u0435\u043d\u043d\u043e\u0433\u043e \u0438\u043d\u0441\u0442\u0440\u0443\u043c\u0435\u043d\u0442\u0430 &#8220;Maintenance Plans&#8221; \u043d\u0435 \u0432\u0441\u0435\u0433\u0434\u0430 \u043e\u043f\u0440\u0430\u0432\u0434\u0430\u043d\u043e, \u043e\u0441\u043e\u0431\u0435\u043d\u043d\u043e \u0434\u043b\u044f \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u0431\u0430\u0437. \u041a\u0440\u0430\u0441\u0438\u0432\u043e \u0441\u0444\u043e\u0440\u043c\u0443\u043b\u0438\u0440\u043e\u0432\u0430\u043d\u043e \u0442\u0443\u0442: \u041d\u0435\u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0438 \u043f\u0440\u043e\u0441\u0442\u043e \u0440\u0435\u0448\u0430\u044e\u0442 \u0432\u043e\u0441\u0441\u0442\u0430\u043d\u0430\u0432\u043b\u0438\u0432\u0430\u0442\u044c \u0438\u043b\u0438 \u043f\u0435\u0440\u0435\u0441\u0442\u0440\u0430\u0438\u0432\u0430\u0442\u044c \u0432\u0441\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u043a\u0430\u0436\u0434\u0443\u044e \u043d\u043e\u0447\u044c \u0438\u043b\u0438 \u043a\u0430\u0436\u0434\u0443\u044e \u043d\u0435\u0434\u0435\u043b\u044e (\u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f, \u043d\u0430\u043f\u0440\u0438\u043c\u0435\u0440, \u0432\u0430\u0440\u0438\u0430\u043d\u0442 \u0441 \u043f\u043b\u0430\u043d\u043e\u043c \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u043d\u0438\u044f) \u0432\u043c\u0435\u0441\u0442\u043e \u0442\u043e\u0433\u043e, \u0447\u0442\u043e\u0431\u044b \u0432\u044b\u044f\u0441\u043d\u044f\u0442\u044c, \u043a\u0430\u043a\u0438\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u044b \u0438 \u043a\u0430\u043a\u043e\u0435 \u043f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 \u0434\u0430\u0441\u0442 \u0443\u0441\u0442\u0440\u0430\u043d\u0435\u043d\u0438\u0435 \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438. \u0425\u043e\u0442\u044f \u044d\u0442\u043e \u043c\u043e\u0436\u0435\u0442 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-94","post","type-post","status-publish","format-standard","hentry","category-4"],"_links":{"self":[{"href":"https:\/\/vmarkovsky.org.ua\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/94","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vmarkovsky.org.ua\/wordpress\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmarkovsky.org.ua\/wordpress\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmarkovsky.org.ua\/wordpress\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vmarkovsky.org.ua\/wordpress\/index.php\/wp-json\/wp\/v2\/comments?post=94"}],"version-history":[{"count":17,"href":"https:\/\/vmarkovsky.org.ua\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/94\/revisions"}],"predecessor-version":[{"id":111,"href":"https:\/\/vmarkovsky.org.ua\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/94\/revisions\/111"}],"wp:attachment":[{"href":"https:\/\/vmarkovsky.org.ua\/wordpress\/index.php\/wp-json\/wp\/v2\/media?parent=94"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmarkovsky.org.ua\/wordpress\/index.php\/wp-json\/wp\/v2\/categories?post=94"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmarkovsky.org.ua\/wordpress\/index.php\/wp-json\/wp\/v2\/tags?post=94"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}