select dc.session_id as [SPID],
dc.client_net_address as [IP клиента],
sp.hostname as [Имя PC клиента],
dc.local_net_address as [IP подключения к серверу],
dc.net_transport as [Протокол подключения],
dc.local_tcp_port as [Порт],
sp.[program_name] as [Имя программы],
sp.loginame
FROM sys.dm_exec_connections dc
inner join master.sys.sysprocesses sp
on dc.session_id=sp.spid
where sp.spid > 50

 

select 
	creation_time,
	last_execution_time,
	execution_count,
	total_worker_time/1000 as CPU,
	convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
	qs.total_elapsed_time/1000 as TotDuration,
	convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
	total_logical_reads as [Reads],
	total_logical_writes as [Writes],
	total_logical_reads+total_logical_writes as [AggIO],
	convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0))as [AvgIO],
	case 
		when sql_handle IS NULL then ' '
		else(substring(st.text,(qs.statement_start_offset+2)/2,(
			case
				when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
				else qs.statement_end_offset    
			end - qs.statement_start_offset)/2  ))
	end as query_text,
	db_name(st.dbid)as database_name,
	object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name,
	qp.query_plan
	
from sys.dm_exec_query_stats  qs

cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
--WHERE 
--creation_time > '2018-12-14 08:00:00.000'
--object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid)  =  'dbo.ChequeExciseInsert' 


ORDER BY creation_time DESC

 

SELECT 		
			DB_NAME(a.database_id)          AS [Database Name]
           --,a.FILE_ID
           ,i.name
           ,a.io_stall_read_ms
           ,a.num_of_reads
           ,CAST(a.io_stall_read_ms / (1.0 + a.num_of_reads) AS NUMERIC(10 ,1)) AS [avg_read_stall_ms]
           ,a.io_stall_write_ms
           ,a.num_of_writes
           ,CAST(
                a.io_stall_write_ms / (1.0 + a.num_of_writes) AS NUMERIC(10 ,1)
            )                             AS [a.avg_write_stall_ms]
           ,a.io_stall_read_ms + a.io_stall_write_ms AS [io_stalls]
           ,a.num_of_reads + a.num_of_writes  AS [total_io]
           ,CAST(
                (a.io_stall_read_ms + a.io_stall_write_ms) / (1.0 + a.num_of_reads + a.num_of_writes) AS NUMERIC(10 ,1)
            )                             AS [avg_io_stall_ms],
            i.physical_name          
FROM   
			sys.dm_io_virtual_file_stats(NULL ,NULL)a
			INNER JOIN 
	   		sys.master_files i
            ON  a.file_id = i.file_id 
            AND            a.database_id = i.database_id
--WHERE i.database_id in  ('7')
			
ORDER BY
       		avg_io_stall_ms                  DESC;

 

Задача делать реиндекс только определенных таблиц.

Создаём таблицу

USE [ax_molniya]
GO

/****** Object:  Table [dbo].[www_reindex_tablename]    Script Date: 08/17/2018 08:45:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[www_reindex_tablename](
	[objectname] [nvarchar](128) NULL,
	[group] [nvarchar](1) NULL,
	[id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO


Наполняем её таблицами которые нужно реиндексировать

Делаем хранимую процедуру

USE [ax_molniya]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[www_reindex_ALTER]
-- Add the parameters for the stored procedure here

-- если readonly = 0 (значение по- умолчанию), тогда выполняется вся работа скрипта)
-- если readonly = 1, тогда предоставится информация о состоянии индексов на таблицу и реиндекс не будет выполнен
@readonly bit = 0,

-- защита от случайного запуска
@password nvarchar(max) = '',

-- тихий режим
-- если @displayoff = 0 (значение по- умолчанию), тогда прогрес выполнения скрипта дублируется текстовыми сообщениями")
-- если @displayoff = 1, тогда прогресс выполнения скрипта не дублируется(например если реиндекс инициализирован джобом,то нет необходимости показывать текстовые сообщения)
@displayoff bit = 0,

-- имя базы данных
@dbname nvarchar(64),
-- имя таблицы
@tablename nvarchar(128),

-- уведомление по email
-- если 0 - выключено, 1 - включено
@emailalert bit = 1


AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;
  -- дата выполнения реиндекс в формате ггггммдд
  DECLARE @wjobday nvarchar(8)
  -- hash password
  DECLARE @adminhash varbinary
  DECLARE @hashpassword varbinary
  -- ключ для выборки данных
  DECLARE @seckey nvarchar(12)
  -- текущее время чч:мм
  DECLARE @b nvarchar(5)
  -- два правых символа от @b
  DECLARE @c nvarchar(2)
  -- два левых символа от @b
  DECLARE @d nvarchar(2)

  -- флаг выполнения задания
  DECLARE @flag bit
  DECLARE @email nvarchar(512)
  DECLARE @at nvarchar(500);
  DECLARE @createindexstart nvarchar(128)
  DECLARE @createindexerror nvarchar(128)
  DECLARE @createindexstop nvarchar(128)
  DECLARE @createindexnone nvarchar(128)
  DECLARE @dropindex nvarchar(128)
  DECLARE @dropindexerror nvarchar(128)
  DECLARE @indexname nvarchar(256)
  DECLARE @createindexcommand nvarchar(max)
  DECLARE @dropindexcommand nvarchar(max)
  DECLARE @errmes nvarchar(512)
  DECLARE @wrongpassalert nvarchar(256)
  -- ALTER переменные
  DECLARE @re_objectid int;
  DECLARE @re_indexid int;
  DECLARE @re_partitioncount bigint;
  DECLARE @re_schemaname nvarchar(130);
  DECLARE @re_objectname nvarchar(130);
  DECLARE @re_indexname nvarchar(130);
  DECLARE @re_partitionnum bigint;
  DECLARE @re_partitions bigint;
  DECLARE @re_frag float;
  DECLARE @re_command nvarchar(4000);
  
  -- получаем соль пароля
  SET @password = CONVERT(nvarchar, @password);
  SET @hashpassword = (SELECT
    HASHBYTES('MD5', @password));
  -- обнуляем флаг
  SET @flag = 0

  -- текущая дата
  SET @wjobday = CONVERT(nvarchar(8), GETDATE(), 112)
  SET @b = (SELECT
    CONVERT(nvarchar(5), GETDATE(), 108))
  SET @c = RIGHT(@b, 2)
  SET @d = LEFT(@b, 2)
  -- получаем значение seckey
  SET @seckey = @wjobday + @d + @c
  

  /*
  
  --------------------#CONFIG---------------------------
  секция с настройками скрипта, необходимо обязательно
  заполнить(проверить) перед запуском
  
  */
  -- имя индекса
  SET @indexname = 'temp_index'
  -- соль пароля на запуск скрипта,НЕ ТРОГАТЬ если не знаешь,что это
  SET @adminhash = 0x385B43E3833EA9639158D535A765196D
  -- имя задания
  --set @jobname = ''
  -- email для уведомления
  SET @email = 'event.messages@ctmol.ru;'
  --SET @email = 'event.messages@ctmol.ru;'
  --SET @email = 'it.sysadmin.dba1@sparural.ru'

  /*
  
   -------------------#END CONFIG------------------------
  
  */

  --------------------------------------------------------------------
  SET @createindexstart = 'Реиндекс на таблице dbo.' + @tablename + ' начат '
  SET @createindexerror = '======ОШИБКА====== еиндекс на таблице на таблице ' + @tablename + ' ======ОШИБКА====== '
  SET @createindexstop = 'Реиндекс на таблице на таблице dbo.' + @tablename + ' завершено '
  SET @createindexnone = 'Все индексы на таблице '+ @tablename + ' в норме,перестраивать не нужно'
  SET @dropindex = 'Удаление временного кластеризованного индекса на таблице dbo.' + @tablename + ' выполнено '
  SET @dropindexerror = '======ОШИБКА====== Удаление временного кластеризованного индекса на таблице dbo.' + @tablename + ' ======ОШИБКА====== '
  SET @createindexcommand = 'CREATE CLUSTERED INDEX [' + @indexname + '] ON [dbo].[' + @tablename + ']([RECID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);'
  --set @createindexcommand = 'ALTER TABLE [dbo].['+@tablename+'] ADD CONSTRAINT ['+@indexname+'] PRIMARY KEY CLUSTERED ([DATAAREAID] ASC,[PRODID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
  SET @dropindexcommand = 'DROP INDEX [' + @indexname + '] ON [dbo].[' + @tablename + '] WITH (ONLINE = ON);'
  --set @dropindexcommand ='ALTER TABLE [dbo].['+@tablename+'] DROP CONSTRAINT ['+@indexname+']'
  ---------------------------------------------------------------------

  -- проверка входных параметров
  IF ((SELECT
      COUNT(name)
    FROM sys.databases
    WHERE [name] = @dbname)
    = 0)
  BEGIN
IF (@displayoff = 0)
    BEGIN
PRINT 'указанной бд [' + @dbname + '] на сервере не существует, проверь секцию #CONFIG в процедуре [www_reindex_' + @tablename + ']'
PRINT ' '
END

    SELECT
        @ErrorMessage = 'указанной бд [' + @dbname + '] на сервере не существует, проверь секцию #CONFIG в процедуре [www_reindex_' + @tablename + ']',
        @ErrorSeverity = 16,
        @ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT

  END
  ELSE
  BEGIN
    IF (@hashpassword <> @adminhash)
    BEGIN
      IF (@displayoff = 0)
      BEGIN
        PRINT '--------------------------------------------------'
        PRINT ''
        PRINT 'ОШИБКА - введен не правильный пароль - ОШИБКА'
        PRINT ''
        PRINT '--------------------------------------------------'
      END
      SET @wrongpassalert = 'ОШИБКА - введен не правильный пароль - ОШИБКА, пользователь ' + SUSER_SNAME()
      EXEC www_reindex_stat_log @objectname = @tablename,
                                @description = @wrongpassalert,
                                @jobday = @wjobday,
                                @seckey = @seckey

SELECT
        @ErrorMessage = @wrongpassalert,
        @ErrorSeverity = 16,
        @ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT
    END
    ELSE
    BEGIN
      IF (@readonly = 1)
      BEGIN
        IF (@displayoff = 0)
        BEGIN
          PRINT 'Дата проведения операции ' + @wjobday
          PRINT '--------------------------------------------------'
          PRINT ''
          PRINT 'Имя базы данных-> ' + @dbname
          PRINT 'Имя таблицы ->' + @tablename
          PRINT ''
          PRINT '--------------------------------------------------'
        END
        -- смотрим статистику по индексам таблицы
        EXEC www_reindex_stat_index_all_log @objectname = @tablename,
                                            @dbname = @dbname,
                                            @readonly = @readonly

      END
      ELSE
      BEGIN
        IF (@displayoff = 0)
        BEGIN
          PRINT 'Дата проведения реиндекса ' + @wjobday
          PRINT '--------------------------------------------------'
          PRINT ''
          PRINT 'Имя базы данных-> ' + @dbname
          PRINT 'Имя таблицы ->' + @tablename
          PRINT ''
          PRINT '--------------------------------------------------'
        END
      /*
      
      шаг 1
      
      */
      BEGIN TRY
        -----------------------------------------------------
        -- смотрим состояние индексов до
        EXEC www_reindex_stat_index_all_log @objectname = @tablename,
                                            @jobday = @wjobday,
                                            @status = 'before',
                                            @dbname = @dbname,
                                            @seckey = @seckey
        IF (@displayoff = 0)
        BEGIN
          PRINT @createindexstart + CAST(GETDATE() AS varchar);
          RAISERROR ('', 0, 1) WITH NOWAIT
        END
        -- пишем в лог, о начале перестройки индексов на таблице
        EXEC www_reindex_stat_log @objectname = @tablename,
                                  @description = @createindexstart,
                                  @jobday = @wjobday,
                                  @seckey = @seckey

      END TRY
      BEGIN CATCH
        SELECT
          ERROR_NUMBER() AS ErrorNumber,
          ERROR_SEVERITY() AS ErrorSeverity,
          ERROR_STATE() AS ErrorState,
          ERROR_PROCEDURE() AS ErrorProcedure,
          ERROR_LINE() AS ErrorLine,
          ERROR_MESSAGE() AS ErrorMessage,
          'шаг 1 ошибка вставки данных в таблицы лога';

        SET @errmes = 'шаг 1 ошибка вставки данных в таблицы лога - ' + ERROR_MESSAGE()
        EXEC www_reindex_stat_log @objectname = @tablename,
                                  @description = @errmes,
                                  @jobday = @wjobday,
                                  @seckey = @seckey

SELECT
        @ErrorMessage = @errmes,
        @ErrorSeverity = 16,
        @ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT

      END CATCH
      -----------------------------------------------------

      /*
      
      шаг 1 конец
      
      */

      /*
      
      шаг 2
      
      */

      /*------------перестройка индекса начало--------------*/
      BEGIN TRY
      
      
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(@dbname),OBJECT_ID(@tablename), NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

