SQL Server Performance Forum – Threads Archive
ntext & nvarchar(max) storage methodsI’m having a problem with a table that contains a IDENTITY column (with clustered index) and a ntext column. The table has approx 20 million rows and is roughly 90GB is size. As you can see from this the vast majority of the data is in the ntext column. This table has a dedicated RAID 10 drive to it. It also receives a large number of updates and inserts throughout the day. The drive that this is based on has a very high avg disk queue (upto 50) and a low throughput (roughly 1-2mb sec). I’m concerned that the data is very heavily fragmented. Doing a DBCC SHOWCONTIG I can see that the table has a 99.98% Scan densisty – which looks fine. However – I know that SQL stores the ntext data outside of the table. So my question is – is there anyway to see the fragmenation of this column and fix it?? The data could become heavily fragemented by the updates, for example a field that was 200 bytes long being updated to a text with 20,000 bytes. As I’m using 2005 I could move to nvarchar(max) – this may help because the smaller values could be stored in the table instead of outside – forcing them to be defragged during a DBCC DBREINDEX – however I think I will still have problem with the larger values. Any information about how SQL stores & manages these columns would be greatly appreciated! Thanks
what else is in this table, if its just the identity and ntext, leave it alone. there is no good way to side step a crappy disk system, if you are getting 1-2MB/sec, assuming thats 8K IO, thats 125-250 IO/sec, which at queue depth 50 really sucks. assuming one did not have a seriously bad disk system, additional steps included putting the ntext and image stuff in a different filegroup on a different partition. this partition could still reside on the same physical disk, but the non-text stuff would reside in a small partition up front, the ntext stuff would reside in a big partition on the rest of the disk
then assuming the bulk of the access is to the non-text stuff, everything resides in a small portion of the disk, hopefully most of which will be in memory.