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
ADVERTISEMENT
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
Oct 6, 2015
I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?
View 0 Replies
View Related
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
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
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
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
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
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
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
Jun 30, 2015
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
View 2 Replies
View Related
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
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
View Related
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
May 25, 2008
Hi,
I'm playing around with IDtsEvents.
I've noticed that the /rep option of dtexec allows me to specify that dataflow events are outputted to the console during execution. Here's an example of a dataflow event:
Code SnippetDataFlow: 2008-05-25 22:27:52.08
Source: DFT My dataflow
Component "Union All" (18) will receive 1807 rows on input "Union All Input 1
" (19)
End DataFlow
However, I can't find a method on IDtsEvents that enables me to capture dataflow events such as the one above and that seems a bit strange to me.
So, question is... Is there a way of capturing dataflow events in an IDtsEvents implementation and if not, why not?
Thanks
Jamie
[Microsoft follow-up]
View 21 Replies
View Related
Nov 18, 2005
Hi all,
I am using an extended stored procedure that sends email using SMTP. The procedure can be downloaded here (http://www.sqldev.net/xp/xpsmtp.htm). If I execute the procedure in QA and it fails for some reason, the error message is printed in the Messages tab. Is there any way to capture this message/messages when I use the XP in my stored procedures?
Your help is much appreciated.
View 3 Replies
View Related
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
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
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
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
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
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
Feb 1, 2012
I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I want see the following:
Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2
get database name and server name in DATA FLOW only (without using a for each in Control Flow)
View 5 Replies
View Related
Jan 13, 2013
Or can it record before and after column changes based on the LSN only?
An extract from a file based legacy accounting system is performed every night. The system does not have a primary key because transactions are managed through program code. (the more things change...). The extract is copied to text in Unix and FTP'd to Windows, where the file is loaded into SQL Server by kill & fill. Because of the expense of modifying the source system, there is enormous inertia/resistance to injecting a primary key at the source, so kill & fill it stays.
In reading about Change Data Capture, it seemed to me that column level insert update and delete are stored in tables that remember the before and after content of each column tracked. In my reading I have seen many references to the LSN to decide when and what to record as changed, but I have not seen any refereference to the necessity of a primary key for Change Data Capture to work. This is in contrast to replication, where the requirement for the existence of a primary key is made plain.
Is it possible to use Change Data Capture against a table without a primary key? How to use it to change the extract from kill and fill to incremental.
View 9 Replies
View Related
Mar 19, 2015
I have created a trigger on a table that get's too many updates so to analyze what records and what columns get updates I have coded a trigger that inserts into a table the record key and a flag on each column to let me know what is updates. Now that I am presenting the data to the developers I have been asked to provide the update / insert or delete statement
So on my trigger I have tried using dynamic management views but its returning the actual trigger code not the parent SQL Script that triggered it.
This is what I am using.
select@dDate as dDate, dest.[dbid], dest.[objectid], dest.[number], dest.[encrypted],
case when sder.[statement_start_offset] > 0 --the start of the active command is not at the beginning of the full command text
thencasesder.[statement_end_offset]
[Code] .....
View 2 Replies
View Related
May 4, 2015
Is there a way to capture any firing for a SP such as (datetime , loginname,..) and not using SQL profiler ?
View 3 Replies
View Related
Oct 25, 2000
I created an extended procedure DLL according to the guidelines set forth in the Books Online. I placed this DLL to be called in a trigger. The DLL fires, but some aspects of the dll fail. Is there a C lang. limitation when used with SQL server? I can do file i/o in the dll (open a file, write to it) but other functions such as C API functions FindWindow / SendMessage always fail. I have tested the DLL outside of SQL server and it works fine. Whats up, I can't beleive that Microsoft would write SQLServer where only certain functions of the DLL called by SQL server will work.. Any ideas would be appreciated! KT
View 1 Replies
View Related
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
Feb 26, 2006
I have a stored proc with a query which checks whether an identical
value is already in the database table. If so, it returns a value of 1.
How do I caputure this value in an asp.net page in order to display a
message accordingly? (using ASP.NET 1.1)
Currently my stored proc looks something like this (snippet only):
If Exists(
SELECT mydoc WHERE....
)
Return 1
Else
...INSERT INTO.... code here.
View 2 Replies
View Related
Jan 8, 2001
I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.
I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.
Thanks.
Tim
View 3 Replies
View Related
Sep 18, 2007
How do I go about if I want to capture a fresh copy of the stored procedure?
Nishi
View 5 Replies
View Related
Oct 28, 2006
I have been using return values to check the status of my stored procedures called from an application. But how does one read the new raiserror values that are passed with sql server 2005 and using ado.net 2.0. Are they returned as parameters or as a dataset or what?
Thanks
smHaig
View 2 Replies
View Related
Jun 3, 2015
SQL 2008 R2
I have a partitioned table in which one of the partitions is on the Primary filegroup. I want to move the data off of that Primary filegroup, and and on to a new filegroup named RTFG6.
Scheme and function currently defined as:
CREATE PARTITION SCHEME [PS1_Left_id] AS PARTITION [PF1_Left_id] TO ([RTFG1], [RTFG2], [RTFG3], [RTFG4], [RTFG5], [PRIMARY])
CREATE PARTITION FUNCTION [PF1_Left_id](int) AS RANGE LEFT FOR VALUES (10, 15, 35, 48, 53)
I've tried split and merge, and for whatever reason, always end up with the Primary filegroup holding data.
How do i get it off of Primary completely, and onto RTFG1 to RTFG6?
I don't want to export to a holding table and re-create the table if i can avoid it, due to identity columns and relationships with multiple tables.
View 0 Replies
View Related
