Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Производительность  [new]
Sertik
Member

Откуда:
Сообщений: 44
Доброго времени суток, имеется MS SQL Server 2008R2, толстая база (400Г.).
Вопрос в том, как выяснить текущую производительность сервера, запросов и прочее. Есть ли какой-то алгоритм, который позволит составить эдакий базовый отчет о производительности и от него дальше плясать.
К примеру обновил статистики или добавил индекс и посмотреть на сколько увеличилась производительность.
Знаю, что профайлером можно отслеживать время выполнения запросов и по этому можно расчитать. Перфмоном собирать данные по счетчикам (но каким именно?).
Буду благодарен за ссылки или объяснения как вы собираете подобную инфу.
3 апр 17, 13:48    [20360747]     Ответить | Цитировать Сообщить модератору
 Re: Производительность  [new]
gerogekochkin
Member

Откуда: Москва
Сообщений: 93
Sertik
Доброго времени суток, имеется MS SQL Server 2008R2, толстая база (400Г.).
Вопрос в том, как выяснить текущую производительность сервера, запросов и прочее. Есть ли какой-то алгоритм, который позволит составить эдакий базовый отчет о производительности и от него дальше плясать.
К примеру обновил статистики или добавил индекс и посмотреть на сколько увеличилась производительность.
Знаю, что профайлером можно отслеживать время выполнения запросов и по этому можно расчитать. Перфмоном собирать данные по счетчикам (но каким именно?).
Буду благодарен за ссылки или объяснения как вы собираете подобную инфу.

По поводу счетчиков PerfMon есть хорошая статья Brent Ozar:

https://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

По поводу оценки производительности - это опять же счетчики производительности, показатели времени отклика дисков, загруженность CPU, а самое главное - с какой целью вы взялись за выяснение "базового" отчета? Жалуются пользователи или руководство поставило задачу, потому что надо?
3 апр 17, 13:59    [20360793]     Ответить | Цитировать Сообщить модератору
 Re: Производительность  [new]
Sertik
Member

Откуда:
Сообщений: 44
За ссылку спасибо, почитаю.
А по поводу отчета, это и для себя (потому что скоро будет оптимизированы диски, дефрагментация индексов и действительно надо проследить), но и для начальства (куда без них), чтобы они видели что не зря работаем.
3 апр 17, 14:05    [20360823]     Ответить | Цитировать Сообщить модератору
 Re: Производительность  [new]
Владислав Колосов
Member

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

Обозреватель объектов - управление - сбор данных и т.д.
Дисковое место потребуется.
3 апр 17, 14:14    [20360872]     Ответить | Цитировать Сообщить модератору
 Re: Производительность  [new]
gerogekochkin
Member

Откуда: Москва
Сообщений: 93
Sertik
За ссылку спасибо, почитаю.
А по поводу отчета, это и для себя (потому что скоро будет оптимизированы диски, дефрагментация индексов и действительно надо проследить), но и для начальства (куда без них), чтобы они видели что не зря работаем.

Думаю, что вам нужно запустить сбор счетчиков до проведения работ и сохранить результат. Затем запустить сбор после и сравнить. Сохранение и обработка результатов, как раз хорошо описаны в статье.
Счетчики покажут вам как вели себя память, cpu, диски, tempdb, ваши baseline для чекпоинтов, соединений и так далее.
Также порекомендовал бы пообщаться непосредственно с пользователями по поводу скорости выполнения их рутинных/периодических операций до и после проведения работ. На сайте, указанном в моей ссылке есть много абсолютно бесплатных хп, которые помогут вам в анализе тех или иных аспектов проивзодительности и лучших практик.
Также - у меня есть список счетчиков PerfMon, который я немного дополнил, основываясь на многих других материалах с пояснениями - во вложении.

К сообщению приложен файл (temp.docx - 16Kb) cкачать
3 апр 17, 14:21    [20360925]     Ответить | Цитировать Сообщить модератору
 Re: Производительность  [new]
Sertik
Member

Откуда:
Сообщений: 44
gerogekochkin, спасибо большое.

Владислав Колосов, я думал об этом, но на данный момент это не выполнимо, а жаль.
3 апр 17, 14:41    [20361029]     Ответить | Цитировать Сообщить модератору
 Re: Производительность  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
по длительности выполнения запросов можно понять.

А рессурсы сервера после оптимизации могут быть и сильнее загружены в итоге, например, оптимизировали io - выросла нагрузка на cpu и тп
3 апр 17, 14:46    [20361055]     Ответить | Цитировать Сообщить модератору
 Re: Производительность  [new]
o-o
Guest
a.tarannikova
по длительности выполнения запросов можно понять.

А рессурсы сервера после оптимизации могут быть и сильнее загружены в итоге, например, оптимизировали io - выросла нагрузка на cpu и тп

поразительное упорство.
другим указывать время есть, позаботиться о своей битой базе -- нет.
дорогой sql_user2, тебя ведь именно это и выдало

to sertik:
вот например
Capturing Baselines on SQL Server: Wait Statistics
3 апр 17, 15:26    [20361287]     Ответить | Цитировать Сообщить модератору
 Re: Производительность  [new]
Владислав Колосов
Member

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

нестатистичечские методы - от лукавого... Лучше всего собрать статистику за квартал. Уж изыщите ресурсы, если хотите не просто время убить.
3 апр 17, 18:21    [20362313]     Ответить | Цитировать Сообщить модератору
 Re: Производительность  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Интересно что никто не посоветовал статистику ожиданий глянуть.
И вообще подход странный, я добавил индекс и жду чтобы система залетала, индексы добаляются под определенные запросы, а не от нечего делать, вот и смотрите насколько они быстрее статнут.
Если же вы решили совсем заморочится то смотрите sys.dm_os_wait_stats и sys.dm_io_virtual_file_stats. Потом посмотрите sys.dm_exec_query_stats. Посмотрите счетчики:
+
- SQLServer:Access Methods\Full Scans/sec
- SQLServer:Access Methods\Index Searches/sec
- SQLServer:Buffer Manager\Lazy Writes/sec
- SQLServer:Buffer Manager\Page life expectancy
- SQLServer:Buffer Manager\Free list stalls/sec
-SQLServer:Buffer Manager\Free Pages
- SQLServer:General Statistics\Processes Blocked
- SQLServer:General Statistics\User Connections
- SQLServer:Locks\Lock Waits/sec
- SQLServer:Locks\Lock Wait Time (ms)
- SQLServer:Memory Manager\Memory Grants Pending
- SQLServer:SQL Statistics\Batch Requests/sec
- SQLServer:SQL Statistics\SQL Compilations/sec
- SQLServer:SQL Statistics\SQL Re-Compilations/sec
- SQLServer:SQL Statistics\Auto Param Attempt/sec и SQLServer:SQL Statistics\Failed Auto Param/sec - для определения Ad hoc queries
- System: Processor Queue Length
- Memory grants outstanding и memory grants pending
- Счетчики Physical Disk: Avg Disk sec\Write или sec\Read - порог 0.15 мск, в реале должно быть даже меньше, в особенности для SAN.
Также латентность операций записи должна быть меньше латентности операций чтения, иначе, возможно, перегружена вся дисковая система.

- Соотношение Index Searches и Index Scan в районе 1000 и более.
- Lazy Writes срабатывает когда у SQL заняты около 75% выделенных буферов, в иделае -> 0
- PLE (buffer pool memory \ 4) * 300
- Free List stalls - кол-во запросов в секунду которым пришлось ждать свободной страницы (<2)
- Free Pages > 640 - кол-во свободных страниц.
- Processes Blocked - кол-во заблокированных процессов, в иделае -> 0.
- User connections - кол-во соеденений
- Lock Waits/sec - кол-во раз в секунду когда SQL не может сразу наложить блокировку, в идеале -> 0.
- Lock Wait Time - общее время ожиданий в миллисекундах для блокировок в последнюю секунду
- Memory Grants Pending - кол-во процессов ожидающих выделение памяти, должно быть в районе 1 или меньше. (если нет, возможно стоит добавить памяти)
- Batch requests - кол-во запросов в секунду которые получает сервер.
Соотношение между SQL Compilation и Batch Requests не более 0.1 (иначе, возможно SQL активно исп. динамический SQL код. Чтобы убедиться нужно посмотреть состав процедурного кеша: Cache Pages, обьект Plan Cache, тип SQL Plans).
Соотношение Workfiles Created\sec и Batch Requests не более 0.2 (workfile содержит стр. не обьеденные IAM и исп. их для внутренних нужд, к примеру для операций хещирование, что может свидетельствовать об отсутствии индексов).
- Соотношение SQL Recompilations и SQL Compilation менее 0.1
- SQL Server: Buufer Manager: Page Reads\sec сколько буфер читает с диска (не более 90...100)
- SQL Server: Access Method: Page Splits/sec < чем 20% Batch Requests (кол-во раз когда SQL при обновлении или вставке разбивал страницу)
также надо учитывать что по многим нет плохо или хорошо It depends... все зависит от вашей системы и возможно что то будет нормой.
Проверьте включены ли у вас флаги 1117 и 1118, нужно для этого
Из банального, как у вас файлы расположены, какой рейд (как то был в одной котнторе, у них рейд 5, лог и данные на одном диске, и они в 10 утра делают full backup на тот же диск который длится 3 часа, в обшем совсем все плохо)
Посмотрите фрагментации вашего журнала тут, в 2014 алгоритм изменился тут
Верно ли у вас установлен max server memory тут
Как построены планы обслуживания (статистика, индексы, backup и т.д.), и это тоже зависит только от вашей системы, ваших RPO и RTO.
Посмотрите лишние или недостающие индексы:
+

SELECT OBJECT_NAME(i.object_id) AS [Table Name],
i.name AS [Not Used Index Name],
case when i.is_unique = 1 then 'UNIQUE ' else '' end + i.type_desc,
s.last_user_update AS [Last Update Time],
s.user_updates AS [Updates]--, i.*
FROM sys.dm_db_index_usage_stats AS s
JOIN sys.indexes AS i
ON i.object_id = s.object_id
AND i.index_id = s.index_id
JOIN sys.objects AS o
ON o.object_id = s.object_id
WHERE s.database_id = DB_ID()
AND ( user_scans = 0
AND user_seeks = 0
AND user_lookups = 0
AND last_user_scan IS NULL
AND last_user_seek IS NULL
AND last_user_lookup IS NULL
)
AND OBJECTPROPERTY(i.[object_id], 'IsSystemTable' ) = 0
AND INDEXPROPERTY (i.[object_id], i.name, 'IsAutoStatistics') = 0
AND INDEXPROPERTY (i.[object_id], i.name, 'IsHypothetical' ) = 0
AND INDEXPROPERTY (i.[object_id], i.name, 'IsStatistics' ) = 0
AND INDEXPROPERTY (i.[object_id], i.name, 'IsFulltextKey' ) = 0
AND i.is_primary_key = 0
AND (i.index_id between 2 AND 250 OR (i.index_id=1 AND OBJECTPROPERTY(i.[object_id],'IsView')=1))
AND o.type <> 'IT'
ORDER BY Updates desc , OBJECT_NAME(i.object_id)

SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
and database_id = DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

из того что на ходу вспомнил, и тут можно еще писать и писать, нет такой кнопки сделать все хорошо также как нет такой кнопки как показать все ли хорошо, многое зависит от вашей системы.
4 апр 17, 05:54    [20363381]     Ответить | Цитировать Сообщить модератору
 Re: Производительность  [new]
aleksrov
Member

Откуда:
Сообщений: 948
А, нет, посоветовали, о-о не заметил сообщение.
4 апр 17, 06:39    [20363394]     Ответить | Цитировать Сообщить модератору
 Re: Производительность  [new]
o-o
Guest
aleksrov
Интересно что никто не посоветовал статистику ожиданий глянуть.

Да неужели? 20361287
4 апр 17, 06:40    [20363395]     Ответить | Цитировать Сообщить модератору
 Re: Производительность  [new]
Sertik
Member

Откуда:
Сообщений: 44
Спасибо всем большое.
4 апр 17, 07:26    [20363422]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить