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

22.03.16

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

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

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

Скачать файлы

Наименование Файл Версия Размер
UsefulScripts.sql
.sql 14,23Kb
106
.sql 001 14,23Kb 106 Скачать

Скрипты не мои, собраны с ресурсов по 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 Конфигурации 1cv8 Бесплатно (free)

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

13.03.2024    2393    spyke    25    

38

Анализируем SQL сервер глазами 1С-ника

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

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

1 стартмани

15.02.2024    7355    149    ZAOSTG    66    

95

Удаление строк из таблицы значений различными способами с замером производительности

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

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

09.01.2024    5766    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8588    ivanov660    6    

75

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    4999    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    15959    skovpin_sa    14    

98

Как эффективно настроить autovacuum в Postgres для 1С

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

Кто не любит убирать мусор? Думаю, практически все, а вот в Postgres это обязательный ритуал для эффективной работы. Как эффективно настроить уборку за 1С в Postgres, можно прочитать в этой статье и еще раз задуматься о бесплатности Postgres.

05.08.2023    4974    1CUnlimited    5    

51
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. V_V_V 23.10.14 17:27 Сейчас в теме
Спасибо. Погонял на тестовом сервере с несколькими базами - некоторые запросы выдали любопытные вещи. На которые раньше не обратил бы внимания и жил бы себе спокойно... :)
2. jan27 732 23.10.14 19:14 Сейчас в теме
(1) не за что, периодически буду обновлять, как найду что-нибудь интересное
3. Armando 1399 24.10.14 00:51 Сейчас в теме
Еще бы описание, как применять полученные результаты.
alkar; Aleksey_3; gorevg; Tedman; Dach; NeronKrasu; molodoi1sneg; Йожкин Кот; +8 Ответить
4. jan27 732 24.10.14 04:57 Сейчас в теме
(3) здесь интернет в помощь, только для типов ожидания кучи статей
6. Armando 1399 24.10.14 12:36 Сейчас в теме
(4) если так рассуждать, то по анализу производительности в инете тоже статей куча. Хотелось бы здесь сразу видеть ссылки на эти статьи, которые помогут расшифровать результаты.
dima_home; AlexeyFreeLife; +2 Ответить
7. jan27 732 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 732 25.10.14 23:41 Сейчас в теме
(14) индексы можно посмотреть отчетом структура бд или любой другой аналогичной, при помощи нее можно найти и избыточные индексы и убрать галочку индексировать с соответствующих реквизитов
20. h00k 50 26.10.14 00:47 Сейчас в теме
(19)jan27
убрать галочку индексировать с соответствующих реквизитов

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

Ну в плане новых типовых это уже не так критично, года с 2008 в 1С всё-таки одумались и перестали в типовых конфигурациях выставлять режим индексирования в реквизитах, оставив специалистам на месте решать какие поля требуют дополнительного индексирования.
24. jan27 732 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 732 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 50 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 1375 03.11.14 23:38 Сейчас в теме
(17) h00k, Запросы 1 и 2. Усреднение часто поднимает вверх "одиночек", есть риск не увидеть массовые запросы с низким значением [Average CPU used] но высоким [Total CPU used].А их оптимизация дает больший эффект.
Конечно и по средней надо смотреть и по "Итого" и по максимальным значениям, комбинация выборок дает более полную картинку.
38. h00k 50 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 732 29.10.14 04:54 Сейчас в теме
(28) у тебя есть шанс быть первым))
30. borda4ev 15 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 732 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 7133 08.11.14 13:21 Сейчас в теме
Почему у некоторых запросов из представления dm_exec_query_stats поле total_elapsed_time меньше чем total_worker_time? Подозреваю, что связано с распараллеливанием, но как в таком случае посчитать время ожидания?
41. jan27 732 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 732 11.11.14 12:17 Сейчас в теме
(44) если у реквизитов проставлен признак индексировать, то лучше убрать (в конфигураторе)
53. sorb 14.11.14 21:12 Сейчас в теме
(44) Painted, при таком количестве записей индекс получится больше, чем сама таблица, ну и при любых соединениях гарантированно будет использован nested loops из-за малого количества записей
47. jan27 732 11.11.14 12:20 Сейчас в теме
(43) порядок реквизитов не меняет некластерный индекс на кластерный
54. sorb 14.11.14 21:16 Сейчас в теме
(47) да, забыл, что это справочник, а не регистр сведений. Но по сути вопроса имхо в данном случае индексы нужны как рыбе зонтик :)
45. jan27 732 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 7133 14.11.14 11:12 Сейчас в теме
Хочу подсчитать среднюю степень параллелизма запроса. Лучшая оценка будет total_worker_time / total_elapsed_time ?
50. jan27 732 14.11.14 11:45 Сейчас в теме
(49) очень грубая оценка. т.к. total_worker_time может быть меньше total_elapsed_time ввиду ожиданий на задержках. Степень параллелизма указывает в ShowPlan statics
51. tormozit 7133 14.11.14 13:01 Сейчас в теме
(50) Это какого представления колонка "ShowPlan statics" ? Если ты про сам план запроса, то понятно, что там все детально видно. Нужна именно оценка для всего запроса. Понятно, что она будет грубой. Я просто ищу наилучшую.
52. jan27 732 14.11.14 13:04 Сейчас в теме
55. jan27 732 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 732 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 732 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 50 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 732 07.01.15 21:25 Сейчас в теме
(58) что-то ссылка не работает, может просто адрес скинешь?
60. wunderland 201 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 Сейчас в теме
Подпишусь, спасибо за подборку.
Оставьте свое сообщение