SQL Server 2019 Database scoped configuration

sakuraime 2,326

are there any good link / demo/ example for the following SQL Server 2019 Database

IDENTITY_CACHE
ISOLATE_SECURITY_POLICY_CARDINALITY
ACCELERATED_PLAN_FORCING
GLOBAL_TEMPORARY_TABLE_AUTO_DROP
VERBOSE_TRUNCATION_WARNINGS

thanks

Accepted answer

AmeliaGu-MSFT 13,976 Reputation points Microsoft Vendor

2021-07-23T06:20:08.203+00:00

Hi sakuraime,

For GLOBAL_TEMPORARY_TABLE_AUTO_DROP option, you can try to create a global temporary table and not use it. By default (GLOBAL_TEMPORARY_TABLE_AUTO_DROP is on), the global temporary table will be deleted automatically. If you create a global temporary table when GLOBAL_TEMPORARY_TABLE_AUTO_DROP is off, the global temporary table needs to be deleted using the DROP TABLE statement, or it will be deleted when the server is restarted.

Best Regards,
Amelia
Please sign in to rate this answer.
sakuraime 2,326 Reputation points

2021-07-23T14:56:09.44+00:00

thanks and how frequent it will be deleted?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

Erland Sommarskog 111.2K Reputation points MVP

2021-07-22T21:38:15.947+00:00
For the first, try this:

CREATE TABLE myidenttable(a int IDENTITY NOT NULL) INSERT myidenttable DEFAULT VALUES

Next kill SQL Server through Task Manager and restart it. Insert a new row to the table. Notice the value. Then flip the setting of IDENTITY_CACHE and redo the exercise.

For the last, try:

CREATE TABLE myshortchar (a char(4)) INSERT myshortchar (a) VALUES ('Too long')

Then flip the switch for VERBOSE_TRUNCATION_WARNINGS and see the difference.

As for links, did you look at https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15?
Please sign in to rate this answer.
sakuraime 2,326 Reputation points

2021-07-23T14:54:52.84+00:00

thanks .. pretty clear for the VERBOSE_TRUNCATION_WARNINGS

but IDENTITY_CACHE , i see the difference , but I can't think of any use cases for this .

Erland Sommarskog 111.2K Reputation points MVP

2021-07-23T21:39:47.22+00:00

but IDENTITY_CACHE , i see the difference , but I can't think of any use cases for this .

Then we are two! But there are people who get mighty upset when their IDENTITY columns jump 1000 and start to scream blue murder. I answered such a thread only a couple of days back. I told the poster that he should ignore the skipped values - or, if he need contiguous values. he should not use IDENTITY at all. But the poster completely ignored my comments and only wanted to know how to set the trace flag. (He was on SQL 2016 which does not have this scoped configuration.)

Up to SQL 2008, there was no cache for IDENTITY at all, but they added one whey the implemented sequences.

sakuraime 2,326 Reputation points

2021-07-24T12:16:52.05+00:00

of coz if user use the column as identify , why not want to be a continuous values? So why sql server skip the values by 1000 ??? what's the use case ?

Erland Sommarskog 111.2K Reputation points MVP

2021-07-24T21:45:56.857+00:00

Speed.

You have lots of concurrent processes inserting rows to which you want to assign an autonumber id. If you do it without IDENTITY (or sequence or GUID), you need to do

BEGIN TRANSACTION SELECT @ID = isnullMAX(id, 0) FROM tbl WITH (UPDLOCK) INSERT tbl (id, col1, col2, ... VALUES (@ID, col1, col2, .. -- More work here.

You have a serialisation point here, and your throughput will suffer. Or more precisely, your system will be too slow.

With IDENTITY or SEQUENCE, there is only a shortlived lock (or latch) on the IDENTITY source, which is released before the transaction commits, so the next process can get an ID. But this also means that if the transaction is rolled back for whatever reason, there will be a gap.

This IDENTITY value must live on disk, so that SQL Server can continue on the next available number also after a restart. But how often should SQL Server update the value on disk? Up to SQL 2008 it did it every time they took a number, but when they implemented sequences, which has a cache by default, they realised that this was silly. So now they only update for every 1000th value (for int). And indeed, for high-concurrency scenario, the cache makes a notable improvement to performance.

It follows from the above that if you want contiguous number, you should not use IDENTITY. The feature is designed to give gaps. Even with the IDENTITY_CACHE turned off. (Which is essentially a go-slower button.)

sakuraime 2,326 Reputation points

2021-07-25T12:13:30.703+00:00

but this is quite a very scary feature , customer would just expecting it's contiguous . So which means every time it put 1000 values in the cache and assume you will use all ?
/?
May I know it happen also a normal restart of sql server ? ( you mention it has to kill the server and start in order to demonstrate)

Erland Sommarskog 111.2K Reputation points MVP

2021-07-25T20:11:28.797+00:00

but this is quite a very scary feature , customer would just expecting it's contiguous

In such case, customer has the wrong expectation. Yes, some users have this expectation, but I would suggest that most experienced SQL developers are aware of that IDENTITY can produce gaps.

So which means every time it put 1000 values in the cache and assume you will use all ?

Or more precisely that it does not matter if there is a gap. It's an artificial key with no meaning on its own. It could be a random number, but it would be a lot more difficult to return a random number and only returning every number once.

May I know it happen also a normal restart of sql server ?

I believe that in case of a clean shutdown of the database, the value on disk is updated with the current value, but I have not tested. And I really don't care. And nor should anyone else.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL Server 2019 Database scoped configuration

1 additional answer

Your answer