Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
Подскажите, пожалуйста, как можно выжать максимум производительности Microsoft SQL Server 2008 R2 Enterprise?

Характеристики сервера.
DELL PowerEdge™ R720 12th Generation DX290
CPU: Dual Intel® Xeon® E5-2620 Hexa Core incl. Hyper-Threading Technology
RAM: 128 GB DDR3 ECC
HDD: RAID 10

SQL Server 2008 R2 Enterprise хостится на ESX которая установлена на выше озвученный сервер.
Серверу выделено:
CPU: 2 процессорных ядра.
RAM: 36 GB
HDD: RAID 10.
Системный раздел: 100 GB
Раздел LOGS: 50 GB
Раздел BASE: 210 GB
Раздел BACKUP: 200 GB

Все разделы кроме системного и для резервного копирования выравнены DiskPart'ом по 64кб.
База tempdb вместе со своим логом лежит в C:\tempdb\
Все базы лежат на диске E:\Microsoft SQL Server\Data
Все логи лежат на диске D:\Microsoft SQL Server\Log

Для приведения базы в порядок был применен скрипт найденный на просторах интернета и оттестированный на тестовом серваке:

DECLARE @IsDetailedScan BIT
SELECT @IsDetailedScan = 1

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
	SELECT '
	ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
		CASE WHEN s.avg_fragmentation_in_percent > 0
			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, 
								CASE WHEN @IsDetailedScan = 1 
									THEN 'DETAILED'
									ELSE 'LIMITED'
								END) s
		WHERE s.page_count > 128 -- > 1 MB
			AND s.index_id > 0 -- <> HEAP
			AND s.avg_fragmentation_in_percent > 0
		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]
	FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
	OPTION (OPTIMIZE FOR (@IsDetailedScan = 1))

PRINT @SQL
EXEC sys.sp_executesql @SQL


Ежедневно выполняется вот такой вот план обслуживания:
http://s005.radikal.ru/i212/1404/0c/194585c31639.png

Также использовался SQL Server Profiler в режиме Tuning.
Два часа собирания статистики и последующий анализ в Помощник по настройке ядра СУБД показали что улучшить базу можно на 82%. Рекомендации были применены. База стала шустрей работать.

Хочется понять, какие еще методы можно применить чтобы понять что можно еще улучшить.
25 апр 14, 10:31    [15933669]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
курсоры, кривые запросы, скалярные функции с обращением к таблицам. Мониторинг блокировок, взаимоблокировок и рефакторинг запросов для решения этих проблем. В общем собираете информацию что медленно работает, что где блокируется, потом планомерно улучшаете, если есть возможность.
25 апр 14, 11:00    [15933940]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
Что подразумевается под "кривыми запросами"? Из 1С в SQL?
Как можно посмотреть "Мониторинг блокировок", "Взаимоблокировок"?

Информацию по остальным пунктам вроде нагуглил.
25 апр 14, 23:40    [15938857]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 16943
автор
База tempdb вместе со своим логом лежит в C:\tempdb\

уже смешно
26 апр 14, 01:09    [15939170]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
msLex
Member

Откуда:
Сообщений: 8191
ScareCrow
автор
База tempdb вместе со своим логом лежит в C:\tempdb\

уже смешно

что именно, "вместе со своим логом" или "C:\tempdb\"?
если первое - это не смертельно.
если второе - то под именем "C:\tempdb\" вполне может "скрываться" 10 рейд из n-го количества SAS дисков
27 апр 14, 12:33    [15942237]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
Ckey
Member

Откуда:
Сообщений: 56
Привет, какая задача скрипта указанного в первом топике с вопросом?

Спасибо
27 апр 14, 14:52    [15942448]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
andy_111
Member

Откуда: Москва
Сообщений: 39
Простите, может так выходные на меня влияют и я не врубаюсь. Судя по скрипту, в нем при степени фрагментации больше 0 делается REBUILD, а при нулевой степени фрагменатции (то есть когда ее ВООБЩЕ нет) - REORGANIZE???
По общим рекомендациям майкрософта (конечно, зависящим от многих вещей) REORGANIZE делается при фрагментации от 5 до 30, а при более высокой степени фрагментации - REBUILD. У вас же вообще все наоборот - REBUILD делается практически всегда (при хотя бы какой-то фрагментации, хоть в доли процента), а REORGANIZE делается, когда фрагментации вообще нет! Так вам никаких серверов не хватит, у вас все ресурсы будут уходить на бесконечные ненужные перестройки индексов.

Вот здесь неплохая тема Дефрагментация индексов со сбором статистики MS SQL 2008 R2 с очень неплохим скриптом обслуживания. Еще почитайте дискуссию в обсуждениях этой темы - там приведены интересные мысли о минимальном влиянии фрагментации индекса на производительность.
27 апр 14, 16:12    [15942623]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
andy_111
Member

Откуда: Москва
Сообщений: 39
Вот здесь https://www.simple-talk.com/books/ лежат очень неплохие бесплатные книги. Советую сначала обратить внимание на книги

SQL Server Performance Tuning Using Wait Statistics: A Beginner's Guide

Troubleshooting SQL Server: A Guide for the Accidental DBA by Jonathan Kehayias and Ted Kreuger

Performance Tuning with SQL Server Dynamic Management Views
27 апр 14, 16:24    [15942654]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
msLex,

автор
что именно, "вместе со своим логом" или "C:\tempdb\"?
если первое - это не смертельно.
если второе - то под именем "C:\tempdb\" вполне может "скрываться" 10 рейд из n-го количества SAS дисков


База tempdb лежит в папке tempdb на диске С.
В этой же папке лежит templog.



andy_111,
автор
ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
CASE WHEN s.avg_fragmentation_in_percent > 0


Простите,может я чего-то не понял.
строчка CASE WHEN s.avg_fragmentation_in_percent > 0
говорит что если фрагментация больше 0
ТО надо сделать
THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON'

ИНАЧЕ
ELSE 'REORGANIZE'


Честно говоря изначально если я правильно помню в этом скрипте вместо моих нулей стояли цифры 5 и 30.
Это уже мое художество.
28 апр 14, 09:50    [15944641]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
Помогите пожалуйста разобраться со скриптом ссылку на который дал andy_111 [url=]http://habrahabr.ru/post/155933/[/url]

Создал тестовую базу. Использую первую часть скрипта

DECLARE @currentProcID INT --Порядковый номер процедуры дефрагментации
--Выбираем последний номер, и просто добавляем единичку
SELECT @currentProcID = ISNULL(MAX(proc_id), 0) + 1 FROM dba_tasks.dbo.index_defrag_statistic
--И заполняем таблицу данными о состоянии индексов
INSERT INTO dba_tasks.dbo.index_defrag_statistic (
    proc_id,
    database_id,
    [object_id],
    table_name,
    index_id,
    index_name,
    avg_frag_percent_before,
    fragment_count_before,
    pages_count_before,
    fill_factor,
    partition_num)
SELECT
    @currentProcID,
    dm.database_id,
    dm.[object_id],
    tbl.name,
    dm.index_id,
    idx.name,
    dm.avg_fragmentation_in_percent,
    dm.fragment_count,
    dm.page_count,
    idx.fill_factor,
    dm.partition_number
FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm
    INNER JOIN sys.tables tbl ON dm.object_id = tbl.object_id
    INNER JOIN sys.indexes idx ON dm.object_id = idx.object_id AND dm.index_id = idx.index_id
WHERE page_count > 8
    AND avg_fragmentation_in_percent > 10
    AND dm.index_id > 0


Выскакивает вот такая ошибка:
Сообщение 208, уровень 16, состояние 1, строка 3
Недопустимое имя объекта "dba_tasks.dbo.index_defrag_statistic".

Что надо сделать?Как заставить скрипт работать?
28 апр 14, 21:33    [15949172]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
serpentariy
Member

Откуда:
Сообщений: 265
M1chA77,
Может создать объект dba_tasks.dbo.index_defrag_statistic?
28 апр 14, 21:45    [15949225]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
serpentariy,

Как и где,подскажи пожалуйста. Только стал вникать.
28 апр 14, 21:55    [15949258]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
serpentariy
Member

Откуда:
Сообщений: 265
M1chA77,
Ну дак посмотрите там, где вы взяли данный скрипт, как определяется dba_tasks.dbo.index_defrag_statistic
28 апр 14, 22:16    [15949334]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
serpentariy,

Никак не определяется...
вот http://habrahabr.ru/post/155933/ скрипт
28 апр 14, 22:21    [15949347]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
igr_ok
Member

Откуда:
Сообщений: 170
M1chA77,

Там же ясно написано "Но начнем с простого, создадим таблицу для хранения этих самых данных". И приведена таблица
28 апр 14, 22:23    [15949350]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
igr_ok,

я создал новую базу (testdb) и в ней создал таблицу со всеми параметрами приведенными в статье
28 апр 14, 22:26    [15949355]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
igr_ok
Member

Откуда:
Сообщений: 170
M1chA77
igr_ok,

я создал новую базу (testdb) и в ней создал таблицу со всеми параметрами приведенными в статье
А автор назвал свою базу dba_tasks.
28 апр 14, 22:35    [15949374]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
igr_ok,

переименовал свою базу в dba_tasks. запустил скрипт. все равно та же ошибка выскакивает.
28 апр 14, 22:39    [15949391]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
igr_ok
Member

Откуда:
Сообщений: 170
M1chA77
igr_ok,

переименовал свою базу в dba_tasks. запустил скрипт. все равно та же ошибка выскакивает.
И таблицу назвал index_defrag_statistic?
28 апр 14, 22:46    [15949403]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
igr_ok,

Спасибо! Первая часть скрипта заработала.
Помоги пожалуйста со второй частью.

--Обьявим необходимые переменные
DECLARE @partitioncount INT --Количество секций
DECLARE @action VARCHAR(10) --Действие, которые мы будем делать с индексом
DECLARE @start_time DATETIME --Начало выполнения запроса ALTER INDEX
DECLARE @end_time DATETIME --Конец выполнения запроса ALTER INDEX
--см описание таблицы
DECLARE @object_id INT 
DECLARE @index_id INT
DECLARE @tableName VARCHAR(250) 
DECLARE @indexName VARCHAR(250)
DECLARE @defrag FLOAT
DECLARE @partition_num INT
DECLARE @fill_factor INT
--Сам запрос, который мы будем выполнять, я поставил MAX, потому как иногда меняю такие скрипты, и забываю поправить размер данной переменной, в результате получаю ошибку.
DECLARE @sql NVARCHAR(MAX)

--Далее объявляем курсор
DECLARE defragCur CURSOR FOR
    SELECT 
        [object_id], 
        index_id, 
        table_name, 
        index_name, 
        avg_frag_percent_before, 
        fill_factor, 
        partition_num 
    FROM dba_tasks.dbo.index_defrag_statistic
    WHERE proc_id = @currentProcID
    ORDER BY [object_id], index_id DESC --Сначала не кластерные индексы

OPEN defragCur
FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num
WHILE @@FETCH_STATUS=0
BEGIN
    SET @sql = N'ALTER INDEX ' + @indexName + ' ON ' + @tableName

    SELECT @partitioncount = count (*)
    FROM sys.partitions
    WHERE object_id = @object_id AND index_id = @index_id;
    
    --В моем случае, важно держать неможко пустого места на страницах, потому, что вставка в тоже таблицы имеете место, и не хочеться тратить драгоценное время пользователей на разбиение страниц
    IF (@fill_factor != 80)
    BEGIN
        @sql = @sql + N' REBUILD WITH (FILLFACTOR = 80)'
        SET @action = 'rebuild80'
    END
    ELSE
    BEGIN --Тут все просто, действуем по рекомендации MS
        IF (@defrag > 30) --Если фрагментация больше 30%, делаем REBUILD
        BEGIN
            SET @sql = @sql + N' REBUILD'
            SET @action = 'rebuild'
        END
        ELSE --В противном случае REORGINIZE
        BEGIN
            SET @sql = @sql + N' REORGANIZE'
            SET @action = 'reorginize'
        END
    END
    
    --Если есть несколько секций
    IF @partitioncount > 1
        SET @sql = @sql + N' PARTITION=' + CAST(@partition_num AS nvarchar(5))
    
    print @sql --Вывод выполняемого запроса
    
    --Фиксируем время старта
    SET @start_time = GETDATE()
    EXEC sp_executesql @sql
    --И время завершения
    SET @end_time = GETDATE()
    
    --Сохраняем время в таблицу
    UPDATE dba_tasks.dbo.index_defrag_statistic
    SET 
        start_time = @start_time,
        end_time = @end_time,
        [action] = @action
    WHERE proc_id = @currentProcID
        AND [object_id] = @object_id
        AND index_id = @index_id
    
    FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num
END
CLOSE defragCur
DEALLOCATE defragCur


Выскакивают вот такие ошибки
Сообщение 137, уровень 15, состояние 2, строка 28
Необходимо объявить скалярную переменную "@currentProcID".
Сообщение 102, уровень 15, состояние 1, строка 44
Неправильный синтаксис около конструкции "@sql".
Сообщение 156, уровень 15, состояние 1, строка 47
Неправильный синтаксис около ключевого слова "ELSE".
Сообщение 137, уровень 15, состояние 2, строка 79
Необходимо объявить скалярную переменную "@currentProcID".
Сообщение 156, уровень 15, состояние 1, строка 85
Неправильный синтаксис около ключевого слова "CLOSE".
28 апр 14, 22:51    [15949410]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
Вот созданная мной таблица
Картинка с другого сайта.
28 апр 14, 22:56    [15949419]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
igr_ok
Member

Откуда:
Сообщений: 170
M1chA77
igr_ok,
Спасибо! Первая часть скрипта заработала.
Помоги пожалуйста со второй частью.
DECLARE @currentProcID INT
там где "Сообщение 102, уровень 15, состояние 1, строка 44
Неправильный синтаксис около конструкции "@sql"."
пропущено SET

P.S. Если ты синтаксиса не знаешь, как ты запросы пишешь?
28 апр 14, 23:23    [15949500]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
igr_ok,

Не знаю синтаксиса. Поэтому и прошу помочь. Накачал книжек по MS SQL,буду изучать.

Осталось две ошибки. Помоги пожалуйста добить скрипт. Хоть немного понимание стал обретать.

Сообщение 137, уровень 15, состояние 2, строка 28
Необходимо объявить скалярную переменную "@currentProcID".
Сообщение 137, уровень 15, состояние 2, строка 79
Необходимо объявить скалярную переменную "@currentProcID".
28 апр 14, 23:30    [15949528]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
igr_ok,

Добавил
DECLARE @currentProcID INT
в самое начало, к остальным DECLARE @ - ошибка пропала

Запустил скрипт. Пишет
[b]Выполнение команд успешно завершено.[/b]
т.е все хорошо.

Запускаю третью часть скрипта, которая соберает информацию о индексах после процедуры дефрагментации:

UPDATE dba
SET
    dba.avg_frag_percent_after = dm.avg_fragmentation_in_percent,
    dba.fragment_count_after = dm.fragment_count,
    dba.pages_count_after = dm.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm
    INNER JOIN dba_tasks.dbo.index_defrag_statistic dba 
        ON dm.[object_id] = dba.[object_id] 
            AND dm.index_id = dba.index_id
WHERE dba.proc_id = @currentProcID
    AND dm.index_id > 0


Выдает ошибку
Сообщение 137, уровень 15, состояние 2, строка 10
Необходимо объявить скалярную переменную "@currentProcID".


Добавляю перед
UPDATE dba

DECLARE @currentProcID INT


выдает сообщение
Выполнение команд успешно завершено.


Все правильно или что-то должно быть по другому?
28 апр 14, 23:42    [15949570]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация MS SQL Server 2008 R2  [new]
M1chA77
Member

Откуда: Самара
Сообщений: 64
Почему спрашиваю, что все нормально или нет потому что на сайте написано

автор
После выполнения такого скрипта, можно получить и посчитать очень много полезной информации. Например, время обслуживания всех индексов и каждого отдельно. Понять как это связано с размером индекса, увидеть эффективность данной операции. Собрав такую информацию за несколько раз, можно немного поменять процедуру, наверняка какие-то индексы фрагментируются больше и быстрее.
28 апр 14, 23:45    [15949585]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить