GENERATE_SERIES() Breaks Minimal Logging

Question

GENERATE_SERIES() Breaks Minimal Logging

Jeff Moden

SSC Guru

Points: 1004809
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 9 posts - 1 through 9 (of 9 total)

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

Mr. Brian Gale One Orange Chip Points: 25026 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: 1004809 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: 125627 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: 1004809 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 2 years, 11 months 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)

Thomas Franz SSCarpal Tunnel Points: 4025 More actions · Answer 5

on my test server the query with the fn_Tally function runs 4:30 min

using

DROP TABLE IF EXISTS dbo.BigTable
DECLARE  @StartDT   DATETIME = '2010'    
        ,@EndDT     DATETIME = '2020'    
        ,@Rows      INT      = 100000000;
DECLARE  @Days      INT      = DATEDIFF(dd,@StartDT,@EndDT)


 SELECT  ProductID  = CHAR(ABS(CHECKSUM(NEWID())%2)+65)
                    + CHAR(ABS(CHECKSUM(NEWID())%2)+65)
                    + CHAR(ABS(CHECKSUM(NEWID())%2)+65)
        ,Amount     = RAND(CHECKSUM(NEWID()))*100
        ,Quantity   = ABS(CHECKSUM(NEWID())%50)+1
        ,TransDT    = RAND(CHECKSUM(NEWID()))*@Days+@StartDT
 INTO dbo.BigTable 
 FROM GENERATE_SERIES(1,@Rows) --TODO uncomment one or the other FROM clause for different test.
  OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) -- force parallel execution plan
?

takes 0:46 min but it is just an unsorted heap

adding the clustered index (if you really want / need it) afterwards takes another 1:44 min (so 2:30 min in total - 2 min less than the Tally-Function)
main benefit of the SELECT INTO vs. INSERT INTO SELECT FROM is, that it SELECT INTO does the writes parallel, while INSERT can do it only single threaded
so whenever you need to fill many rows in a new (usually temporary) table, SELECT INTO is much faster (I'm not talking about a few hundred or thousand rows that will be written in milliseconds anyway; dropping and recreating the #tmp table will create more overhead and nullifies the effect in that case)

God is real, unless declared integer.

Jeff Moden SSC Guru Points: 1004809 More actions · Answer 6

Thanks for the feedback, Thomas. I'll try to see what the differences are. What are you setup for system/database MAXDOP, which edition and version of SQL Server were you using, how much memory is allocated to SQL Server and what's the total memory of the machine (or VLM)?

Also, what was the Recovery Model you were using when you ran it and how much log file did it consume?

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

Thomas Franz SSCarpal Tunnel Points: 4025 More actions · Answer 7

Dev-Server with MaxDOP = 8 (of 32 logical CPUs)
768 GB RAM
SQL 2025 Developer (Enterprise), always most current CU
Simple Recovery for the database
Log-File on SSDs, the data files on a wild mix of SSD/HDDs automatical controlled by a SAN (historical grown, okay for our testing stuff but terrible slow, when I try to restore a large database)
tempdb on SSDs too
Max logfile used during the SELECT INTO 569.01 MB

Max logfile during Create Index: 385.08 MB

CREATE NONCLUSTERED INDEX ic_BigTable__TransDT_ProductId ON dbo.BigTable
       (TransDT, ProductId)
  WITH (DATA_COMPRESSION = ROW, DROP_EXISTING = OFF, FILLFACTOR = 100, STATISTICS_INCREMENTAL = OFF, SORT_IN_TEMPDB = ON)
    ON [PRIMARY]
   ;
GO?

PS: both statements runs faster today and took just 42 seconds each
Edit: run the Statement with fn_Tally again Log file usage was 1936.39 MB, so much more than with the SELECT INTO / CREATE INDEX combined. Query took this time (maybe because I run a loop that tracked the max log file usage in a second session) 11:38 minutes

Statement for tracking the logfile usage:

-- Session 2 (monitoring)

DECLARE @MaxPct decimal(10,2) = 0;
DECLARE @MaxMB  decimal(18,2) = 0;

WHILE EXISTS
(
    SELECT 1
    FROM sys.dm_exec_requests
    WHERE session_id = 105      -- session running the main statement
)
BEGIN
    SELECT
        @MaxPct = CASE
                     WHEN used_log_space_in_percent > @MaxPct
                     THEN used_log_space_in_percent
                     ELSE @MaxPct
                  END,
        @MaxMB = CASE
                    WHEN used_log_space_in_bytes / 1024.0 / 1024 > @MaxMB
                    THEN used_log_space_in_bytes / 1024.0 / 1024
                    ELSE @MaxMB
                 END
    FROM sys.dm_db_log_space_usage;

    WAITFOR DELAY '00:00:00.100'; -- 100 ms
END;

SELECT
    MaxLogUsedPercent = @MaxPct,
    MaxLogUsedMB      = @MaxMB;?

This reply was modified 3 days, 19 hours ago by Thomas Franz.

God is real, unless declared integer.

Jeff Moden SSC Guru Points: 1004809 More actions · Answer 8

Thank you, Thomas!!! I really appreciate the details. I start/restart some tests and get back here.

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