{"id":321,"date":"2013-07-01T10:22:06","date_gmt":"2013-07-01T15:22:06","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=321"},"modified":"2014-03-07T10:29:49","modified_gmt":"2014-03-07T16:29:49","slug":"identify-and-correct-sql-server-forwarded-records","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/identify-and-correct-sql-server-forwarded-records\/","title":{"rendered":"Identify and Correct SQL Server Forwarded Records"},"content":{"rendered":"

Forwarded records in SQL Server can cause performance issues on heap tables because the record outgrows the page and the database engine uses pointers to reference the data. For those that don’t know what a heap is, it is a table without a clustered index. The best practice is to have a clustered index on every table, however, sometimes there are cases when a clustered index is not needed. In this case, DBA’s should be aware of all heap tables and should be concerned about forwarded records causing poor performance.\u00a0 In this post, I’ll discuss forwarded records and how fix them.<\/p>\n

Forwarded records<\/b> are records in a SQL Server table that have grown too large for the page that it currently resides on.\u00a0 These types of records can only be found in heaps because tables with a clustered index keep the data sorted based on the clustered index.\u00a0 With a heap, once the record outgrows the page, the record is moved to a new page and a forwarding pointer<\/b> is left in the original location to point to the new location.<\/p>\n

Let’s look at an example of a forwarding record.<\/p>\n

First, let’s create a table and insert some records:<\/p>\n

\n
CREATE TABLE [dbo].[Demo](\r\n [ID] [int] IDENTITY(1,1),\r\n [Server] [nvarchar](50) NULL,\r\n [DatabaseName] [nvarchar](100) NULL,\r\n [timestamp] [datetime] default getdate())\r\nGO \r\n    CREATE INDEX idx_Server ON Demo(Server)\r\nGO\r\n    INSERT INTO Demo\r\n    (Server,DatabaseName)\r\n    VALUES  ('Server1', 'DB1') \r\n    INSERT INTO Demo\r\n    (Server,DatabaseName)\r\n    VALUES  ('Server2', 'DB2')\r\n    INSERT INTO Demo\r\n    (Server,DatabaseName)\r\n    VALUES  ('Server3', 'DB3')\r\nGO 100<\/pre>\n<\/div>\n
\"SQL
\n<\/a>Next, let’s use the following DMV query to check our table:<\/p>\n
\n
SELECT\r\n    OBJECT_NAME(ps.object_id) as TableName,\r\n    i.name as IndexName,\r\n    ps.index_type_desc,\r\n    ps.page_count,\r\n    ps.avg_fragmentation_in_percent,\r\n    ps.forwarded_record_count\r\nFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps\r\nINNER JOIN sys.indexes AS i\r\n    ON ps.OBJECT_ID = i.OBJECT_ID  \r\n    AND ps.index_id = i.index_id\r\nWHERE OBJECT_NAME(ps.object_id) = 'Demo'<\/pre>\n<\/div>\n
\"SQL<\/a><\/p>\n
Notice that the HEAP index type has 0 forwarded records.<\/p>\n
Next we will change the table definition by modifying the DatabaseName<\/i> column.<\/p>\n
\n
ALTER TABLE Demo ALTER COLUMN [DatabaseName] nvarchar(MAX)<\/pre>\n<\/div>\n
If we run the DMV query again you will see that the forwarded_record_count<\/i> has changed (along with a lot of fragmentation):<\/p>\n
\"SQL<\/a><\/p>\n
The page count increased to 3 on the HEAP indicating that a new page was created and 50 forwarded records were created.<\/p>\n
In a real world situation you will probably not know exactly when this happens so by changing the WHERE clause in the DMV query you can find all the forwarded records in a database. I would monitor heap tables at least monthly and if you notice high I\/O on a heap, then that should be a hint that something needs to be looked at and possibly modified.<\/p>\n
\n
SELECT\r\n    OBJECT_NAME(ps.object_id) as TableName,\r\n    i.name as IndexName,\r\n    ps.index_type_desc,\r\n    ps.page_count,\r\n    ps.avg_fragmentation_in_percent,\r\n    ps.forwarded_record_count\r\nFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps\r\nINNER JOIN sys.indexes AS i\r\n    ON ps.OBJECT_ID = i.OBJECT_ID  \r\n    AND ps.index_id = i.index_id\r\nWHERE forwarded_record_count > 0<\/pre>\n<\/div>\n
On my Test DB, you will notice I have two HEAP tables that have forwarded records including the one I just created.<\/p>\n
\"SQL<\/a>
\nClick here to view the rest of this post.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Forwarded records in SQL Server can cause performance issues on heap tables because the record outgrows the page and the database engine uses pointers to reference the data. For those that don’t know what a heap is, it is a table without a clustered index. The best practice is to have a clustered index on […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[53],"tags":[54,74,75,57],"class_list":["post-321","post","type-post","status-publish","format-standard","hentry","category-database-design","tag-design","tag-forwarded-records","tag-heap","tag-tuning"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/321","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=321"}],"version-history":[{"count":1,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/321\/revisions"}],"predecessor-version":[{"id":326,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/321\/revisions\/326"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=321"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=321"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=321"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}