Microsoft Innovation Center: Presentation Aug 29 2013

On August 29th, 2013, I have presented on topic "SQL Server: Performance Tuning using Waits and Queues Methodology" at Microsoft Innovation Center, Lahore (Pakistan). It is always hard to explain SQL Server internals, but with new idea of animated videos I have tried my best to make it simple and easy to learn. Soon, you will find these animated videos on blog, also.

SQL Server: Row Count for Specific Data Page

Recently found that for one of our production server, we have to use repair_allow_data_loss parameter to resolve page corruption. But question was how many rows we can loss for a specific corrupt page ???

Rows count on a specific page can be found if we get a page header print  by DBCC Page(). Obviously, before using DBCC Page() you must enable trace (3604).

DBCC TRACEON(3604)

GO

DBCC PAGE('DatabaseName',1,323008,0)

GO

In above case DatabaseName is my database name, 1 is data file id, 323008 is page number for which we need number of rows and 0 parameter value is for print header only. According to MSDN, we have 4 options for this parameter.

• 0 - print just the page header
• 1 - page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitmaps)
• 2 - page header plus whole page hex dump
• 3 - page header plus detailed per-row interpretation

In output, under Page Header section, m_slotCnt shows number of rows on this particular page. In my case it is 736.