if ((select count(*) from #work_to_do) = 0 )
BEGIN
IF (@displayoff = 0)
BEGIN
print @createindexnone + CAST(GETDATE() AS varchar);
RAISERROR ('', 0, 1) WITH NOWAIT
END

-- пишем в лог
EXEC www_reindex_stat_log @objectname = @tablename,
                                  @description = @createindexnone,
                                  @jobday = @wjobday,
                                  @seckey = @seckey

END
-- 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 @re_objectid, @re_indexid, @re_partitionnum, @re_frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @re_objectname = QUOTENAME(o.name), @re_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 = @re_objectid;
SELECT @re_indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @re_objectid AND index_id = @re_indexid;
SELECT @re_partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @re_objectid AND index_id = @re_indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @re_frag < 30.0
SET @re_command = N'ALTER INDEX ' + @re_indexname + N' ON ' + @re_schemaname + N'.' + @re_objectname + N' REORGANIZE';
IF @re_frag >= 30.0
SET @re_command = N'ALTER INDEX ' + @re_indexname + N' ON ' + @re_schemaname + N'.' + @re_objectname + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON)';
IF @re_partitioncount > 1
SET @re_command = @re_command + N' PARTITION=' + CAST(@re_partitionnum AS nvarchar(10));
EXEC (@re_command);
IF (@displayoff = 0)
BEGIN
PRINT N'Executed: ' + @re_command +CAST(GETDATE() AS varchar);
RAISERROR ('', 0, 1) WITH NOWAIT
END

END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
      
      
 
    -- EXEC sp_executesql @createindexcommand
        
        
        
        
        -- пишем в лог
        EXEC www_reindex_stat_log @objectname = @tablename,
                                  @description = @createindexstop,
                                  @jobday = @wjobday,
                                  @seckey = @seckey
        IF (@displayoff = 0)
        BEGIN
          -- выводим на экран
          PRINT @createindexstop + CAST(GETDATE() AS varchar);
          RAISERROR ('', 0, 1) WITH NOWAIT
        END
        --ставим флаг
        SET @flag = 1
      END TRY
      BEGIN CATCH
        SELECT
          ERROR_NUMBER() AS ErrorNumber,
          ERROR_SEVERITY() AS ErrorSeverity,
          ERROR_STATE() AS ErrorState,
          ERROR_PROCEDURE() AS ErrorProcedure,
          ERROR_LINE() AS ErrorLine,
          ERROR_MESSAGE() AS ErrorMessage,
          'ошибка при создании индекса';

        SET @errmes = 'ошибка при создании индекса- ' + ERROR_MESSAGE()
        EXEC www_reindex_stat_log @objectname = @tablename,
                                  @description = @errmes,
                                  @jobday = @wjobday,
                                  @seckey = @seckey
SELECT
        @ErrorMessage = @errmes,
        @ErrorSeverity = 16,
        @ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT

        IF (@displayoff = 0)
        BEGIN
          -- выводим на экран, если не получилось
          PRINT @createindexerror + CAST(GETDATE() AS varchar);
        END
        -- ставим флаг
        SET @flag = 0
      END CATCH
        /*------------перестройка индекса конец--------------*/

/*

шаг 4

        */
        -- если предыдущее действие завершилось успешно,то делаем следующий шаг
        IF (@flag = 1)
        BEGIN

        -- пишем инфу о индексах после проведения операции
        BEGIN TRY
          EXEC www_reindex_stat_index_all_log @objectname = @tablename,
                                              @jobday = @wjobday,
                                              @status = 'after',
                                              @dbname = @dbname,
                                              @seckey = @seckey

          SET @flag = 1
        END TRY
        BEGIN CATCH
          SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage,
            'шаг 4 ошибка вставки данных в таблицы лога';

          SET @errmes = 'шаг 4 ошибка вставки данных в таблицы лога - ' + ERROR_MESSAGE()
          EXEC www_reindex_stat_log @objectname = @tablename,
                                    @description = @errmes,
                                    @jobday = @wjobday,
                                    @seckey = @seckey


SELECT
        @ErrorMessage = @errmes,
        @ErrorSeverity = 16,
        @ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT

          SET @flag = 0
        END CATCH

        END

        IF (@displayoff = 0)
        BEGIN
          IF (@flag = 1)
          BEGIN
            SELECT
              t1.[index_id],
              t1.[index_type_desc],
              t1.[avg_fragmentation_in_percent],
              t2.[avg_fragmentation_in_percent],
              t1.[avg_fragmentation_in_percent] - t2.[avg_fragmentation_in_percent] AS [profit]
            FROM [dbo].[www_reindex_stat_index_all] t1
            FULL OUTER JOIN [www_reindex_stat_index_all] t2
              ON t1.index_id = t2.index_id
            WHERE t1.objectname = @tablename
            AND t1.seckey = @seckey
            AND t1.status = 'before'
            AND t2.status = 'after'
            AND t2.objectname = @tablename
            AND t2.seckey = @seckey

          END
        END

        IF (@flag = 1)
        BEGIN
BEGIN TRY
SET @at = 'задание по реиндексу выполнено ' + @tablename + ' '+CAST(GETDATE() AS varchar);

if (@emailalert = 1)
EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'atrans',
@recipients = @email,
@subject = @tablename,
@body = @at

END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage,
'ошибка отправки email';


SELECT
@ErrorMessage = 'ошибка отправки email',
@ErrorSeverity = 16,
@ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT
END CATCH

        END
        ELSE
        BEGIN
BEGIN TRY
SET @at = 'Ошибка - задание по реиндексу ' + @tablename + CAST(GETDATE() AS varchar);
if (@emailalert = 1)
EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'atrans',
@recipients = @email,
@subject = @tablename,
@body = @at
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage,
'ошибка отправки email';

SELECT
@ErrorMessage = 'ошибка отправки email',
@ErrorSeverity = 16,
@ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT

END CATCH
        END

      /*
      
      шаг 4 конец
      
      */

      -- конец бегина с параметром ридонли
      END;
    -- конец бегина с проверкой пароля
    END;
  -- конец первого бегина
  END;

-- конец процедуры
END

Запуск хранимой процедуры

DECLARE @objectname nvarchar(130);
DECLARE partitions1 CURSOR FOR SELECT objectname FROM www_reindex_tablename where [group] = 'a'
-- Open the cursor.
OPEN partitions1;
-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions1
           INTO @objectname
        IF @@FETCH_STATUS < 0 BREAK;
 
        --print @objectname
 
        exec www_reindex_alter
		@dbname = 'ax_molniya',
		@tablename =  @objectname,
		@password='день победы',
		@emailalert = 1,
		@displayoff = 1,
		@readonly = 0      
 
         END;
-- Close and deallocate the cursor.
CLOSE partitions1;
DEALLOCATE partitions1;

Хранимая процедура для записи лога

USE [ax_molniya]
GO

/****** Object:  StoredProcedure [dbo].[www_reindex_stat_index_all_log]    Script Date: 08/17/2018 09:02:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[www_reindex_stat_index_all_log]
-- Add the parameters for the stored procedure here
@objectname nvarchar(128)
,@jobday nvarchar(8) = ''
,@status nvarchar(10) = 'before'
,@dbname nvarchar(64)
,@seckey nvarchar(12) = ''
,@readonly bit = 0

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF(@readonly = 0)
BEGIN
insert into www_reindex_stat_index_all(
[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] ,
[jobday],
[objectname],
[status],
[seckey]
)
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] ,
@jobday as [jobday],
@objectname as [objectname],
@status as [status],
@seckey as [seckey]
FROM sys.dm_db_index_physical_stats(DB_ID(@dbname), OBJECT_ID(@objectname), NULL, NULL , 'LIMITED');
END
ELSE
BEGIN
SELECT
[database_id],
@objectname as [objectname],
[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(@dbname), OBJECT_ID(@objectname), NULL, NULL , 'LIMITED');
END


    -- Insert statements for procedure here

 
END

GO

Таблица для записи лога

USE [ax_molniya]
GO

/****** Object:  Table [dbo].[www_reindex_stat_index_all]    Script Date: 08/17/2018 09:04:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[www_reindex_stat_index_all](
	[database_id] [smallint] NULL,
	[object_id] [int] NULL,
	[index_id] [int] NULL,
	[partition_number] [int] NULL,
	[index_type_desc] [nvarchar](60) NULL,
	[alloc_unit_type_desc] [nvarchar](60) NULL,
	[index_depth] [tinyint] NULL,
	[index_level] [tinyint] NULL,
	[avg_fragmentation_in_percent] [float] NULL,
	[fragment_count] [bigint] NULL,
	[avg_fragment_size_in_pages] [float] NULL,
	[page_count] [bigint] NULL,
	[avg_page_space_used_in_percent] [float] NULL,
	[record_count] [bigint] NULL,
	[ghost_record_count] [bigint] NULL,
	[version_ghost_record_count] [bigint] NULL,
	[min_record_size_in_bytes] [int] NULL,
	[max_record_size_in_bytes] [int] NULL,
	[avg_record_size_in_bytes] [float] NULL,
	[forwarded_record_count] [bigint] NULL,
	[createdate] [datetime] NOT NULL,
	[createdate_unix] [int] NOT NULL,
	[jobday] [nvarchar](8) NOT NULL,
	[objectname] [nvarchar](128) NULL,
	[status] [nvarchar](10) NULL,
	[seckey] [nvarchar](12) NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[www_reindex_stat_index_all] ADD  DEFAULT (getdate()) FOR [createdate]
GO

ALTER TABLE [dbo].[www_reindex_stat_index_all] ADD  DEFAULT (datediff(second,'1970-01-01 00:00:00',getutcdate())) FOR [createdate_unix]
GO


 

 

use msdb
go
SELECT 
    [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
  , [sJOB].[description] AS [JobDescription]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
  , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
       , CASE
          WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
          ELSE 'Yes'
          END AS [IsScheduled]
    , CASE 
        WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
        WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
        WHEN [freq_type] = 1 THEN 'One Time'
      END [ScheduleType]
    , CASE [freq_type]
        WHEN 1 THEN 'One Time'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN 128 THEN 'Start whenever the CPUs become idle'
      END [Occurrence]
    , CASE [freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                    + ' week(s) on '
                    + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
                    + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
                    + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
                    + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
                    + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) 
                     + ' of every '
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        WHEN 32 THEN 'Occurs on '
                     + CASE [freq_relative_interval]
                        WHEN 1 THEN 'First'
                        WHEN 2 THEN 'Second'
                        WHEN 4 THEN 'Third'
                        WHEN 8 THEN 'Fourth'
                        WHEN 16 THEN 'Last'
                       END
                     + ' ' 
                     + CASE [freq_interval]
                        WHEN 1 THEN 'Sunday'
                        WHEN 2 THEN 'Monday'
                        WHEN 3 THEN 'Tuesday'
                        WHEN 4 THEN 'Wednesday'
                        WHEN 5 THEN 'Thursday'
                        WHEN 6 THEN 'Friday'
                        WHEN 7 THEN 'Saturday'
                        WHEN 8 THEN 'Day'
                        WHEN 9 THEN 'Weekday'
                        WHEN 10 THEN 'Weekend day'
                       END
                     + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                     + ' month(s)'
      END AS [Recurrence]
    , CASE [freq_subday_type]
        WHEN 1 THEN 'Occurs once at ' 
                    + STUFF(
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 2 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 4 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 8 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
      END [Frequency]

    , [sSCH].[name] AS [JobScheduleName]
    --,[sJSTP].database_name
   , Last_Run = CONVERT(DATETIME, RTRIM(run_date) + ' '
        + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),
        ' ','0'),3,0,':'),6,0,':'))
  , case [sJSTP].Last_run_outcome
          When 0 then 'Failed'
          when 1 then 'Succeeded'
          When 2 then 'Retry'
          When 3 then 'Canceled'
          When 5 then 'Unknown'
   End as Last_Run_Status

  ,Last_Run_Duration_HHMMSS = STUFF(STUFF(REPLACE(STR([sJSTP].last_run_duration,7,0),
        ' ','0'),4,0,':'),7,0,':')
    , Max_Duration = STUFF(STUFF(REPLACE(STR(l.run_duration,7,0),
        ' ','0'),4,0,':'),7,0,':')
  , Next_Run= CONVERT(DATETIME, RTRIM(NULLIF([sJOBSCH].next_run_date, 0)) + ' '
        + STUFF(STUFF(REPLACE(STR(RTRIM([sJOBSCH].next_run_time),6,0),
        ' ','0'),3,0,':'),6,0,':'))
    , CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
      END AS [JobDeletionCriterion]

    , [sSVR].[name] AS [OriginatingServerName]
    ,[sJSTP].subsystem
    ,[sJSTP].command
  ,h.message

FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
        ON [sJOB].[originating_server_id] = [sSVR].[server_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
        ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        ON [sJOB].[job_id] = [sJSTP].[job_id]
        AND [sJOB].[start_step_id] = [sJSTP].[step_id]
    LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
        ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
        ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]

        left JOIN
    (
        SELECT job_id, instance_id = MAX(instance_id),max(run_duration) AS run_duration
            FROM msdb.dbo.sysjobhistory
            GROUP BY job_id
    ) AS l
    ON sJOB.job_id = l.job_id
left JOIN
    msdb.dbo.sysjobhistory AS h
    ON h.job_id = l.job_id
    AND h.instance_id = l.instance_id
ORDER BY [JobName]

Ниже приведено краткое описание каждого из полей, возвращаемых из указанного выше запроса:

  • [JobName]: имя задания агента SQL Server.
  • [JobOwner]: владелец задания.
  • [JobCategory]: категория, к которой относится задание, например моментальный снимок репликации, обслуживание базы данных, отправка журналов и т.д.
  • [JobDescription]: описание задания.
  • [JobStartStepNo]: номер шага, из которого задано задание для запуска. SQL Server позволяет нам выполнять несколько шагов в рамках задания и задания может начинаться с того, что пользователь хочет, чтобы он начинал с.
  • [JobStartStepName]: имя шага, с которого задание задано начать.
  • [JobCreatedOn]: дата и время создания задания.
  • [JobLastModifiedOn]: дата и время последнего изменения задания.
  • [IsEnabled]: индикатор, показывающий, включено ли задание или отключен.
  • [IsScheduled]: индикатор, указывающий, запланировано ли задание  или нет. Работы могут быть запланированы для запуска в указанный день (ы)  в указанное время или может быть вызван с помощью кода типа T-SQL и т.д.
  • [ScheduleType]: тип расписания.
  • [Возникновение]: появление графика, такого как Daily, Weekly,  Ежемесячно и т.д.
  • [Повторение]: повторение графика, например, определенного дня,  Специальные дни недели, количество недель и т.д.
  • [Частота]: как часто задание должно выполняться в день (ы), когда  он должен запускаться, например: Происходит только один раз в запланированном  день (ы), Происходит каждые 2 часа в назначенный день и т.д. между  указанное время начала и окончания.
  • [JobScheduleName]: имя расписания, связанного с заданием.  SQL Server позволяет нам связывать несколько расписаний с одним заданием,  в этом случае вышеупомянутый запрос возвращает одну строку для каждого  график, связанный с каждым заданием.
  • [Last_Run]: дата и время выполнения задания в последний раз  (соответствует самому последнему запуску).
  • [Last_Run_Status]: состояние или результат последнего запуска задания.
  • [Last_Run_Duration_HHMMSS]: Продолжительность последнего прогона, представленного в  Часы: Минуты: Формат секунд.
  • [Max_Duration]: максимальная продолжительность задания была выполнена в  Часы: Минуты: Формат секунд.
  • [Next_Run]: дата и время выполнения задания в следующий раз. Эта  информация доступна только для заданий, которые запланированы (a  расписание связано с заданием).
  • [JobDeletionCriterion]: критерий для удаления задания. SQL  Агент сервера имеет функцию, которая позволяет нам удалять/удалять задание  основанный на определенном критерии, так что нет необходимости вручную  удалять/очищать задания.
  • [OriginatingServerName]: сервер, с которого выполнялось задание.
  • [Подсистема]: тип операции, например, интеграция с SQL Server  Пакет услуг, Transact-SQL Script (T-SQL), ActiveX Script и т.д.
  • [Команда]: фактическая команда, которая будет выполнена  подсистема.
  • [сообщение]: информация о успехе/неудаче работы и т.д.
IF (SELECT COUNT(*)
FROM dbo.sysjobs AS s 
INNER JOIN sysjobsteps AS s2 (nolock) ON s2.job_id = s.job_id
INNER JOIN dbo.sysjobhistory sh (nolock) ON sh.instance_id=(SELECT MAX(sjh.instance_id) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=s2.step_id) 
			  AND sh.run_date>=(SELECT MAX(sjh.run_date) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=0)
WHERE s.[enabled]=1 AND sh.run_status=0)>0
BEGIN
DECLARE @tableHTML AS NVARCHAR(max)

SET @tableHTML =
    N'<H1>Ошибки в джобах на sql сервере '+@@SERVERNAME+'</H1>' +
    N'<table border="1">' +
    N'<tr><th>Джоб</th><th>ID шага</th>' +
    N'<th>Имя шага</th><th>команда</th><th>база</th>' +
    N'<th>последний запуск</th><th>ошибка</th></tr>' +
    CAST ( ( SELECT td = s.name,'', td = s2.step_id,'', td = s2.step_name, '',td = cast(s2.command AS VARCHAR(100)),'', td = s2.database_name,'',
       td = s2.last_run_date,'', td = cast(sh.[message] AS VARCHAR(100))
FROM sysjobs AS s (nolock)
INNER JOIN sysjobsteps AS s2 (nolock) ON s2.job_id = s.job_id
INNER JOIN dbo.sysjobhistory sh (nolock) ON sh.instance_id=(SELECT MAX(sjh.instance_id) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=s2.step_id) 
			  AND sh.run_date>=(SELECT MAX(sjh.run_date) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=0)
WHERE s.[enabled]=1 AND sh.run_status=0
--ORDER BY s.name, s2.step_id
FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;


DECLARE @subject AS NVARCHAR(max)
SET @subject='Ошибка в работе джобов на сервере '+@@SERVERNAME

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'atrans'
,@recipients = 'mail'
,@subject = @subject
,@importance ='Normal'
,@body =  @tableHTML
,@body_format = 'HTML' 






END

 

sELECT TOP 20
	qs.last_execution_time AS Last_execution_time,
	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) AS Query_text, 
	qp.query_plan AS Query_plan,
	qs.execution_count AS Execution_count
FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
	CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
--WHERE 
WHERE
/* datepart(YEAR,qs.last_execution_time) BETWEEN 2018 AND 2018 and
		datepart(month,qs.last_execution_time) BETWEEN 06 AND 06 and
		datepart(day,qs.last_execution_time) BETWEEN 20 AND 22
	
		 --AND		CONVERT(CHAR(8),last_execution_time,8) BETWEEN '09:00:00' and '23:50:00' */


qs.last_execution_time > '2018-05-20 11:30:00.000' /* 1. Date & Time filter */
	--and qt.text like '%ChequeHead%'	/* 2. SQL query text filter */
	and qt.text not like '%Query Finder%' /* 3. Special condition */
ORDER BY qs.last_execution_time DESC
DBCC FREEPROCCACHE WITH NO_INFOMSGS

 

declare @dt as datetime
set @dt= getdate()-01 -- дата, меньше которой будем обновлять


begin try

      select o.name as [objname],s.name as [shname],o.object_id,st.name as     [stname]

      into #t_stat

    from sys.stats st

        inner join  sys.objects o  on st.object_id=o.object_id

        inner join   sys.schemas  s on o.schema_id=s.schema_id

      where o.type='U'

      and STATS_DATE(st.object_id, st.stats_id)  <@dt    --фильтр по дате

      --and o.name='dbservers'                             --фильтра по обеъкту

end try

begin catch
      print (cast(error_number() as nchar(5))+'_'+ ERROR_MESSAGE())
      drop table #t_stat
end catch

--2 открываем курсом и обновляем статистику
declare cur1 cursor
             for select objname,shname,stname from #t_stat

open cur1
declare @stname sysname
declare @obj sysname
declare @sh sysname

fetch next from cur1 into @obj,@sh,@stname

WHILE (@@FETCH_STATUS <> -1)
BEGIN

     execute ('update statistics ['+@sh+'].['+@obj+'] '+@stname +' WITH FULLSCAN ')
      print ('update statistics ['+@sh+'].['+@obj+'] ['+@stname +'] WITH FULLSCAN ')

      fetch next from cur1 into @obj,@sh,@stname

end

close cur1
DEALLOCATE cur1
--select * from #t_stat

drop table #t_stat
GO
DBCC FLUSHPROCINDB(6)

SELECT DB_ID() AS [Database ID]; -- узнать дб ид у базы
GO

 

SELECT DatabaseName = DB_NAME(),
       TableName = OBJECT_NAME(s.[object_id]),
       IndexName = i.name,
       user_updates,
       system_updates,
      'alter index ['+i.name+'] ON [' +OBJECT_SCHEMA_NAME(i.object_id, DB_ID())+ '].['+OBJECT_NAME(s.[object_id])+'] DISABLE' as [Disable],
      'exec sp_rename ''['+OBJECT_SCHEMA_NAME(i.object_id, DB_ID())+'].['+OBJECT_NAME(s.[object_id])+'].['+i.name+']'',''disable_'+i.name+''',''INDEX''' as [Rename]
  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
        s.user_seeks   = 0 and
        s.user_scans   = 0 and
        s.user_lookups = 0 and
        i.is_disabled  = 0 and
        i.is_unique = 0 and
        i.is_primary_key = 0 and
        i.type_desc <> 'HEAP'
  order by user_updates + system_updates desc

 

-- текущая ситуация на сервере (выполняемые запросы)
select session_id, status, wait_type, command, last_wait_type, percent_complete, qt.text, total_elapsed_time/1000 as [total_elapsed_time, сек],
       wait_time/1000 as [wait_time, сек], (total_elapsed_time - wait_time)/1000 as [work_time, сек]
  from sys.dm_exec_requests as qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  where session_id >= 50 and session_id <> @@spid
  order by 1

-- текущая ситуация на сервере (выполняемые запросы)
select *
  from sys.sysprocesses where spid > 50 and spid <> @@spid and status <> 'sleeping'
  order by spid, ecid


-- проверка базы
DBCC CHECKDB WITH NO_INFOMSGS;


-- размер файлов логов
DBCC SQLPERF (Logspace) 


-- исправляет неточности в подсчете страниц для всех баз
exec sp_msforeachdb 'USE ?; DBCC UPDATEUSAGE(?)'

-- чистим tempdb без перезапуска сервера
DBCC FREESYSTEMCACHE ('ALL')
DBCC SHRINKFILE ...

-- ставим "контрольную точку" для всех баз
exec sp_msforeachdb 'USE ?; CHECKPOINT'


-- фрагментированные индексы
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

-- что в кэше
;WITH cacheobjects
AS
(
SELECT 
  cacheobjtype,
  objtype, 
  CAST(cast(SUM(ISNULL(cast(size_in_bytes as bigint),0)) as bigint)/1024./1024. AS DECIMAL(20,2)) AS TotalMB, 
  AVG(ISNULL(cast(size_in_bytes as bigint),0))/1024. AS AvgKB, 
  COUNT(*) AS [Count], 
  SUM(usecounts) AS usecounts, 
  AVG(CAST(usecounts AS BIGINT)) AvgUseCount, 
  SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) UsedOnceCount,
  CAST(SUM(CASE WHEN usecounts = 1 THEN ISNULL(size_in_bytes,0)/1024./1024. ELSE 0. END) AS DECIMAL(20,2)) AS UsedOnceMB
  
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype, objtype
)
SELECT 
  cacheobjtype, 
  objtype, 
  TotalMB, 
  AvgKB, 
  [Count], 
  AvgUseCount, 
  UsedOnceCount,
  CAST(1.* UsedOnceCount/[Count] AS DECIMAL(20,2)) AS [UsedOnceCount%],
  UsedOnceMB,
  CAST(1.* UsedOnceMB/TotalMB AS DECIMAL(20,2)) AS [UsedOnceMB%]
