SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Executing HUGE scripts with SQLCMD


Executing HUGE scripts with SQLCMD

Author
Message
v.bartosh
v.bartosh
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 133
We've got a system which generates an SQL script to be executed against some test database, and were using SQLCMD for running that script. Everything was fine while the size of the script was relatively small, but now we've ran into gigabyte sizes and more, and the script execution started failing with out of memory error. The script itself is just a bunch of 'exec storedprocedure's, split into batches by 'GO' statement after every 1000 exec's (every 21k lines of code effectively, it's about 1MB size)
I suppose that SQLCMD tries to read the whole file into memory and then makes some splits/conversions with it, consuming more and more. Is there any way to restritct it to reading and running just one batch at a time?
Thomas Rushton
Thomas Rushton
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: Moderators
Points: 17234 Visits: 8426
There's a Connect item to increase / remove the file size limit for sqlcmd - https://connect.microsoft.com/SQLServer/feedback/details/1844733/sqlcmd-exe-file-size-limit-is-2-gb
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (331K reputation)SSC Guru (331K reputation)SSC Guru (331K reputation)SSC Guru (331K reputation)SSC Guru (331K reputation)SSC Guru (331K reputation)SSC Guru (331K reputation)SSC Guru (331K reputation)

Group: General Forum Members
Points: 331854 Visits: 33962
Try switching to PowerShell.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ZZartin
ZZartin
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22121 Visits: 15937
I have to think that if you're generating scripts that are multiple GB's something else can be improved other than just finding a way to actually run them.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)

Group: General Forum Members
Points: 788957 Visits: 45931
v.bartosh - Tuesday, December 19, 2017 2:15 AM
We've got a system which generates an SQL script to be executed against some test database, and were using SQLCMD for running that script. Everything was fine while the size of the script was relatively small, but now we've ran into gigabyte sizes and more, and the script execution started failing with out of memory error. The script itself is just a bunch of 'exec storedprocedure's, split into batches by 'GO' statement after every 1000 exec's (every 21k lines of code effectively, it's about 1MB size)
I suppose that SQLCMD tries to read the whole file into memory and then makes some splits/conversions with it, consuming more and more. Is there any way to restritct it to reading and running just one batch at a time?


I suspect that you're generating a script that uses a RBAR stored procedure to insert data into tables and that you're generating an EXEC for every row you're trying to insert.

If that's what you're doing, try the following, instead. Whatever "system" you're using to generate the SQLCMD stuff, change that to generate a TAB delimited file and using BULK INSERT to load the data file into a table. Then, if you insist on continuing to use the RBAR stored procedure to input the data, just loop through the rows of the table to do it.

A much better thing to do would be to still create the TAB delimited file and BULK INSERT it into a table and then rewrite the RBAR stored procedure to do things in bulk, as well.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
v.bartosh
v.bartosh
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 133
Jeff Moden - Tuesday, December 19, 2017 9:32 PM
I suspect that you're generating a script that uses a RBAR stored procedure to insert data into tables and that you're generating an EXEC for every row you're trying to insert.

If that's what you're doing, try the following, instead. Whatever "system" you're using to generate the SQLCMD stuff, change that to generate a TAB delimited file and using BULK INSERT to load the data file into a table. Then, if you insist on continuing to use the RBAR stored procedure to input the data, just loop through the rows of the table to do it.

A much better thing to do would be to still create the TAB delimited file and BULK INSERT it into a table and then rewrite the RBAR stored procedure to do things in bulk, as well.

You are most surely right, but in this particular case we cannot do that, at least not the easy way. My question was not about the speed optimization, but about the possibility to run the script at all.
But after looking into the script, we found out that there was an unescaped quote in some string values, causing SQLCMD to interpret the script incorrectly, hence the 'out of memory' error. Why not the syntax error - that I do not know.

After we had fixed those quotes the script ran just fine, with SQLCMD consuming 15MB memory maximum.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)SSC Guru (788K reputation)

Group: General Forum Members
Points: 788957 Visits: 45931
v.bartosh - Wednesday, December 20, 2017 3:15 AM
Jeff Moden - Tuesday, December 19, 2017 9:32 PM
I suspect that you're generating a script that uses a RBAR stored procedure to insert data into tables and that you're generating an EXEC for every row you're trying to insert.

If that's what you're doing, try the following, instead. Whatever "system" you're using to generate the SQLCMD stuff, change that to generate a TAB delimited file and using BULK INSERT to load the data file into a table. Then, if you insist on continuing to use the RBAR stored procedure to input the data, just loop through the rows of the table to do it.

A much better thing to do would be to still create the TAB delimited file and BULK INSERT it into a table and then rewrite the RBAR stored procedure to do things in bulk, as well.

You are most surely right, but in this particular case we cannot do that, at least not the easy way. My question was not about the speed optimization, but about the possibility to run the script at all.
But after looking into the script, we found out that there was an unescaped quote in some string values, causing SQLCMD to interpret the script incorrectly, hence the 'out of memory' error. Why not the syntax error - that I do not know.

After we had fixed those quotes the script ran just fine, with SQLCMD consuming 15MB memory maximum.


I realize that your question had nothing to do with performance optimization. My additional rhetoric on the subject of performance is something to consider because this will become a performance problem when the company can least afford for it become a problem. Wink

I also agree that my suggestion about using something like a TAB delimited file wouldn't be easy because it would require you good folks to change the source code that produces the current output and build some new code on the SQL Server side to receive the data of the new output.

My concern about the process and the company that you work for is genuine, though. There hasn't yet been a company I've worked for that hasn't done something similar to the current process. The nature of business puts everyone in the "if it works, don't mess with it" mode and that's fine... right up to the point where it no longer works in the time allotted or causes resource issues that slow other processes down. Of course, that's the worst time to find out about it because, chances are, you're under the gun to have it work in the time allotted and all hell breaks loose trying to come up with a quick fix and there isn't anything quick about such a fix.

The most difficult and longest to code part of my suggestion is to change the output to a delimited file, write the T-SQL to import it into a table, and then write the code to loop through the table to run the stored procedure one row at a time. While that may seem fruitless unless you change the final proc to work in an other than RBAR fashion, it won't be fruitless. It'll make it a whole lot easier to come up with a quick fix when the proverbial poo hits the fan because you won't have to find someone that can make the change at the source and make the table load work. Instead, you might just be able to tweak some things in the final stored procedure to temporarily get you past the crisis moment and then fix the proc to run in a non-RBAR mode in a much less urgent fashion.

So my recommendation continues... convince management to be proactive on this one so that the entire group doesn't have to go reactive when the eventuality of problems with the current method rear their ugly heads when you can least afford for them to do so (like they have at every company I've seen use such methods). And, yeah... if they refuse, get the refusal in writing because heads tend to roll during and after a crisis. Protect your head. Wink

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search