Use of the C: drive by the Profiler UI and XE Live Data Viewer

I had an email from a fellow MVP this week who is in the process of learning Extended Events (hooray!). One question this person had was whether Extended Events had the same issue as Profiler where the C: drive can be heavily used and potentially run out space.

To clarify, with regard to Profiler UI, if you are using the UI to capture events (not a server side trace that writes to a file which is the preferred method), the Profiler UI does file caching of events locally when it runs against a SQL Server instance. It also performs caching when reading an existing file. These cached events are stored on the C:\drive by default, unless you have changed the User TMP location in Environment Variables (Control Panel | System | Edit the system environment variables | Advanced | Environment Variables… ):

Depending on what events you have configured for Profiler, your filter(s), the workload, and how long you run Profiler, you could generate more events than the UI can handle. Therefore, they’ll start buffering to the User TMP location. If you’re not paying attention, you can fill up the C: drive. This can cause applications (including SQL Server) to generate errors or stop working entirely. Not good.

Reference: https://msdn.microsoft.com/en-us/library/ms174203.aspx

Now, back to the original question. Does the same problem exist for Extended Events? Only if you’re using the Live Data Viewer.  After you have an event session created (you can just use system_health for this example), within Management Studio, go to Management | Extended Events | Sessions, select the session and right-click and select Watch Live Data:

As events are captured, they will show up in the data view. As with the Profiler UI, the number of events that appear will depend on the session configuration and the workload. The Live Data Viewer will only show a maximum of one million (1,000,000) events. Once that number has been exceeded, it will start to cache events to the User TMP location, just like the Profiler UI. And just like the Profiler UI, that can fill up the C: drive if that is still the User TMP location. Note that the Live Data Viewer will automatically disconnect and stop displaying events if the engine determines it’s negatively affecting performance. If the event session’s internal memory buffers fill up and the events cannot be dispatched to the event stream for consumption, the engine will disconnect the UI from the event stream. –

[More on the viewer if you’re interested: Introducing the Extended Events Reader]

There are two action items for you:

1. Don’t use the Profiler UI unless it’s a NON-Production environment.
   1. If you refuse to give up that woobie (name the movie reference) at least change the User TMP location to something other than C:
2. If you use the Live Data View in Extended Events for event sessions that generate a lot of events, change the User TMP location to something other C:

Shout out to Jonathan for a review of this one.