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.

SQL Server: A Query Slow in SSMS, Fast in Application, WHY?

Today, a colleague asked me, why his simple select query is taking around 3000ms (3 Seconds) to execute while, same query is quite fast when executed from application.

Answer is simple: SQL Server Management Studio use RBAR-Row By Agonizing Row method to fetch rows and inform row by row to SQL Server that row is received while on other hand application which don’t use RBAR method, inform once after whole batch is received and reluctantly is fast as compared to SSMS or those applications which use RBAR method.

To confirm that query is running slow just because of RBAR factor, I have used extended events for single session waits analysis, a well defined method by Paul Randal. Output was as following: 

 NETWORK_IO is basically ASYNC_NETWORK_IO, when working with extended events. According to BOL “Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.”

But a more proper definition for this type of wait you can find on Karthik PK’s Blog. He stats that “When a query is fired, SQL Server produces the results ,place it in output buffer and send it to client/Application. Client/Application then fetch the result from the Output buffer, process data  and sends an acknowledgement to SQL Server. If client/Application takes long time to send acknowledgement then SQL Server waits on ASYNC_NETWORK_IO (SQL 2005/2008) or  Network_IO (SQL 2000) before it produces additional results.“

Hence proved that, our query delay was just because of NETWORK_IO wait (2870ms out of total 3000ms) and we were on the same machine where SQL Server was installed so no chances of any network problem and its only RBAR method of SQL Server Management Studio which was causing this delay.