SQL Server Performance Forum – Threads Archive

Fragmentation problems

Hi there, I have ran into a situation of a few tables with wide record size and guids as primary clustered indexes (great combination). I see there is lots of talk about avoiding doing this but this is helpful to know during development. In maintenance however, it is a different story. For reasons it is important to explain here guids cannot be dropped (typically I would have to browse thousands of lines of code and start making changes everywhere plus change a few hundreds of tested financial reports). So reverting to … say int identity (lots of people love the sound of that) is not an option. I’ve been looking around and found this interesting extended stored procedure (http://www.sqldev.net/xp/xpguid.htm) that is supposed to generate sequential guids. Apparently this a new feature in SQL Server 2005, but for starters ALL of our customers have SQL Server 2000 and that will not change soon. Does anyone have any experience with these stored procs? Can you verify that the generated GUID is actually Unique (provided that there is a network card on the server)? I was hoping that I can upgrade my dbs to use this sequential guid and eliminate page-splits and page locality problems. Do you have any idea if this will give me better results? Any other suggestions/proposals will be appreciated. Thanx
KKT

Not sure about the sequential GUID. You could eliminate the page-splits by not having the GUID as the key field for the CLUSTERED index. You ignored that as 1 of the possibilities that you could try. Not eliminate it completely, just have an unclustered index on that field, and consider another field(s) for the CLUSTERED index, or simply don’t have a clustered index on the table at all.

http://www.sql-server-performance.com/zn_guid_performance.asp for information on GUID. I recommend a numeric IDENTITY column as the clustering key. Anyway, a guid does meet the criteria fairly well – it’s certainly unique, it’s usually static and it’s relatively narrow. I believe you will get more information this GUID from Gert Drapers as it is his own process. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I would agree with not necessarily having the GUID as part of the clustered index unless you’re constantly looking at groupings of the GUID or ranges, you group by the GUID, you search by it, or it’s a table where the GUID is constantly used to find a single value in the table (as in you already have the GUID and just want the row of data that GUID is in). There are many times where the GUID is not used for any of those though. In that case, putting the clustered index on it is just a waste of space. Make the clustered index include the FEW fields that are most recently used and provide the best life in processing through optimization of how the actual data is ordered and the speed it can be accessed in through speeding up of ranges, groups, unique searches, etc. Keep an unclustered index on the GUID if you really need one at all and a unique constraint if you need to guarantee uniqueness of the column. In my opinion, a lot of people waste their clustered index because they put it on a column that is never used by the engine. Be careful about creating HUGE clustered indexes as the nonclustered indexes will be much larger. This can bloat the database and actually slow down the overall performance of the system. Also, be careful about not having clustered indexes at all. Things tend to creep up on you, so don’t make your decisions without thought for where the sytems will be a year or two. Read up on the architecture of indexes. Run tests with large amounts of data. Then, make your decisions on the final index layout. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>