SQL сервер 2008: обслуживание, анализ производительности

22.03.16

База данных - HighLoad оптимизация

Полезные скрипты и запросы для SQL 2008: обслуживание, анализ производительности.

Файл со всеми скриптами во вложении.

Скачать файл

ВНИМАНИЕ: Файлы из Базы знаний - это исходный код разработки. Это примеры решения задач, шаблоны, заготовки, "строительные материалы" для учетной системы. Файлы ориентированы на специалистов 1С, которые могут разобраться в коде и оптимизировать программу для запуска в базе данных. Гарантии работоспособности нет. Возврата нет. Технической поддержки нет.

Наименование SM По подписке [?] Купить один файл
UsefulScripts.sql
.sql 14,23Kb
106
106
1 SM
Скачать Купить за 1 850 руб.

Скрипты не мои, собраны с ресурсов по MS SQL. Опубликованы скрипты и запросы, которыми сам часто пользуюсь

Большинство скриптов статьи и многие другие представлены в конфигурации:

 Обновление однотипных конфигураций, работа с SQL и другие регламентные операции

Обслуживание индексов

Степень фрагментации индексов

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

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

Расшифровка статистик ожиданий

Пример результата:

Следует иметь ввиду, что статистика не сохраняется при перезапуске SQL Server, и все данные накапливаются с момента последнего сброса статистики или перезапуска сервера.

Очистка статистик ожидания:

DBCC SQLPERF(waitstats, CLEAR)
GO

Рабочие протоколы

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

Пример:

регламентные процедуры SQL статистика ожиданий индексы план запроса

См. также

HighLoad оптимизация Программист Платформа 1С v8.3 Бесплатно (free)

Метод очень медленно работает, когда параметр приемник содержит намного меньше свойств, чем источник.

06.06.2024    7215    Evg-Lylyk    61    

41

HighLoad оптимизация Программист Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    4238    spyke    28    

48

HighLoad оптимизация Программист Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    6442    vasilev2015    20    

40

HighLoad оптимизация Инструменты администратора БД Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих запросов на sql, ожиданий, конвертация запроса в 1С и рекомендации, где может тормозить.

2 стартмани

15.02.2024    10113    206    ZAOSTG    74    

110

HighLoad оптимизация Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Принимать, хранить и анализировать показания счетчиков (метрики) в базе 1С? Почему бы нет? Но это решение быстро привело к проблемам с производительностью при попытках построить какую-то более-менее сложную аналитику. Переход на PostgresSQL только временно решил проблему, т.к. количество записей уже исчислялось десятками миллионов и что-то сложное вычислить на таких объемах за разумное время становилось все сложнее. Кое-что уже практически невозможно. А что будет с производительностью через пару лет - представить страшно. Надо что-то предпринимать! В этой статье поделюсь своим первым опытом применения СУБД Clickhouse от Яндекс. Как работает, что может, как на нее планирую (если планирую) переходить, сравнение скорости работы, оценка производительности через пару лет, пример работы из 1С. Все это приправлено текстами запросов, кодом, алгоритмами выполненных действий и преподнесено вам для ознакомления в этой статье.

1 стартмани

24.01.2024    4357    glassman    17    

39

HighLoad оптимизация Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    9169    doom2good    49    

69
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. V_V_V 23.10.14 17:27 Сейчас в теме
Спасибо. Погонял на тестовом сервере с несколькими базами - некоторые запросы выдали любопытные вещи. На которые раньше не обратил бы внимания и жил бы себе спокойно... :)
2. jan27 733 23.10.14 19:14 Сейчас в теме
(1) не за что, периодически буду обновлять, как найду что-нибудь интересное
3. Armando 1400 24.10.14 00:51 Сейчас в теме
Еще бы описание, как применять полученные результаты.
alkar; Aleksey_3; gorevg; Tedman; Dach; NeronKrasu; molodoi1sneg; Йожкин Кот; +8 Ответить
4. jan27 733 24.10.14 04:57 Сейчас в теме
(3) здесь интернет в помощь, только для типов ожидания кучи статей
6. Armando 1400 24.10.14 12:36 Сейчас в теме
(4) если так рассуждать, то по анализу производительности в инете тоже статей куча. Хотелось бы здесь сразу видеть ссылки на эти статьи, которые помогут расшифровать результаты.
dima_home; AlexeyFreeLife; +2 Ответить
7. jan27 733 24.10.14 12:41 Сейчас в теме
(6) начнем с того, какой результат вы хотите расшифровать?
9. necropunk 9 24.10.14 14:39 Сейчас в теме
(6) Armando, тут должна быть ссылка на книгу "Настольная книга эксперта по технологическим вопросам"... Серьезно, тут не пара статей будет. Даже не пара десятков.... Я бы так и гуглил: "производительность 1С план запроса", "производительность 1С индексы", "производительность 1С блокировки... я так и гуглил в свое время, почитал и пошел книжки читать, чтобы информация не кусками надерганная была, а упорядоченная...
5. AlX0id 24.10.14 11:00 Сейчас в теме
Утащил в эвернот, спасибо )
8. necropunk 9 24.10.14 14:35 Сейчас в теме
Отлично, да, спасибо, самому не писать и не искать. Как раз все доработки дописал и до производительности добрался.
10. necropunk 9 24.10.14 14:39 Сейчас в теме
Подписаться забыл на тему.
11. Bad_Developer 24.10.14 14:56 Сейчас в теме
Спасибо за тему. Подписался.
12. Lucechiaro 25.10.14 13:12 Сейчас в теме
Спасибо! Очень полезный инструментарий. Давно хотел собрать себе такой же.
13. Алексей_Ч 4 25.10.14 19:27 Сейчас в теме
Спасибо буду изучать.
14. JohnyDeath 301 25.10.14 19:58 Сейчас в теме
Хороший наборчик.
Вот если бы еще все это с анализом на стороне 1С. Т.е. чтоб и поля показал, из которых индексы состоят и т.п. Например как у Алексея: http://infostart.ru/public/81694/
19. jan27 733 25.10.14 23:41 Сейчас в теме
(14) индексы можно посмотреть отчетом структура бд или любой другой аналогичной, при помощи нее можно найти и избыточные индексы и убрать галочку индексировать с соответствующих реквизитов
20. h00k 51 26.10.14 00:47 Сейчас в теме
(19)jan27
убрать галочку индексировать с соответствующих реквизитов

Для регистров немного сложнее, может потребоваться изменение порядка следования измерений или включение и настройка агрегатов.
22. jan27 733 26.10.14 09:27 Сейчас в теме
(20) да, согласен, я говорил о простых и явно избыточных по определенному реквизиту, к тому же к сожалению платформа 1С не позволяет создавать рекомендуемые индексы
23. h00k 51 26.10.14 15:36 Сейчас в теме
(22)jan27
я говорил о простых и явно избыточных по определенному реквизиту

Ну в плане новых типовых это уже не так критично, года с 2008 в 1С всё-таки одумались и перестали в типовых конфигурациях выставлять режим индексирования в реквизитах, оставив специалистам на месте решать какие поля требуют дополнительного индексирования.
24. jan27 733 26.10.14 18:18 Сейчас в теме
(23) однако, это не мешает некоторым "специалистам" индексировать эти реквизиты, о чем я и толкую. Не у всех конфиурации под контролем своих программистов, а достаются в наследство от "топовых" франчей. Еще раз подчеркиваю, что подход должен быть сугубо индивидуальный и я осознанно не привожу конкретики типа делай раз, делай два и ... опа ускорение 1с в 100 раз
25. AlX0id 26.10.14 22:07 Сейчас в теме
(23) h00k,
Ага. См. регистры аналитики учета затрат в УПП )
Ну есть все же поля, однозначно требующие индексирования - и будь конфа тридцать три раза типовой - индекс там стоять должен )
21. JohnyDeath 301 26.10.14 01:10 Сейчас в теме
(19) ну а почему бы сразу не сделать один отличный инструмент и не вошкаться в кучке маленьких переключаясь из одной в другую?
15. AlexeyFreeLife 25.10.14 19:58 Сейчас в теме
Зачем клонировать давным давно написанное без своих рекомендаций и результатов опыта?

Лучше прочитать оригинал статьи от .microsoft: http://msdn.microsoft.com/ru-ru/magazine/cc135978.aspx
alexscamp; tormozit; borda4ev; gadjik; ediks; JohnyDeath; +6 1 Ответить
18. jan27 733 25.10.14 23:34 Сейчас в теме
(15) спасибо за ссылку, мне следовало указать её в статье
16. JohnyDeath 301 25.10.14 20:05 Сейчас в теме
И вот еще в копилку http://www.1cpp.ru/forum/YaBB.pl?num=1310006278/7#7 . Нарыл это Алексей Лустин (lustin), за что ему большое спасибо.
17. h00k 51 25.10.14 22:08 Сейчас в теме
Временами, при восстановлении базы из архива может сбиться настройка смещения дат, что чревато серьезными проблемами.
Проверить настройку смещения дат можно следующим скриптом:
SELECT TOP 1 Offset FROM _YearOffset


