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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
Aug 4, 2015
For one of our SQL server 2005 Ent edition 64 bit SP4 which has transnational replication set up and used for heavy reporting, i was trying to counter out the performance of slow running queries which basically runs and get suspended and most often are seeing waiting:So i tried to analyse the wait stats and come up with below stats where ASYNC_NETWORK_IO dominated for a collection of two weeks data.
View 15 Replies
View Related
Oct 5, 2015
I would like to know what a particular session is waiting on (cpu, io, memory etc).
For that I try to figure out how to use extended events. I would prefer to avoid the use files written to db-server, why I tried "histogram target".
Well my attempt does not produce usefull output,
-- check current event sessions
SELECT a.name,
CASE WHEN b.name IS NOT NULL
THEN 'Started'
ELSE 'Stopped'
END AS current_state
[Code] ....
The intention was to get a pareto chart, to see the top wait events in the session. I would also like to see amount of cpu used. Based on this tuning effort could be prioritized.
View 6 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
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
Jan 25, 2012
I have two extended events sessions running on a server. I do have sql jobs that automatically stop the XE sessions and import the results to working tables. I see two "ALTER EVENT SESSION XXXX ON SERVER STATE = STOP" statements that are being executed for more than 2 days, the wait types are XE_SERVICES_MUTEX and PREEMPTIVE_XE_SESSIONCOMMIT. This is not the first time I see this behavior.I do not want to kill the sessions (I guess the sessions won't die anyway) neither restart the sql service
View 7 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
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
Oct 19, 2000
Hi:
I like to know how I may store some user-specific data at the beginning of a session and retrieve the same data later during the session in a SQL command. For those of you familiar with ORACLE, I am trying to mimic the SET_CLIENT_INFO and GET_CLIENT_INFO functions of ORACLE's DBMS_APPLICATION_INFO package on SQL Server.
Any suggestions?
thanks,
Peter
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
Jul 22, 2014
I am using Ola Hallengrens scripts for index and stats maintenance but I am wondering what most people to in terms of the maintenance schedules. At present we do an index rebuild reorg weekly, but do people also do update stats nightly?
I suppose there is an element of "it depends" here in that the data may be fairly static so the update stats may not be required, or if heavily updated then perhaps rebuilding indexes may be required more frequently.
View 5 Replies
View Related
Mar 24, 2015
We have a file import job. This job typically imports millions of records into a SQL2008 DB. After the load the DB performance goes down the drain. Thus far, their solution has been to rebuild indexes on effected tables. I'd like to come up with a better solution. My guess is that after the load, the statistics are shot until the next stats update.
What is the best way to handle this scenario? There must be some way to keep the stats current during a big data load.
View 3 Replies
View Related
Apr 9, 2014
I noticed today a session that was executing a FULL SCAN update as follows:
UPDATE STATISTICS [XXXX].[XXXX].[XXXX] [_WA_Sys_00000009_318D45CA] WITH FULLSCAN
When I checked the sys.dm_exec_query_memory_grants DMV for the session I could see the following values:
requested_memory_kb granted_memory_kb used_memory_kb max_used_memory_kb
145,705,216 145,705,216 139,977,336 139,980,408
When I checked the Properties of the Statistic I can see it is on a varchar(3) field when there are only 3 different values in there - all char(1)
The total size of the data in the table according to the Disk Usage By Top Table Report is 199,680,712KB
So my question is this...
For the UPDATE STATS on this one column with FULL SCAN, does SQL Server read the entire table into the Buffer Pool. If so then if the table had 199,680,712KB of data then why did the session request 145,705,216KB.
Or does SQL Server just read the column and ClusteredIndex Key into the Buffer Pool?
View 1 Replies
View Related
Oct 17, 2014
I have this t-sql code which will get some table stats on one database at a time, I was wondering how I would get it to loop through all databases so it will pull the stats from all tables in all databases. Here is my code:
Select object_schema_name(UStat.object_id)
+ '.' + object_name(UStat.object_id) As [Object Name]
,Case
When Sum(User_Updates + User_Seeks + User_Scans + User_Lookups) = 0 Then Null
Else Cast(Sum(User_Seeks + User_Scans + User_Lookups) As Decimal)
/ Cast(Sum(User_Updates
[code]....
View 1 Replies
View Related
Mar 18, 2015
I have a job that runs nightly, rebuild index. job runs fine every night but every few weeks it fails.
USE msdb
GO
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = 'Y',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
This calls the Sp that does the Reindex. It fails at the update statistics with a very generic message. like " Command: UPDATE STATISTICS [xxxx_DB].[dbo].[xxxx_xxx] [_WA_Sys_00000007_49C3F6B7] [SQ... The step failed."
I suspect it has more error but this is all it is showing me when I right click on the job history. therefore, I updated the job step in the advance tab with log to a txt file. Am I on the right track or there is another way to see error some where else.
I looked at the logs but they didn't show any thing.
View 9 Replies
View Related
Apr 15, 2014
I am currently investigating aa high avg write time ms issue (145ms) which seems to be only occuring on the tempdb data files.I have followed the recommended setup of TEMPDB in that
1. Data files = number of physical cores
2. Data files and logfiles are on separate partitions away from the other databases.
3. Tempdb is presized and no incremental file increases look like they are happening with frequency.
We have sharepoint 2012 setup on other sql servers and with TEMPDB setup following the same guidelines, with far more Sharepoint activity on a similary specified hardware which is why its confusing.FileIO auditing on the partitions themselves shows that the FileIO is very fast on the partitions that the tempdb data file which leads me to beleive that Sharepoint may be the culprit perhaps due to excess use of tempdb with operations taking a long time to resolve.
View 3 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
Jan 15, 2015
I am logging into a SQL instance to run the following query:
DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'sharemasterFULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
The share in which the folder is to be created has my account added with full permissions to create files. However this command fails unless I add the SQL Service account user with rights to the folder also.
Is this expected behaviour, is this something specific to extended stored procedures?
View 0 Replies
View Related
Feb 12, 2015
What is the use of running DBCC FREESESSIONCACHE.
How frequently can we do this on all the databases.
What happens if we schedule to run it every 1min.
Are there any advantages/disadvantages of running this on all databases.
View 5 Replies
View Related
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