7

What is the best way to get very large SQL Server 2016 result sets (over 75 GB) to a CSV file? The engineers need this output to look for correlations.

The bcp route for a 73.5 GB file filled up tempdb and started crashing other applications, including the ETL process.

Our users want to export up to 500 GB.

What process would use the least amount of resources so that other applications keep running?

3
  • 1
    It may be useful to post a sample query (and even better, a query plan). It's possible that the query, rather than the export mechanism, is what is burning tempdb space. If so, there may well be query/index optimisations that would mitigate. Oct 3, 2017 at 18:25
  • I would, if you absolutely have to to it this way, suggest multiple csv files, limited by 10 000 rows at a time. It's difficult to see what it causing the resultset to be so big, do you have so many columns and weird data types, or just loads of rows? This method they require does seem very ancient, and loading a 75GB csv file into laptop/desktop memory is asking for issues. Oct 4, 2017 at 19:45
  • Is your tempdb big enough? I would think you would need a very large tempdb data and log to export that much data. Try giving tempdb 500GB data / 500GB log and see if it works. BCP is definitely the most efficient means of extracting data if I had to choose. Oct 20, 2017 at 21:23

3 Answers 3

6

I, too, used to have problems exporting large result sets (7 - 8 GB) to delimited files. Neither SQLCMD nor BCP nor SSIS could handle dynamic result sets, dynamic text-qualification, adding a column header row, etc. So, I built my own tool to handle this. It currently exits as the DB_BulkExport Stored Procedure in the SQL# SQLCLR library (that I wrote), though the plan is to break it out into a stand-alone export utility and expand on the features.

Please note that the DB_BulkExport Stored Procedure is only available in the Full (paid) version of SQL# (i.e. it is not in the Free version), but I am not aware of any free utility to do this type of thing (hence why I wrote my own).

Regarding the export of large result sets: I so far have not run into problems with memory since it writes each row out to the file as it is read from the result set.

If you only need this export for a single table / query that doesn't really change in terms of structure / schema, then it might be best to write a small, specialized app yourself, either in .NET or PowerShell. The tricky part is in handling dynamic requirements. But if you know what the columns are named and the datatypes, etc, then it should be a simple-enough matter of opening the output file, executing the query, and then for every row in the SqlDataReader just String.Concat everything together, including text-qualification where needed and applying appropriate Format specifiers when needed (i.e. Date(Time) values).

3

PowerShell may work well for you.

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DBName -Query $ExportSQL | Export-CSV -Path $ExportFile -NoTypeInformation

Invoke-Sqlcmd is included in the SqlServer module from Microsoft (Install-Module sqlserver).

I'm not super sure, but assume that Invoke-SqlCmd uses ExecuteReader which streams data and doesn't use memory.

Earlier versions of PowerShell excessively logged CSV functions so make sure you try with a newer version.

3
  • 1
    Invoke-SqlCmd will put all data in memory, tried on PowerShell 5.1 Apr 18, 2018 at 13:42
  • I'm not super sure, but assume that Invoke-SqlCmd uses ExecuteReader which streams data and doesn't use memory. Nope, unfortunately it doesnt do that. It reads the whole query into an in-memory DataTable before piping it on to the next cmdlet. Its fast but very heavy on memory
    – codeulike
    Jun 23, 2023 at 8:16
  • There’s a way to do it faster using native PowerShell. Aaron Nelson once tweeted about it, I’ll see if I can find it. But ultimately, I used the LumenWorks dll and now Invoke-DbaQuery streams large datasets. Jun 24, 2023 at 9:22
1

I came across the same problem and ended up having to develop a custom solution in PowerShell that would solve it.

Basically, the main idea of my solution is to use an intermediate data structure (a DataTable) that collects the query data, but a certain number of rows at a time. Once the maximum capacity of the intermediate data structure is reached, its contents are written to the target file, emptied, and immediately loaded with the next rows of data from the data source.

You'll find all the details of the implementation in this article.

The repository of the solution is on GitHub at this link under MIT license.

2
  • 1
    This looks really useful thanks
    – codeulike
    Jun 23, 2023 at 8:19
  • @codeulike welcome! :)
    – lucazav
    Jun 24, 2023 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.