Vadim's KB

DBCC opentran;

EXEC Sp_lock
EXEC Sp_who2

DBCC inputbuffer (<spid>)

SELECT *
FROM   sys.sysprocesses
WHERE  dbid = Db_id('dababase')
       AND blocked > 0

SELECT t1.resource_type,
       t1.resource_database_id,
       t1.resource_associated_entity_id,
       t1.request_mode,
       t1.request_session_id,
       t2.blocking_session_id,
       o1.NAME         'object name',
       o1.type_desc    'object descr',
       p1.partition_id 'partition id',
       p1.rows         'partition/page rows',
       a1.type_desc    'index descr',
       a1.container_id 'index/page container_id'
FROM   sys.dm_tran_locks AS t1
       INNER JOIN sys.dm_os_waiting_tasks AS t2
               ON t1.lock_owner_address = t2.resource_address
       LEFT OUTER JOIN sys.objects o1
                    ON o1.object_id = t1.resource_associated_entity_id
       LEFT OUTER JOIN sys.partitions p1
                    ON p1.hobt_id = t1.resource_associated_entity_id
       LEFT OUTER JOIN sys.allocation_units a1
                    ON a1.allocation_unit_id = t1.resource_associated_entity_id

SELECT *
FROM   sys.dm_tran_database_transactions
WHERE  database_id = Db_id('dababase') 

DECLARE @DateNow DATETIME

SELECT @DateNow = Dateadd(dd, 0, Datediff(dd, 0, Getdate()))

DECLARE @SQL NVARCHAR(max)

SELECT @SQL = (SELECT
              '     UPDATE STATISTICS ['
                      + Schema_name(o.[schema_id]) + '].[' + o.NAME
                      + '] [' + s.NAME + ']         WITH FULLSCAN' + CASE WHEN
              s.no_recompute = 1
                                    THEN
                      ', NORECOMPUTE' ELSE '' END + ';'
               FROM   (SELECT [object_id],
                              NAME,
                              stats_id,
                              no_recompute,
                              last_update = Stats_date([object_id], stats_id)
                       FROM   sys.stats WITH(nolock)
                       WHERE  auto_created = 0
                              AND is_temporary = 0) s
                      JOIN sys.objects o WITH(nolock)
                        ON s.[object_id] = o.[object_id]
                      JOIN (SELECT p.[object_id],
                                   p.index_id,
                                   total_pages = Sum(a.total_pages)
                            FROM   sys.partitions p WITH(nolock)
                                   JOIN sys.allocation_units a WITH(nolock)
                                     ON p.[partition_id] = a.container_id
                            GROUP  BY p.[object_id],
                                      p.index_id) p
                        ON o.[object_id] = p.[object_id]
                           AND p.index_id = s.stats_id
               WHERE  o.[type] IN ( 'U', 'V' )
                      AND o.is_ms_shipped = 0
                      AND ( last_update IS NULL
                            AND p.total_pages > 0
                             -- never updated and contains rows
                             OR last_update <= Dateadd(dd, CASE
                                                             WHEN
                                               p.total_pages > 4096
                                                           -- > 4 MB
                                                           THEN -2
                                                             -- updated 3 days ago
                                                             ELSE 0
                                                           END, @DateNow) )
               FOR xml path(''), type)

.value('.', 'NVARCHAR(MAX)')
PRINT @SQL

EXEC sys.Sp_executesql
  @SQL 

DECLARE @SQL NVARCHAR(max)
DECLARE cur CURSOR local read_only forward_only FOR
  SELECT '     ALTER INDEX [' + i.NAME + N'] ON ['
         + Schema_name(o.[schema_id]) + '].[' + o.NAME
         + '] ' + CASE WHEN s.avg_fragmentation_in_percent > 30 THEN
         'REBUILD WITH (SORT_IN_TEMPDB = ON'
         -- Enterprise, Developer
         + CASE WHEN Serverproperty('EditionID') IN (1804890536, -2117995310)
         THEN
         ', ONLINE = ON' ELSE '' END + ')' 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(), NULL, NULL, NULL,
                 'DETAILED') 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

      EXEC sys.Sp_executesql
        @SQL

      FETCH next FROM cur INTO @SQL
  END

CLOSE cur

DEALLOCATE cur 

В журнале Application появляются сообщения об ошибках вида "Event filter with query "SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE TargetInstance ISA "Win32_Processor" AND TargetInstance.LoadPercentage > 99" could not be reactivated in namespace "//./root/CIMV2" because of error 0x80041003. Events cannot be delivered through this filter until the problem is corrected."

Описание и решение - https://support.microsoft.com/en-us/kb/2545227.