FROM cacheobjects
UNION ALL
SELECT 'Total', 'Total', 
  SUM(TotalMB), 
  CAST(SUM(TotalMB)/SUM(CAST([Count] AS BIGINT))*1024. AS DECIMAL(20,2)) AS AvgKB, 
  SUM(Count), 
  SUM(CAST(usecounts AS BIGINT))/SUM(CAST([Count] AS BIGINT)) AS AvgUseCount, 
  SUM(UsedOnceCount), 
  CAST(1.* SUM(UsedOnceCount)/SUM([Count]) AS DECIMAL(20,2)) AS [UsedOnceCount%],
  SUM(UsedOnceMB),
  CAST(1.* SUM(UsedOnceMB)/SUM(TotalMB) AS DECIMAL(20,2)) AS [UsedOnceMB%]
FROM cacheobjects
ORDER BY TotalMB DESC

-- задержки
SELECT TOP 10
 [Wait type] = wait_type,
 [Wait time (s)] = wait_time_ms / 1000,
 [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 
               / SUM(wait_time_ms) OVER())
  FROM sys.dm_os_wait_stats
  WHERE wait_type NOT LIKE '%SLEEP%' 
  ORDER BY wait_time_ms DESC;


-- итоговое число отсутствующих индексов для каждой базы данных
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 10 
       [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;


-- неиспользуемые индексы, кандидаты на удаление
SELECT DatabaseName = DB_NAME(),
       TableName = OBJECT_NAME(s.[object_id]),
       IndexName = i.name,
       user_updates,
       system_updates,
      'alter index [' +OBJECT_SCHEMA_NAME(i.object_id, DB_ID())+ '].['+i.name+'] ON ['+OBJECT_NAME(s.[object_id])+'] DISABLE' as [Disable],
      'exec sp_rename ''['+OBJECT_SCHEMA_NAME(i.object_id, DB_ID())+'].['+OBJECT_NAME(s.[object_id])+'].['+i.name+']'',''disable_'+i.name+''',''INDEX''' as [Rename]
  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
        s.user_seeks   = 0 and
        s.user_scans   = 0 and
        s.user_lookups = 0 and
        i.is_disabled  = 0 and
        i.is_unique = 0 and
        i.is_primary_key = 0 and
        i.type_desc <> 'HEAP'
  order by user_updates + system_updates desc


-- запросы с высокими издержками на ввод-вывод
SELECT TOP 10
       [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count,
       [Total IO] = (total_logical_reads + total_logical_writes),
       [Execution count] = qs.execution_count,
       [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE
                                                                               WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
                                                                               ELSE qs.statement_end_offset
                                                                             END - qs.statement_start_offset)/2),
       [Parent Query] = qt.text,
       [DatabaseName] = DB_NAME(qt.dbid)
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  ORDER BY [Average IO] DESC


-- запросы с высоким использованием ресурсов ЦП
SELECT TOP 10
       [Average CPU used] = total_worker_time / qs.execution_count,
       [Total CPU used] = total_worker_time,
       [Execution count] = qs.execution_count,
       [Individual Query] = SUBSTRING(qt.text,qs.statement_start_offset/2, 
         (CASE
            WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2),
       [Parent Query] = qt.text,
       [DatabaseName] = DB_NAME(qt.dbid)
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  ORDER BY [Average CPU used] DESC;


-- запросы, страдающие от блокировки
SELECT TOP 10
       [Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count,
       [Total Time Blocked] = total_elapsed_time - total_worker_time,
       [Execution count] = qs.execution_count,
       [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE
            WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2),
       [Parent Query] = qt.text,
       [DatabaseName] = DB_NAME(qt.dbid)
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  ORDER BY [Average Time Blocked] DESC;


-- нагрузку на подсистему ввода-вывода
select top 5 
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
     Execution_count, 
    statement_start_offset as stmt_start_offset, 
    plan_handle,
    qt.text
  from sys.dm_exec_query_stats  qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  order by  (total_logical_reads + total_logical_writes) Desc


-- какой процессор что делает
SELECT DB_NAME(ISNULL(s.dbid,1)) AS [Имя базы данных],
       c.session_id AS [ID сессии],
       t.scheduler_id AS [Номер процессора],
       s.text AS [Текст SQL-запроса]
  FROM sys.dm_exec_connections AS c
  CROSS APPLY master.sys.dm_exec_sql_text(c.most_recent_sql_handle) AS s
  JOIN sys.dm_os_tasks t ON t.session_id = c.session_id AND
                            t.task_state = 'RUNNING'
  ORDER BY c.session_id DESC


-- контроль "несжатости"
SELECT tbl.name,
       i.name,
       p.partition_number AS [PartitionNumber],
       p.data_compression_desc AS [DataCompression],
       p.rows  AS [RowCount]
  FROM sys.tables AS tbl
  LEFT JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
  INNER JOIN sys.partitions AS p ON p.object_id = CAST(tbl.object_id AS int) AND
                                    p.index_id = CAST(i.index_id AS int)
  where p.data_compression_desc <> 'PAGE' and
        p.rows >= 2000000
  order by p.rows desc, 3


-- статистика по операциям в БД
SELECT t.name AS [TableName],
       fi.page_count AS [Pages],
       fi.record_count AS [Rows],
       CAST(fi.avg_record_size_in_bytes AS int) AS [AverageRecordBytes],
       CAST(fi.avg_fragmentation_in_percent AS int) AS [AverageFragmentationPercent],
       SUM(iop.leaf_insert_count) AS [Inserts],
       SUM(iop.leaf_delete_count) AS [Deletes],
       SUM(iop.leaf_update_count) AS [Updates],
       SUM(iop.row_lock_count) AS [RowLocks],
       SUM(iop.page_lock_count) AS [PageLocks]
  FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) AS iop
  JOIN sys.indexes AS i ON iop.index_id = i.index_id AND
                           iop.object_id = i.object_id
  JOIN sys.tables AS t ON i.object_id = t.object_id AND
                          i.type_desc IN ('CLUSTERED', 'HEAP')
  JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS fi ON fi.object_id=CAST(t.object_id AS int) AND
                                                                                     fi.index_id=CAST(i.index_id AS int)
  GROUP BY t.name, fi.page_count, fi.record_count, fi.avg_record_size_in_bytes, fi.avg_fragmentation_in_percent
  ORDER BY [RowLocks] desc



-- дата обновления статистики
SELECT STATS_DATE(t1.object_id, stats_id),
      'UPDATE STATISTICS ['+ OBJECT_SCHEMA_NAME(t1.object_id, DB_ID()) + '].[' + object_name(t1.object_id) + ']([' + t1.name + ']) WITH FULLSCAN',
       t4.rows
  FROM sys.stats as t1
  inner join sys.objects as t2 on t1.object_id = t2.object_id
  left join sys.indexes  as t3 on t3.object_id = t1.object_id and
                                  t3.name = t1.name
  left join (select object_id, index_id, sum(rows) as rows
               from sys.partitions 
               group by object_id, index_id
            ) as t4 on t4.object_id = t3.object_id and
                       t4.index_id  = t3.index_id
  where STATS_DATE(t1.object_id, stats_id) < GETDATE()-5 and
        -- не учитываем отключенные индексы
        t3.is_disabled = 0 and
        -- исключаем автостатистику, по идее, в нормально спроектированной системе
        -- она создана по редким ad-hoc запросам, поэтому не является обязательной
        -- для принудительного обновления
        t1.auto_created = 0 and
        -- исключаем служебные объекты 
        t2.is_ms_shipped = 0
  order by t4.rows


-- i/o-нагрузка на файлы баз
SELECT DB_NAME(saf.dbid) AS [База данных],
       saf.name AS [Логическое имя],
       vfs.BytesRead/1048576 AS [Прочитано (Мб)],
       vfs.BytesWritten/1048576 AS [Записано (Мб)],
       saf.filename AS [Путь к файлу]

  FROM master..sysaltfiles AS saf
  JOIN ::fn_virtualfilestats(NULL,NULL) AS vfs ON vfs.dbid = saf.dbid AND
                                                  vfs.fileid = saf.fileid-- AND
                                                  --saf.dbid NOT IN (1,3,4)
  where vfs.BytesRead/1048576 <> 0 or
        vfs.BytesWritten/1048576 <> 0
  ORDER BY vfs.BytesRead/1048576 + BytesWritten/1048576 DESC


-- i/o-нагрузка на диски
SELECT SUBSTRING(saf.physical_name, 1, 1)    AS [Диск],
       SUM(vfs.num_of_bytes_read/1048576)    AS [Прочитано (Мб)],
       SUM(vfs.num_of_bytes_written/1048576) AS [Записано (Мб)]
  FROM sys.master_files AS saf
  JOIN sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs ON vfs.database_id = saf.database_id AND
                                                         vfs.file_id = saf.file_id AND
                                                         saf.database_id NOT IN (1,3,4) AND
                                                         saf.type < 2
  GROUP BY SUBSTRING(saf.physical_name, 1, 1)
  ORDER BY [Диск]


-- быстрая загрузка экселевского файла
select *
  from opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties="Excel 8.0;IMEX=1"')...[Лист1$]


-- занимаемое на диске место
SELECT TOP 1000
       (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
       a3.name AS [schemaname],
       a2.name AS [tablename],
       a1.rows as row_count,
      (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
       a1.data * 8 AS data,
      (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
      (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused,
      'ALTER TABLE [' + a2.name  + '] REBUILD' as [sql]
  FROM (SELECT ps.object_id,
               SUM(CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
               SUM(ps.reserved_page_count) AS reserved,
               SUM(CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data,
               SUM(ps.used_page_count) AS used
          FROM sys.dm_db_partition_stats ps
          GROUP BY ps.object_id
       ) AS a1
  LEFT JOIN (SELECT it.parent_id,
                    SUM(ps.reserved_page_count) AS reserved,
                    SUM(ps.used_page_count) AS used
               FROM sys.dm_db_partition_stats ps
               INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
               WHERE it.internal_type IN (202,204)
               GROUP BY it.parent_id
            ) AS a4 ON (a4.parent_id = a1.object_id)
  INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
  INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
  WHERE a2.type <> N'S' and a2.type <> N'IT'
  ORDER BY 8 DESC


-- под какие объекты выделена память 
select count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
  from sys.dm_os_buffer_descriptors as bd
  inner join (select object_id as objectid,
                     object_name(object_id) as name,
                     index_id,allocation_unit_id
                from sys.allocation_units as au
                inner join sys.partitions as p on au.container_id = p.hobt_id and (au.type = 1 or au.type = 3)
                union all
                select object_id as objectid,
                       object_name(object_id) as name,
                       index_id,allocation_unit_id
                  from sys.allocation_units as au
                  inner join sys.partitions as p on au.container_id = p.partition_id and au.type = 2
             ) as obj on bd.allocation_unit_id = obj.allocation_unit_id
  left outer join sys.indexes ind on obj.objectid = ind.object_id and
                                     obj.index_id = ind.index_id
  where bd.database_id = db_id() and
        bd.page_type in ('data_page', 'index_page')
  group by obj.name, ind.name, obj.index_id
  order by cached_pages_count desc
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

 

 

 

 

 

 

 

Хранимая процедура что бы посмотреть в каком состоянии репликации.

CREATE PROCEDURE GetReplicationAgentStatus
AS

BEGIN
set nocount on
set transaction isolation level read uncommitted 

/*
Make sure your agents are in the correct category 
i.e Merge agents under REPL-Merge, 
Distribution agents under REPL-Distribution 
and LogReader agent under REPL-LogReader
*/

select s.job_id,s.name,s.enabled,c.name as categoryname into #JobList 
from msdb.dbo.sysjobs s inner join msdb.dbo.syscategories c on s.category_id = c.category_id
where c.name in ('REPL-Merge','REPL-Distribution','REPL-LogReader')

create TABLE #xp_results  
   (job_id                UNIQUEIDENTIFIER NOT NULL,
    last_run_date         INT              NOT NULL,
    last_run_time         INT              NOT NULL,
    next_run_date         INT              NOT NULL,
    next_run_time         INT              NOT NULL,
    next_run_schedule_id  INT              NOT NULL,
    requested_to_run      INT              NOT NULL, 
    request_source        INT              NOT NULL,
    request_source_id     sysname          COLLATE database_default NULL,
    running               INT              NOT NULL,
    current_step          INT              NOT NULL,
    current_retry_attempt INT              NOT NULL,
    job_state             INT              NOT NULL)

insert into #xp_results 
exec master.dbo.xp_sqlagent_enum_jobs 1, ''

select j.name,j.categoryname,j.enabled, AgentStatus = CASE WHEN r.running =1 THEN 'Running' else 'Stopped'   end
from #JobList j inner join #xp_results r on j.job_id=r.job_id

-- Uncomment the below portion and use correct parameters to send email alert
/*
if exists (select j.name,j.categoryname,j.enabled,r.running
from #JobList j inner join #xp_results r   on j.job_id=r.job_id where running =0 )
begin
   declare @subject nvarchar(100)
   select @subject = N'Replication Agents Status on '+@@servername

   EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'ProfileName',
      @recipients = N'email id',
      @subject = @subject,
      @body = 'One or more agents found stopped'
end
*/
drop table #JobList,#xp_results
END

Продолжение следует

Как исправить ошибку

The OLE DB provider "SQLNCLI" for linked server "s0069" reported a change in schema version between compile time ("185675752805467") and run time ("185684342736281") for table

возникает при не правильном кеш плане у вьюхи. делаем команду

EXECUTE sp_refreshview N'имя вьюхи'

 

Хранимая процедура

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[alertjob_test]    Script Date: 04/25/2018 08:59:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[alertjob_test]
zakupki NVARCHAR(max)
AS
BEGIN
set nocount ON
DECLARE @job_id VARCHAR(50)
SET @job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE [name] = zakupki)
IF (not object_id('tempdb..#x') is null) drop table #x 
SELECT  TOP 2 s.instance_id INTO #x FROM msdb.dbo.sysjobhistory s WHERE s.job_id = @job_id AND s.step_name LIKE '(Job outcome)'  ORDER BY s.instance_id DESC
IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = zakupki)
BEGIN
	IF (not object_id('tempdb..#z') is null) drop table #z 
	SELECT ja.job_id, j.name as job_name, ja.start_execution_date, isNull(last_executed_step_id,0) + 1 as current_executed_step_id,	js.step_name INTO #z 
		FROM msdb.dbo.sysjobactivity ja left join msdb.dbo.sysjobhistory jh on ja.job_history_id = jh.instance_id inner join msdb.dbo.sysjobs j on ja.job_id = j.job_id inner join msdb.dbo.sysjobsteps js on ja.job_id = js.job_id and isNull(ja.last_executed_step_id,0) + 1 = js.step_id
			WHERE ja.session_id = (select top 1 session_id from msdb.dbo.syssessions order by agent_start_date desc) and start_execution_date is not null and stop_execution_date is NULL
	IF EXISTS ( select job_name from #z where job_name = zakupki )
		BEGIN
    		SELECT 1
		END
	ELSE
		BEGIN
			IF EXISTS (SELECT s.run_status FROM msdb.dbo.sysjobhistory s WHERE s.job_id = @job_id AND s.instance_id > (SELECT MIN(instance_id) FROM #x) AND s.run_status = 0) 
				BEGIN
					SELECT 2
				END
			IF ((SELECT  TOP 1 s.run_status FROM msdb.dbo.sysjobhistory s WHERE s.job_id = @job_id AND s.step_name LIKE '(Job outcome)' ORDER BY s.instance_id DESC) = 3)
				BEGIN
					SELECT 2
				END
			ELSE
				BEGIN
					SELECT 0
				END
		END
	DROP TABLE #x
	DROP TABLE #z
END
ELSE 
	BEGIN
		SELECT 2
	END
END


--DROP PROCEDURE dbo.alertjob_test

--EXEC msdb.dbo.alertjob_test zakupki = N'test' 



				

GO


Настройка в prtg: