1

Topic: To save long requests in a broad gull

Boys, greetings.
And how long requests in a broad gull to save?
For example, if the request more than 1  is fulfilled - to write down it in a broad gull.
there is a flag any...

2

Re: To save long requests in a broad gull

a.tarannikova wrote:

Boys, greetings.
And how long requests in a broad gull to save?
For example, if the request more than 1  is fulfilled - to write down it in a broad gull.
there is a flag any...

I not the boy, but a question rose:
What is "broad gull" where you would like to be inscribed?

3

Re: To save long requests in a broad gull

Hupin wrote:

it is passed...
I not the boy, but a question rose:
What is "broad gull" where you would like to be inscribed?

Yes though where, in any directory.
It is possible and  to add.
The main thing to receive these requests, which long.
It is visible only through ODBC when connection
https://msdn.microsoft.com/ru-ru/library/ms403323 (v=sql.110).aspx
How to force a SQL Server so to do?

4

Re: To save long requests in a broad gull

Not, well such a broad gull it is possible to dream up.
For example a separate label and the request identifier, time and performance period.
And here is how in the general text to separate and identify requests from each other?

5

Re: To save long requests in a broad gull

982183 wrote:

Not, well such a broad gull it is possible to dream up.
For example a separate label and the request identifier, time and performance period.
And here is how in the general text to separate and identify requests from each other?

In an idele as at postgresql that was:
At duration exceeding parameter runtime and the request text is written to a broad gull.

6

Re: To save long requests in a broad gull

a.tarannikova wrote:

it is passed...
Yes though where, in any directory.
It is possible and  to add.
The main thing to receive these requests, which long.
It is visible only through ODBC when connection
https://msdn.microsoft.com/ru-ru/library/ms403323 (v=sql.110).aspx
How to force a SQL Server so to do?

Can install any  type of that produces Idera and there there is a catching of different requests and ..

7

Re: To save long requests in a broad gull

To use request to DMV with result saving in your "broad gull"

8

Re: To save long requests in a broad gull

a.tarannikova;
As we made... Collector SQL of requests (SQL Trace) by this or that criterion is launched. All requests which occupied more than 1 second for example gather.
[ul]
For this purpose from SQL Profile export of trace becomes and forms SQL script. In it it is possible to deliver file name.
The script of creation of trace is issued in the form of stored procedure with auto start adjustments (sp_procoption CreateSQLTrace, startup, true). Procedure forms in master a DB of everyone  cluster SQL. Thus at start of everyone  there is a new trace.
For everyone  in trace the folder in a folder of the general access \\logserver\Temp\SQLTraces \[instanceName] is specified
[/ul]
Sequence of operation the such:
[ul]
SQL the tracer saves in the specified file (new everyone 5 mb) the description of long requests
Under the schedule the data is imported to basis of dens, and further goes to the tool convenient to you for review (at us it Raygun).
[/ul]

9

Re: To save long requests in a broad gull

Hupin wrote:

it is passed...
Can install any  type of that produces Idera and there there is a catching of different requests and ..

And the SQL Server is not capable of the such? sad((

10

Re: To save long requests in a broad gull

skyANA wrote:

a.tarannikova;
As we made... Collector SQL of requests (SQL Trace) by this or that criterion is launched. All requests which occupied more than 1 second for example gather.
[ul]
For this purpose from SQL Profile export of trace becomes and forms SQL script. In it it is possible to deliver file name.
The script of creation of trace is issued in the form of stored procedure with auto start adjustments (sp_procoption CreateSQLTrace, startup, true). Procedure forms in master a DB of everyone  cluster SQL. Thus at start of everyone  there is a new trace.
For everyone  in trace the folder in a folder of the general access \\logserver\Temp\SQLTraces \[instanceName] is specified
[/ul]
Sequence of operation the such:
[ul]
SQL the tracer saves in the specified file (new everyone 5 mb) the description of long requests
Under the schedule the data is imported to basis of dens, and further goes to the tool convenient to you for review (at us it Raygun).
[/ul]

In  99 % of the information fall where Duraion is NULL, at setting Duration> 1000
How you filter it? Already after route end?

11

Re: To save long requests in a broad gull

a.tarannikova wrote:

in  99 % of the information fall where Duraion is NULL, at setting Duration> 1000
How you filter it? Already after route end?

To deliver in the filter duration a daw "Exclude rows that do not contain values"?

12

Re: To save long requests in a broad gull

Shakill wrote:

it is passed...
To deliver in the filter duration a daw "Exclude rows that do not contain values"?

what useful tick!

13

Re: To save long requests in a broad gull

Rolg Hupin, skyANA, Shakill
KISS-KISS!

14

Re: To save long requests in a broad gull

skyANA;
Badly, after all already there is a mechanism of events

15

Re: To save long requests in a broad gull

a.tarannikova wrote:

it is passed...
And the SQL Server is not capable of the such? sad((

It is capable, but in a combination with the user

16

Re: To save long requests in a broad gull

a.tarannikova;
Not gracefully, but works:
Fulfill  from  time in 10 seconds,
Lines at which req_elapsed_sec> that you understand as "long"
Write down in a label or send on soap
You certainly catch not everything, but to you suffices for optimization and   requests.

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

17

Re: To save long requests in a broad gull

Alexander Us;
.
Good request. Works norms.

18

Re: To save long requests in a broad gull

It seems that sys.dm_exec_query_stats invented for weaklings...
The presents  always  , extended events, sys.dm_exec_requests, etc.

19

Re: To save long requests in a broad gull

Not, the presents  time a day in an offline translate basis;
That all plans and their statisticans successfully  smile
More shortly, still it is necessary to have time to remove before arrival of skillful pens

20

Re: To save long requests in a broad gull

a.tarannikova,
We do :

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'

On  it is launched  with a following script inside.
@longrunningthreshold one minute in this example
Well and last - the fourth   do not forget

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 = "center"> 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 A mail profile ';
@body = @body;
@body_format = ' html ';
@recipients = ' recipients email address';
@subject = ' ALERT: Long Running Queries';
END

21

Re: To save long requests in a broad gull

o-o wrote:

not,  time a day basis in an offline translates the presents;

And to overload the server forgot?

22

Re: To save long requests in a broad gull

982183 wrote:

it is passed...
And to overload the server forgot?

And I here at what?
---
The previous administrators implemented this in a type , and it and migrated on the new server.
It is called "to unhitch  from basis"

23

Re: To save long requests in a broad gull

At all and.
Basis transfer in  it only the first level of knowledge of a karma.
The second - server reboot.