It seems like there could be a good answer to that question, but it sounds more relevant to academia. Like you could answer the question by citing research papers about query optimization.
Wow I've never seen a smile that wide you must be really happy!
I would assume that the performance loss due to optimise for unknown is likely far bigger than the extra time spent compiling the query. Suppose it depends how often it's run / compiled
@Forrest After the plan is already compiled the first time i mean
Optimise for unknown will usually run slower than an individually optimised plan right? That's what I mean. There will be a tipping point based on how often the query is used
@JoeObbish Came across a server with LPIM enabled, SQL max mem set to 80 GB and it only had 40 GB of RAM. That was interesting when the OS basically ran out of memory and the whole machine died
Interesting that checkpoints in simple mode recovery are triggered when the log is 70% full, yet an automatic checkpoint doesn't seem to occur when the log is full and an error is generated by a transaction running out of log space. Blog post incoming for tomorrow.
I'm using an extended events session to watch checkpoints happen while I fill the log. They occur roughly every couple of minutes, but of course they can't actually clear the log because the transaction is continuuing.
:50824048 there are system generated checkpoints occurring, but it seems like only when the log is actively being written to (above 70% full). Once the transaction rolls back, no automatic checkpoint seems to occur.
yah, seems like a bit of an edge case, but interesting none the less. I'm keeping all the checkpoint settings to defaults because most folks will have it setup like that.
I ran a transaction last night that filled the log in this test database. This morning, after many hours of nothing happening, no automatic checkpoint had occurred, and the log was 99.6% full, with over 7GB of log records.
recovery time would still probably be under a minute though.
the implication is, if you fill the log with a very large transaction, and no automatic checkpoint occurs, other transactions may get "log full" errors without causing an automatic checkpoint.
I've seen ACTIVE_TRANSACTION in the log_reuse_wait_desc column in sys.databases with no active transactions in sight. I've been wondering how that could occur with automatic checkpointing, but this seems to point to a potential answer.
@George.Palacios yah, the rollback completely finished.
@ErikDarling like I said, it's an edge case. But probably not one that is all that uncommon where devs write code.
I've tried it both ways. As just a single statement, like UPDATE blah SET x = 't'; and wrapped in a TRY...CATCH with a ROLLBACK in the catch. Either way seems to be the same with regards to the checkpoint not happening.
possibly of interest: in the past I've written inserts in a tight loop (one transaction per insert) that filled the log with a simple recovery model. had to add a checkpoint to the loop
If I understand what you're talking about correctly, I have seen this question before. The misconception is that an automatic checkpoint should happen whenever the log is more than 70% full. That's not what the docs say. They say an additional automatic checkpoint is queued if the log becomes 70% full.
In Microsoft SQLServer 2017+ I would like to grant the dbcreator role on a single user but only allow her to create databases whose name matches a fixed prefix.
Is it possible to do this at the database level using a built-in feature or a stored procedure?
@PaulWhite agreed. I didn't read it like the checkpoint would continue to occur after the log reaches 70% - the docs are clear that a checkpoint is queued when the log reaches 70%. I am seeing system generated checkpoints occurring frequently after the log is very full, though.
> ...the Database Engine generates automatic checkpoints. The frequency depends on the recovery interval advanced server configuration option, which specifies the maximum time that a given server instance should use to recover a database during a system restart.
> The Database Engine estimates the maximum number of log records it can process within the recovery interval. When a database using automatic checkpoints reaches this maximum number of log records, the Database Engine issues an checkpoint on the database.
@PaulWhite I'm guessing here, but the thread that determines whether a checkpoint is necessary only gets fired when a log record gets inserted into the log. Hence, why I'm not seeing a checkpoint for hours on end in this database that has nothing going on, even though the log is 99.8% full, and very large.
> In-place upgrade. Upgrade instances of SQL Server 2019 CTP 3.1 from instances of SQL Server 2012 through SQL Server 2017 and SQL Server CTP 3.0. Upgrade from a SQL Server 2019 CTP below 3.0 is not supported, a new installation must be performed.
Ahh i see, for 2016 its faster like 5-10 secs. The ones in SQL2012/2014 its about a min with hint and about 3-4 mins without them. I am checking if this can be further improved as for 2012/2104 i can use 8649 but does not help that much, but yes still works. Overall your answer works my req, will check on how to make this faster. Thanks a ton, again. — BeginnerDBA6 mins ago
I actually can't find that TF in the docs list of trace flags. I did find it mentioned on the CSS blog. I guess that blog is enough to make it considered documented and supported?
When I run the following
select t.type
from (values ('Green'),('Blue'),('Red')) as t(type)
for xml path('')
I receive this output
<type>Green</type>
<type>Blue</type>
<type>Red</type>
If I run the following
select t.type + '/'
from (values ('Green'),('Blue'),('Red')) as t(type)
f...
In Microsoft SQLServer 2017+ I would like to grant the dbcreator role on a single user but only allow her to create databases whose name matches a fixed prefix.
Is it possible to do this at the database level using a built-in feature or a stored procedure?
When I run the following
select t.type
from (values ('Green'),('Blue'),('Red')) as t(type)
for xml path('')
I receive this output
<type>Green</type>
<type>Blue</type>
<type>Red</type>
If I run the following
select t.type + '/'
from (values ('Green'),('Blue'),('Red')) as t(type)
f...
