December 19, 2017 at 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?
December 19, 2017 at 2:55 am
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
Thomas Rushton
blog: https://thelonedba.wordpress.com
December 19, 2017 at 5:51 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2017 at 6:14 am
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.
December 19, 2017 at 9:32 pm
v.bartosh - Tuesday, December 19, 2017 2:15 AMWe'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
Change is inevitable... Change for the better is not.
December 20, 2017 at 3:15 am
Jeff Moden - Tuesday, December 19, 2017 9:32 PMI 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.
December 20, 2017 at 8:48 am
v.bartosh - Wednesday, December 20, 2017 3:15 AMJeff Moden - Tuesday, December 19, 2017 9:32 PMI 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. 😉
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. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply