GENERATE_SERIES() Breaks Minimal Logging

Question

GENERATE_SERIES() Breaks Minimal Logging

Jeff Moden

SSC Guru

Points: 1003869
More actions
September 18, 2023 at 12:00 am

#4264817

Comments posted to this topic are about the item GENERATE_SERIES() Breaks Minimal Logging
--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.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24692 More actions · Answer 1

That is a bit scary and odd... have you reported this to MS? If they are aware of it and it is a limitation of the function, they should document it for cases like what you use it for where you are generating a lot of data. It should be documented that generating large series' will result in large transaction logs.

Also, as a thought (I didn't test this but curious if anyone else did) - does "OPTIMIZE FOR UNKNOWN" rather than "RECOMPILE" help with the minimal logging with variables or not? I know when I write stored procedures, I prefer "OPTIMIZE FOR UNKNOWN" rather than "RECOMPILE" IF someone determines that RECOMPILE is "required". All cases I've seen in my department, OPTIMIZE FOR UNKNOWN handles things just as well as RECOMPILE. BUT in my scenario, the stored procedures we write have unique sets of parameters when they are run in most cases.

Once nice thing about what you found is that it SHOULD RARELY have an issue with production level code. I know my prod boxes are ALL set to full recovery if possible (I have 1 3rd party tool that throws errors if it finds its database in full recovery mode and the tool is not managed by me so I was asked to put it into simple recovery mode).

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

@Brian,

It's not as rare as you might think in production code if you use TempDB or a "scratch/working" database to get "balls-out" ( https://en.wiktionary.org/wiki/balls-out ) performance in code, especially when the main database is necessarily in the FULL Recovery Model. We use the technique at work, A LOT!

As for "Optimize for Unknown", I've not tested it. I'll try to get a test in tonight. Thank you for the suggestion! I've not had such "good luck" with it as you apparently have had, but it's worth a try here.

As far as letting MS know... I had the serendipitous opportunity to do so directly this morning, as well as another subject concerning performance in 2022. As for the suggestion site, I'm not likely to make any suggestions there anymore unless MS has had a drastic change in the people evaluating such posts.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125522 More actions · Answer 3

Maybe the most efficient way to mockup 100 million rows of randomized test data would be to leverage PowerShell or Python to create text files and then BCP them into the SQL Server table.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

Eric M Russell wrote:

Maybe the most efficient way to mockup 100 million rows of randomized test data would be to leverage PowerShell or Python to create text files and then BCP them into the SQL Server table.

Thanks for the feedback, Eric. Always appreciated.

Possibly. The only way to find out is for someone to test that. I'm not the fellow that can do that properly. We need an expert and, again, I don't even qualify a user of either.

This reply was modified 7 months, 1 week ago by Jeff Moden.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)