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

Откуда:
Сообщений: 153
Мальчики, привет.

А как длинные запросы в лог сохранять?


Например, если запрос более 1 сек выполняется - записать его в лог.


Навернека есть флаг какой-нибудь...
4 окт 17, 14:49    [20842356]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 1601
a.tarannikova
Мальчики, привет.

А как длинные запросы в лог сохранять?


Например, если запрос более 1 сек выполняется - записать его в лог.


Навернека есть флаг какой-нибудь...


я не мальчик, но вопрос встал:

что такое "лог", куда вы хотели бы вписаться?
4 окт 17, 14:51    [20842368]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
Ролг Хупин
a.tarannikova
Мальчики, привет.

А как длинные запросы в лог сохранять?


Например, если запрос более 1 сек выполняется - записать его в лог.


Навернека есть флаг какой-нибудь...


я не мальчик, но вопрос встал:

что такое "лог", куда вы хотели бы вписаться?



Да хоть куда, в любую директорию.

Можно и алертлог добавлять.


Главное получить эти запросы, которые длинные.



Это видимо только через ODBC когда соединение
https://msdn.microsoft.com/ru-ru/library/ms403323(v=sql.110).aspx


Как заставить сам SQL Server так делать?
4 окт 17, 14:53    [20842381]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
982183
Member

Откуда:
Сообщений: 1358
Не, ну что такое лог можно пофантазировать.
Например отдельная табличка и идентификатором запроса, временем и сроком выполнения.
а вот как в общем тексте отделить и идентифицировать запросы друг от друга?
4 окт 17, 14:54    [20842387]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
982183
Не, ну что такое лог можно пофантазировать.
Например отдельная табличка и идентификатором запроса, временем и сроком выполнения.
а вот как в общем тексте отделить и идентифицировать запросы друг от друга?



В иделе как у postgresql чтобы было:

При длительности превышающей параметр в лог пишется время выполнения и текст запроса.
4 окт 17, 14:55    [20842391]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 1601
a.tarannikova
Ролг Хупин
пропущено...


я не мальчик, но вопрос встал:

что такое "лог", куда вы хотели бы вписаться?



Да хоть куда, в любую директорию.

Можно и алертлог добавлять.


Главное получить эти запросы, которые длинные.



Это видимо только через ODBC когда соединение
https://msdn.microsoft.com/ru-ru/library/ms403323(v=sql.110).aspx


Как заставить сам SQL Server так делать?


можете установить какую-нибудь хрень типа тех, что производит Idera и там есть ловля разных запросов и т.д..
4 окт 17, 14:57    [20842400]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 4671
использовать запрос к DMV с сохранением результата в ваш "лог"
4 окт 17, 15:03    [20842416]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
skyANA
Member

Откуда: Зеленоград
Сообщений: 22808
a.tarannikova,

как сделали мы... запускается сборщик SQL запросов (SQL Trace) по тому или иному критерию. Например собираются все запросы, которые заняли более 1 секунды.

  • Для этого из SQL Profile делается экспорт трассировки и создается SQL script. В нем можно поставить имя файла.
    Скрипт создания трассировки оформлен в виде хранимой процедуры с настройками автоматического запуска (sp_procoption CreateSQLTrace, startup, true). Сама процедура создается в master БД каждого инстанса кластера SQL. Таким образом при старте каждого инстанса создается новая трассировка.
  • Для каждого инстанса в трассировке указана своя папка в папке общего доступа \\logserver\Temp\SQLTraces\[instanceName]

Последовательность работы такая:

  • SQL трассировщик сохраняет в указанный файл (новый каждые 5 mb) описание долгих запросов
  • По расписанию данные импортируются в базу логов, а далее отправляются в удобный вам инструмент для просмотра (у нас это Raygun).
4 окт 17, 15:07    [20842431]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
Ролг Хупин
a.tarannikova
пропущено...



Да хоть куда, в любую директорию.

Можно и алертлог добавлять.


Главное получить эти запросы, которые длинные.



Это видимо только через ODBC когда соединение
https://msdn.microsoft.com/ru-ru/library/ms403323(v=sql.110).aspx


Как заставить сам SQL Server так делать?


можете установить какую-нибудь хрень типа тех, что производит Idera и там есть ловля разных запросов и т.д..



А сам SQL Server не способен на такое?
4 окт 17, 15:09    [20842442]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
skyANA
a.tarannikova,

как сделали мы... запускается сборщик SQL запросов (SQL Trace) по тому или иному критерию. Например собираются все запросы, которые заняли более 1 секунды.

  • Для этого из SQL Profile делается экспорт трассировки и создается SQL script. В нем можно поставить имя файла.
    Скрипт создания трассировки оформлен в виде хранимой процедуры с настройками автоматического запуска (sp_procoption CreateSQLTrace, startup, true). Сама процедура создается в master БД каждого инстанса кластера SQL. Таким образом при старте каждого инстанса создается новая трассировка.
  • Для каждого инстанса в трассировке указана своя папка в папке общего доступа \\logserver\Temp\SQLTraces\[instanceName]

Последовательность работы такая:

  • SQL трассировщик сохраняет в указанный файл (новый каждые 5 mb) описание долгих запросов
  • По расписанию данные импортируются в базу логов, а далее отправляются в удобный вам инструмент для просмотра (у нас это Raygun).



в профайлер 99% информации валится где Duraion is NULL, при установке Duration>1000

Как вы ее отфильтровываете? уже после завершения трассы?
4 окт 17, 15:11    [20842453]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
Shakill
Member

Откуда: мск
Сообщений: 1767
a.tarannikova
в профайлер 99% информации валится где Duraion is NULL, при установке Duration>1000

Как вы ее отфильтровываете? уже после завершения трассы?


поставить в фильтре duration галку "Exclude rows that do not contain values"?
4 окт 17, 15:17    [20842482]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
Shakill
a.tarannikova
в профайлер 99% информации валится где Duraion is NULL, при установке Duration>1000

Как вы ее отфильтровываете? уже после завершения трассы?


поставить в фильтре duration галку "Exclude rows that do not contain values"?



МММммм какая полезная галочка!
4 окт 17, 15:25    [20842513]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
a.tarannikova
Member

Откуда:
Сообщений: 153
Ролг Хупин, skyANA, Shakill

ЧМОКЕ-ЧМОКЕ!
4 окт 17, 17:16    [20843017]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 31500
Блог
skyANA,

Плохо, ведь уже есть механизм событий
5 окт 17, 08:07    [20844128]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 1601
a.tarannikova
Ролг Хупин
пропущено...


можете установить какую-нибудь хрень типа тех, что производит Idera и там есть ловля разных запросов и т.д..



А сам SQL Server не способен на такое?


способен, но в комбинации с юзером
5 окт 17, 10:56    [20844509]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
Alexander Us
Member

Откуда:
Сообщений: 883
a.tarannikova,

не изящно, но работает:

выполняйте запрс из джоба раз в 10 секунд,
строки у которых req_elapsed_sec > того, что вы понимаете как "долго"
записывайте в табличку или отправляйте на мыло

Вы конечно отловите не всё, но Вам хватит для оптимизации и аларма долгиз запросов.

SELECT 
 req.command command_type
,sqltext.TEXT command_text
, SUBSTRING(sqltext.text, (req.statement_start_offset/2)+1, 
    ((CASE req.statement_end_offset
        WHEN -1 THEN DATALENGTH(sqltext.text)
        ELSE req.statement_end_offset
        END - req.statement_start_offset)/2) + 1) AS statement_text
,db_name(req.database_id) db_name
,req.session_id
,req.blocking_session_id
,req.status as req_status
,req.cpu_time req_cpu_time_ms
,ses.cpu_time ses_cpu_time_ms
,req.total_elapsed_time / 1000.0 req_elapsed_sec
,ses.original_login_name
,ses.host_name
,ses.program_name
,ses.host_process_id
,ses.client_interface_name
,ses.memory_usage
--,ses.last_request_start_time
--,ses.last_request_end_time
,ses.reads
,ses.writes
,ses.logical_reads
,ses.row_count 
FROM 
		sys.dm_exec_requests req with(nolock)
JOIN sys.dm_exec_sessions ses with(nolock) on req.session_id=ses.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 
where 1=1
and req.database_id > 4
and req.session_id <> @@SPID
5 окт 17, 11:27    [20844655]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
LSV
Member

Откуда: Киев
Сообщений: 30150
Alexander Us,

спс.
Хороший запрос. Работает норм.
5 окт 17, 13:33    [20845255]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
invm
Member

Откуда: Москва
Сообщений: 7504
Похоже, что sys.dm_exec_query_stats придумали для слабаков...
Настоящие джедаи всегда пользуют профайлер, extended events, sys.dm_exec_requests и т.п.
5 окт 17, 15:42    [20845750]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
o-o
Guest
не, настоящие джедаи раз в сутки базу в оффлайн переводят,
чтобы все планы и их статистики успешно похерить :)
короче, еще надо успеть выгрести до прибытия умелых ручек
5 окт 17, 16:05    [20845824]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
Col
Member

Откуда: Торонто
Сообщений: 134
a.tarannikova,
Делаем алерт:
EXEC msdb.dbo.sp_add_alert @name=N'Long run queries', 
    @message_id=0, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=5, 
    @category_name=N'[Uncategorized]', 
    @performance_condition=N'SQLSERVER:Transactions|Longest Transaction Running Time||>|1', 
    @job_id=N'eb3ca402-cab2-4395-a2cf-13c47cf89323'

По алерту запускаем джоб со следующим скриптом внутри.
@longrunningthreshold одна минута в этом примере
Ну и последний - четвертый среп подредактиривать не забудьте

DECLARE @xml NVARCHAR(max)
DECLARE @body NVARCHAR(max)
-- specify long running query duration threshold
DECLARE @longrunningthreshold int
SET @longrunningthreshold=1 
-- step 1: collect long running query details.
;WITH cte
AS (SELECT [Session_id]=spid,
[Sessioin_start_time]=(SELECT start_time
FROM sys.dm_exec_requests
WHERE spid = session_id),
[Session_status]=Ltrim(Rtrim([status])),
[Session_Duration]=Datediff(mi, (SELECT start_time
FROM sys.dm_exec_requests
WHERE spid = session_id),
Getdate()
),
[Session_query] = Substring (st.text, ( qs.stmt_start / 2 ) + 1,
( ( CASE qs.stmt_end
WHEN -1
THEN
Datalength(st.text)
ELSE qs.stmt_end
END
-
qs.stmt_start ) / 2 ) +
1)
FROM sys.sysprocesses qs
CROSS apply sys.Dm_exec_sql_text(sql_handle) st)
 
-- step 2: generate html table 
SELECT @xml = Cast((SELECT session_id AS 'td',
'',
session_duration AS 'td',
'',
session_status AS 'td',
'',
[session_query] AS 'td'
FROM cte
WHERE session_duration >= @longrunningthreshold 
FOR xml path('tr'), elements) AS NVARCHAR(max))
 
-- step 3: do rest of html formatting
SET @body =
'<html><body><H2>Long Running Queries ( Limit > 1 Minute ) </H2>< table border = 1 BORDERCOLOR="Black"> < tr>< th align="centre"> Session_id </th> <th> Session_Duration(Minute) </th> <th> Session_status </th> <th> Session_query </th></tr>'
SET @body = @body + @xml + '</table></body></html>'
 
-- step 4: send email if a long running query is found.
IF( @xml IS NOT NULL )
BEGIN
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'your database mail profile',
@body = @body,
@body_format ='html',
@recipients = 'recipients email address',
@subject = 'ALERT: Long Running Queries';
END
6 окт 17, 03:24    [20846946]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
982183
Member

Откуда:
Сообщений: 1358
o-o
не, настоящие джедаи раз в сутки базу в оффлайн переводят,

А перегрузить сервер забыли?
6 окт 17, 04:32    [20846951]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
o-o
Guest
982183
o-o
не, настоящие джедаи раз в сутки базу в оффлайн переводят,

А перегрузить сервер забыли?

а я тут при чем?
---
предыдущие админы реализовали сие в виде джоба, и он так и мигрировал на новый сервер.
это называется "отцепить аксессников от базы"
6 окт 17, 09:38    [20847170]     Ответить | Цитировать Сообщить модератору
 Re: Сохранять долгие запросы в лог  [new]
982183
Member

Откуда:
Сообщений: 1358
Совершенно не причем.

Перевод базы в оффлай это только первый уровень познания кармы.
Второй - перезагрузка сервера.
6 окт 17, 11:33    [20847532]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить