Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
sql_new_user
Guest
Есть сильно нагруженная БД OLTP. Сотни, тысячи транзакций в секунду.
База довольно быстро растёт.

Вопрос заключается в том, в чём эффективнее указать авторасширение для .Mdf и .ldf в % или Mb
для повышения производительности?

Не может ли быть такой ситуации, когда массовая вставка приводит к снижению производительности
по причине того, что постоянно выделяется по 10 Мб/100Мб/XМб? И новой транзакции не достаточно
выделенных Х Мб... а в процентах было бы лучше, так как сразу бы места много выделилось.

То ли лучше сразу много места выделять - или помалу но часто?

надеюсь вопрос мой понятен.
8 окт 14, 11:32    [16675156]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Эффективнее сделать так, чтобы автоприращения не было во время массовых вставок.
8 окт 14, 11:40    [16675231]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
sql_new_user
Guest
Гавриленко Сергей Алексеевич
Эффективнее сделать так, чтобы автоприращения не было во время массовых вставок.


Вопрос: как оценить/понять/доказать, что снижение производительности связано с частым
авторасширением базы? Как мониторить каждое новое авторасширение базы?

Чтобы прийти утром на работу, дать запрос/и/или где-то увидеть, что, скажем за день/неделю
N раз выполнялось авторасширение базы по X Мб?

В идеале хочется получить табличку из трёх полей:

1 поле - дата, когда было выполнено авторасширение
2 поле - на сколько увеличилось место при авторасширении в Мб/Гб
3 поле - имя базы, для которое выполнялось данное авторасширение

Можете помочь с таким запросом к системным представлениям, чтобы получить такой результат?
8 окт 14, 11:55    [16675369]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
sql_new_user, можно вытащить из стандартной трассировки

DECLARE @TraceFileName sysname
SELECT @TraceFileName = path  FROM sys.traces WHERE id = 1
 
  SELECT 
         DB_NAME(DatabaseID) AS DatabaseName,
         CASE WHEN EventClass = 92 THEN 'Data File' ELSE 'Log File' END AS FileType,
         CONVERT(varchar(8), StartTime, 112) AS GrowthDate,
         SUM((IntegerData*8)/1024) AS GrowthInMB
    FROM sys.fn_trace_gettable(@TraceFileName, default)
   WHERE EventClass IN (92, 93)
GROUP BY DB_NAME(DatabaseID), EventClass, CONVERT(varchar(8), StartTime, 112)
ORDER BY DatabaseName, GrowthDate 
8 окт 14, 12:27    [16675621]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+

sql_new_user

Вопрос: как оценить/понять/доказать, что снижение производительности связано с частым
авторасширением базы? Как мониторить каждое новое авторасширение базы?



sys.dm_os_waiting_stats
sys.dm_os_latch_stats

Мониторить: Extended Events, например
8 окт 14, 12:28    [16675640]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4539
sql_new_user, SQL Server Database Growth and Autogrowth Settings
+
DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT 
  ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName  
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB 
,(ftg.duration/1000)AS DurMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg 
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id  
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime
8 окт 14, 12:32    [16675668]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4539
Долго я копипастил...
8 окт 14, 12:32    [16675669]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
sql_new_user
Guest
Shakill
sql_new_user, можно вытащить из стандартной трассировки

DECLARE @TraceFileName sysname
SELECT @TraceFileName = path  FROM sys.traces WHERE id = 1
 
  SELECT 
         DB_NAME(DatabaseID) AS DatabaseName,
         CASE WHEN EventClass = 92 THEN 'Data File' ELSE 'Log File' END AS FileType,
         CONVERT(varchar(8), StartTime, 112) AS GrowthDate,
         SUM((IntegerData*8)/1024) AS GrowthInMB
    FROM sys.fn_trace_gettable(@TraceFileName, default)
   WHERE EventClass IN (92, 93)
GROUP BY DB_NAME(DatabaseID), EventClass, CONVERT(varchar(8), StartTime, 112)
ORDER BY DatabaseName, GrowthDate 


Спасибо огромное!
Это то, что нужно!

Скажите, правильно ли я понимаю, если запрос выше не возвращает ни одной записи - значит прироста не было?
но это очень странно... может данные хранятся определённое число времени, а потом удаляются?
8 окт 14, 12:37    [16675708]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
o-o
Guest
sql_new_user,

это значит, что перезатерся дефолтный трейс
8 окт 14, 12:39    [16675721]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
sql_new_user
Guest
2 buser :

почему-то на базе OLTP не возвращает строк...
терзают сомнения, действительно ли не было ни одного авторасширения?

у меня был рестарт службы mssql.
возможно после рестарта данные не сохраняются и с момента рестарта службы
не было ни одного авторасширения. так?
8 окт 14, 12:41    [16675735]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
sql_new_user
Guest
o-o
sql_new_user,

это значит, что перезатерся дефолтный трейс


А в каких случаях он перезатирается? где искать его?
чтобы уберечь от перезатирования
8 окт 14, 12:43    [16675755]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
o-o
Guest
еще раз для нечитателей:

if you’ve installed SQL Server in the default location, the captured trace information for a default instance will be in the fi le C:\Program Files\MicrosoftSQL Server\MSSQL10.MSSQLSSERVER\MSSQL\LOG\Log.trc.
Every time you stop and restart SQL Server, or reach the maximum file size of 20 MB, a new trace file is created with a
sequential numerical suffix, so the second trace file would be Log_01.trc, followed by Log_02.trc, and so on. If all the trace log fi les are removed or renamed, the next trace fi le starts at log.trc again. SQL Server will keep no more than five trace files per instance, so when the
sixth file is created, the earliest one is deleted.
8 окт 14, 12:44    [16675757]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
NickAlex66
Member

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

Правило из BP: по 10% до 10GB, далее по 1GB
ЗЫ: учетке дать права "Запуск операций по обслуживанию тома" или что-то вроде того.
8 окт 14, 12:44    [16675765]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
o-o
Guest
sql_new_user,

извиняюсь за обзывательство, я тоже временами долго набираю :)
никак не уберечь, копировать себе куда-то, ручками или по расписанию
8 окт 14, 12:46    [16675785]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
sql_new_user
Guest
o-o
еще раз для нечитателей:

if you’ve installed SQL Server in the default location, the captured trace information for a default instance will be in the fi le C:\Program Files\MicrosoftSQL Server\MSSQL10.MSSQLSSERVER\MSSQL\LOG\Log.trc.
Every time you stop and restart SQL Server, or reach the maximum file size of 20 MB, a new trace file is created with a
sequential numerical suffix, so the second trace file would be Log_01.trc, followed by Log_02.trc, and so on. If all the trace log fi les are removed or renamed, the next trace fi le starts at log.trc again. SQL Server will keep no more than five trace files per instance, so when the
sixth file is created, the earliest one is deleted.


Очень ценная информация! Честно, я не знал.
Спасибо, буду его бэкапить тогда.

Получается когда мне понадобится посмотреть информацию по авторасширению за предыдущие периоды,
и у меня будет пять файлов-логов на диске, я тогда их заменяю на старые трейсы из бэкапа и запрос работает?

всё правильно?
8 окт 14, 13:00    [16675880]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
sql_new_user
Guest
o-o
еще раз для нечитателей:

if you’ve installed SQL Server in the default location, the captured trace information for a default instance will be in the fi le C:\Program Files\MicrosoftSQL Server\MSSQL10.MSSQLSSERVER\MSSQL\LOG\Log.trc.
Every time you stop and restart SQL Server, or reach the maximum file size of 20 MB, a new trace file is created with a
sequential numerical suffix, so the second trace file would be Log_01.trc, followed by Log_02.trc, and so on. If all the trace log fi les are removed or renamed, the next trace fi le starts at log.trc again. SQL Server will keep no more than five trace files per instance, so when the
sixth file is created, the earliest one is deleted.


а если у меня там пять трейс-логов или, скажем, три, и я выполняю запрос, любезно предоставленный мне выше,
то информация будет браться сразу из пяти/трёх логов? или из самого последнего?
8 окт 14, 13:03    [16675897]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
Владислав Колосов
Member

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

автор
как оценить/понять/доказать, что снижение производительности связано с частым
авторасширением базы? Как мониторить каждое новое авторасширение базы?


Включите Управление - Сбор данных в SSMS.
8 окт 14, 13:03    [16675907]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
sql_new_user, можно создать свою отдельную трассировку только на эти события и поставить ей ограничение на размер побольше
8 окт 14, 13:04    [16675913]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
o-o
Guest
sql_new_user
а если у меня там пять трейс-логов или, скажем, три, и я выполняю запрос, любезно предоставленный мне выше,
то информация будет браться сразу из пяти/трёх логов? или из самого последнего?


fn_trace_gettable ( 'filename' , number_files )

Arguments
' filename '
Specifies the initial trace file to be read. filename is nvarchar(256), with no default.

number_files
Specifies the number of rollover files to be read. This number includes the initial file specified in filename. number_files is an int.

Remarks
If number_files is specified as default, fn_trace_gettable reads all rollover files until it reaches the end of the trace
8 окт 14, 13:12    [16675954]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
sql_new_user
Guest
o-o
sql_new_user
а если у меня там пять трейс-логов или, скажем, три, и я выполняю запрос, любезно предоставленный мне выше,
то информация будет браться сразу из пяти/трёх логов? или из самого последнего?


fn_trace_gettable ( 'filename' , number_files )

Arguments
' filename '
Specifies the initial trace file to be read. filename is nvarchar(256), with no default.

number_files
Specifies the number of rollover files to be read. This number includes the initial file specified in filename. number_files is an int.

Remarks
If number_files is specified as default, fn_trace_gettable reads all rollover files until it reaches the end of the trace

спасибо! разжевали, в рот положили)
8 окт 14, 13:13    [16675969]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
o-o
Guest
sql_new_user
Получается когда мне понадобится посмотреть информацию по авторасширению за предыдущие периоды,
и у меня будет пять файлов-логов на диске, я тогда их заменяю на старые трейсы из бэкапа и запрос работает?

в запросе просто новое расположение файлов укажите: fn_trace_gettable ( 'filename' , number_files )
8 окт 14, 13:26    [16676077]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
NickAlex66
Правило из BP: по 10% до 10GB, далее по 1GB
ЗЫ: учетке дать права "Запуск операций по обслуживанию тома" или что-то вроде того.
Если это действительно из BP, то это очень дурацкое правило, которое подразумевает, что кто-то должен сидеть и мониторить, а не стал ли файл данных больше 10ГБ и не пора ли менять настройки? А если файлов сотни? Проще один раз настроить и забыть.
Есть другое правило, намного лучше: НИКОГДА, ни при каких обстоятельствах не выставлять автоприрост в процентах. Прирост не должен быть очень маленьким - будет файловая фрагментация и не очень большим, а то время на расширение файла превисит время таймаутов для мелких OLTP запросов. А если мы указываем %, то сначала, пока файлы небольшие, то прирост будет мизерных, а потом наоборот огромным. Толку никакого от этих процентов. Если не хочется сильно заморачиваться, то поставьте около 500МБ всем файлам и забудьте об этом. Ну и как уже было посеветовано, включите Instant File Initialization

Если хочется заморочиться, то автоприращений в идеале вообще не должно быть. Автоприращение это на случай аварийной ситуации. А так, нужно мониторить количество свободного места в файла и раширять файлы вручную по мере необходимости.
sql_new_user
o-o
Remarks
If number_files is specified as default, fn_trace_gettable reads all rollover files until it reaches the end of the trace

спасибо! разжевали, в рот положили)
Этот самый DEFAULT будет работать только в том случае, если имя файла указано в общем виде, как log.trc, а не как log_39.trc. Короче, вариант запроса, который вам дал buser обработает все файлы, в отличие от запроса Shakill где сервер покажет вам данные только одного трейс файла.

Shakill
sql_new_user, можно создать свою отдельную трассировку только на эти события и поставить ей ограничение на размер побольше
Если уж очень захочется, то лучше повесить Extended Event сессию, вместо трассировки. Событие называется database_file_size_change, включает в себя все изменения всех файлов.
Но я бы не тратил на это время. Только если в ознакомительных целях.
8 окт 14, 20:42    [16678766]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
NickAlex66
Member

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

"кто-то должен сидеть и мониторить" - джоб обслуживания оч хорошая сиделка - в нужное время скорректирует автоприрост и расширит базу, если нужно.
8 окт 14, 21:32    [16678937]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
NickAlex66
Mind,

"кто-то должен сидеть и мониторить" - джоб обслуживания оч хорошая сиделка - в нужное время скорректирует автоприрост и расширит базу, если нужно.
10% для файла в 10 МБ это 1МБ. Смысла никакого, один сплошной вред. Почему сразу не настроить нормально? Зачем нужен джоб, который будет АВТОматически настраивать АВТОприращение?
8 окт 14, 21:54    [16679003]     Ответить | Цитировать Сообщить модератору
 Re: В чём эффективнее указать авторасширение для базы в % или Mb для сильнонагруженных OLTP?  [new]
o-o
Guest
ой!
скрипт buser-а спрятан, если б не замечание Mind-а, мог бы вовсе остаться незамеченным.

а что такое :: в ::fn_trace_gettable, простите?
я только знаю такое :: при выдаче прав.

...и даже непонятно, на что гуглить, чтобы найти объяснение.
расскажите, кто знает, плиз!
8 окт 14, 21:56    [16679009]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить