Index Fragmenation

I'm having trouble with slow performance due to index fragmentation. I'll insert 20000 records into a table and then have index fragmentation of like 67%! The database is so slow query this data back (over 3 minutes!). After rebuilding the index on the primary key (the only index) the same query takes less than 2 seconds. I'm using GUID as my primary key and I have recently switched from using NEWID() to using the new NEWSEQUENTAILID() to generate them. Can anyone suggest why I'm still having such a hard time with fragmentation?Unless you really NEED a GUID because you need an id that HAS to be unique across an entire network, I'd use an integer or bigint. There are a lot of articles out there regarding the performance hit associated with GUID's primarily due to page splits if memory serves. Is your primary key also a clustered index?
|||Yes it is a clustered index. I thought the problem with GUIDs in general is that they are pseudo-random. That is why I went to the newsequentialid() function that generates sequential guids across a machine.|||Index fragementation caused by using GUIDs in indexed columns shouldn't be causing the issues you're seeing.

While it is certainly true that you'll see very high index fragmentation for these columns (our production DB often has 95%+ fragmentation), this isn't necessarily going to kill your performance. We did extensive performance comparisons before decided to go with nearly 100% GUIDs are primary keys. There was certainly a perf difference, but it was negligible overall.

In fact, INSERT performance may actually increase thanks to the fact that disk hot spots are far less common.

I would examine the query plans before/after your defragmentation/rebuild your index. I think something else must be going on here.|||Its very likely that you have out of date statistics on the table that is giving you a bad query plan after the inserts. Rebuilding the index automatically updates statistics. Try just running UPDATE STATISTICS TableName after the INSERT and see if that gives you a better query plan.