Get size of SqlDataReader payload

Question

I am trying to figure out the size of the payload that gets returned from SQL Server. The code I have is using ADO.NET:

using (var command = new SqlCommand("SELECT * FROM [dbo].[Products]", connection))
{
  SqlDataReader reader = command.ExecuteReader(); 
  // find # of bytes of payload returned

However, I am unsure how to get the size of what got returned from SQL server. Is there a way using the SqlDataReader I can do that?

In many cases you can't -- because the reader will stream data as it's read. — Bryan B
– Bryan B, Commented Mar 19, 2014 at 20:56
I have never tried this. I would use count(*) instead of select * from products instead. Then, estimate the memory based on the data types of the columns ? — Erran Morad
– Erran Morad, Commented Mar 19, 2014 at 20:56

Erran Morad · Accepted Answer · 2014-03-20 23:59:27Z

1

Try the query below. Replace the shop by your table name and id by your column name.

DECLARE @maxRowSize int
SET @maxRowSize =
(
SELECT SUM(DataSize) AS MaxRowSize
FROM
(
SELECT COLUMN_NAME, DATALENGTH(COLUMN_NAME) as DataSize
from information_schema.columns 
where table_name = 'shop' -- enter table name only, do NOT enter the schema
) AS SCHEM
)
SELECT COUNT(Id) as NumOfRows, --optional
@maxRowSize as MaxRowBytes, --optional
COUNT(id) * @maxRowSize as MaxQueryBytes
FROM Shop -- Enter schema name if needed

edited Mar 20, 2014 at 23:59

answered Mar 19, 2014 at 22:08

Erran Morad

4,78312 gold badges45 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erran Morad Over a year ago

Another query to get the table info -

SELECT column_name as 'Column Name',  data_type as 'Data Type', character_maximum_length as 'Max Length' FROM information_schema.columns WHERE table_name = 'shop'

Erran Morad Over a year ago

I created a related question here - stackoverflow.com/questions/22518384/…

Collectives™ on Stack Overflow

Get size of SqlDataReader payload

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related