Для оценки самых тяжелых запросов я использую немного другие скрипты:
1. Топ 10 самых тяжелых для процессора
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;
Показать

2. Топ 10 самых тяжелых по вводу/выводу
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;
Показать


Статистика ввода/вывода по файлам баз данных
USE master
GO
SELECT TOP 10	DB_NAME(saf.dbid)				AS [База данных]
	,	saf.name					AS [Логическое имя]
	,	vfs.BytesRead/1048576				AS [Прочитано (Мб)]
	,	vfs.BytesWritten/1048576			AS [Записано (Мб)]
	,	saf.filename 					AS [Путь к файлу]
FROM		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)
ORDER BY	vfs.BytesRead/1048576 + BytesWritten/1048576 DESC
GO
Показать

Статистика ввода/вывода по дискам
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 [Диск]
GO
Показать


Производительность журнала транзакций
SELECT      (wait_time_ms - signal_wait_time_ms) / waiting_tasks_count 	AS [Время отклика долговременного носителя журнала (ms)] 
              ,    max_wait_time_ms 					AS [Максимальное время ожидания (ms)]
FROM        sys.dm_os_wait_stats
WHERE       wait_type = 'WRITELOG' AND waiting_tasks_count > 0;
_MPV_; creatermc; v.l.; V.Nikonov; reallord; kaa79; wunderland; tormozit; vggrigoryev; Il; V_V_V; jan27; JohnyDeath; +13 Ответить
37. Sergey.Noskov 1399 03.11.14 23:38 Сейчас в теме
(17) h00k, Запросы 1 и 2. Усреднение часто поднимает вверх "одиночек", есть риск не увидеть массовые запросы с низким значением [Average CPU used] но высоким [Total CPU used].А их оптимизация дает больший эффект.
Конечно и по средней надо смотреть и по "Итого" и по максимальным значениям, комбинация выборок дает более полную картинку.
38. h00k 51 04.11.14 06:45 Сейчас в теме
(37)Sergey.Noskov Да, возможно это стоило указать в комментариях к запросам. Как-то не пришло в голову что коллеги могут не додуматься изменить опубликованную версию запроса для получения полной картины, и будут довольствоваться только средними показателями.
26. Painted 49 27.10.14 15:19 Сейчас в теме
Многострадальный sys.dm_exec_query_stats можно еще использовать так, топ 30 запросов по длительности блокировки.
SELECT TOP 30 
 (total_elapsed_time - total_worker_time) / qs.execution_count AS [Average Time Blocked],
 total_elapsed_time - total_worker_time AS [Total Time Blocked],
 qs.execution_count AS [Execution count],
 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) AS [Individual Query], 
 qt.text [Parent Query],
 DB_NAME(qt.dbid) AS [Database name]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
/* этот кусок отберет по базе, если надо по всем - удалить */
 /*WHERE DB_NAME(qt.dbid)='ИмяБазы'*/
 ORDER BY [Average Time Blocked] DESC;
Показать
27. Painted 49 27.10.14 15:35 Сейчас в теме
Наблюдаем за памятью: если Free Pages меньше 300, объем ОЗУ узкое место в производительности системы.
SEL ECT * FR OM sys.sysperfinfo  where counter_name like 'Page Writes%' or counter_name like 'Page reads%' 
or counter_name like 'lazy%' or counter_name like 'Page Life%' or counter_name like 'Memory Grants Pending%'
or (counter_name = 'Free pages' and [object_name] LIKE '%BUFFER MANAGER%')
28. пользователь 28.10.14 22:26
Когда уже про PostreSQL все разжуют? Справедливости ради например =)
29. jan27 733 29.10.14 04:54 Сейчас в теме
(28) у тебя есть шанс быть первым))
30. borda4ev 16 29.10.14 11:00 Сейчас в теме
Кину сюда, похожий материал из своей коллекции:
http://habrahabr.ru/post/241079/
32. sorb 29.10.14 11:51 Сейчас в теме
Вот бы еще скрипт с интеллектуальным обновлением статистики: обновить статистику только по тем таблицам, где например более 5% измененных записей (как с фрагментацией индексов)
34. JohnyDeath 301 29.10.14 14:31 Сейчас в теме
36. sorb 30.10.14 17:16 Сейчас в теме
(34) JohnyDeath, Ух, дух захватывает, спасибо!
39. AlX0id 05.11.14 12:18 Сейчас в теме
33. m191 122 29.10.14 14:28 Сейчас в теме
У меня подобное сделано. но это концепт http://infostart.ru/public/145342/ - при желании можно допиливать под себя.
35. jan27 733 30.10.14 11:34 Сейчас в теме
Перекрывающаяся статистика

