Identity insert - performance

Question

I have one solution where I insert rows where I use GUIDs instead of an Identity. The Guid is generated in c#.

I have another solution where the table, indexes etc are identical, but instead of GUIDs I use identities and let Sql-server generate them.

In the later case I get a performance issue.... Ideas?

//Daniel

EDIT

I really am sorry! I got home installed the profiler and saw that I was comparing apples and pears.... A big miss from my side. I was invoking different sql's. Now the duration is about the same.

Again thanks, and I'm sorry!

//Daniel

Answer 1

In order to assist you with your question, we first need more information about your configuration and performance metrics.

Environment:

• What Edition of SQL Server are you running?
• What is the CPU and RAM spec?
• What is the configuration of your Storage Sub-system?
• Provide the table schema's and associated index definitions.
• Provide test code demonstrating how the inserts are performed.

Performance:

• What wait_types are you seeing on the server during your Inserts i.e. what resource is SQL Server waiting on? Are you CPU bound/Disk Bound or perhaps you have page contention.
• Do the wait_types differ depending on when you run the test with Identity Vs GUID?

Initial Hypothesis/Speculation:

You may be seeing page contention when performing identity inserts, as the leading column in the index is continually increasing, the contending resource will be the last page in the B-Tree.

This is of course only a theory until we have more details from you to work with.

Answer 2

I honestly would expect the performance issue to be with the guid as the identity. When you use guids, if you use newid() instead of newsequentialid()you have the potential for page splits because of the new record being inserted into the middle of the table instead of the end.