SQL Server – Datatype – Text – InEfficiency

Background

In SQL Server, the Text datatype has been with us for a while.  In legacy applications it can cause hidden drags.

Lab

Data Model

Original Design

OriginalTable

Revised Design

RevisedTable

Explanation:

  1. In the original design, we have two two columns, notes and resolution that are defined as having the text column
  2. In the revised design, the two columns have been declared as varchar(8000)

Load Data

Load Data – Using Insert

Base Table

Here is our base table

CSPNotes

Query Plan

When we load 1.5 million records using simply insert statement, here is what our query plan looks like.

InsertData

Statistics IO

xfer

Explanation
  1. Query Cost
    • Somehow the table with the text data type column is @ 4%, while the one with varchar is @ 96%
  2. Statistics I/O
    • For the text data type column we have 18,782,300 logical reads.  And, the varchar column is @ 24,709,969 reads

Storage Utilization

Let us compare the storage allocated

Code:


exec sp_spaceused '[dbo].[CSRNotes_v1]'
exec sp_spaceused '[dbo].[CSRNotes_v2]'

Output

StorageUsed

Explanation

  1. The original design uses 381 MB for data and 262 MB for indexes
  2. The original design uses 245 MB for data and 374 MB for indexes

Fetch Data

Query Plan

QueryPlan

Statistics I/O

StatisticsIO

Explanation

  1. Query Plan
    • Comparatively, the original design is at 61% and the revised design is at 40%
    • On our original design the targeted table experienced a Table Scan that forced parallelism
    • On the other hand, we were able to use a covering index in our re-design
  2. Statistics I/O
    • Worktable Operations
      • In the original design, we have two worktable operations
      • In the revised design, we have a single worktable operation
    • For the targeted table
      • In the original design, we have 25 scan counts and 21647 logical reads
      • In the revised design, we have scan count and 7 logical reads

Metadata

Storage Allocation


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);



 

 

Additional Reading

Improving SQL Server Performance
https://msdn.microsoft.com/en-us/library/ff647793.aspx

Columns that use the text data type have extra overhead because they are stored separately on text/image pages rather than on data pages. Use the varchar type instead of text for superior performance for columns that contain less than 8,000 characters.

Summary

Our pain is due to the fact that text columns can not be included in index definitions.

In our re-design, we converted the text column to varchar and added it to one of our indexes.

The data size for our re-design is smaller and the cumulative index size is bigger.

But, our targeted query will be a bit more performant.

References

Microsoft Customer Service and Support (CSS) SQL Support – CSS SQL Escalation Services

 

Q/A

Metadata

Rebuild

 

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