February 23, 2014 at 4:12 pm
Perry Whittle (2/21/2014)
after checking there is a trace flag
-T272This will revert to the behaviour in previous versions of the product
I did check on that one. But what would be the affect on server behaviour if this trace runs continuously. Our environment is OLTP we can't run trace all day 24X7.
February 23, 2014 at 11:51 pm
It forces identities in sql server to behave as it did in previous versions. The trace flag is added to the service startup list to persist it on service restarts.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 24, 2014 at 7:10 am
Perry Whittle (2/23/2014)
It forces identities in sql server to behave as it did in previous versions. The trace flag is added to the service startup list to persist it on service restarts.
So you mean no affect on the server performance ??
February 24, 2014 at 7:33 am
why would it affect performance, it basically tells sql server not to use the new style of identity maintenance and act like a sql 2008 instance in that respect
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 24, 2014 at 10:45 am
Perry,
Just to let you know...
If you require the same identity generation semantics as previous versions of SQL Server there are two options available:
• Use trace flag 272
o This will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.
• Use a sequence generator with the NO CACHE setting(http://msdn.microsoft.com/en-us/library/ff878091.aspx)
o This will cause a log record to be generated for each generated sequence value. Note that the performance of sequence value generation may be impacted by using NO CACHE.
o Example:
CREATE SEQUENCE s1 AS INT START WITH 1 NO CACHE;
CREATE TABLE t1 (Id INT PRIMARY KEY DEFAULT NEXT VALUE FOR s1, col INT NOT NULL);
February 24, 2014 at 11:56 am
the above answer was given by microsoft in the below article--
what exactly he meant by performance of identity column or sequence column will be affected?
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply