Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.
Give this article a Google +1!
Issue 7 Number 4 April 2015
Forwarded records often cause query performance that is hard to understand and thus harder to fix. The indexing may be correct. There may be no problem with memory, disk or CPU. the query may be correctly written. It seems to you that this query should be lightning fast, but it is plodding along for about 10 seconds before completing.
forwarded records could be your problem.
Forwarded records only occur on heap tables (tables that have no clustered index). They occur when a row in the table exceeds the maximum size allowed for rows in SQL Server (8060 bytes). This occurs when varchar columns in the table are updated with a larger value. The update may make the total size of the row data larger than is permitted. When that happens, the data is moved to a new page and a pointer is left in its place.
When the heap table is scanned, following these pointers slows the operation down significantly. It is similar to index page splitting causing index fragmentation and query slowness.
Execute this query in the database in question:
This will show you all the tables in the database that have any forwarded records, the number of forwarded records and the size of the table.
Easy, create a clustered index on the affected tables. If you are using SQL 2008r2 or later you could also use the ALTER TABLE...REBUILD command to defrag the forwarded records but I don't know why anyone would do that. The clustered index is a permanent fix. the ALTER command would have to be repeated periodically.
If you found this newsletter useful, please tweet it to friends.
Call 503.914.6442Tweet This Article.
I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.
As always, if you have questions or comments give me a call at 503.914.6442 or send me email.
Kurt Survance, SQL Consulting, Inc.
On this page:
Elsewhere on this site: