Advanced SQL tutorial pdf, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, forwarded record, Forwarding Pointers, Free Download SQL SERVER Interview questions, Handling Large SQL Server Tables with Data Partitioning, Heaps have forwarding pointers, How to handle a large table on SQLServer - Server Fault, how to handle huge millons of data on sql server, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, sql server - Best way to index this very large table, SQL Server - General Interview Questions and Answers, sql server 2008 - How to handle huge table, sql server 2008 - SQL large table design, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL Server Forums - How to handle huge database tables, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions
Can we have page split for a HEAP? How can we track page splits?
The answer to this question is We cannot have page splits in heap.
The forwarding pointer is applicable to only heap having non-clustered indexes. SQL Server actually implemented this as a optimization technique to improve heap update performance.
Example – In heap table Customer, We are updating LastName from ‘K’ to ‘I am Pawan Kumar Khowal’.
Now assume that the modified row size has increased and cannot fit on the page it currently resides on. Here the record is moved to a new page, and a small forwarding record is left at the original location. The forwarding record points to the new location of the record, which is known as a forwarded record or forward pointer. This is performed as a performance optimization technique so that all the nonclustered indexes on the heap do not have to be altered with the new location of the heap record.
This improves the performance of INSERT and UPDATE operations; otherwise this will take considerable time for inserting and updating records on heap.
Query to track page splits for an index.
-- SELECT IOS.INDEX_ID, O.NAME AS OBJECT_NAME, I.NAME AS INDEX_NAME, IOS.LEAF_ALLOCATION_COUNT AS PAGE_SPLIT_FOR_INDEX, IOS.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(N'DB_NAME'),NULL,NULL,NULL) IOS JOIN SYS.INDEXES I ON IOS.INDEX_ID=I.INDEX_ID AND IOS.OBJECT_ID = I.OBJECT_ID JOIN SYS.OBJECTS O ON IOS.OBJECT_ID=O.OBJECT_ID WHERE O.TYPE_DESC='USER_TABLE' --
I hope you have enjoyed the article. Cheers, Thanks for reading !