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

Откуда:
Сообщений: 16
Всем добра. народ есть база 100 000 гигов стоит простая модель восстановления

Итак суть вопроса зашел на оф сайт мелкомягких нашел статью по оптимизации работы BD, так вот в ней написано, что если база больше 200 метров то желательно поставить автоувеличение tempdb на 10 процентов.

Из многих статей в инете нашел закономерность при связке 1с и SQl размеры ставятся 200 метров на базу увеличение и 50 метров на лог.


Так где же истина при моей базе в 100 000 гигов , так же интересует вопрос какими должны быть параметры автоувеличения при базах в 5 10 20 гигов.

Перерыл весь гугл не чего дельного показывающие закономерность не нашел.

Прошу помощи у гуру с небольшим описание что к чему и почему.


Спасибо!!!
17 май 16, 15:28    [19184405]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
Владислав Колосов
Member

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

ставьте требуемый максимальный размер и отключите автоприращение. Как определить требуемый размер? Для малой базы установите автоприращение 200мб, для большой 1000мб. Дождитесь завершения квартальной отчетности. Прибавьте 5% к размеру и уберите автоприращение. Во время техобслуживания остановите SQL сервер и дефрагментируйте раздел диска с базами, если это не хранилище. Запустите сервис. Теперь не будет затрат на рост tempdb и фрагментацию. С журналом можно так же поступить. Пока не создадут отчет, выбирающий 100 Гб во временную таблицу :)
17 май 16, 15:43    [19184511]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
ramshik
Member

Откуда:
Сообщений: 16
А чем плохо автоприращение ?


Если параметры "Для малой базы установите автоприращение 200мб, для большой 1000мб"

какие тогда будут для лога ?


И как разделить понятие маленькая БД и большая по размерам в ГБ.
17 май 16, 15:47    [19184543]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
Владислав Колосов
Member

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

зачем Вам затраты на приращение, если известен конечный размер?

Найдите 10% в Мб от текущего размера для автоприращения. Не имеет значения.
17 май 16, 15:53    [19184598]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
ramshik
Member

Откуда:
Сообщений: 16
Размер БД со временем растет так как хранятся данные с отчетами и.т.д

Так же в БД заносятся новые позиции товара с описанием и картинками.

Размеры хранилища 10 рейд из 4х SSD позволяют еще работать долго без вопроса о памяти при 90 гиговой база темл лога 23 гига.


Хочется просто разобраться в логике нарощения от чего зависит, и какие есть зависимости.

Пользователей у меня 120 человек. каких то жутких запросов в БД можно сказать не бывает.
17 май 16, 15:59    [19184646]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
o-o
Guest
ramshik
есть база 100 000 гигов
...
при моей базе в 100 000 гигов

у вас база в 100Тб?
очень хорошее надо темпдб,
чтобы DBCC CHECKDB отработало на такой базе.
а не расскажете (для расширения кругозора)
сколько времени длится чекдб на базе в 100Тб?
у нас суммарный размер баз всего 8Тб,
но чекдб по всем базам сутки пашет.
а тут сотня.
это нашими темпами недели бы не хватило
17 май 16, 17:05    [19185114]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
o-o,

и это всё на 4х SSD :)
17 май 16, 17:17    [19185180]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31427
Владислав Колосов
зачем Вам затраты на приращение, если известен конечный размер?
Откуда будут затраты на автоприращение, которое никогда не произойдёт?
Чем плохо сделать базу необходимого размера, и установить автоприращение???
17 май 16, 18:07    [19185391]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
alexeyvg
Владислав Колосов
зачем Вам затраты на приращение, если известен конечный размер?
Откуда будут затраты на автоприращение, которое никогда не произойдёт?
Чем плохо сделать базу необходимого размера, и установить автоприращение???
Ну как чем, если отключить автоприращение, то в какой то момент база таки навернется и тогда позовут незаменимого ДБА, чтобы все пофиксал.
17 май 16, 22:35    [19186263]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
alexeyvg
Владислав Колосов
зачем Вам затраты на приращение, если известен конечный размер?
Откуда будут затраты на автоприращение, которое никогда не произойдёт?
Чем плохо сделать базу необходимого размера, и установить автоприращение???

Я об этом и писал - сразу установить.
18 май 16, 13:28    [19188369]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Mind
alexeyvg
пропущено...
Откуда будут затраты на автоприращение, которое никогда не произойдёт?
Чем плохо сделать базу необходимого размера, и установить автоприращение???
Ну как чем, если отключить автоприращение, то в какой то момент база таки навернется и тогда позовут незаменимого ДБА, чтобы все пофиксал.


А этом и фишка - так сразу г-но код можно вычислить или безумных пользователей, которые выполняют запросы всего по всему, или зациклившиеся изменения или... много еще чего можно открыть для себя, если ограничить рост tempdb.
18 май 16, 13:30    [19188386]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Владислав Колосов

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


Ага особенно когда генеральный придет к главному бухгалтеру за сальдо на конец прошлого года, внезапно, а тут херак и вместо цифер ошибка, думаю в этом случае больше всего нового для себя откроет как раз ДБА:)
18 май 16, 13:50    [19188488]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
KoDT
Member

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

Ну как чем, если отключить автоприращение, то в какой то момент база таки навернется и тогда позовут незаменимого ДБА, чтобы все пофиксал.


ну во-первых, база не сломается, а будет сыпать ошибки переполнения темп дб.
во-вторых, за местом в tempdb нужно следить! И следить именно DBA любой системой мониторинга, имеющейся у вас!

В свое время сталкивались с такой проблемой. Я в свое время наваял процедуру, добавил в добавил ее в агент, которая постоянна обновляет табличку с актуальным состоянием места.

Сначала создал табличку с актуальными данными:
CREATE TABLE dbo.free_space_files_db (
  drive NVARCHAR(20) NULL
 ,disk_space_free_mb DECIMAL(18, 2) NULL
 ,data_base_name NVARCHAR(20) NULL
 ,file_type NVARCHAR(20) NULL
 ,file_size DECIMAL(18, 2) NULL
 ,space_free_mb DECIMAL(18, 2) NULL
 ,space_used_mb DECIMAL(18, 2) NULL
 ,space_used_persent DECIMAL(18, 2) NULL
) ON [PRIMARY]
GO


Исходный код такой:
CREATE PROCEDURE dbo.check_free_space_files_db
AS
BEGIN
  TRUNCATE TABLE dbo.free_space_files_db
  CREATE TABLE #TMPFIXEDDRIVES 
  (     
    DRIVE  CHAR(1), 
    MBFREE INT
  ) 

  INSERT INTO #TMPFIXEDDRIVES 
  EXEC xp_FIXEDDRIVES 

  CREATE TABLE #TMPSPACEUSED ( 
    DBNAME    VARCHAR(50), 
    FILENME   VARCHAR(50), 
    SPACEUSED FLOAT) 

  INSERT INTO #TMPSPACEUSED 
  EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, 
  fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''') 

    INSERT into dbo.free_space_files_db
    SELECT t.drive, t.DISKSPACEFREE AS disk_space_free_mb, t.DATABASENAME AS data_base_name, t.FILETYPE AS file_type
          ,SUM(t.FILESIZE) AS file_size
          ,SUM(t.SPACEFREE) AS space_free_mb
          ,SUM(t.SPACEUSED) AS space_used_mb
          ,CAST((SUM(t.SPACEUSED) / SUM(t.FILESIZE) * 100) AS DECIMAL(18, 2)) AS space_used_persent
      FROM (SELECT   C.DRIVE, 
                   CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS DECIMAL(18, 2))  AS DISKSPACEFREE,  --Мб
                   A.NAME AS DATABASENAME, 
                   --B.NAME AS FILENAME, 
                   CASE B.TYPE  
                     WHEN 0 THEN 'DATA' 
                     ELSE TYPE_DESC 
                   END AS FILETYPE, 
                   CAST(CAST((CAST(B.max_size AS DECIMAL(18, 2)) * 8 / 1024.0) AS DECIMAL(18,2)) AS DECIMAL(15,2)) AS FILESIZE, --Мб
                   CAST((CAST(B.max_size AS DECIMAL(18, 2)) * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE
                   --B.PHYSICAL_NAME 
                   ,CAST(D.SPACEUSED / 128.0 AS DECIMAL(15,2)) AS SPACEUSED
          FROM     SYS.DATABASES A 
                   JOIN SYS.MASTER_FILES B 
                     ON A.DATABASE_ID = B.DATABASE_ID 
                   JOIN #TMPFIXEDDRIVES C 
                     ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE 
                   JOIN #TMPSPACEUSED D 
                     ON A.NAME = D.DBNAME 
                        AND B.NAME = D.FILENME ) t
  GROUP BY t.DRIVE, t.DISKSPACEFREE, t.DATABASENAME, t.FILETYPE
  ORDER BY t.DATABASENAME, t.FILETYPE DESC
           
  DROP TABLE #TMPFIXEDDRIVES 

  DROP TABLE #TMPSPACEUSED
END



В результате выполнения в таблице free_space_files_db будет информация о свободном месте всех баз на сервере. в зависимости от типа (file_type) можно посмотреть на сколько забита основная база (file_type = DATA) и transaction log (file_type = LOG).

На самом деле четкого ответа не будет, сколько места нужно под TEMPDB, опытным путем мы остановились на 10% от размера основной базы. Сам TEMPDB жестко ограничили предельным размером, так как кривой код может проводить автоинкрементирование файлов до бесконечности (вполне может стать больше самой базы :) ! Нужно отлавливать проблемы и заставлять их решать., а отлавливать на 75% от максимального размера и "бить в колокола"!

Переполнение TEMPDB на прямую зависит от пряморукости ваших программистов! Мой вам совет, ограничьте размер и отслеживайте переполнение.
18 май 16, 14:13    [19188682]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
KoDT
Member

Откуда:
Сообщений: 34
TaPaK
o-o,

и это всё на 4х SSD :)


Дешевле будет рамдиск подключить... Операций чтения/записи проходит очень много, устаните диски менять.
18 май 16, 14:27    [19188790]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
aleks2
Guest
Mind
alexeyvg
пропущено...
Откуда будут затраты на автоприращение, которое никогда не произойдёт?
Чем плохо сделать базу необходимого размера, и установить автоприращение???
Ну как чем, если отключить автоприращение, то в какой то момент база таки навернется и тогда позовут незаменимого ДБА, чтобы все пофиксал.


С автоприращением тоже позовут... когда место на диске кончится.
19 май 16, 09:43    [19191963]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
KoDT
Member

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

автор
С автоприращением тоже позовут... когда место на диске кончится.


при этом ошибки будут на много хуже! не на уровне БД, а на уровне сервера когда физически место закончится. Поэтому, как я и говорил:
1) максимальный размер фалов tempDB должен быть ограничен сверху!
2) автоинкрементирование занимаемого места внутри файла применять можно (не обязательно выделять весь доступный объем файла, пусть себе увеличивается).

То есть установить начальный размер, шаг автоувеличения и предельный размер файла.
19 май 16, 10:17    [19192154]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
KoDT
1) максимальный размер фалов tempDB должен быть ограничен сверху!
Должен может и должен, но при этом желательно (если есть возможность) выделять запасной массив с файлами по 1 Мб на случай, чтобы по ночам/выходным не будили.
KoDT
Дешевле будет рамдиск подключить..
Если мы говорим о внешнем рамдиске, то они прекрасны, пока tempdb на них помещается. Если о софтварном, то выгоднее память под буфер-пул использовать, чем дублировать в ней данные временных таблиц.
19 май 16, 10:25    [19192205]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
o-o
Guest
KoDT
aleks2,

автор
С автоприращением тоже позовут... когда место на диске кончится.


при этом ошибки будут на много хуже! не на уровне БД, а на уровне сервера когда физически место закончится.

да что ж за фигня-то такая, ну лежит у нас темпдб на своем отдельном диске,
и зачем же его ограничивать, если больше, чем размер диска, занять все равно не сможет?
и что это за страшная ошибка будет на уровне сервера, когда темпдб упрется в размер собственного диска?
19 май 16, 10:49    [19192388]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
o-o,

в общем-то на до смотреть на конкретные условия эксплуатации, на оказалось выгоднее ограничить размер по причинам, о которых я выше писал.
19 май 16, 10:59    [19192453]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
o-o
Guest
Владислав Колосов
в общем-то на до смотреть на конкретные условия эксплуатации, на оказалось выгоднее ограничить размер по причинам, о которых я выше писал:
А этом и фишка - так сразу г-но код можно вычислить или безумных пользователей, которые выполняют запросы всего по всему, или зациклившиеся изменения или...
много еще чего можно открыть для себя, если ограничить рост tempdb.

какой экстрим.
чтобы узнать, кто и чем наполняет темпдб,
совсем необязательно его переполнять.
sys.dm_db_task_space_usage знает все об использовании темпдб.
я опрашиваю его раз в 5 минут и кладу результаты в табличку,
если sum(internal_objects_alloc_page_count - internal_objects_dealloc_page_count +
user_objects_alloc_page_count - user_objects_dealloc_page_count) /1024 > 0
ничего не наворачивается,
а я в любой момент могу посмотреть чемпионов по закакиванию темпдб.
вот результат на сегодня.
как видите, больше всех темпдб использует DBCC CHECKDB,
проверяя таблицы с блобами (DBCC CHECKTABLE).
т.е. если какой гений ограничит рост темпдб,
однажды навернется именно CHECKDB.

К сообщению приложен файл. Размер - 135Kb
19 май 16, 14:41    [19193871]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
o-o,

спасибо за sys.dm_db_task_space_usage, я мониторю по размеру временных таблиц.
Админ, конечно же, должен вести себя как следователь полиции - подозревать всех :)
19 май 16, 15:29    [19194175]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
o-o
Guest
Владислав Колосов
я мониторю по размеру временных таблиц.

а как вы потом анализируете?
если это какие-то рабочие таблицы какого-то dbcc checkdb ,
то как понять, что именно эта таблица для такой-то команды?
да даже и пользовательский запрос.
у меня по сессии выбирается еще и текст запроса, спровоцировавший рост объектов в темпдб, а у вас?
19 май 16, 16:27    [19194635]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31427
Гавриленко Сергей Алексеевич
Если мы говорим о внешнем рамдиске, то они прекрасны, пока tempdb на них помещается. Если о софтварном, то выгоднее память под буфер-пул использовать, чем дублировать в ней данные временных таблиц.
Интересный вопрос.
А это кто то замерял, экспериментировал, не в курсе?
Если я делаю много операций в tempdb, то её перенос на софтварный РАМ-диск ничего не даст, по сравнению с простой отдачей этой памяти сиквелу?
19 май 16, 16:52    [19194815]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
o-o
Guest
alexeyvg
Если я делаю много операций в tempdb, то её перенос на софтварный РАМ-диск ничего не даст, по сравнению с простой отдачей этой памяти сиквелу?

я так понимаю, ГСА намекает, что памяти лишней не бывает.
когда на диске лежит много больше (Tb), чем памяти у сервера
(а у кого не так?)
да еще мы кусок отрежем под темпдб,
это ж PLE вообще в 0 уйдет
19 май 16, 16:59    [19194861]     Ответить | Цитировать Сообщить модератору
 Re: оптимальный размер tempdb  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31427
o-o
alexeyvg
Если я делаю много операций в tempdb, то её перенос на софтварный РАМ-диск ничего не даст, по сравнению с простой отдачей этой памяти сиквелу?

я так понимаю, ГСА намекает, что памяти лишней не бывает.
когда на диске лежит много больше (Tb), чем памяти у сервера
(а у кого не так?)
да еще мы кусок отрежем под темпдб,
это ж PLE вообще в 0 уйдет
Это понятно, то есть когда сервер, например, делает сортировку в tempdb, то наверное выгоднее не держать tempdb в РАМ, а просто эту память отдать сиквелу.
Но вот если я сам делаю много временных таблиц, активно работаю с tempdb, то будет ли сиквел держать эти объекты в памяти, не насилуя диски, или он не сумеет всё это правильно распределить?
19 май 16, 17:05    [19194903]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить