Wouldn’t it be great to be able to directly monitor specific behaviors within SQL Server, like, oh, I don’t know, knowing exactly when, and how, someone is using BULK INSERT? Well, you can, thanks to Extended Events through the bulk_insert_usage event.
Bulk_insert_usage
The BULK INSERT command is extremely useful within SQL Server. It’s a way to move data into the database and provide some formatting on the way, efficiently, all through T-SQL. Hard to argue with the utility. Obviously, if you’re doing traditional data collection through Trace or Extended Events, you’ll see BULK INSERT commands within the T-SQL. However, Extended Events provides a specific event that tracks just the behavior of BULK INSERT: bulk_insert_usage.
Documentation on this is somewhat sparse. Some of the best is from a standard source, Jason Brimhall and his excellent blog. The description in the event itself is a little sparse:
Occurs when bulk insert statement is compiled.
It tells you when the event fires, but not what it’s doing. However, we can go and look at the output from the event itself to get more information:
So what we’re not getting here is the T-SQL (although, toss in an Action and you’ll have that too). What we are getting is details on how the BULK INSERT operation is being run. Jason does a great job of setting up a demo for this, so I’ll leave you to look at his stuff.
The key for me is that we can monitor behaviors on the system using Extended Events that we simply couldn’t do before.
Conclusion
I was just looking through the events trying to find something to talk about. I spotted the bulk_insert_usage event. After looking at the description I knew I needed to post something about it, just because it was so sparse. Sure enough Jason has a great description (and more). However, not much else is out there. It’s this kind of thing that I desperately want to draw your attention to. There’s so much more you can do with Extended Events beyond simply capturing query metrics, although you can do that too. Explore the things Extended Events has to offer. You’re going to be pleasantly surprised.