1

Summary

I have two vanilla SQL Server instances running on identical (Win2012) VMs, one is SQL Server 2008 R2, the other is SQL Server 2012. I run the same DDL script on each to create a single DB table with a full-text index. I run the same SQL script that adds 10bytes at a time to the indexed column of a single row of the table until it reaches 1MB. On 2008R2 this takes about 45s with ~2.8M logical reads, while on 2012 it takes about 2mins with ~125M logical reads. Later versions of SQL Server show similar results to 2012. I'd love to know why this degradation is happening, and if there is some configuration that can be used on 2012 to achieve 2008 R2 performance.

To reproduce

  1. In SQL Server Management Studio, create a new DB with default options.
  2. Run the following script to create the table and index,
CREATE SCHEMA [core]
GO

CREATE TABLE [core].[document](
                [id] [bigint] IDENTITY(1,1) NOT NULL,
                [extension] [nvarchar](255) NOT NULL,
                [data] [varbinary](max) NULL,
CONSTRAINT [pk_document] PRIMARY KEY CLUSTERED 
(
                [id] ASC
) ON [PRIMARY]
)
GO

create fulltext catalog ftc_core_document;
GO

create fulltext index on core.document(data type column extension)
  key index pk_document
  on ftc_core_document
  with change_tracking auto;
GO
  1. Run the following SQL to insert a row and update its data column 10bytes at a time until it reaches 1MB,
INSERT INTO [core].[document]
           ([extension]
           ,[data])
     VALUES
           ('txt'
           ,0)
GO

DECLARE @total_size int;
set @total_size = 0;

while (@total_size < (1 * 1024 * 1024))
  begin
                UPDATE core.document set data .write(CONVERT(varbinary, '1234567890'), NULL, NULL) where id = 1
                SELECT @total_size = DATALENGTH(data) from core.document where id = 1
  end

Results

Using SQL Server Management Studio's Standard Report, 'Performance - Top Queries by Total CPU Time' I see the following results,

For SQL Server 2008 R2 (total time 44s),

enter image description here

For SQL Server 2012 (total time 1m:55s),

enter image description here

Note

  1. Turning off full text indexing on core.document on 2012 results in time dropping to 41s and logical reads dropping to 0.7M
  2. It appears to be full text indexing's change tracking that causes the performance issue. Running with change tracking off and using incremental population of the full-text index resolves the issue.
  3. The estimate/actual execution plan is the same across both 2008 R2 and 2012
5
  • Is your SQL Server 2012 fully patched? At least service pack 4? Oct 28 '21 at 12:18
  • It happens because of change tracking, as you pointed out in Note 2 and the data access pattern you're using. The default of AUTO is not what you want, so turn it off and update the index manually. Oct 28 '21 at 21:15
  • @EricPrévost Thanks for your reply Eric, yes SQL 2012 is patched ... but not only that, I see similar results with SQL 2016, 2017 and 2019.
    – smillied
    Oct 29 '21 at 2:23
  • @RandolphWest Thanks Randolph, have you any thoughts on why 2008R2 performs so much better though? Does change tracking work differently since 2012?
    – smillied
    Oct 29 '21 at 2:27
  • A huge number of things changed in 2012, including memory management, core licensing, and yes, things like change tracking. Oct 29 '21 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.