SQL Server – ROW_OVERFLOW_DATA ?

Background

Chasing down a hard to find error.  The SQL Server Engine continues to use an unhelpful index.

Unfortunately, the system is based on SQL Server v2005 and so unable to use filtered index.

Review Table’s Storage Allocation

Let us look at the index sizes and see whether it is choosing the awful index simply because it is much smaller.


declare @object sysname

set @object = 'dbo.CSRNotes'

select 
		  [schema] = tblS.[name]
		, [object] = tblO.name
		, [index] = tblI.name
		, [indexID] = tblI.index_id
		, tblAU.type_desc
		, [fileGroup] = tblFG.name
		, [totalPages] = tblAU.total_pages
		, [usedPages] = tblAU.used_pages

from   sys.system_internals_allocation_units tblAU

	join sys.system_internals_partitions tblIP

		on tblAU.container_id = tblIP.partition_id

	Join sys.partitions As tblP

		On  tblIP.object_id = tblP.object_id
		And tblIP.index_id = tblP.index_id

	inner join sys.indexes As tblI

		On  tblP.object_id = tblI.object_id
		And tblP.index_id = tblI.index_id

	inner join sys.objects As tblO

	        On  tblP.object_id = tblO.object_id

	inner join sys.schemas As tblS

		On  tblO.schema_id = tblS.schema_id

	inner join sys.filegroups as tblFG

		On  tblAU.filegroup_id = tblFG.data_space_id

where tblP.object_id = object_id(@object)

order by 
	  tblI.index_id


Output:

RowOverFlowData

Output:

  1. It is not more narrower, but also noticed an index bearing ROW_OVERFLOW_DATA moniker.
  2. ROW_OVERFLOW_DATA ?
    • It is worth noting that there no pages allocated nor used by the ROW_OVERFLOW_DATA item

Investigate Data Length

Let us investigate our actual record column’s data length and see whether we really need the over-sized column definition.

Query Data – Ordering  by Length

Column – Notes


select top 10 
		  [notes_length] = datalength(notes)
		, [resolution_length] = datalength([resolution])
from   [dbo].[CSRNotes]
order by isNull(datalength(notes), 0) desc


Column – Resolution


select top 10 
		  [notes_length] = datalength(notes)
		, [resolution_length] = datalength([resolution])
from   [dbo].[CSRNotes]
order by isNull(datalength(resolution), 0) desc

Output:

Notes
ColumnLength

Resolution

ColumnLength-Resolution

Output:
  1. As our biggest column is less than 4000, we can reduce the column’s length from varchar(8000)  to varchar(4000).
  2. For Column 2, Resolution, our largest row is less than 1000, and we can change our definition from 4000 to 2000.

Change Tables Column Max Data length

Using Alter Table \ Alter Column, change table’s column data length


alter table [dbo].[CSRNotes]
	alter column [notes] varchar(4000) null

alter table [dbo].[CSRNotes]
	alter column [resolution] varchar(2000) null

Harden Changes

Harden changes by re-writing data


 update [dbo].[CSRNotes]
 set [notes] = [notes]
 ;

 update [dbo].[CSRNotes]
 set [resolution] = [resolution]
 ;

Rebuild Clustered Index


 ALTER INDEX [PK_CSRNotes]
 ON [dbo].[CSRNotes]
 REBUILD 


Revisit Table’s Storage Allocation

RowOverFlowData.20151029.0712PM

Interpretation:

We no longer have the the Row OverFlowData record.

 

Review Table’s Fragmentation

Another area we took a quick look at is the fragmentation level of the table and indexes.

Here is the code:

Code


declare @object    sysname
declare @objectID  int

set @object = 'dbo.CSRNotes'
set @objectID = object_id(@object)

SELECT 
	  [schema]
		= object_schema_name(ctePS.object_id)

	, [object]
		=object_name(ctePS.object_id)

	, [indexName]
		= tblSI.name

	, [indexID]
		= tblSI.index_id

	, [indexType]
		= tblSI.type_desc

	, [pageCount]
		= ctePS.[page_count]

	, [storageInMB]
		= cast
		   (
		     (ctePS.[page_count] *1.0/128) 
			  as decimal(10,2)
		   )

	, [fragmentation%]
		= cast
		    (
			ctePS.avg_fragmentation_in_percent
		 	   as decimal(10,2)
		   )


FROM   sys.dm_db_index_physical_stats 
       (
	    db_id()
	   , @objectID
	   , NULL
	   , NULL 
	   , 'LIMITED'
    	) ctePS

	inner join sys.indexes tblSI

	    on ctePS.[object_id] = tblSI.object_id
	    and ctePS.index_id = tblSI.index_id

where index_level = 0


 

Output

fragmentation.20151030.0857AM

Interpretation

We can see that there is a bit of fragmentation that we should likely address.

Summary

The change did not help us achieve the better index, and so our problem is likely a bug with the SQL Server Engine’s Optimizer.

References

  1. Looking into SQL Server page types
    http://www.greekdotnetbloggers.com/Posts/tabid/64/ID/1856/Looking-into-SQL-Server-page-types.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s