Helping out a developer get to the bottom of an issue, I was running profiler against his connection, and saw the following
INSERT BULK <tablename> <columnlist>
no indication of the data that was being passed.
Not being entirely sure what this was, I cut and paste this into a query window in SSMS, executed and got
Command(s) completed successfully.
later noticed that this connection was showing in sp_who2 as SUSPENDED.
Any clues?
INSERT BULK is what the .net SqlBulkCopy class uses under the hood. I don't think it's meant to be used directly, but you can certainly look at the .NET source code (which is now available) to get a better idea of what it's doing. The short story is that it issues this command, then writes a whole load of stuff directly to the TDS stream. Definitely scary territory!
Edit -> this isn't a seeder, right? Please delete my answer if it is :)
I know this post is nearly a decade old but wanted to say "thanks" because it's just what I needed to understand for a problem I'm having in SQL Server 2016. It has to do with the rampant amount of "Unused" space trapped in indexes because of INSERT BULK due to an "improvement" that MS made in 2016. They allocate an entire extent (8 pages) as a minimum to improve performance of "BULK" inserts of any kind and they don't check for existing, already allocated pages in the process to "save time".
The "fix" is to enable Trace Flag 692 ( https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?redirectedfrom=MSDN&view=sql-server-2017 ), which turns the new feature off.
Here's the complete text from the Trace Flag 692 entry on the page that I provided the link for... I've embolded the problem and I thank you again for identifying the source of the "INSERT BULK" entries that I captured with SQL Profiler.
----------------------------------------------------------------------------------------------------------------------------------------
692
Disables fast inserts while bulk loading data into heap or clustered index. Starting SQL Server 2016 (13.x), fast inserts is enabled by default leveraging minimal logging when database is in simple or bulk logged recovery model to optimize insert performance for records inserted into new pages. With fast inserts, each bulk load batch acquires new extent(s) bypassing the allocation lookup for existing extent with available free space to optimize insert performance.
With fast inserts, bulk loads with small batch sizes can lead to increased unused space consumed by objects hence it is recommended to use large batchsize for each batch to fill the extent completely. If increasing batchsize is not feasible, this trace flag can help reduce unused space reserved at the expense of performance.
Note: This trace flag applies to SQL Server 2016 (13.x) RTM and higher builds.
Scope: global or session
1 Person is following this question.
