Extended Events With Pair Matching

Oct 12, 2015

I can only do one match at a time -- Like can only do either the sql_statement_(start and end), or sp_statement_(start or end). Is there any way to capture both in the same session? Or since I am adding both the events in the ADD EVENT section, can I query it somehow to get unmatched SP or SQL?

Code:

USE master;
GO
-- Create the Event Session
IF EXISTS(SELECT *
FROM sys.server_event_sessions
WHERE name='TimedOutSQL')

[code]...

View 2 Replies


ADVERTISEMENT

Transact SQL :: Emailing Session Events For Extended Events

Feb 3, 2014

I am using this code for LongRunning Queries.

CREATE EVENT SESSION LongRunningQuery
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
WHERE sqlserver.sql_statement_completed.duration > 60000

[Code] ...

Here Instead of writing to XML file how can send an EMAIL if a query runs more than 1 minute in my server ?

View 2 Replies View Related

SQL 2012 :: Extended Events For Deadlock?

Oct 6, 2014

I've setup a deadlock monitor using extended events like this.

CREATE EVENT SESSION [deadlock] ON SERVER
ADD EVENT sqlserver.lock_deadlock(
ACTION(package0.process_id,sqlserver.client_app_name,sqlserver.client_hostname,
sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle)

[Code] ....

Deadlock happened couple of days ago. I'm trying to determine the cause of deadlocks. What script should I use to pull that information to see what objects/processes caused deadlock?

View 8 Replies View Related

SQL 2012 :: Extended Events And Client IP Address

Jan 14, 2015

I want to gather (and filter by) client ip address in an extended events session. So far, I have the sqlserver.login event added, but I can find no way to get client ip address added to the action or predicate.

I can filter by the pred_source field "session_nt_domain" or "nt_domain" but this is not always populated by all logins and also, this domain only lists machine name, such as FRED, as opposed to the full domain FRED.foo.bar.

Is it possible to gather login client ip address in an extended events session? Surely internal SQL Server processes have access to it because the login audit can populate client ip address.

View 1 Replies View Related

SQL 2012 :: Capturing Errors And RPC Completed With Extended Events

Jun 17, 2014

I have an existing EE setup that captures all failing queries (see code below). The problem is that I also want to somehow capture RPC_starting so that I can see which parameters are passed in whenever a query fails. Is there a way to somehow capture those two events (error_reported & rpc_starting), but only capture rpc_starting when there is actually an error reported?Or maybe just an event on rpc_starting and somehow filter to only capture when there is an error?

Existing error_reported EE code:
CREATE EVENT SESSION [what_queries_are_failing] ON SERVER ADD EVENT sqlserver.error_reported (
ACTION(sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, sqlserver.session_id,
package0.collect_system_time, sqlserver.transaction_id, sqlserver.username, sqlserver.client_hostname)

[code]....

View 6 Replies View Related

SQL 2012 :: Extended Events Capturing Info On Dev Environment But Not On PRO?

Jan 27, 2015

I do have the following Extended Event session on my Dev box;

CREATE EVENT SESSION [sp_showplan] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1)
ACTION(sqlserver.plan_handle)
WHERE ([package0].[equal_uint64]([object_type],(8272)) AND [sqlserver].[equal_i_sql_unicode_string]([object_name],N'MyStoreProcedure')))
ADD TARGET package0.event_file(SET filename=N'E:DBA_AuditSP_Exec.xel',metadatafile=N'E:DBA_AuditSP_Exec.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

The idea is being able to capture the execution plan when the program invokes the store procedure, regardless of the database.

This works on my Dev box. When I manually trigger "MyStoreProcedure" from database A , the event is saved. The same thing happens when I do that from database B. Ok ... so far, so good.

So I went to the live production environment and setup my Extended Events session. But it's saving nothing. I was able to check that the store procedure was executed on several databases but my extended events session never grabbed the plan.

What could be the reason for this? Memory starvation maybe? Is there something I am doing wrong?

View 5 Replies View Related

SQL 2012 :: Extended Events Duration In Milliseconds Or Microseconds?

Apr 15, 2015

When you view the Extended Events "Watch Live Data" is the duration in milliseconds or microseconds? I'm assuming it's milliseconds, but if you look at the timestamp difference from start to complete it doesn't add up to the duration amount? It looks like it's just the difference between the timestamps?

View 5 Replies View Related

SQL Server 2012 :: Right Way To Query Extended Events Session?

Jun 4, 2015

I use this code in a utility procedure (for performance testing) but it is really slow.

For example, a session with three events is taking 5 seconds to complete this query:

DECLARE @xml xml=
(
SELECT CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = @name
);

with data as

(
select
convert(varchar(128),convert(varbinary(128),'0x'+n.value('(action[@name="context_info"]/value/text())[1]','varchar(128)'),1)) context
, n.value('(data[@name="duration"]/value/text())[1]','int')/1000.0 duration
, n.value('(data[@name="cpu_time"]/value/text())[1]','bigint')/1000.0 cpu_time
, n.value('(data[@name="physical_reads"]/value/text())[1]','bigint') physical_reads

[code].....

So, I was wondering (considering the buffer is usually only holding a few hundred events)

1. Is this the wrong way to query data from a ring buffer?

2. Is there any way to make this code quicker?

3. Is it better to target a file store rather than a ring buffer for this?

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003D
D8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000

[Code] .....

View 6 Replies View Related

SQL Server Admin 2014 :: Duration In Extended Events

Jun 11, 2015

I am wanting to get/filter on all queries and procs that take longer than 2 seconds to run (I'll balance real values later) but I'm not sure which Action out of the XE that I need.

I am using SQL Server 2014 and thought I had used sqlserver.sql_statement_completed.duration > 2000 in a previous version.

View 5 Replies View Related

SQL 2012 :: Can Combine Performance Monitor Counters With Extended Events

Oct 14, 2014

In the past, I've combined server side traces with Perfmon successfully, which is pretty useful, I know that. I would like to do the same with Extended Events, so I can correlate and analyze the server side as well.

View 4 Replies View Related

SQL 2012 :: Extended Events And Wait Stats For Specific Session

Mar 4, 2015

I wanted to demonstrate something about CXPACKET wait type. For the purpose of this demo I've created a query in AdventureWorks database that uses a parallel query plan, an extended events session that captured the wait statistics for a single session and a query that shows the extended event's data. I ran it and it worked fine. Then I dropped and recreated the event session (to clear the data), in a new window I wrote a transaction that updated the table fallowed by waitfor statement so the first query will be blocked for few seconds and ran the whole thing again. The select statement was blocked as expected (ran for 9 seconds instead on 1 second as it ran without the blocking), but the wait stats that I got were almost identical to the those that I got without blocking the query.

--Query that uses parallel query plan
With MyCTE as (
select top 50 * from Sales.SalesOrderHeader)
select top 10000 * from Sales.SalesOrderHeader, MyCTE
order by newid()

[code]....

View 1 Replies View Related

SQL Server 2012 :: Using Xquery To Get Event Data Back From Extended Events?

Feb 25, 2014

I am trying to use xquery to get event data back from extended events. I am trying to use some sample data from Grant Fritchey but I am getting null records back. Below is the xml - I just want to retrieve a distinct list of the client_hostname and client_app_name.

<event name="login" package="sqlserver" timestamp="2014-02-19T23:53:54.299Z">
<data name="is_cached"><value>true</value></data><data name="is_dac">
<value>false</value></data><data name="database_id"><value>7</value>
</data><data name="packet_size"><value>8000</value></data><data name="options">

[Code] ....

The query I have that doesn't work is :

WITH xEvents AS
(SELECT object_name AS xEventName,
CAST (event_data AS xml) AS xEventData
FROM sys.fn_xe_file_target_read_file
('C:LoginTraceShared_0*.xel', NULL, NULL, NULL))
SELECT distinct top 1000 xEventName,
xEventData.value('(/event/data[@action_name=''Client_APP_Name'']/value)[1]','varchar') Client_APP_Name,
xEventData.value('(/event/data[@action_name=''Client_Host_Name'']/value)[1]','varchar') Client_Host_Name
FROM xEvents

View 3 Replies View Related

SQL Server 2008 :: Extended Events Capture A Specific Proc Parameter

Feb 19, 2015

I know very little about Extended Events, but I know it is supposed to be more powerful than Profiler. The SQL Instance involved is 2008R2. I was asked whether we could capture when a stored proc was called with a certain parameter. So for example, if dbo.usp_mystoredproc is called and is passed a value of '12345a' for @customer, can that be captured? I would want to know the time it was called, the parameter and parameter value. Probably would be interested in the SPID or LoginName as well.

View 4 Replies View Related

SQL Server Admin 2014 :: Gathering Results From Extended Events Trace

Jun 16, 2015

I need to find any stored procedures that have not been used over a certain time period.

I have set up an extended events session to gather sp_statement_starting and _completed.

The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.

Do I have to repeat this process for every different object_id or is there a way I can query the trace results, using the object_id as my search criteria as one query ?

View 9 Replies View Related

SQL Tools :: Not Able To Clear Data Within Session Of Extended Events Unlike Trace

Nov 10, 2015

I have created a session in extended events and want to frequently monitor the events that i have filtered .The problem i am facing is i am not able to clear the previous data as we do it in SQL trace. i am able to see all the data that is occupied till now.whenever i start the test i want the current data only to be displayed.The work around is either delete the session and create a new one every-time which i do not want to do .I am able to see the CLEAR DATA FROM TABLE button but it is in Disabled state all the time.

View 2 Replies View Related

SQL Server Admin 2014 :: How To Read And Load Extended Events Into A Table For Querying

Jun 19, 2015

I am setting up extended events more or less just fine, however I am a bit confused as to how to read and load them into a table for querying. In particular the offset part - is there a way to load just a given dates worth in?

I've got the files configured to be 20MB before rolling over, the XE is running all the time.

So if i load in the full file now, say that covers 2.5 days worth, when I load it again tomorrow to get the updated data I'm also reloading today, which is a waste?

I presume I am going about this wrong, but lack an example that really goes into detail of practicalities of loading this data.

View 0 Replies View Related

Transact SQL :: Convert Or Change All Existing Traces To Extended Events In Server 2012

May 27, 2015

We are planning to convert or change all existing Traces to Extended Events in SQL server 2012. What is the procedure to convert custom traces. We have already created some below custom traces: like this we are planning to convert for all servers.

exec sp_trace_setevent @TraceID, 20, 23, @on
exec sp_trace_setevent @TraceID, 20, 8, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 64, @on
exec sp_trace_setevent @TraceID, 20, 1, @on
exec sp_trace_setevent @TraceID, 20, 21, @on

[code]...

View 6 Replies View Related

DB Engine :: How To Add Execution Plan To Extended Events Results Also When Capturing Deadlock Event

Oct 24, 2015

We know we can use the event lock_deadlock and xml_deadlock_report to capture the deadlock info,  however I also want to capture the execution plans for all of the SPIDs in the deadlock graph,  how to output the execution plans to the extended events trace results either ? such as if there is an action for execution plan or workaround for it ?If there is no built in action for execution plan , may I know if we can add the customized info to the extended events results file also ? Such as when the deadlock related event happens , then we can run a query to get some info ,then added the info along with other info such as sql_text, dbname  etc  to the events trace results file either ? The reason is if we also know the execution plans when the deadlock happens, it is useful to turning the query based on the execution plans to reduce deadlock happening .

View 5 Replies View Related

SQL Server Error Logs: What Events Is It Logging? Can We Edit And Include DDL Events?

Jun 20, 2007

Hello experts. I have been searching for anything about this but found very little. What are the events logged in SQL Server Error Logs aside from Successful/Failed Login, Backup/Restore/Recover database and, start/init sql server? Can we configure this to log other events, like CREATE or DBCC events for example? If so, how? Thanks a lot.

View 1 Replies View Related

Events Calendar Questions: How To Find Events In The Week End

Jan 28, 2006

I have a start date, end date for each event.

I want to list all events between the start and end date comes in Saturday or Sunday.
in SQL server 2005 TSQL statement.

any insights ?

View 1 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod

May 15, 2008

i have created the folowing function but keep geting an error.

Only functions and extended stored procedures can be executed from within a function.

Why am i getting this error!

Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)

set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'

execute sp_executesql @sqlval
return @ret
end

View 3 Replies View Related

Name Value Pair

Mar 12, 2008

Hi,I need to display a dataset where everything is dynamic.e.g. I have a table with columns "Code", "Description" and "Inspected" andanother table with columns "UserCode", "Name", "PostCode" and "Town" etcAnd I need to dislay data like this from a single db proc with parameters:-(TableName, ColumnName, ColumnValue)Procedure called with these parameters (CodeTable, Code, TD001) would returna dataset like this:-----------------------------------|Code | TD001|Description| Printer|Inspected | Y---------------------------------Not----------------------------------|TD001|Printer|Y---------------------------------Procedure called with these parameters (UserTable, UserCode, CP1) wouldreturn a dataset like:----------------------------------|UserCode | CP1|Name | Charles|PostCode | 2000|Town | Sydney---------------------------------Not---------------------------------|CP1| Charles| 2000| Sydney---------------------------------Any ideas how I would code the database proc, I did consider using XML butnot sure.ThanksAJP

View 3 Replies View Related

Returning Matching/Non Matching Records

Feb 4, 2007

Hi All

I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.

I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data

In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible

As always any help or direction would be appreciated.

View 5 Replies View Related

Pair Of Records

Sep 17, 2004

Hi folks,

I am trying to write a query to get data in pairs, for example, i have data like this:
sr_no week_no
1 24-A
2 24-B
3 24-C
4 25-A
5 25-B
6 26-A
7 26-B

I want to get data in pairs i.e. data for week_no 24-A and 24-B will come togather? is it possible?

Any urgent help will be highly appreicated.

Thanks

View 9 Replies View Related

Name Value Pair Design

Jul 20, 2005

I want to store many different types of objects in a single table. Iwas thinking of using the name value pair approach to achieve this.Does anybody have any experience with a such a design?The table might look like thisCREATE TABLE NV (pk int, type int, [name] varchar(100), valuevarchar(100))--Insert a manager - type = 1INSERT INTO NV (pk, type, [name], val)VALUES (11, 1, 'FirstName', 'John')INSERT INTO NV (pk, type, [name], val)VALUES (11, 1, 'LastName', 'Smith')INSERT INTO NV (pk, type, [name], val)VALUES (11, 1, 'Position', 'CEO')--Insert an employee - type = 2INSERT INTO NV (pk, type, [name], val)VALUES (21, 2, 'FirstName', 'Joe')INSERT INTO NV (pk, type, [name], val)VALUES (21, 2, 'LastName', 'Blog')INSERT INTO NV (pk, type, [name], val)VALUES (21, 2, 'Position', 'Developer')--Insert an inventory item - type = 3INSERT INTO NV (type, [name], val)VALUES (13, 3, 'Name', 'Chair')INSERT INTO NV (type, [name], val)VALUES (13, 3, 'Color', 'White')INSERT INTO NV (type, [name], val)VALUES (3, 3, 'Price', '$150')

View 3 Replies View Related

Key-Value Pair Design

Aug 8, 2007

Hi,
I have to log the Details of the incoming xml message into databse.
But the values logged will vary with the message.So I cant fix the mumber of columns.
I thought of using table in which the fields are logged as Key-Value Pairs. The table looks as below.

TransactionID ColumnKey ColumnValue
1111 PONumber 123
1111 Sender xxx
1111 Recever yyy

using dynomic query i was able to get the results as follows
TransactionID PONumber Sender Receiver
1111 123 xxx yyy

Till now every thing was fine. but now i got new requirement where i have to identify each column with its parent. For example if we consider the line items of the PO, table may look like below.

TransactionID ChildKey ChildValue ParantKey ParantValue
1111 PONumber 123 null null
1111 Sender xxx null null
1111 Recever yyy null null

1111 ItemName soap ItemID 123
1111 Quantity 4 ItemID 123
1111 UnitPrice 2.2 ItemID 123
1111 ItemName Brush ItemID 222
1111 Quantity 5 ItemID 222
1111 unitPrice 4.4 ItemID 222

I am unable to design the database which satisfy the requirement of the reporting.
I not even know how to query the data which is logged like this.
Help me by giving the inputs to design databse for the above problem and to query the data .

advance thanks
Srinivasa Mahendrakar

View 4 Replies View Related

How Do I Delete One Of A Pair Of Records.

Sep 8, 2007

I have a table, gdbdoc,  that contains record-key pairs, linking records in another table.   There is no significance in the order of the link: if records A and B are linked, then I don't care whether the link is A -> B or B -> A, and my normal query logic is     SELECT ... Where DCIindiid = A ...     union     SELECT ... Where DCILinkid = A(DCIindiid = key1, DCILinkid = Key2)
The link-creation process normally checks whether there is already a link in either direction.  Thus before creating a link A->B the logic checks to see whether either the A->B or B->A link record exists, and a new link is not created if the link already exists in either direction.  However recently one of my processes bypassed the reverse-link check, and I've ended up with a few hundred cases where there is both an A->B link and a B->A link. 
If I run a query: -      select gd1.* from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid
this displays all the records where one record links A -> B and there is also another record that links B -> A. 
How do I write a query to delete ONE of the pair of duplicate records?  I have two problems: -
Problem 1:  Table gdbdoc is keyed on (DCIindiid, DCILinkid).  Both guids are needed to create a unique key, and the table does not have a single key field.  You can't write    DELETE gdbdoc where DCIIndiid, DCILinkid IN select gd1.dciindiid, gd1.linkid                             from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid
as the DELETE ... SELECT ... syntax only seems to support a single returned value.  
Problem 2.  If we solved problem 1, we would (I think) delete BOTH the A->B link and the B->A link , whereas I only want to delete one of these links.
Afterthought:  Problem 2 seems easily solvable:  add "Where gd1.DCIindiid < gd1.DCILinkid" to the DELETE ... statement.  Although the concept of "<" doesn't really mean anything with a guid, this is accepted by SQL, and halves the number of records returned by the select.  Obviously I don't care which of the two links (A->B or B->A) is deleted.
Regards, Robert Barnes

View 3 Replies View Related

Need Another Pair Of Eyes Sql 'Order By'

Feb 9, 2006

my page suddenly stopped working when I wasn't working on it and it seems to be down to the 'ORDER BY' part of my SQL. I'm here alone as usual and I need someone to glance at the sql strings below. (yes, I do need the select *)
If I run this in SQL Manager it works fine:
SELECT * from dest_search WHERE trip_type like 'Trekking' ORDER BY start_date
if I do the same from my asp page it fails but if I leave out 'ORDER BY start_date' it works.

the error I get is:
Microsoft OLE DB Provider for SQL Server error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/Newindex/trip_types.asp, line 53

line 53 is the 'desc = oRS...' bizarrely
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
country = oRS("country")
53---> desc = oRS("description")
url_link = oRS("url_link")
startDate = oRS("start_date")
endDate = oRS("end_date")
trip = oRS("trip_type")
difficulty = oRS("difficulty")
not all the descriptions are filled in (some are null) but that doesn't stop SQL manager from working or unordered results coming up fine in my web page.

any comments gratefully received thanks.

View 6 Replies View Related

Primary Key Pair Constraints

Apr 29, 2008

Hi Folks,

I would like to create a table with primary key pair:

Key1 : nchar(10)
Key2: nchar(10)
Value: money

That is, Key1 and Key2 are the primary key columns for the table. I would like to think of (Key1='Foo', Key2='Bar') to be the "same" as (Key1='Bar', Key2='Foo'). Is there a way to enforce this as a table constraint, or do I have to enforce this manually in all procedures that modify and read the table?

Thanks!
Adam Cataldo

View 1 Replies View Related

Key/value Pair Table Update Query

Jan 18, 2005

I'm working with a table that I've created called Config which contains key/value pairs used to get and set site-wide settings. I'm now trying to create a web form which updates the table but I'm not sure how to create the most effective UPDATE query.

Table of course takes this form key | value
---------------------------------
config_setting1 | value1
config_setting2 | value2

I'm working with a System.Collections.Specialized.StringDictionary Class object which contains all of the pairs from my webform... anybody have a creative way to build an UPDATE string using this object???

Thanks for any help and suggestions,
ecolner@yahoo.com

View 1 Replies View Related

Transact SQL :: Select Data In Pair?

Sep 22, 2015

my business user want all record where glcode must start with 2 and 4

<sample data

Tran_No 

GLCode

abcd123

2123

abcd123

21235

abcd123

4289

[code]....

View 6 Replies View Related

Show Difference In Pair Of Nearly Identical Rows

Sep 28, 2007

I am trying to create an exception report that will show the difference between two versions of the same row. (Combination of two different sources in sql, with source 1 having childID = 0 and the other source having childID = 1; parentID is the link between them)

The results are as follows:

ParentID - ChildID - Col1 - Col2 - Col3
1 - 0 - AA - BB - CC
1 - 1 - AA - BF - CC
2 - 0 - GG - NN - TT
2 - 1 - DE - NN - TA
3 - 0 - etc
3 - 1 - etc
4 - etc

How can I render the differences in red in RS?

View 1 Replies View Related

How To Do Pair-wise Comparision Like Oracle In MS Sql Server 2000?

Apr 18, 2008



How can we do pair wise comaprission using Sub query as generally can be done in Oracle?


Thanx

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved