with igs as

(

select *

from sys.dm_db_missing_index_group_stats

)

, igd as

(

select *,

isnull(equality_columns,'')+','+isnull(inequality_columns,'') as ix_col

from sys.dm_db_missing_index_details

)

select --top(10)

'use ['+db_name(igd.database_id)+'];

create index ['+'ix_'+replace(convert(varchar(10),getdate(),120),'-','')+'_'+convert(varchar,igs.group_handle)+'] on '+

igd.[statement]+'('+

case

when left(ix_col,1)=',' then stuff(ix_col,1,1,'')

when right(ix_col,1)=',' then reverse(stuff(reverse(ix_col),1,1,''))

else ix_col

end

+') '+isnull('include('+igd.included_columns+')','')+' with(online=on, maxdop=0)

go

' command

,igs.user_seeks

,igs.user_scans

,igs.avg_total_user_cost

from igs

join sys.dm_db_missing_index_groups link on link.index_group_handle = igs.group_handle

join igd on link.index_handle = igd.index_handle

where igd.database_id = db_id()

order by igs.avg_total_user_cost * igs.user_seeks desc

Но их легко можно найти были по маске

select * from sys.indexes where name like 'ix[_]2017%'

 

Первый запрос показывает текущую фрагментацию индексов базы

SELECT TOP 100
       DatbaseName = DB_NAME(),
       TableName = OBJECT_NAME(s.[object_id]),
       IndexName = i.name,
       i.type_desc,
       [Fragmentation %] = ROUND(avg_fragmentation_in_percent,2),
       page_count,
       partition_number,
       'alter index [' + i.name + '] on [' + sh.name + '].['+ OBJECT_NAME(s.[object_id]) + '] REBUILD' + case
                                                                                                           when p.data_space_id is not null then ' PARTITION = '+convert(varchar(100),partition_number)
                                                                                                           else ''
                                                                                                         end + ' with(maxdop = 4,  SORT_IN_TEMPDB = on)' [sql]
  FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
  INNER JOIN sys.indexes as i ON s.[object_id] = i.[object_id] AND
                                 s.index_id = i.index_id
  left join sys.partition_schemes as p on i.data_space_id = p.data_space_id
  left join sys.objects o on  s.[object_id] = o.[object_id]
  left join sys.schemas as sh on sh.[schema_id] = o.[schema_id]
  WHERE s.database_id = DB_ID() AND
        i.name IS NOT NULL AND
        OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 and
        page_count > 100 and
        avg_fragmentation_in_percent > 10
  ORDER BY 4, page_count

Скрипт, приведенный ниже, запускает реорганизацию либо перестроение индексов для таблиц базы исходя из текущей дефрагментации (отбирает индексы, дефрагментированные более 10%, затем. если фрагментация менее 30% — реорганизация индексов, если более или равно 30% — перестроение). Я бы рекомендовал использовать как регламентное задание.

USE [myDB]
GO

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
          FROM partitions
          INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;

        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

 -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
         IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
         IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
         IF @partitioncount > 1
             SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

        EXEC (@command);

        PRINT N'Executed: ' + @command;

    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

SET QUOTED_IDENTIFIER OFF;
GO

отсутствующие индексы

Общее количество отсутствующих индексов в базах:

SELECT [DatabaseName] = DB_NAME(database_id),
       [Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC

Отсутствующие индексы, вызывающие высокие издержки:

SELECT TOP 100 
       [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0),
       avg_user_impact,
       TableName = statement,
       [EqualityUsage] = equality_columns,
       [InequalityUsage] = inequality_columns,
       [Include Cloumns] = included_columns
  FROM sys.dm_db_missing_index_groups g 
  INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle 
  INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
  WHERE database_id = DB_ID()
  ORDER BY [Total Cost] DESC

К анализу недостающих индексов можно еще добавить активность таблиц на чтение/запись:

-- Table Reads and Writes
-- Heap tables out of scope for this query. Heaps do not have indexes.
-- Only lists tables referenced since the last server restart
SELECT  @@ServerName AS ServerName ,
        DB_NAME() AS DBName ,
        OBJECT_NAME(ddius.object_id) AS TableName ,
        SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups)
                                                               AS Reads ,
        SUM(ddius.user_updates) AS Writes ,
        SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups
            + ddius.user_updates) AS [Reads&Writes] ,
        ( SELECT    DATEDIFF(s, create_date, GETDATE()) / 86400.0
          FROM      master.sys.databases
          WHERE     name = 'tempdb'
        ) AS SampleDays ,
        ( SELECT    DATEDIFF(s, create_date, GETDATE()) AS SecoundsRunnig
          FROM      master.sys.databases
          WHERE     name = 'tempdb'
        ) AS SampleSeconds
FROM    sys.dm_db_index_usage_stats ddius
        INNER JOIN sys.indexes i ON ddius.object_id = i.object_id
                                     AND i.index_id = ddius.index_id
WHERE   OBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1
        AND ddius.database_id = DB_ID()
GROUP BY OBJECT_NAME(ddius.object_id)
ORDER BY [Reads&Writes] DESC;
GO

Неиспользуемые индексы

-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the following step.
SELECT TOP 1
        DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,user_updates    
        ,system_updates    
        -- Useful fields below:
        --, *
INTO #TempUnusedIndexes
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND    user_seeks = 0
    AND user_scans = 0 
    AND user_lookups = 0
    AND s.[object_id] = -999  -- Dummy value to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?]; 
-- Table already exists.
INSERT INTO #TempUnusedIndexes 
SELECT TOP 10    
        DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,user_updates    
        ,system_updates    
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE  s.database_id = DB_ID()
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND    user_seeks = 0
    AND user_scans = 0 
    AND user_lookups = 0
    AND i.name IS NOT NULL    -- Ignore HEAP indexes.
ORDER BY user_updates DESC
;
'

-- Select records.
SELECT TOP 100 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
-- Tidy up.
DROP TABLE #TempUnusedIndexes

Индексы с высокими издержками

-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the following step.
SELECT TOP 1
        [Maintenance cost]  = (user_updates + system_updates)
        ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
INTO #TempMaintenanceCost
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() 
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND (user_updates + system_updates) > 0 -- Only report on active rows.
    AND s.[object_id] = -999  -- Dummy value to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?]; 
-- Table already exists.
INSERT INTO #TempMaintenanceCost 
SELECT TOP 10
        [Maintenance cost]  = (user_updates + system_updates)
        ,[Retrieval usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() 
    AND i.name IS NOT NULL    -- Ignore HEAP indexes.
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND (user_updates + system_updates) > 0 -- Only report on active rows.
ORDER BY [Maintenance cost]  DESC
;
'

-- Select records.
SELECT TOP 100 * FROM #TempMaintenanceCost 
ORDER BY [Maintenance cost]  DESC
-- Tidy up.
DROP TABLE #TempMaintenanceCost

Часто используемые индексы

-- Create required table structure only.
-- Note: this SQL must be the same as in the Database loop given in the -- following step.
SELECT TOP 1
        [Usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
INTO #TempUsage
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE   s.database_id = DB_ID() 
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
    AND (user_seeks + user_scans + user_lookups) > 0 
-- Only report on active rows.
    AND s.[object_id] = -999  -- Dummy value to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?]; 
-- Table already exists.
INSERT INTO #TempUsage 
SELECT TOP 10
        [Usage] = (user_seeks + user_scans + user_lookups)
        ,DatabaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
FROM   sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    AND s.index_id = i.index_id 
WHERE   s.database_id = DB_ID() 
    AND i.name IS NOT NULL    -- Ignore HEAP indexes.
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
    AND (user_seeks + user_scans + user_lookups) > 0 -- Only report on active rows.
ORDER BY [Usage]  DESC
;
'

-- Select records.
SELECT TOP 100 * FROM #TempUsage ORDER BY [Usage] DESC
-- Tidy up.
DROP TABLE #TempUsage

Самые тяжелые запросы

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qp.query_plan,
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time

FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Параллельные планы запросов

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES

(DEFAULT

  'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT

  query_plan AS CompleteQueryPlan,

  n.value('(@StatementText)[1]', 'VARCHAR(4000)')

  AS StatementText, n.value('(@StatementSubTreeCost)[1]',

  'VARCHAR(128)') AS StatementSubTreeCost, dm_ecp.usecounts

FROM sys.dm_exec_cached_plans AS dm_ecp

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS dm_eqp

CROSS APPLY query_plan.nodes

  ('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')

  AS qp(n)

WHERE

n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1

GO

Статистики ожиданий

SELECT TOP 10
        wait_type ,
        max_wait_time_ms wait_time_ms ,
        signal_wait_time_ms ,
        wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
                                    AS percent_total_waits ,
        100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
                                    AS percent_total_signal_waits ,
        100.0 * ( wait_time_ms - signal_wait_time_ms )
        / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM    sys.dm_os_wait_stats 
WHERE   wait_time_ms > 0 -- уберем нулевые задержки 
        AND wait_type NOT IN 
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
  'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
  'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
  'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
  'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
  'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
  'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
  'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
  'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
  'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
  'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
  'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC
select program_name,net_transport
from sys.dm_exec_sessions as t1
left join sys.dm_exec_connections AS t2 ON t1.session_id=t2.session_id
where not t1.program_name is null

 

 

 

 

 

 

 

DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT '
	PRINT ''Перестройка  [' + i.name + N'] начато ''  + Cast(GETDATE() as varchar);
	
	ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
		CASE WHEN s.avg_fragmentation_in_percent > 30
			THEN 'REBUILD WITH (FILLFACTOR = 80, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON)
			
	PRINT ''Перестройка  [' + i.name + N'] завершено '' + Cast(GETDATE() as varchar) '
ELSE 'REORGANIZE'
		END + ';'
		
		


	FROM (
		SELECT 
			  s.[object_id]
			, s.index_id
			, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
		FROM sys.dm_db_index_physical_stats(DB_ID('ax_molniya'), OBJECT_ID('dbo.INVENTTRANS'), NULL, NULL, 'LIMITED') s
		WHERE s.page_count > 128 -- > 1 MB
			AND s.index_id > 0 -- <> HEAP
			AND s.avg_fragmentation_in_percent > 5
		GROUP BY s.[object_id], s.index_id
	) s
	JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
	JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]

OPEN cur

FETCH NEXT FROM cur INTO @SQL

WHILE @@FETCH_STATUS = 0 BEGIN

	print (@SQL);

	FETCH NEXT FROM cur INTO @SQL
	
END 

CLOSE cur 
DEALLOCATE cur

Что значит строка (FILLFACTOR = 80, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON) :

[stextbox id=»info» caption=»FILLFACTOR =»]Указывает значение в процентах, показывающее, насколько полным компонент Database Engine должен сделать конечный уровень каждой страницы индекса во время создания или изменения индекса. Аргумент fillfactor должен быть целым числом от 1 до 100. Значение по умолчанию равно 0.[/stextbox]

[stextbox id=»info» caption=»PAD_INDEX «]Определяет разреженность индекса. Значение по умолчанию — OFF.
ON
Процент свободного места, определяемый параметром FILLFACTOR, применяется к страницам индекса промежуточного уровня. Если FILLFACTOR не указан и одновременно PAD_INDEX установлен в состояние ON, то используется значение коэффициента заполнения, хранимое в таблице sys.indexes.
OFF или fillfactor не указан
Страницы промежуточного уровня заполняются почти полностью. При этом остается достаточно места по крайней мере для одной строки максимального размера, которого может достигать индекс, в зависимости от набора ключей в промежуточных страницах.
Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL). [/stextbox]

[stextbox id=»info» caption=»STATISTICS_NORECOMPUTE «]Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.
ON
Устаревшие статистики не пересчитываются автоматически.
OFF
Автоматическое обновление статистических данных включено.
Чтобы восстановить автоматическое обновление статистики, следует установить STATISTICS_NORECOMPUTE в значение OFF или выполнить UPDATE STATISTICS без предложения NORECOMPUTE. [/stextbox]

[stextbox id=»info» caption=» ALLOW_ROW_LOCKS «]Когда присвоены значения ALLOW_ROW_LOCKS = ON и ALLOW_PAGE_LOCK = ON, при доступе к индексу допустимы блокировки на уровне строк, уровне страниц и уровне таблиц. Компонент Database Engine выберет соответствующую блокировку и может повысить уровень блокировки с уровня строки или таблицы до уровня страницы.
Если присвоены значения ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCK = OFF, при доступе к индексу допустима только блокировка на уровне таблиц. Дополнительные сведения о настройке гранулярности блокировки индекса см. в разделе Настройка блокировки индекса. [/stextbox]

[stextbox id=»info» caption=»ALLOW_PAGE_LOCKS»]Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.
ON
Блокировки страниц допустимы при доступе к индексу. Необходимость в блокировке страниц определяет компонент Database Engine.
OFF
Блокировки страниц не используются.
ПримечаниеПримечание
Индекс не может быть реорганизован, если ALLOW_PAGE_LOCKS установлен в состояние OFF.[/stextbox]

[stextbox id=»info» caption=»SORT_IN_TEMPDB»]Указывает, следует ли сохранять результаты сортировки в базе данных tempdb. Значение по умолчанию — OFF.
ON
Промежуточные результаты сортировки, которые используются при индексировании, хранятся в базе данных tempdb. Это может сократить время, требуемое для создания индекса, если база данных tempdb размещена на иных дисках, нежели пользовательская база данных. Однако это увеличивает использование места на диске, которое используется при индексировании.
OFF
Промежуточные результаты сортировки хранятся в той же базе данных, где и индекс.
Если выполнение сортировки не требуется или если сортировка может быть выполнена в памяти, параметр SORT_IN_TEMPDB пропускается. [/stextbox]

[stextbox id=»info» caption=»ONLINE «]Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.
Для XML-индекса или пространственного индекса поддерживается только значение ONLINE = OFF; при ONLINE = ON возникает ошибка.
ПримечаниеПримечание
Операции с индексами в сети доступны только в выпусках SQL Server Enterprise, Developer и Evaluation.
ON
Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Это позволяет продолжить выполнение запросов или обновлений для базовых таблиц и индексов. В начале операции совмещаемая блокировка (S) исходного объекта поддерживается в течение очень короткого времени. Если создается некластеризованный индекс, то по завершении операции на короткое время создается блокировка типа S (совмещаемая) для источника. Блокировка типа SCH-M (изменения схемы) запрашивается, если кластеризованный индекс создается или удаляется в режиме в сети либо, происходит перестроение кластеризованного или некластеризованного индекса. При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON.
OFF
Блокировки таблиц применяются во время выполнения операций с индексами. Операция с индексами в режиме «вне сети», которая создает, перестраивает или удаляет кластеризованный, пространственный или XML-индекс либо перестраивает или удаляет некластеризованный индекс, получает блокировку изменения схемы (Sch-M) для этой таблицы. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT. [/stextbox]

SELECT  IndStat.database_id,
                IndStat.object_id,
                QUOTENAME(s.name) + '.' + QUOTENAME(o.name) AS [object_name],
                IndStat.index_id,
                QUOTENAME(i.name) AS index_name,
                IndStat.avg_fragmentation_in_percent,
                IndStat.partition_number,
                (SELECT count (*) FROM sys.partitions p
                        WHERE p.object_id = IndStat.object_id AND p.index_id = IndStat.index_id) AS partition_count
FROM sys.dm_db_index_physical_stats
    (DB_ID('ax_molniya'), OBJECT_ID(NULL), NULL, NULL , 'LIMITED') AS IndStat
        INNER JOIN sys.objects AS o ON (IndStat.object_id = o.object_id)
        INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
        INNER JOIN sys.indexes i ON (i.object_id = IndStat.object_id AND i.index_id = IndStat.index_id)
WHERE IndStat.avg_fragmentation_in_percent > 10 AND IndStat.index_id > 0

 

Первым делом создадим табличку куда будет собираться статистика.

 CREATE TABLE kmosk.dbo.reindeks_SALESLINE
  ( [database_id] VARCHAR(max)
      ,[object_id] VARCHAR(max)
      ,[index_id] VARCHAR(max)
      ,[partition_number] VARCHAR(max)
      ,[index_type_desc] VARCHAR(max) 
      ,[alloc_unit_type_desc] VARCHAR(max) 
      ,[index_depth] VARCHAR(max) 
      ,[index_level] VARCHAR(max) 
      ,[avg_fragmentation_in_percent] VARCHAR(max) 
      ,[fragment_count] VARCHAR(max) 
      ,[avg_fragment_size_in_pages] VARCHAR(max) 
      ,[page_count] VARCHAR(max) 
      ,[avg_page_space_used_in_percent] VARCHAR(max) 
      ,[record_count] VARCHAR(max) 
      ,[ghost_record_count] VARCHAR(max) 
      ,[version_ghost_record_count] VARCHAR(max) 
      ,[min_record_size_in_bytes] VARCHAR(max) 
      ,[max_record_size_in_bytes] VARCHAR(max) 
      ,[avg_record_size_in_bytes] VARCHAR(max) 
      ,[forwarded_record_count] VARCHAR(max)
    , vreme datetime NOT NULL DEFAULT GETDATE())
   
       

После этого создадим задание которое будет наполнять табличьку

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'ax_molniya');
SET @object_id = OBJECT_ID(N'ax_molniya.[dbo].[SALESLINE]');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;

 
   insert INTO kmosk.dbo.reindeks_SALESLINE
   ([database_id]
      ,[object_id]
      ,[index_id]
      ,[partition_number]
      ,[index_type_desc]
      ,[alloc_unit_type_desc]
      ,[index_depth]
      ,[index_level]
      ,[avg_fragmentation_in_percent]
      ,[fragment_count]
      ,[avg_fragment_size_in_pages]
      ,[page_count]
      ,[avg_page_space_used_in_percent]
      ,[record_count]
      ,[ghost_record_count]
      ,[version_ghost_record_count]
      ,[min_record_size_in_bytes]
      ,[max_record_size_in_bytes]
      ,[avg_record_size_in_bytes]
      ,[forwarded_record_count])
     
   SELECT 
   [database_id]
      ,[object_id]
      ,[index_id]
      ,[partition_number]
      ,[index_type_desc]
      ,[alloc_unit_type_desc]
      ,[index_depth]
      ,[index_level]
      ,[avg_fragmentation_in_percent]
      ,[fragment_count]
      ,[avg_fragment_size_in_pages]
      ,[page_count]
      ,[avg_page_space_used_in_percent]
      ,[record_count]
      ,[ghost_record_count]
      ,[version_ghost_record_count]
      ,[min_record_size_in_bytes]
      ,[max_record_size_in_bytes]
      ,[avg_record_size_in_bytes]
      ,[forwarded_record_count]
      
    FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED')
    END;
    
    
 --INSERT INTO    kmosk.dbo.test2 (DATA)
  --VALUES (GETDATE());


Задание будет наполнять табличку в 8 утра и 8 вечера