SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Problems inserting millions of rows into IDENTITY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jhilb007
Starting Member

17 Posts

Posted - 08/21/2014 :  11:40:52  Show Profile  Reply with Quote
I have a number of large tables using GUIDs for record Ids. Performance is ok. They are not sequential GUIDs.

Trying to go to BIGINT with IDENTITY, as PK (CLUSTERED) instead for performance.

However, my inserts into IDENTITY BIGINT keyed tables (typically millions of rows per table a day) simply lock-up or error with a not-enough locks error.

Adding TABLOCKX helps, but not what I want to do. A process that took 3 minutes a day using non-sequential GUIDs jumped to 18 minutes when changed to BIGINT and required a TABLOCKX on the INSERT to get it done.

Everything I read says to get off GUIDS, but the alterative, BIGINT IDENTITY seems to be-counter productive, and not possible in some cases.

Breaking the INSERT into smaller batches is not an option. I need to be able to push millions of rows into the table at a time.

Jeff

tkizer
Almighty SQL Goddess

USA
37460 Posts

Posted - 08/21/2014 :  12:35:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
Is it in one transaction? Adding millions of rows to a bigint identity column should not be an issue. You've got something wrong in the environment.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jhilb007
Starting Member

17 Posts

Posted - 08/21/2014 :  12:46:12  Show Profile  Reply with Quote
Yes, one transaction. However the I just realized that the queries having the performance issue are all around INSERTs where the data source is another database (not the one inserting into). Actually they are from a view on another database. I suspect that has something to do with it. I will try inserting from the VIEW locally, then into my BIGINT keyed table to see if there is a difference.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37460 Posts

Posted - 08/21/2014 :  12:47:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
If you are inserting millions of rows in one transaction, then I would suggest changing that process so that it does it using a bulk approach and not with insert.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 08/21/2014 :  16:14:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try making the table a heap (drop the clustered attribute on the pk).
Then you should avoid some page contention problem. See http://kejser.org/clustered-indexes-vs-heaps/




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000