This article was published by http://www.sqlservercentral.com on 22 May, 2014. It featured a script and extended events session that captures the call stack during index rebuilds. It identifies data movement between file groups resulting from partitioning tables switching out and merged data at the wrong boundary points, Debug Symbols were used as a diagnostic tool.
If the partitioning MERGE command is executed for the wrong range after a SWITCH OUT of historic data, data movement between file groups may be necessary before or during the next index rebuild. The SQLCMD lab setup script:
- Created a test database and 2 partitioned tables, one table is range right, the other range left. Each contains 6 partitions, the first and last partitions are empty. The 4 middle partitions in each table contain data in the ranges between 1 to 999, 1,000 to 1,999, 2,000 to 2,999, 3,000 to 3,999.
- Inserted 4,000 rows of test data
- Data in the lowest populated partition range (1 – 999) is switched out, partition 2 in each table
- The merge command is executed but at the wrong boundary for the RANGE RIGHT table (1001) causing a move of data between file groups.
- Stop the script before running the MERGE final INDEX REBUILD’s, download the SQL Server Debug symbols and create an extended events session to capture the call stack.
Immediately after the MERGE / before the final INDEX REBUILD, the system ‘Data Space’ views for the RANGE RIGHT table show where the data will be (FG2), not where it actually still is (FG3). The data seems to have moved between file groups instantly in fact but the operation has actually been deferred until the index rebuild, or before as a background task.
The lower file group (FG2) is retained by the MERGE command when the partition scheme is RANGE RIGHT. The data previously resided in File Group 3 so a move is required before or during the next index rebuild.
Extended Events and the Call Stack
An extended event session was started before the MERGE and final index rebuilds (RANGE RIGHT & RANGE LEFT) and the call stack analysed for various wait types including WRITE_COMPLETION and IO_COMPLETION. There were no noticeable, consistent bucketized call stack differences for any of the wait types captured. There were differences during ‘file-written’ events, both tables included the call stack below although it was invoked more for the RANGE RIGHT table and it’s associated data movement between file groups.
XeSqlPkg::file_written::Publish+1ba [ @ 0+0x0 FireWriteEvent+16c [ @ 0+0x0 FCB::AsyncWrite+1bc [ @ 0+0x0 SQLServerLogMgr::FlushLC+7b4 [ @ 0+0x0 SQLServerLogMgr::AppendLogRequest+1a2 [ @ 0+0x0 SQLServerLogMgr::ReserveAndAppend+74 [ @ 0+0x0 XdesRMReadWrite::GenerateLogRec+5ac [ @ 0+0x0 PageRef::FormatFull+1a3 [ @ 0+0x0 CBulkAllocator::FinishCurrentBuf+97 [ @ 0+0x0 CBulkAllocator::AllocateLinkedAndFormattedLeafPage+f2 [ @ 0+0x0 CIndBuild::AllocateNextIndexPage+15 [ @ 0+0x0 CIndBuild::InsertRow+bf2 [ @ 0+0x0 RowsetCreateIndex::InsertIndexRow+39e8 [ @ 0+0x0 CValRowCrtIdx::SetDataX+1d6 [ @ 0+0x0 0x000007FE070F6FE7 CQScanIndexNew::NonOptimizedGetAndInsertRows+f3 [ @ 0+0x0 CQScanIndexNew::GetRow+31 [ @ 0+0x0 CQScanNLJoinTrivialNew::GetRow+126 [ @ 0+0x0 CQueryScan::GetRow+81 [ @ 0+0x0 CXStmtQuery::ErsqExecuteQuery+36a [ @ 0+0x0 CXStmtDML::XretDMLExecute+2f3 [ @ 0+0x0 CXStmtDML::XretExecute+ad [ @ 0+0x0 CMsqlExecContext::ExecuteStmts [ @ 0+0x0 CMsqlExecContext::FExecute+a33 [ @ 0+0x0
The call stack below only appeared during INDEX REBUILD of the RANGE RIGHT table.
XeSqlPkg::file_written::Publish+1ba [ @ 0+0x0 FireWriteEvent+16c [ @ 0+0x0 FCB::AsyncWrite+1bc [ @ 0+0x0 SQLServerLogMgr::UpdateFileHdr+29b [ @ 0+0x0 SQLServerLogMgr::FlushLC+97f [ @ 0+0x0 SQLServerLogMgr::AppendLogRequest+1a2 [ @ 0+0x0 SQLServerLogMgr::ReserveAndAppend+74 [ @ 0+0x0 PageRef::ModifyBitsNonTransactional+3e4 [ @ 0+0x0 ChangeGhostPageState+116 [ @ 0+0x0 ChangeGhostPageState+129 [ @ 0+0x0 PageRef::MarkPfsGhostState+24 [ @ 0+0x0 IndexPageRef::MarkGhost+135e [ @ 0+0x0 BTreeRow::DeleteRecord+1713 [ @ 0+0x0 IndexDataSetSession::DeleteRow+296 [ @ 0+0x0 RowsetNewSS::DeleteRows+4e [ @ 0+0x0 CMEDScan::DeleteRow+82 [ @ 0+0x0 CMEDCatKatmaiIndex::DropRowset+2df [ @ 0+0x0 VisibleHoBt::DropHoBt+357 [ @ 0+0x0 SEDropRowsetInternal+68f [ @ 0+0x0 DDLAgent::SEDropRowsets+4b [ @ 0+0x0 CIndexDDL::DropRowset+72 [ @ 0+0x0 CIndexDDL::DropAllRowsets+ae [ @ 0+0x0 CIndexDDL::DropIndexEntryAndRowsets+1c0 [ @ 0+0x0 CIndexDDL::ConstructIndicesOffline+539 [ @ 0+0x0
It was hard work but the call stack did consistently show differences during this test. The approach wouldn’t be suitable for anything other than a development environment but viewing the call stack provided insights into the inner works of SQL Server than are not always available in the DMV’s.
Ideas for a test lab – http://www.brentozar.com/archive/2013/01/sql-server-table-partitioning-tutorial-videos-and-scripts/
Clarify range left/range right partitioning – http://www.sqlskills.com/blogs/kimberly/clarifying-left-and-right-in-the-defintion-of-a-partition-function-in-sql-server-2005/
Extended Events Session & Debug Symbols – http://www.sqlskills.com/blogs/paul/determine-causes-particular-wait-type/
Partitioning Query, modified to include scheme to qualify tables and an outer join to file groups so empty ones are included – http://davidpeterhansen.com/view-partitions-in-sql-server/