66

There is quite a bit of valuable information that SQL Server tracks for you by default. Since SQL Server 2005 there has been a "default trace" that runs in the background, and since SQL Server 2008 there has been an Extended Events session automatically running, called system_health. You can also find certain information from the SQL Server error log, the ...


31

It is a mix of Millisecond, Microsecond and some are unknown. Easiest way to find I used below tsql code to determine which is what. SELECT p.name package_name, o.name event_name, c.name event_field, DurationUnit= CASE WHEN c.description LIKE '%milli%' THEN SUBSTRING(c.description, ...


15

I found the problem. My login had a different default database than the master database. When I changed my default database to master, the error went away, and I was able to Watch Live Data on the extended event sessions. To change the default database, in SSMS, I expanded the server, Security, Logins. I right-clicked on my user credentials. On the Login ...


11

Thanks to @Tom V for identifying this blog post that identifies the need for a temporary table. Adapting the ideas in the blog post, this now works very quickly: /*************************** shred the Event Data into readable form ***************************/ DECLARE @xml XML; SELECT TOP(1) @xml = CONVERT(xml, xet.target_data) FROM sys....


9

When using SQL Server Enterprise Edition (or running any edition newer than 2016 SP1) I would recommend going with SQLAudit functionality. This will give you some great granualar information about who is touching your tables, and the commands that are being executed. For something like this you would want to use a database audit specification along with a ...


9

How many actions are allowed? Does it vary by event? I did some research and yes, there is a limit to the number of actions and events that can be added to an extended event definition. It's not a "hard" value but based on many different inputs, thus one definition that doesn't work could work with just the removal of a single event or a single action in a ...


9

How is the mechanism working in SQL Server of rolling over the files The files will roll over when any of the following occurs: the file reaches its max size (in this case, 5 MB) the XE session is stopped and restarted for any reason This could be someone actually running the command: ALTER EVENT SESSION [session_name] ON SERVER STATE = STOP; Or it ...


8

Out of the box, no. You'd need to hook into the event stream for extended events and then take action based on that. Tom Stringer has a good overview and sample code to do this! See also Introducing the Extended Events Reader by Mike Wachal. Jonathan Kehayias also describes it in detail in his Pluralsight course.


8

I have seen this issue before, what seems to happen if you have this type of sequence: create session start session stop session add columnar data (action or field) start session The GUI doesn't show the added columnar data. Your options here would be to directly query the target or to clear/rename the old session definition's XEL files.


8

For large XE trace files, I use custom tooling with QueryableXEventData. I've found this to be much faster than XML parsing in T-SQL. Below is a basic PowerShell that imports selected fields and actions from an rpc_completed event trace into a table in 10K batches. You'll need to include an Add-Type command for the Microsoft.SqlServer.XE.Core.dll and ...


8

Include the module_start event filtered on the stored procedure name and a histogram target bucket based on the object_name field. This will capture and aggregate the execution counts every time the stored procedure is executed, whether it is called directly or indirectly (i.e. called other stored procedures and triggers). CREATE EVENT SESSION [...


7

Two things that are the problem. There is no database_name action in SQL Server 2008 R2 (it was introduced in SQL Server 2012): select action_name = p.name + '.' + o.name from sys.dm_xe_objects o inner join sys.dm_xe_packages p on o.package_guid = p.guid where o.object_type = 'action' and o.name like '%database%'; The output from that is: ...


7

The sys.dm_xe_object_columns DMV shows metadata for each of the columns available for each event. Included is a "description" column, which is useful in answering your question. select name , object_name , type_name , description from sys.dm_xe_object_columns where name = 'cpu_time' -- any XE column name here This will show you ...


7

Your original sample code wasn't re-runnable (the table creation wasn't in there, or the population of rows) so I rewrote it to flesh it out and make it re-runnable. The problem is that your query is trivially simple: it doesn't matter whether stats are updated or not, it's going to produce the same plan. This tweak will make your SELECT query bypass ...


7

Extended Events can capture all the events that are traced by auditing? Yes and no. The built in auditing framework is built upon the extended events framework. This means any of the built in auditing will be serviced via the XE framework, assuming it is setup. Can we get all the functionalities of auditing just by using XEvents? You can get most, but ...


6

You can change this behavior with the ALTER SERVER CONFIGURATION statement. The flexibility to modify all of the necessary parameters (path, max files, max file size) should give you enough control to get it to where you want. See the bottom of the referenced link above for samples/examples. Here is one copied from the source: ALTER SERVER CONFIGURATION ...


6

Not sure what you are seeing and not seeing in the event log but it works as expected for me (version 11.0.3000). The mistake you did with your extra path expressions is that you forgot Stuff in the path. Testing this with the extended events and looking at the execution plan for operator Table Valued Function XML Reader ... (there should be none), I found ...


6

Getting the SQL from a DDL Trigger for whatever query that is dropping this Stored Procedure will only help so much. If the query is coming from Dynamic SQL from a Stored Procedure, or from a release script, or an integration test, application code, etc, then you will likely only capture the DROP PROCEDURE ... which doesn't give much of a clue as to where ...


6

These statements look to be activity generated by SQL Server tools, management API, or a third party product. This activity happens on regular connections, not a system session which the is_system field identifies. You might be able to add a filter on client_app_name to exclude the unwanted activity unless some of the activity from that application or tool ...


6

As Kin suggested in his comment, the F$ share is problematic since that only allows administrator access. Although you've provided file-system access to the folders in question, the account does not have access to the share. Create a share specifically for the xel files, and provide access to the SQL Server Service account on both the Share, and the folder....


6

I can't test this theory at the moment, but based on the most recent capture data posted to GitHub, I would say that the reason that thee <process> node is empty is that it requires a currently running request (many of the attributes are found in sys.dm_exec_requests and not in sys.dm_exec_sessions) and without a currently running request, it can't ...


6

You can get all the system_health event files and their oldest event like this: DECLARE @file_name AS nvarchar(max); DECLARE @file_path AS nvarchar(max); SELECT @file_name = CAST(st.target_data AS xml).value( N'(EventFileTarget/File/@name)[1]', N'nvarchar(max)') FROM sys.dm_xe_sessions s INNER JOIN sys.dm_xe_session_targets st ...


6

Below is a powershell example that summarizes events by name from the local system_health target files using the new Microsoft [SqlServer.XEvent PowerShell module][1]. I've found it's much faster to process high event volumes using .NET/PowerShell than parsing XML in T-SQL. You can schedule this as a SQL Agent job to identify what is driving the event ...


5

Yes you can do that using Event Notification for deadlocks / blocking / create database / drop database, and many more events as outlined here. Below is the script that will help you with Deadlock detection and email in real time : It will create an alert as well as a TSQL Job to fire with all the details emailed to DBA team. look for change Here to ...


5

I believe this issue was fixed in SQL Server 2008 R2 SP2 Cumulative Update #1 (10.50.4260): KB #2511963 - FIX: Query stops responding in SQL Server 2008 or in SQL Server 2008 R2 if the query alters or stops an Extended Events session They don't mention your specific wait type but I have seen others who have and stated that this fix resolved their issue. So,...


5

Of course there will be an increase on CPU, as any other process on the server. But Extended Events are recommended precisely due to the low resource needs of running them. Using Extended Events to capture information takes much fewer resources than using the long old known profiler tool for example. Use it wisely of course, don't setup and run thousands of ...


5

sp_statement_completed is doing exactly what you are asking it to : fire for every statement in a procedure. You likely want to change that to sqlserver.module_end - but some other things in your session don't seem to match what you're after anyway, like transaction_id, when would instance name ever be different, and capturing both database_id and ...


5

No, there is no way to rename an Extended Events session without dropping and recreating it. The NAME property is not part of the ALTER EVENT SESSION syntax, nor is it supported by sp_rename. Simply use SSMS to script and recreate the session easily. You don't lose anything by doing this.


5

First, identify the folder that is the problem. In SSMS, with results set to grid, run the following query: SELECT target_data = CONVERT(xml, target_data) FROM sys.dm_xe_session_targets WHERE target_package_guid = '5B2DA06D-898A-43C8-9309-39BBBE93EBBD' AND target_name = N'event_file'; Should return one row, and you should be able to click on it from ...


Only top voted, non community-wiki answers of a minimum length are eligible