GUID as OK make for lots of I/O
-
I believe that I am having a lot of I/O bottlenecks due to how the data is laid out on the disk when using a GUID as a PK. I am trying to delete data from a number of bloated tables and it takes forever to delete records even when I have the server to myself. The tables range in size from 1M to 40M rows. The smaller tables take less time and the large ones are s..l..o..w...I don't disable constraints when deleting, although in testing this makes the deletion go much faster. One bottleneck I do know of is that the Data, Log and Indexes are all on the same physical RAID-1 drive. That will get fixed ina few weeks, but I need to focus on trying to tune the DB for less I/O.
Is there a way to reorder data by adding a new column INT IDENTITY and making a clustered index on that column?
I would think that this would order the data sequentially on the drive, making it easier to read and write data. My goal is to only have the last 6 months of data in the production DB and archive the old data to DB on another server.
Thanks,
--Frank
-
If the clustered index is on the GUID, you can get an improvement by rebuilding the clustered index, as it's probably badly fragmented. Once the index is rebuild you should see a reduction in IOs.
You can check to see how badly the index is fragmented by using DBCC ShowContig (<TableName>
Moving the cluster would certainly be a good idea. Guids are really bad for clustered indexes due to their random nature. Your clustered index will get fragmented very quickly, leadiong to heavy IOs and slowed queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply