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

Откуда: UA
Сообщений: 94
Добрый день.

Товарищи, подскажите куда копать.
Есть БД под MS SQL 2005 Standard порядка 70 гиг. Процедур хранимых нет, приложение генерирует запросы и отправляет на сервер. Запросы простейшие - а-ля SELECT * FROM CUSTOMER WHERE CUSTOMERID={STR}. Натравил Profiler, насобирал за полчаса около гига трэйсов (выбирал только 1 event - "Batch Completed"). Ну и параллельно PerfMon'ом снимал показания счётчиков. Прогнал трэйс-файл через ClearTrace (прога, группирующая одинаковые запросы). Получилось около 800 разных запросов. Первые 100 из них вызывались от 1000 до 200 000 раз за время трэйса (30 минут). Но!

Счётчик SQLServer:SQL Statistics\SQL Compilations/sec даёт след. картину:
Average 364.380104
Median 312.450007
Min 1.001174
Max 1953.853548
Std Deviation 205.441383

Количество рекомпиляций - 0

Под SQL Server отведено 14 Гб памяти, счётчики SQLServer:Memory Manager\Target Server Memory (KB) и SQLServer:Memory Manager\Total Server Memory (KB) имеют одинаковые значения, т.е. по идее ему больше и не надо. Об этом говорит и Page life expectancy.

Количество подключений SQLServer:General Statistics\User Connections
Average 65.503289
Median 57.000000
Min 45.000000
Max 110.000000
Std Deviation 20.987523

Настройки сессии для каждого подключения идентичны. В чём может быть проблема?
19 янв 12, 19:38    [11935535]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
...в догонку

Как определить для ad-hoc query что-то типа Cache Hit (как для stored procedure)?
19 янв 12, 19:41    [11935552]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
Есть подозрения, что как раз эти компиляции неслабо грузят проц (40-75%)
19 янв 12, 19:56    [11935638]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
Подскажите на что ещё обратить внимание, чтобы понять причину сей картины?
19 янв 12, 20:20    [11935797]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
Mind
Member

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

Как определить для ad-hoc query что-то типа Cache Hit (как для stored procedure)?

Cache Hit он для любого запроса, не важно процедура или нет.
19 янв 12, 21:55    [11936080]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
JustCurious
Добрый день.

Товарищи, подскажите куда копать.
Есть БД под MS SQL 2005 Standard порядка 70 гиг. Процедур хранимых нет, приложение генерирует запросы и отправляет на сервер. Запросы простейшие - а-ля SELECT * FROM CUSTOMER WHERE CUSTOMERID={STR}.

{STR} - это что параметр или кусок текста? Если второе, у вас есть два варианта:
1. Оторвать руки криворуким девелоперам и заставить их все переписать.
2. Если это стороннее приложение, то можно попробовать форсировать параметризацию. Рекомендую сначала проверить на тест сервере.
ALTER DATABASE [YourDatabaseName] SET PARAMETERIZATION FORCED WITH NO_WAIT
19 янв 12, 22:03    [11936108]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
JustCurious,

примите за правило хорошего тона программирования на T-SQL - указание схем объектов, даже если она dbo или sys.

Сообщение было отредактировано: 20 янв 12, 06:32
20 янв 12, 06:32    [11936686]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
Mind
JustCurious
Добрый день.

Товарищи, подскажите куда копать.
Есть БД под MS SQL 2005 Standard порядка 70 гиг. Процедур хранимых нет, приложение генерирует запросы и отправляет на сервер. Запросы простейшие - а-ля SELECT * FROM CUSTOMER WHERE CUSTOMERID={STR}.

{STR} - это что параметр или кусок текста? Если второе, у вас есть два варианта:
1. Оторвать руки криворуким девелоперам и заставить их все переписать.
2. Если это стороннее приложение, то можно попробовать форсировать параметризацию. Рекомендую сначала проверить на тест сервере.
ALTER DATABASE [YourDatabaseName] SET PARAMETERIZATION FORCED WITH NO_WAIT


Спасибо за ответ. Нет, {STR} - это на самом деле параметр (первичный ключ - GUID), просто ClearTrace "подчищает" все параметры, чтобы обнаружить одинаковые запросы.
20 янв 12, 17:54    [11941820]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
tpg
JustCurious,

примите за правило хорошего тона программирования на T-SQL - указание схем объектов, даже если она dbo или sys.


Да, я слышал, что план может не использоваться повторно если не указаны схемы, либо они указаны не во всех запросах одного типа.
БД досталась в наследство, не знаю, возможно ли будет переписать код приложения, но клиенты подгоняют "ASAP выдать на-гора TOP 5 items that will improve performance"...
20 янв 12, 18:07    [11941960]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
Glory
Member

Откуда:
Сообщений: 104751
JustCurious
Да, я слышал, что план может не использоваться повторно если не указаны схемы,

Не "может не использовать", а "не будет использовать повторно"

BOL - Execution Plan Caching and Reuse
The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:

SELECT * FROM Contact
SELECT * FROM Person.Contact
20 янв 12, 18:14    [11942047]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
Glory
JustCurious
Да, я слышал, что план может не использоваться повторно если не указаны схемы,

Не "может не использовать", а "не будет использовать повторно"

BOL - Execution Plan Caching and Reuse
The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:

SELECT * FROM Contact
SELECT * FROM Person.Contact


Да, я это читал. неправильно выразился. Но в моём случае все запросы написаны без имени схемы.
Счётчик "SQLServer:SQL Statistics\SQL Compilations/sec" будет накручиваться, если 2 соединения выполняют один и тот же запрос одновременно? Ну я имею в виду, считает ли он компиляцию плана запроса (то, чего может быть только 1 или 2 в памяти) или планы выполнения выполняемые одновременно в разных соединениях?
20 янв 12, 18:24    [11942137]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
Glory
Member

Откуда:
Сообщений: 104751
JustCurious
Счётчик "SQLServer:SQL Statistics\SQL Compilations/sec" будет накручиваться, если 2 соединения выполняют один и тот же запрос одновременно? Ну я имею в виду, считает ли он компиляцию плана запроса (то, чего может быть только 1 или 2 в памяти) или планы выполнения выполняемые одновременно в разных соединениях?

Считает. Ведь в каждом соединении может быть своя схема по-умолчанию и нельзя сказать, какой фактически использовался - dbo.tabl1, user1.tabl1 илт user2.tabl1
20 янв 12, 18:29    [11942170]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
Glory
JustCurious
Счётчик "SQLServer:SQL Statistics\SQL Compilations/sec" будет накручиваться, если 2 соединения выполняют один и тот же запрос одновременно? Ну я имею в виду, считает ли он компиляцию плана запроса (то, чего может быть только 1 или 2 в памяти) или планы выполнения выполняемые одновременно в разных соединениях?

Считает. Ведь в каждом соединении может быть своя схема по-умолчанию и нельзя сказать, какой фактически использовался - dbo.tabl1, user1.tabl1 илт user2.tabl1

Да, но в этом случае будут скомпилированы именно отдельные планы (не знаю, как их правильно назвать, чтоб не вызвать путаницу - те, которых 1 или 2 максимум). А если надо выполнить запрос, план выполнения которого уже есть, но в данный момент такой же запрос выполняется в другом соединении, то у каждого соединения будет типа свой "экземпляр этого плана". При этом счётчик накручивается?
20 янв 12, 18:35    [11942205]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
Crimean
Member

Откуда:
Сообщений: 13147
пользователей всех пересадите в схему dbo - по умолчанию они все в своих схемах
лишние схемы в базе грохните
жаль что у вас 2005 - начиная с 2008 есть "optimize for ad hoc workloads" - можно чуть облегчить жизнь серверу
p.s.
forced параметризацию включить базе пробовали?
20 янв 12, 22:00    [11943116]     Ответить | Цитировать Сообщить модератору
 Re: Большое число компиляций  [new]
JustCurious
Member

Откуда: UA
Сообщений: 94
Crimean
пользователей всех пересадите в схему dbo - по умолчанию они все в своих схемах
лишние схемы в базе грохните
жаль что у вас 2005 - начиная с 2008 есть "optimize for ad hoc workloads" - можно чуть облегчить жизнь серверу
p.s.
forced параметризацию включить базе пробовали?

Спасибо, попробую. Насчёт forced не скажу, надо проверить. Если я не путаю ничего, то приложение использует один логин для подключения к серверу, так что по сути пользователь тоже один.
21 янв 12, 19:37    [11945641]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить