6

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?

  • 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. – Paul Holmes Oct 3 '17 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. – Henrico Bekker Oct 4 '17 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. – Joshua Guttman Oct 20 '17 at 21:23
7

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).

4

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.

  • Invoke-SqlCmd will put all data in memory, tried on PowerShell 5.1 – Ivan Akcheurov Apr 18 '18 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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