Text in Row

Question

Text in Row

fhanlon

SSChampion

Points: 13131
More actions
December 1, 2005 at 12:33 pm

#110952

I have a table that is not performing too well. It is defined as:
CREATE TABLE [dbo].[MDS_NoteText] (
    [MDS_JurisdictionID] [char] (13) COLLATE Latin1_General_BIN NOT NULL ,
    [MDS_FacilityID] [char] (13) COLLATE Latin1_General_BIN NOT NULL ,
    [MDSRESID] [char] (13) COLLATE Latin1_General_BIN NOT NULL ,
    [ParentType] [numeric](19, 5) NOT NULL ,
    [ParentSequenceNum] [numeric](19, 5) NOT NULL ,
    [ParentDate] [datetime] NOT NULL ,
    [ParentTime] [datetime] NOT NULL ,
    [MDS_Note_Index] [char] (5) COLLATE Latin1_General_BIN NOT NULL ,
    [SequenceNum] [numeric](19, 5) NOT NULL ,
    [DEX_ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,
    [MDS_NoteText] [text] COLLATE Latin1_General_BIN NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[MDS_NoteText] ADD
    CONSTRAINT [PKMDS_NoteText] PRIMARY KEY NONCLUSTERED
    (
        [MDS_JurisdictionID],
        [MDS_FacilityID],
        [MDSRESID],
        [ParentType],
        [ParentSequenceNum],
        [ParentDate],
        [ParentTime],
        [MDS_Note_Index],
        [SequenceNum]
    ) ON [PRIMARY] ,
     CHECK (datepart(hour,[ParentDate]) = 0 and datepart(minute,[ParentDate]) = 0 and datepart(second,[ParentDate]) = 0 and datepart(millisecond,[ParentDate]) = 0),
     CHECK (datepart(day,[ParentTime]) = 1 and datepart(month,[ParentTime]) = 1 and datepart(year,[ParentTime]) = 1900)
GO
The SELECT statement in the proc that is getting poor response uses all columns of the index resulting in an INDEX SEEK. I usually get good response executing the proc from QA. I still get fairly bad response from the application. The specific example I am using to test returns a lot of rows. I did a showcontig on this table and got:
DBCC SHOWCONTIG scanning 'MDS_NoteText' table...
Table: 'MDS_NoteText' (1939590048); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 17162
- Extents Scanned..............................: 2360
- Extent Switches..............................: 2359
- Avg. Pages per Extent........................: 7.3
- Scan Density [Best Count:Actual Count].......: 90.93% [2146:2360]
- Extent Scan Fragmentation ...................: 99.87%
- Avg. Bytes Free per Page.....................: 483.6
- Avg. Page Density (full).....................: 94.03%
According to some notes I have:
"Scan Density [Best Count: Actual Count]: One of the most useful of the percentages returned by DBCC SHOWCONTIG. This is the ratio between the Best Count of extents and the Actual Count of extents. This percentage should be as near to 100% as possible. Lower percentages indicate external fragmentation.
Logical Scan Fragmentation: Shows the ratio of pages that are out of order. This percentage should be between 0% and 10% with anything higher indicating external fragmentation.
Extent Scan Fragmentation: Shows any gaps between extents. This percentage should be 0% and higher percentages indicate external fragmentation. "
Our Extent Scan Fragmentation is very high. I checked the fragmentation of the MDSHC.mdf file. It is in 2 extents. That doesn't seem very fragmented. Any idea why our Extent Scan Fragmentation is high? Could this be contributing to poor response with notes? The notes are stored in column of type text. I understand that text type columns may not actually stored within the table. On a test version of this database I ran the following command EXEC sp_tableoption 'MDS_NoteText', 'text in row', '4000' and then updated each row by adding a blank character to force the tesxt to store in row. I was getting better response.  Is this coincidence or should Text in row work better?  How can I get better Extent Scan Fragmentation?
Francis

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

You really need a clustered index on the table otherwise you have a HEAP which over time will have "holes" where data is removed or updated with deferred updates. Rebuilding the indexes on a table where there is no clustered index will not remove your leaf level fragmentation in the table.

As to text data, generally if you can fit in page then use a varchar. That said there are sometimes advantages to keeping the text data off page. I'm sure Kalem Delany has this covered in Inside SQL books.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

fhanlon SSChampion Points: 13131 More actions · Answer 2

Thanks for your comments. I'll look into this.

Francis