September 24, 2014 at 11:29 am
adrian.buzila (9/24/2014)
My DBA (I'm developer) did not agree with the flag, said it will impact performance, sql2008 identity is more resource intensive.I implemented you first solution with reseed max value. It will take about one week to say is ok (this is the average frequency of duplicates)
As a particularity it happends on tables with a lot of columns. One with 760 columns (318 nvarchar, 143 uniqueidentifier, 100 datetime, 97 bit, 72 float 18 int, 10 decimal, 2 tinyint), another with 177 columns. What about you, do you find any particularity?
One insert is issued using exec(@sql) another one uses MERGE. So this is not a particularity.
What's the technical explanation for your DBA to say that? Did the perform any testing and the trace flag degraded performance during his test? if he did, what type of testing did he perform?
I'm a DBA, not a Developer. And I strongly disagree with his comment that will impact performance. My only concern was about the supportability of this flag on future releases, but does not seem to be deprecated. Of course, he's ultimately the DBA, and not sure about your company's organizational chat, but he may or may not have authority to reject or approve the workaround, so I won't comment on that, each company has different rules.
The SP2 did not fix the problem and Microsoft has not released a hotfix. So what's the DBA solution for you, the Developer? Change your code?
I did not find any performance impact using this, and I have 1.5 TB database using it without any problem. It fixes a problem, as a workaround, but it does.
September 24, 2014 at 12:57 pm
I can not comment on his decision.
Anyway, it will be intresting to see in what circumstances it happends. This way we can hope it will get fixed.
In my case is the same stuncture of database on different servers. One database instance is 100GB and another 250GB
One table is 770 columns, 150 MB, 90.000 rows.
Another 177 columns, 600MB, 180.000 rows.
I tried to reproduce on testing server. no way. It happends on production.
One server uses Alwayson High availability.
Its a warehouse database and inserts/deletes/updates are issued once a day by a sql agent job.
September 24, 2014 at 1:08 pm
We use HA too ... not sure if that is part of it or not.
Does it only happen on the HA server, or you get the error on the server with no HA too?
September 24, 2014 at 1:19 pm
Both HA and not HA
March 4, 2015 at 2:33 pm
I ran into this same problem with SQL Server 2012 and this was one of the few places where anyone took seriously the possibility that the identity seed was not being externally reset. In any case, running my application under load would generally result in the problem occurring within an hour. After upgrading to SP2CU4, I could no longer reproduce the problem after 12+ hours of runtime. Perhaps something in CU4 resolved it. I am in the process of testing the application with SQL Server 2014 to see if it has the same behavior.
March 5, 2015 at 9:14 am
sqlcentral.dah328 (3/4/2015)
I ran into this same problem with SQL Server 2012 and this was one of the few places where anyone took seriously the possibility that the identity seed was not being externally reset. In any case, running my application under load would generally result in the problem occurring within an hour. After upgrading to SP2CU4, I could no longer reproduce the problem after 12+ hours of runtime. Perhaps something in CU4 resolved it. I am in the process of testing the application with SQL Server 2014 to see if it has the same behavior.
Thanks for sharing. I'm still running the trace flag on my 4 Pro boxes, it was my workaround.
Are you referring to this link ?
March 5, 2015 at 9:21 am
sql-lover (3/5/2015)
sqlcentral.dah328 (3/4/2015)
I ran into this same problem with SQL Server 2012 and this was one of the few places where anyone took seriously the possibility that the identity seed was not being externally reset. In any case, running my application under load would generally result in the problem occurring within an hour. After upgrading to SP2CU4, I could no longer reproduce the problem after 12+ hours of runtime. Perhaps something in CU4 resolved it. I am in the process of testing the application with SQL Server 2014 to see if it has the same behavior.Thanks for sharing. I'm still running the trace flag on my 4 Pro boxes, it was my workaround.
Are you referring to this link ?
I am not using sequences anywhere in my application, but the description of the sequence bug describes my symptoms exactly. I wonder if SQL Server 2012 uses sequences under the covers to implement integer identity columns. If so, that would explain a lot.
March 5, 2015 at 9:46 am
sqlcentral.dah328 (3/5/2015)
sql-lover (3/5/2015)
sqlcentral.dah328 (3/4/2015)
I ran into this same problem with SQL Server 2012 and this was one of the few places where anyone took seriously the possibility that the identity seed was not being externally reset. In any case, running my application under load would generally result in the problem occurring within an hour. After upgrading to SP2CU4, I could no longer reproduce the problem after 12+ hours of runtime. Perhaps something in CU4 resolved it. I am in the process of testing the application with SQL Server 2014 to see if it has the same behavior.Thanks for sharing. I'm still running the trace flag on my 4 Pro boxes, it was my workaround.
Are you referring to this link ?
I am not using sequences anywhere in my application, but the description of the sequence bug describes my symptoms exactly. I wonder if SQL Server 2012 uses sequences under the covers to implement integer identity columns. If so, that would explain a lot.
The way IDs are handled in SQL2012 changed and it's the same in SQL2014. It now uses a caching mechanism. I confirmed this few years ago online but I don't have the link handy. MS did this because performance reasons. But it seems there was a bug and under memory pressure, somehow, it flushes the values and the engine losses track of them. I was almost 99% sure about this but I was not able to find any hotfix then. Our only fix at work was the trace flag and basically brings the old SQL2005/2008 ID behaviour back to the engine.
Please keep us posted! I'm already planning to migrate or setup some SQL2014 boxes at work and I may eliminate the flag and install CU4 if this address the issue, which looks like.
Thanks again for replying my 2 years old thread.
March 5, 2015 at 9:49 am
sqlcentral.dah328 (3/5/2015)
sql-lover (3/5/2015)
sqlcentral.dah328 (3/4/2015)
I ran into this same problem with SQL Server 2012 and this was one of the few places where anyone took seriously the possibility that the identity seed was not being externally reset. In any case, running my application under load would generally result in the problem occurring within an hour. After upgrading to SP2CU4, I could no longer reproduce the problem after 12+ hours of runtime. Perhaps something in CU4 resolved it. I am in the process of testing the application with SQL Server 2014 to see if it has the same behavior.Thanks for sharing. I'm still running the trace flag on my 4 Pro boxes, it was my workaround.
Are you referring to this link ?
I am not using sequences anywhere in my application, but the description of the sequence bug describes my symptoms exactly. I wonder if SQL Server 2012 uses sequences under the covers to implement integer identity columns. If so, that would explain a lot.
It may be a bug related to maintaining ACIDity in high transaction volume applications, and identity / sequence incrementation is just the most noticable side effect, because it results in duplicate key errors.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 5, 2015 at 9:57 am
Eric M Russell (3/5/2015)
It may be a bug related to maintaining ACIDity in high transaction volume applications, and identity / sequence incrementation is just the most noticable side effect, because it results in duplicate key errors.
I do believe the same. It is also consistent with our particular SQL workload at work.
It was introduced to our systems just right after I upgraded to SQL2012.
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply