>>> Suppose there is a table with a compound primary key and said key is clustered.
>>>When I insert a record, I'm taking an educated guess that because of the clustered index, the actual table is re-ordered in some manner if a new record falls between other records.
>>>
>>>And yes, that's generally correct. Now, you can set the FILL FACTOR to reduce how frequently page splits occur. If you set a value of 80 (and this is from MSDN), that means 20% of the data page is left empty, to allow/reserve for space that would otherwise cause a page split. I've read different reports on how effective this can be. So mileage will definitely vary based on the usage.
>>
>>But that means that FILL FACTOR is a workaround.
>
>Agreed - though in a highly insert-intensive environment where a special clustered index is needed, it can be helpful. In these types of situations, a DBA is trying to balance fragmentation, insert performance, etc.
>
>
>They have a new in-memory OLTP engine called Hekaton that blows the doors off the regular OLTP engine for high-insert scenarios.

Hi Kevin,

Our app is a high-insert app. Is the performance gain from Hekaton as significant if the current DB (2008R2) fits in memory and is backed by SSD?