Table Partitioning – Extended Events & Debug Symbols

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.

Partition13

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

Conclusion
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.

References
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/

 



Categories: Partitioning

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: