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

Откуда:
Сообщений: 202
Версия Microsoft SQL Server 2014 (SP2-CU5) (KB4013098) - 12.0.5546.0 (X64) Apr 3 2017 14:55:37 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64>

В Resource Governor можно создать пул, но в нем задаются рамки только загрузки процессора, памяти, ну и степень параллелизма.
Есть ли способы ограничить другие общие ресурсы? Например - ширину полосы к дискам, объем, который запрос может получить в tempdb?
По этому последнему хотелось бы иметь какой то жесткий ограничитель. Типа как с запросом, если предполагаемая стоимость выполнения больше N - запрос не выполняется и падает с ошибкой. Если запросу может понадобиться больше N Гб в tempdb - аналогично.
13 июл 17, 09:22    [20639322]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
iii2,

автор
Например - ширину полосы к дискам
MAX_IOPS_PER_VOLUME?
для памяти есть хинт на стейтмент max_memory_grant
автор
объем, который запрос может получить в tempdb?
это вообще не понятно зачем

автор
По этому последнему хотелось бы иметь какой то жесткий ограничитель. Типа как с запросом, если предполагаемая стоимость выполнения больше N - запрос не выполняется и падает с ошибкой.
насколько я знаю, то такого нет, но можете нарисовать джоб который будет смотреть на запросы и прибивать тяжелые... но странно всё это как-то
13 июл 17, 09:38    [20639408]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
iii2
Member

Откуда:
Сообщений: 202
TaPaK
iii2,
автор
объем, который запрос может получить в tempdb?
это вообще не понятно зачем

Объяснение, правда весьма корявое, следующее:
Фреймворк, используемый разработчиками, иногда рождает божественной красоты запросы, типа декартового произведения пары табличек миллион на миллион, и требующие для хранения в темпдб петабайтов данных.
Ну, размер темпдб мы уже зафиксировали, так что оно внезапно не падает, потому что диска не осталось, но пока такой запрос выполняется, остальные, как вы понимаете, сосут (в плохом смысле этого слова).
Разработчики категорически против установки лимита на стоимость запроса, потому что [вдруг] перестают работать некоторые нужные и легальные запросы.

Собственно, что делать - ума не приложу.
13 июл 17, 10:09    [20639587]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
Alozar
Member

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

Вы извините конечно. Но если у вас допускается выполнение указанных вами запросов, то вам уже ничего не поможет, ибо ваши чудо-разработчики всегда найдут способ убить систему.
13 июл 17, 10:33    [20639697]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
iii2,

н победить такую CRM можно только прибиванием плланов + хинты или железом
13 июл 17, 10:37    [20639713]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
iii2
Member

Откуда:
Сообщений: 202
Alozar
iii2,

Вы извините конечно. Но если у вас допускается выполнение указанных вами запросов, то вам уже ничего не поможет, ибо ваши чудо-разработчики всегда найдут способ убить систему.

Я понимаю, но такова селяви. Этот вопрос я не контролирую.
13 июл 17, 10:48    [20639773]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
iii2
Member

Откуда:
Сообщений: 202
TaPaK
iii2,

н победить такую CRM можно только прибиванием плланов + хинты или железом

Прибиванием планов не получится. Текст запросов генерируется в момент выполнения приложения (там от действия пользователя зависит) фреймворком.
Хинты...
А как на уровне сервера указать, чтобы при выборе планов использовал более жадные до процессора, чем для памяти? (Данную проблему не решит, но в принципе, могло бы в других моментах помочь)? Есть трейсфлаг такой?

А железом как?
Там и так терабайт SSD под tempdb. :(
13 июл 17, 10:57    [20639810]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
Владислав Колосов
Member

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

сделайте алёрт на размер заполнения tempdb.
13 июл 17, 11:08    [20639852]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
iii2
Member

Откуда:
Сообщений: 202
Короче говоря, взялся курить MSDN: http://technet.microsoft.com/ru-ru/library/ms176029(v=sql.105).aspx

Пока мысль настроить задание агента, чтобы опрашивал динамические представления, описанные выше, ну и потом прибивал слишком зарвавшиеся запросы.
Не знаю, правда, как на всё это будут разработчики реагировать :-)

Буду экспериментировать.
13 июл 17, 12:27    [20640230]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
s_ustinov
Member

Откуда: Munchen, DE
Сообщений: 2168
iii2
Короче говоря, взялся курить MSDN: http://technet.microsoft.com/ru-ru/library/ms176029(v=sql.105).aspx

Пока мысль настроить задание агента, чтобы опрашивал динамические представления, описанные выше, ну и потом прибивал слишком зарвавшиеся запросы.
Не знаю, правда, как на всё это будут разработчики реагировать :-)

Буду экспериментировать.

Ну могу себе представить состояние разработчика, когда на одном экземпляре выполняется без ошибок, а на другом (больше данных / другая статистика и т.п.) вылетает с ошибкой...
13 июл 17, 12:42    [20640307]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
SFlash
Member

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

Лови скриптик, выдает все временные таблицы с размерами и привязкой к SPID. Можно настроить джоб чтоб килял нужный SPID, при превышении размера временной таблицы

DECLARE @FileName VARCHAR(MAX)= (SELECT SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc' FROM sys.traces WHERE is_default = 1)

;WITH tt (name,SPID)
as
(
SELECT   o.name, gt.SPID  
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt  
JOIN tempdb.sys.objects AS o   
     ON gt.ObjectID = o.OBJECT_ID  
WHERE gt.DatabaseID = 2 
  AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)  
  AND o.create_date >= DATEADD(ms, -100, gt.StartTime)   
  AND o.create_date <= DATEADD(ms, 100, gt.StartTime)
)
SELECT TBL.name AS ObjName
	  ,tt.SPID
      ,STAT.row_count AS StatRowCount 
      ,STAT.used_page_count * 8 AS UsedSizeKB 
      ,STAT.reserved_page_count * 8 AS RevervedSizeKB 
FROM tempdb.sys.partitions AS PART 
     INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT 
         ON PART.partition_id = STAT.partition_id 
            AND PART.partition_number = STAT.partition_number 
     INNER JOIN tempdb.sys.tables AS TBL 
         ON STAT.object_id = TBL.object_id 
	 join tt on TBL.name=tt.name
ORDER BY TBL.name;
13 июл 17, 12:51    [20640354]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
Addx
Member

Откуда:
Сообщений: 957
s_ustinov
iii2
Короче говоря, взялся курить MSDN: http://technet.microsoft.com/ru-ru/library/ms176029(v=sql.105).aspx

Пока мысль настроить задание агента, чтобы опрашивал динамические представления, описанные выше, ну и потом прибивал слишком зарвавшиеся запросы.
Не знаю, правда, как на всё это будут разработчики реагировать :-)

Буду экспериментировать.

Ну могу себе представить состояние разработчика, когда на одном экземпляре выполняется без ошибок, а на другом (больше данных / другая статистика и т.п.) вылетает с ошибкой...


Могу представить себе состояние администратора, когда идет попытка выделить в tempdb место под петабайт данных. ))
13 июл 17, 12:59    [20640380]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
o-o
Guest
SFlash
iii2,

Лови скриптик, выдает все временные таблицы с размерами и привязкой к SPID. Можно настроить джоб чтоб килял нужный SPID, при превышении размера временной таблицы

DECLARE @FileName VARCHAR(MAX)= (SELECT SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc' FROM sys.traces WHERE is_default = 1)

;WITH tt (name,SPID)
as
(
SELECT   o.name, gt.SPID  
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt  
JOIN tempdb.sys.objects AS o   
     ON gt.ObjectID = o.OBJECT_ID  
WHERE gt.DatabaseID = 2 
  AND gt.EventClass = 46 -- (Object:Created Event from sys.trace_events)  
  AND o.create_date >= DATEADD(ms, -100, gt.StartTime)   
  AND o.create_date <= DATEADD(ms, 100, gt.StartTime)
)
SELECT TBL.name AS ObjName
	  ,tt.SPID
      ,STAT.row_count AS StatRowCount 
      ,STAT.used_page_count * 8 AS UsedSizeKB 
      ,STAT.reserved_page_count * 8 AS RevervedSizeKB 
FROM tempdb.sys.partitions AS PART 
     INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT 
         ON PART.partition_id = STAT.partition_id 
            AND PART.partition_number = STAT.partition_number 
     INNER JOIN tempdb.sys.tables AS TBL 
         ON STAT.object_id = TBL.object_id 
	 join tt on TBL.name=tt.name
ORDER BY TBL.name;

а при чем тут вообще временные таблицы?
заваливают темпдб обычно наикрутейшие дистинкты и прочие сорты
13 июл 17, 13:24    [20640509]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
msLex
Member

Откуда:
Сообщений: 7730
o-o
а при чем тут вообще временные таблицы?
заваливают темпдб обычно наикрутейшие дистинкты и прочие сорты

что там возвращает скрипт, я не глядел, но ведь все spill-ы материализуются в настоящие таблицы в tempdb, нет?
13 июл 17, 13:32    [20640551]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
iii2,

анализировать планы выполняемых запросов StatementEstRows на наличие максимально допустимого количества строк и прибивать процессы если превышено
13 июл 17, 13:40    [20640581]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
iii2
Member

Откуда:
Сообщений: 202
WarAnt
iii2,

анализировать планы выполняемых запросов StatementEstRows на наличие максимально допустимого количества строк и прибивать процессы если превышено

Это должно примерно так выглядеть?
1. Получаем xml выполняемого запроса.
2. Парсим, ищем параметр StatementEstRows в каждой операции плана.
3. Если где-то находим значение, предположим, выше миллиарда - прибиваем запрос.

Так?
13 июл 17, 13:50    [20640624]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
SFlash
Member

Откуда:
Сообщений: 143
o-o
заваливают темпдб обычно наикрутейшие дистинкты и прочие сорты

И как они там хранятся? Или темпдб у нас отдельный вид базы и там могут быть объекты без id, имени и т.д.?
13 июл 17, 14:14    [20640733]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
iii2
WarAnt
iii2,

анализировать планы выполняемых запросов StatementEstRows на наличие максимально допустимого количества строк и прибивать процессы если превышено

Это должно примерно так выглядеть?
1. Получаем xml выполняемого запроса.
2. Парсим, ищем параметр StatementEstRows в каждой операции плана.
3. Если где-то находим значение, предположим, выше миллиарда - прибиваем запрос.

Так?


да идея в этом
13 июл 17, 14:34    [20640835]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
iii2
Собственно, что делать - ума не приложу.
Забить на проблему.
Если фреймворк генерирует подобные запросы, то это проблема фреймворка. Или разработчиков, не умеющих с ним работать.
В нормальном фреймворке обязана присутствовать возможность в конкретном месте отказаться от генерации и использовать написанный вручную запрос.
iii2
А как на уровне сервера указать, чтобы при выборе планов использовал более жадные до процессора, чем для памяти? (Данную проблему не решит, но в принципе, могло бы в других моментах помочь)? Есть трейсфлаг такой?
Если забивать не хочется, можно попробовать через Plan Gude прикрутить к таким запросам option(fast N).
Но время выполнения запроса может возрасти радикально. А может вообще не помочь.
13 июл 17, 15:21    [20641131]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
s_ustinov
Member

Откуда: Munchen, DE
Сообщений: 2168
Addx
s_ustinov
пропущено...

Ну могу себе представить состояние разработчика, когда на одном экземпляре выполняется без ошибок, а на другом (больше данных / другая статистика и т.п.) вылетает с ошибкой...


Могу представить себе состояние администратора, когда идет попытка выделить в tempdb место под петабайт данных. ))

Вот именно.
13 июл 17, 15:29    [20641190]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
iii2
Это должно примерно так выглядеть?
1. Получаем xml выполняемого запроса.
2. Парсим, ищем параметр StatementEstRows в каждой операции плана.
3. Если где-то находим значение, предположим, выше миллиарда - прибиваем запрос.

Так?
И запрос, в котором есть, например, сканирование таблицы/индекса, превышающее лимит строк, но не нагружающее tempdb, будет прибит.
А запрос с кривыми оценками, который в реальности будет нагружать termpdb, продолжит выполнятся.
13 июл 17, 15:30    [20641196]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
s_ustinov
Member

Откуда: Munchen, DE
Сообщений: 2168
invm
iii2
Собственно, что делать - ума не приложу.
Забить на проблему.
Если фреймворк генерирует подобные запросы, то это проблема фреймворка. Или разработчиков, не умеющих с ним работать.
В нормальном фреймворке обязана присутствовать возможность в конкретном месте отказаться от генерации и использовать написанный вручную запрос.

+100500
Вести лог. Выявлять ситуации, при которых генерятся "чудные" запросы - и отправлять багрепорты разрабам. Всё остальное только усугубит проблему.
13 июл 17, 15:33    [20641220]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
o-o
Guest
SFlash
o-o
заваливают темпдб обычно наикрутейшие дистинкты и прочие сорты

И как они там хранятся? Или темпдб у нас отдельный вид базы и там могут быть объекты без id, имени и т.д.?

хранятся в виде worktables.
встречный вопрос: а что вы выгребаете из дефолтного трэйса, неужто worktables?
пример приведите строки трэйса, где записан worktable
---
хорош мне приписывать то, чего я не говорю.
претензии у меня не к тому, что в темпдб хранится и его переполняет,
а к тому, что выгребаете вы
13 июл 17, 16:33    [20641589]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
iii2
Member

Откуда:
Сообщений: 202
s_ustinov
invm
пропущено...
Забить на проблему.
Если фреймворк генерирует подобные запросы, то это проблема фреймворка. Или разработчиков, не умеющих с ним работать.
В нормальном фреймворке обязана присутствовать возможность в конкретном месте отказаться от генерации и использовать написанный вручную запрос.

+100500
Вести лог. Выявлять ситуации, при которых генерятся "чудные" запросы - и отправлять багрепорты разрабам. Всё остальное только усугубит проблему.

Мы это, безусловно, делаем.
Я сейчас workaround ищу.
14 июл 17, 08:18    [20642913]     Ответить | Цитировать Сообщить модератору
 Re: Существует ли способ для ограничения конкретного запроса (пользователя, etc) в объеме temp  [new]
s_ustinov
Member

Откуда: Munchen, DE
Сообщений: 2168
iii2
s_ustinov
пропущено...

+100500
Вести лог. Выявлять ситуации, при которых генерятся "чудные" запросы - и отправлять багрепорты разрабам. Всё остальное только усугубит проблему.

Мы это, безусловно, делаем.
Я сейчас workaround ищу.

Убивать запросы, часть из которых могут являться корректными - не выход.
14 июл 17, 10:28    [20643347]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить