Thursday, March 31, 2011

GUIDS mess up defragmenting

I'm sure most of you know already that the use of GUIDS or UniqueIdentifiers is messy for doing defragmenting; because they are not sequential, they tend to cause problems for indexes and while you can decrease your FillFactor, I found that I'd rather not defrag an index only to find it at 99% fragmented again within a few hours.

I wrote a little script to help me identify the indexes that have an included column which is of type UniqueIdentifier.


-- CHANGE THE DB TO THE ONE YOU WANT TO SEE INDEXES WITH GUIDS IN THEM

;WITH LIST AS (
SELECT p.object_id
,OBJECT_NAME(p.object_id) AS TableName
,I.name AS IndexName
,C.Name AS ColName
,T.name AS DataType
FROM sys.partitions P
JOIN sysobjects O ON P.object_id = object_id(O.name)
JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id
JOIN sys.index_columns IC ON p.object_id = IC.object_id AND p.index_id = IC.index_id
JOIN sys.columns C ON P.object_id = C.object_id AND IC.column_id = C.column_id
JOIN sys.types T ON c.system_type_id = T.system_type_id
WHERE T.name = 'uniqueidentifier'
AND P.partition_number = 1
AND O.xtype='U'
)
SELECT DISTINCT *
FROM LIST
ORDER BY TableName,ColName

No comments:

Post a Comment