@SeanGallardy It's probably an edge case, but that said, who knows how many people are experiencing log rushes like this while not having the skills to trace it back to query store. It does make sense to run the DBCC reorg on internal tables after cleanup completes, since the user can't do that.
We have a SQL Server 2019 CU18 where we discovered a strange issue with querystore.
Normally the average size of the hourly logbackup is 40MB but as soon as we enable querystore the average size of the logbackup is 2.5GB.
There are (according to querystore) 140.000 queries executed/hour. This is ...
@PaulWhite That's where it seems the data is leading, I just don't work enough with QS to definitively say one way or another. My overall reasoning for saying what I said about "how the log works" is that the OP seems to believe you can enable QS and there be no log impact when data is saved to the database and subsequently removed.
The code is nicely organized and obviously designed not to get too much in the way (as a background task shouldn't), but it's a bit of a bugger to trace through
There's a state machine that sort of retriggers itself every five seconds to continue with the next stage of the task
I have had no luck getting the XE query_store_capture_threshold_changed_feedback_loop or its friend to fire. I don't suppose it's directly related, but you never know with an OP
I remember when QS first came out, the number of support calls we had for it was crazy. It's since been a very low generator (after 2019 came out) and I haven't seen much for 2022, granted I moved teams by that point.
Not fully because I'm a bit grumpy they sold QS plan forcing as if it were some completely new implementation, rather than reusing the existing plan guide core
I'm feeling fairly pleased with myself this morning. Just rewrote a Powershell script that I use to import rows from a set of very big text files into a database. The previous version was doing RBAR INSERT INTO ... statements for every row in the source text file. I added batching via a table type and a .Net DataTable, with a variable batch size, and the rows-per-second went from ~300 to ~25000 per second.
I could undoubtedly make it run faster by making all kinds of tweaks. For instance, the database is part of a Distributed Availability Group. The table has a clustered index, an identity column, and a non-clustered index. The database is stored on a rather slow direct-attached RAID 10 array of 7200rpm disks used for a lot of different things. The network is only gigabit. etc.
there are over 66 million rows in the table so far and we're only on the .com tld, and only at attempt-settings.com. Seems like this table might be quite big by the time it's done.
actually I downloaded the zone files directly from ICANN for every TLD in existence (aside from several that don't allow you to download them, such as .ca and some very weird Chinese ones).
so I'm ingesting the zone files into a database to do analysis
The AG in async mode didn't have any log send or redo queue. So why was there a freeze for 5 minutes? Can you give some logical explanation. No where does document say that entire applications will be halted for 5+ minutes upon mode change to sync. Infact there is a session timeout of 10 seconds which will auto change mode to async if there is significant delay in commits. So I request you to give logical answer rather than quoting links to full length documentations. — variable2 hours ago
Good morning. Do any of you have an experience with SSMS Boost plugin? How does it compare to SQL Prompt? I got SSMS Boost at my current workplace and I feel like I would prefer SQL Prompt instead. Is it just me being unable to use it efficiently, or is just somewhat worse?
@ErikReasonableRatesDarling We recently started monitoring these, and if we detect queries with more than hundred values in the IN () list, we will create ticket asking devs to rewrite their shit. They don't seem to appreciate how cool that is, though.
Sadly, don't know. But for some reason they want to iterate over hundreds of Ids. One would naturally assume, they care about some particular attributes of the entities, and not some imaginary identifiers.
Well, the alternative is for them to not use ORM, at least for these queries.
Of course, much easier for them is to rewrite the query with long IN() into several with relatively shorter INs - which is something I am sure is happening.
my TLD ingester is now working on the .com tld on domain names starting with b and we're already over 92 million rows.
I dropped the clustered index, and the non-clustered index, and the primary key constraint, and am inserting into the table using the TABLOCKX hint, and it's going pretty quickly, but wow is that going to be a big table lol.
before I run this again, I'll have it report the domain name it is working on along with the row number. It currently only shows the number of rows it has consumed once per batch.
Well, I think you said it had a clustered index and a non clustered index originally. Bulk minimally logged load to a clustered index is possible. Parallel loads are tricky
Loading to a heap is fine too, but counterproductive if you are going to recreate the clustered index
The follow is a paragraph from Microsoft Docs:
New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt. Rebuild the heap by removing and reapplying compression, or by creating and removing a clustered index.
I can't figure out why this...
still working on the .com zone. The query I was using to get the most recently allocated page for the table is no longer playing nicely, so I'm not even sure where we are alphabetically
I was not expecting 64, I was expecting 1. But who knows what lurks in that black box.
I suppose if I passed the data into a stored procedure instead of executing an INSERT INTO directly in Powershell I might get a better chance. At least I could populate a #temp table from the table variable then use that as the source of the INSERT ... SELECT statement.
having said that I suppose I could do that in Powershell too
The optimal approach depends very much on the transformations you need on the raw data file and what final table arrangement you want. Worst case, you'll spend 24h loading the data then discover it's wrong or unworkable and you need to start again. All part of the fun, I suppose
The follow is a paragraph from Microsoft Docs:
New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt. Rebuild the heap by removing and reapplying compression, or by creating and removing a clustered index.
I can't figure out why this...