WITH    autostats ( object_id, stats_id, name, column_id )
 
AS ( SELECT   sys.stats.object_id ,
 
sys.stats.stats_id ,
 
sys.stats.name ,
 
sys.stats_columns.column_id
 
FROM     sys.stats
 
INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id
 
AND sys.stats.stats_id = sys.stats_columns.stats_id
 
WHERE    sys.stats.auto_created = 1
 
AND sys.stats_columns.stats_column_id = 1
 
)
 
SELECT  OBJECT_NAME(sys.stats.object_id) AS [Table] ,
 
sys.columns.name AS [Column] ,
 
sys.stats.name AS [Overlapped] ,
 
autostats.name AS [Overlapping] ,
 
'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id)
 
+ '].[' + OBJECT_NAME(sys.stats.object_id) + '].['
 
+ autostats.name + ']'
 
FROM    sys.stats
 
INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id
 
AND sys.stats.stats_id = sys.stats_columns.stats_id
 
INNER JOIN autostats ON sys.stats_columns.object_id = autostats.object_id
 
AND sys.stats_columns.column_id = autostats.column_id
 
INNER JOIN sys.columns ON sys.stats.object_id = sys.columns.object_id
 
AND sys.stats_columns.column_id = sys.columns.column_id
 
WHERE   sys.stats.auto_created = 0
 
AND sys.stats_columns.stats_column_id = 1
 
AND sys.stats_columns.stats_id != autostats.stats_id
 
AND OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0
Показать


http://www.pythian.com/blog/sql-server-statistics-maintenance-and-best-practices/
40. tormozit 7193 08.11.14 13:21 Сейчас в теме
Почему у некоторых запросов из представления dm_exec_query_stats поле total_elapsed_time меньше чем total_worker_time? Подозреваю, что связано с распараллеливанием, но как в таком случае посчитать время ожидания?
41. jan27 733 10.11.14 15:17 Сейчас в теме
(40) подозреваю, что используя данное представление, никак... подробнее Об этом запросе: http://blog.sqlauthority.com/2011/02/08/sql-server-sos_scheduler_yield-wait-type-day-8-of-28/
42. Painted 49 11.11.14 11:17 Сейчас в теме
Поизучал свои базы и обнаружил, что у таблицы "группы пользователей" сильно нагружен некластерный индекс. А кластерный, более быстрый, простаивает в основном. Вот если я поменяю кластерность, ничем не чревато? Ну, не считая нарушения запретов 1С.
43. sorb 11.11.14 11:45 Сейчас в теме
(42) Painted, поменяйте порядок реквизитов в конфигураторе так, чтобы некластерный индекс стал кластерным. Хотя особой разницы в том, какой индекс, нет. Странно то, что индекс вообще используется - если записей сильно меньше 1000, то сканирование дешевле обходится. Или у Вас групп супермного?
44. Painted 49 11.11.14 11:52 Сейчас в теме
(43) sorb, Групп штук 30. Индексы лучше вообще убрать?
46. jan27 733 11.11.14 12:17 Сейчас в теме
(44) если у реквизитов проставлен признак индексировать, то лучше убрать (в конфигураторе)
53. sorb 14.11.14 21:12 Сейчас в теме
(44) Painted, при таком количестве записей индекс получится больше, чем сама таблица, ну и при любых соединениях гарантированно будет использован nested loops из-за малого количества записей
47. jan27 733 11.11.14 12:20 Сейчас в теме
(43) порядок реквизитов не меняет некластерный индекс на кластерный
54. sorb 14.11.14 21:16 Сейчас в теме
(47) да, забыл, что это справочник, а не регистр сведений. Но по сути вопроса имхо в данном случае индексы нужны как рыбе зонтик :)
45. jan27 733 11.11.14 12:12 Сейчас в теме
(42) следуя http://msdn.microsoft.com/en-us/library/ms186342.aspx такое возможно. учитывая. что кластерный индекс по умолчанию по первичному ключу, со стороны 1С - при обновлении все вернется на свои места... стоит ли овчинка выделки?
Можете попробовать на копии базы и сравнить производительность
48. wildskiff 11.11.14 20:59 Сейчас в теме
Не самописные скрипты можно было бы и бесплатно опубликовать. Однако за материал спасибо - пригодилось. )
alexscamp; +1 Ответить
49. tormozit 7193 14.11.14 11:12 Сейчас в теме
Хочу подсчитать среднюю степень параллелизма запроса. Лучшая оценка будет total_worker_time / total_elapsed_time ?
50. jan27 733 14.11.14 11:45 Сейчас в теме
(49) очень грубая оценка. т.к. total_worker_time может быть меньше total_elapsed_time ввиду ожиданий на задержках. Степень параллелизма указывает в ShowPlan statics
51. tormozit 7193 14.11.14 13:01 Сейчас в теме
(50) Это какого представления колонка "ShowPlan statics" ? Если ты про сам план запроса, то понятно, что там все детально видно. Нужна именно оценка для всего запроса. Понятно, что она будет грубой. Я просто ищу наилучшую.
52. jan27 733 14.11.14 13:04 Сейчас в теме
55. jan27 733 19.11.14 15:51 Сейчас в теме
Статистика ожиданий, мне больше понравилась
WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',         N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',            N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',          N'CHECKPOINT_QUEUE',
        N'CHKPT',                       N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',            N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',          N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',       N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',             N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                    N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',           N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',        N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',             N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',              N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',           N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',             N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',         N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',        N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',            N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',         N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',       N'WAIT_FOR_RESULTS',
        N'WAITFOR',                     N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',         N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',          N'XE_TIMER_EVENT')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO
Показать
56. jan27 733 25.11.14 09:22 Сейчас в теме
Параллельные запросы и использование ЦПУ
http://www.jasonstrate.com/2009/04/find-query-plans-that-may-utilize-parallelism/
на всякий случай скрипты
SELECT TOP 50

OBJECT_NAME(p.objectid, p.dbid) as [object_name]

,qs.execution_count

,qs.total_worker_time

,qs.total_logical_reads

,qs.total_elapsed_time

,CASE statement_end_offset WHEN -1 THEN q.text

ELSE SUBSTRING(q.text, statement_start_offset/2, (statement_end_offset-statement_start_offset)/2) END as sql_statement

,p.query_plan

,q.text

,cp.plan_handle

FROM sys.dm_exec_query_stats qs

INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q

WHERE cp.cacheobjtype = 'Compiled Plan'

AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float') > 0

ORDER BY qs.total_worker_time/qs.execution_count DESC

Показать


WITH cQueryStats

AS (

SELECT qs.plan_handle

,MAX(qs.execution_count) as execution_count

,SUM(qs.total_worker_time) as total_worker_time

,SUM(qs.total_logical_reads) as total_logical_reads

,SUM(qs.total_elapsed_time) as total_elapsed_time

FROM sys.dm_exec_query_stats qs

GROUP BY qs.plan_handle

)

SELECT TOP 50

OBJECT_NAME(p.objectid, p.dbid) as [object_name] ,qs.execution_count

,qs.total_worker_time

,qs.total_logical_reads

,qs.total_elapsed_time

,p.query_plan

,q.text

,cp.plan_handle

FROM cQueryStats qs

INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) as q

WHERE cp.cacheobjtype = 'Compiled Plan'

AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)', 'float') > 0

--ORDER BY qs.total_worker_time/qs.execution_count DESC
ORDER BY qs.total_worker_time DESC

Показать
57. jan27 733 11.12.14 10:09 Сейчас в теме
запросы к sys.dm_exec_cached_plans (количество adhoc, prepared и др.

select COUNT(*) as adhoc from sys.dm_exec_cached_plans
WHERE objtype = 'ADHOC'

select Count(*) as prepared from sys.dm_exec_cached_plans
WHERE objtype = 'Prepared'

select Count(*) as [Proc] from sys.dm_exec_cached_plans
WHERE objtype = 'Proc'

select Count(*) as [view] from sys.dm_exec_cached_plans
WHERE objtype = 'view'
Показать
58. h00k 51 11.12.14 11:05 Сейчас в теме
Нашел еще один неплохой набор скриптов, автор скрипы понемногу дополняет и поддерживает в актуальном состоянии.

Адрес: https://dl.dropboxusercontent.com/u/13748067/SQL%20Server%202012%20%20Diagnostic%20Informa­tion%20Queries%20(April%202014).sql
59. jan27 733 07.01.15 21:25 Сейчас в теме
(58) что-то ссылка не работает, может просто адрес скинешь?
60. wunderland 202 10.01.15 18:19 Сейчас в теме
(58) Ссылка не открывается...

На просторах нашел http://habrahabr.ru/post/136481/ и там про sp_WhoIsActive
61. PVG_73 17 30.03.16 13:15 Сейчас в теме
Интересная подборка, спасибо!
62. jaroslav.h 180 14.07.16 17:11 Сейчас в теме
Подпишусь, спасибо за подборку.
Оставьте свое сообщение