SQL Server Performance

Defrag Data within file (nvarchar, etc)

Discussion in 'Performance Tuning for DBAs' started by benwilson, Apr 25, 2005.

  1. benwilson New Member

    Hi All,
    My boss has asked me about defraging data within a datafile. Our database has a lot of nvarchar fields, and he was wondering if it is possible to defrag this kind of data (our system allows for many updates of these fields, possibly causeing fragmentation if the new value is longer than the old).

    From what i have read on this site and elsewhere, it doesnt sound like this is possible- is this really the case?

    Thanks
    Ben
  2. dineshasanka Moderator

    There was a discussion on table partioning on yesterday. I think you are alos refering same issue
  3. benwilson New Member

    i dont think i am talking about the same thing...let me explain a bit more...

    Say i have a table that has several columns, one of which is colA NVARCHAR(50).

    When data is inserted into a row, the value inserted into colA is 'Mountain'. As i understand it, since it is an nvarchar column, SQL server will allocate enough space to store the value 'Mountain' (8 characters). Say this value is changed to 'Mountain Designs' so it is now 16 characters long. The second 8 characters wont be stored in the same physical place on the disk as SQL Server had to find space for the additional 8 characters when the update was done, so to return the value 'Mountain Designs' both locations on the drive would need to be read. What i want to do is 'defrag' the data so it is stored in the one physical location
  4. mmarovic Active Member

    Do you have clustered indexes on tables you want to defragment? Do you have indexes on nvarchar columns mentioned? If so, run dbcc showcontig to see how much data and indexes are fragmented. When you identify fragmented ones you can rebuild/defragment related indexes. Since data are stored in a leaf level of clustered index, defragmenting clustered index will defragment data in the table.
  5. FrankKalis Moderator

  6. satya Moderator

    As long as index defratmentation is managed the performance will not be affected and SQL Server will place the data as per its suitable methods to retrieve.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. benwilson New Member

    mmarovic, not all the nvarchars are included in the indexes

    Frank, we thought the data would need to be defraged to improve read time- if all the data in one field was in the same place surely it would be quicker to read from disk than if it is split? though from what Satya said, this isnt the case?

  8. mmarovic Active Member

    They are if you have clustered index on each table involved. Does it mean there is a table with nvarchar columns without clustered index ?
  9. benwilson New Member

    all the tables have clustered indexes- and i would say with a reasonable amount of certainty the clustered indexes are all on the primary keys. eg tbl_Applicant<br /><br /><pre id="code"><font face="courier" size="2" id="code">CREATE TABLE [tbl_Applicant] (<br />[TransactionId] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,<br />[ApplicantType] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,<br />[Title] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,<br />[FirstName] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,<br />[MiddleName] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,<br />[LastName] [nvarchar] (4<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE Latin1_General_CI_AS NULL ,<br />[DOB] [datetime] NULL ,<br />[Gender] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,<br />[MaritalStatus] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,<br />[Dependants] [int] NULL ,<br />[EmailAddress] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,<br />[ResidencyStatus] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,<br />[Nationality] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,<br />[NoOtherCommitments] [bit] NULL ,<br />[LastEnquiry] [datetime] NULL ,<br />[ContainerName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,<br />[UniqueId] [bigint] IDENTITY (1, 1) NOT NULL ,<br />CONSTRAINT [PK_tbl_Applicant] PRIMARY KEY CLUSTERED <br />(<br />[TransactionId],<br />[ApplicantType]<br />) WITH FILLFACTOR = 85 ON [PRIMARY] ...etc) ON [PRIMARY]<br />GO</font id="code"></pre id="code"><br />But the other nvarchars (name for instance) are not part of the clustered index...maybe i am misunderstanding how clustered indexes work??
  10. mmarovic Active Member

    Leaf level of clustered index is row itself. You are right that clustered index is ordered by index columns and also other levels (then leaf) contain only columns clustered index is defined on. However, for defragmentation purpose it is enough to have clustered index. Defragmenting clustered index means data pages will be defragmented. The only exception would be blog types (like text, ntext,...), but it is not your problem.
  11. benwilson New Member

    Thankyou! i think i follow now! Will just have to convince the boss defragging the indexes is enough...
  12. FrankKalis Moderator

    Let your boss read the MS whitepaper I've posted. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />And maybe he should explore the complete wisdom of the SQL Server Books Online [<img src='/community/emoticons/emotion-5.gif' alt=';)' />] <br />scnr.<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  13. benwilson New Member

    Thanks Frank...will see what i can do- unfortunately (as i have proved myself through my 101 posts (yay for me!)) just cause you read it in BOL doesnt mean you understand it!
  14. FrankKalis Moderator

    Yes, that's unfortunately true. Finding information there isn't intuitive either. But the big point for BOL is, that it is written by Microsoft and some people might take this more valid as some postings in an online community.

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  15. benwilson New Member

    very true! the good news is that i have explained to/convinced him that defragging the indexes is all we can do, and he is happy with that.<br /><br />Thanks again everyone...maybe microsoft needs a BOL for dummies <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  16. satya Moderator

    I agree that too and in order to get what you want the search tab on BOL browser is really helpful.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page