So what is the solution? Well it really depends. But the solution I wrote walks you through
Setting the default fill factor
Tracking page splits
Lowering the fill factor
Read on to learn more.
There was a question in dba.stackexchange.com titled “module_end extended events duration in microseconds?” that I answered and it was Microsecond in that instance.
Later on I questioned myself if the duration is always in Microseconds for extended events. I found, it is a mix of Millisecond, Microsecond and some are unknown meaning famous NULL. I wrote below tsql code to determine which is what.
Click through for the script that Taiob used to determine the answer.
Tracy Boggiano shows how to enhance the built-in Extended Events session for Availability Groups:
Now that the extended events session is setup we can use some queries to query information about our AGs and error messages happening on our servers. First to query when the server failover and becomes your primary server query the event availability_replica_state_change. The first 10 lines just read in the files for the extended event session so you don’t have to identify the exact filenames. Then we parse the xml event for get the timestamp, previous state, current state, repliace name, and group name for the event FROM the filenames we collected before. In the WHERE clause were are looking for when the state has changed to PRIMARY_NORMAL indicating a failover to the server.
Read on for scripts showing what she extends and also how to query this data.
Kenneth Fisher describes what is in the system_health Extended Events session:
Per BOL you get the following information:
Errors with a severity of >= 20.
Memory related errors (Errors 17803, 701, 802, 8645, 8651, 8657 and 8902).
Non-yielding scheduler problems (Error 17883).
Deadlocks.
Sessions that have waited on locks for > 30 seconds.
Sessions waiting for a long time on preemptive waits (waits on external API calls).
Read on to learn more of the things this session contains as well as a couple ways you can access the data.
Andrew Pruski shows how to use extended events to find queries with errors:
What this is going to do is create an extended event that will automatically startup when the SQL instance starts and capture all errors recorded that have a severity level greater than 10.
Full documentation on severity levels can be found here but levels 1 through 10 are really just information and you don’t need to worry about them.
I’ve also added in some extra information in the ACTION section (for bits and bobs that aren’t automatically included) and have set the maximum number of files that can be generated to 10, each with a max size of 5MB.
Check it out. At one point, I had created a small WPF application to show me errors that extended events caught. It completely freaked out a developer when I IM’d him and told him how to fix the query he’d just run from the privacy of his cube, with me nowhere to be seen.
Dave Mason whipped up an application to track when a particular extended event fires:
Event Responses: when an event is handled, specify how you want to respond to it. In the “Event Responses” section, there are two options: play a system sound or send email (you must choose at least one option). Pick the system sound desired and/or enter an email address.
Dave has made the source code available as well so you can extend the functionality.
Arun Sirpal has an Extended Events session which tracks growth events on a database file:
A quick post that is hopefully useful, I wanted a quick way to find the time, size of the database file size change and who caused it.
I went down the extended events route using sqlserver.database_file_size_change event. By the way I am no Extended Events expert, I write a lot via trial and error I am trying to wean off Profiler.
Read on for the script as well as a query which shreds the XML and returns a result set.
Wayne Sheffield concludes his two-part series on converting a trace to use Extended Events:
This query extracts the data from XE file target. It also calculates the end date, and displays both the internal and user-friendly names for the resource_type and mode columns – the internal values are what the trace was returning.
For a quick recap: in Part 1, you learned how to convert an existing trace into an XE session, identifying the columns and stepping through the GUI for creating the XE session. In part 2 you learned how to query both the trace and XE file target outputs, and then compared the two outputs and learned that all of the data in the trace output is available in the XE output.
Read the whole thing.
Arun Sirpal is a dangerous man of mystery and danger, but mostly danger:
I did a dangerous thing, and I want to make sure that YOU DO NOT do the same.
I was creating a couple of extended events sessions and was playing around with some actions. I ended up with the following code where I was after a guy called Shane:
The probability that you intend to set a breakpoint in SQL Server via Extended Event is quite low (low enough that if you’re doing it, you should already know what you’re doing), but click through to see exactly what damage you can do.
Now that you have this XE session scripted out, it can be easily installed on multiple servers. If you encounter a deadlock problem, you can easily start the XE session and let it run to trap your deadlocks. They will be persisted to a file dedicated for the deadlocks. You can use my Deadlock Shredder script at http://bit.ly/ShredDL to read the deadlocks from the file and shred the deadlock XML into a tabular output.
Note that the default system_health XE session also captures deadlocks. I like to have a dedicated session for just deadlocks. As lightweight as XE is, sometimes it may benefit a server to turn off the system_health session. Additionally, Jonathan Kehayias has a script that will take a running trace and completely script out an XE session for it. This script can be found at https://www.sqlskills.com/blogs/jonathan/converting-sql-trace-to-extended-events-in-sql-server-2012/. Even though this script is available, I like to figure things out for myself so that I can learn what is actually going on.
Extended Events are extremely useful for administrators, typically with a fraction of the overhead cost of server-side (much less Profiler) traces.
Kevin Feasel
2017-07-25
Extended Events, Indexing