What are forwarded records?

Forwarded records are records that reside on a new page when a heap table (table without clustered index) is updated, and the rows are no longer able to stay on the same page,

which causes a forwarding pointer to point to the new page that the row resides in.

Many forwarded records can cause serious I/O issues, and if this happens a lot, it is advised to create a clustered index on the table.

You can also rebuild the table to remove the forwarded records (high overhead).

 

 

sys.dm_db_index_physical_stats

sys.dm_db_index_physical_stats is the dmv used to find the forwarded records,

but this is a very I/O intensive dmv to query, which is why we will apply filtering in the script.

 

 

The script:

We will use a temp table to apply filtering, and only include the heap tables in our dmv checks.

 

SELECT O.*
INTO #temp
FROM   sys.objects O
inner join sys.tables AS T on o.object_id = T.object_id
INNER JOIN sys.schemas AS S 
ON T.schema_id = S.schema_id 
INNER JOIN sys.indexes AS I 
ON T.object_id = I.object_id 
AND I.type = 0;


SELECT  DB_NAME(db_id()) as DatabaseName,
		t.name as TableName,
		index_id, 
		index_type_desc,
		avg_fragmentation_in_percent,
		page_count,
		record_count, 
		forwarded_record_count 
FROM
#temp t
CROSS APPLY 
sys.dm_db_index_physical_stats
(
   DB_ID()
   ,t.object_id
   ,NULL
   ,NULL
   ,'DETAILED'
)
WHERE forwarded_record_count > 0 and t.is_ms_shipped =0

DROP TABLE #temp

 

 

I hope this script helped you achieve your goal, with the least amount of overhead.

 

 

Intro

For a customer, I recently migrated one of their databases from 2012 to 2017, whilst putting compression on all the indexes.

The process of recreating the Indexes on 8 new filegroups took ~3hours for a 400GB database.

Specs

From SQL Server 2012 (11.0.7001)

To SQL Server 2017 CU 1 (14.0.1000)

Windows Server 2012 R2

 

Query execution

When manually executing the queries, life was bliss.

When putting the queries needed to complete the task in a job with a few steps for automation purposes, the job completed since there was try – catch logic present.

When I checked the errors captured I was greeted with this message, which was not present when I manually ran the queries:

“CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.”

 

What is the QUOTED_IDENTIFIER setting?

Setting this on means that characters enclosed in double quotes, “ “ are treated as identifiers instead of literals. The default for this is ON, and this is a general better practice.

From Microsoft docs:

“When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers…”

“When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. “

“SET QUOTED_IDENTIFIER must be ON when you are creating a filtered index.” https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql

Since I had filtered indexes, QUOTED_IDENTIFIER needed to be ON at all times when executing the query’s.

 

Testing

If I execute the following statement under my account (sysadmin) on the database to be compressed

DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF';

IF ( (256 & @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON';

SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;
QUOTED_IDENTIFIER
ON

 

When I put the query to check the quoted identifier in a job and run it, (in the user database to be compressed) this happens:

 

create table dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER varchar(3)) 
DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF'; IF ( (256 & @@OPTIONS) = 256 ) 
SET @QUOTED_IDENTIFIER = 'ON'; SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER; 
insert into dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER) select @QUOTED_IDENTIFIER

 


select * from dbo.QUOTEDIDENTIFIER_CHECK;


QUOTED_IDENTIFIER
OFF

 

A simple approach to resolve this was to set it to ON.

 

QUOTED_IDENTIFIER
ON

 

And that is what I did, added SET QUOTED_IDENTIFIER ON; to the executed statements.

But, I also wanted to know what causes this.

 

More testing

When tested with the job on a different server :11.0.7001.0

QUOTED_IDENTIFIER
OFF

On yet another server: 14.0.3015.40

QUOTED_IDENTIFIER
OFF

 

After playing around with the connections at server and database level, I found what caused this.

 

The Culprit

 

When checking the profiler when executing the job, I found that it gets explicitly set to off before the step execution. Under the SQLSERVERAGENT service account (also not best practice but it illustrates the point better).

Closer look:

Executed by the agent:

Seems that this is default behavior for the Agent service. For now, I have not found a better way than explicitly setting quoted identifier on .

The reason behind this behavior, is something that I am still looking for, since it is not documented in the Microsoft docs on quoted identifier.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql