Bulk insert can be an extremely helpful tool to help ingest data into SQL Server efficiently. Sometimes, it is necessary to capture metrics in regards to the bulk insert in order to understand the who, when, and potentially why related to the insert process.
Many moons ago, I worked with a client issue in regards to performance issues stemming around linked servers. From that work, I shared an article about monitoring linked servers.
Also stemming from that work was a clear pattern that there were some issues also relating to bulk inserts. This experience helped me to generate a means to monitor and trap useful data while troubleshooting. From this monitoring solution, we were able to isolate specific processes for the client to help improve performance.
In this article, I will share the solution that I created through the power of Extended Events.
Bulk Insert Monitoring
When you are looking to trace for bulk inserts via Extended Events (XEvents), it is crucial to remember that there are multiple events. In addition, there are multiple means to perform a bulk insert. I show three methods to perform a bulk insert in my demo script.
The bulk insert methods I will explore are BCP, BULK INSERT, and OPENROWSET(BULK). Each has advantages and disadvantages, none of which I will explore in this article. Suffice it to say that each has slightly different uses and it is worth testing them to see which works best for your needs!
Bulk Events
Extended Events gets more and more powerful with each update to SQL Server. Sometimes this is demonstrated through the addition of certain events. …
Continue reading on jasonbrimhall.info.
